mysql user management, privileges, routines and triggers
MySQL User Management
User Account Management
User Account: In MySQL, a user account is a combination of a username and host from which a user can connect to the MySQL server.
Creating a User Account: To create a user account, use the CREATE USER statement, specifying the username and host. For example:
sql
code
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
Renaming a User Account: You can rename a user account using the RENAME USER statement. For example:
sql
code
RENAME USER 'old_username'@'hostname' TO 'new_username'@'hostname';
Dropping a User Account: Use the DROP USER statement to remove a user account. For example:
sql
code
DROP USER 'username'@'hostname';
User Privileges
User Privileges: Privileges define what actions users are allowed to perform on the MySQL server and its databases.
Granting Privileges: Use the GRANT statement to grant specific privileges to a user on a database or table. For example, to grant all privileges on a database:
sql
code
GRANT ALL PRIVILEGES ON database.* TO 'username'@'hostname';
Revoking Privileges: Use the REVOKE statement to revoke previously granted privileges. For example, to revoke all privileges:
sql
code
REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'hostname';
Administrative Privileges: Administrative privileges, such as CREATE USER, DROP USER, and GRANT, allow users to manage other users and their privileges.
Database Access Privileges
Database Access Privileges: These privileges control what a user can do within a specific database or table.
SELECT: Allows reading data from a table.
INSERT: Allows adding data to a table.
UPDATE: Allows modifying existing data in a table.
DELETE: Allows removing data from a table.
MySQL Routines and Triggers
Routines
Routines: Routines in MySQL are stored procedures or functions that can be executed on the database server.
Stored Procedures: A stored procedure is a named set of SQL statements that can be executed with a single command.
Stored Functions: A stored function returns a single value and can be used within SQL statements.
Creating a Routine: You can create routines using CREATE PROCEDURE or CREATE FUNCTION statements.
Executing Routines: Routines are executed using the CALL statement.
Triggers
Triggers: Triggers are actions that are automatically executed in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table.
Types of Triggers: There are BEFORE and AFTER triggers that can be defined to run either before or after the triggering event.
Creating Triggers: Use the CREATE TRIGGER statement to define triggers.
Trigger Events: Triggers can be set to activate on specific events on a table, e.g., before an INSERT or after an UPDATE.