Mastering MySQL User Management and Privilege Control

Users and Privileges

The root user is the administrator and possesses all privileges in MySQL. It is not recommended to grant all users full server access; maintaining data and structural integrity requires restricting specific tasks to authorized users only.

Users and privileges are intimately related. You can create users without immediately assigning privileges. MySQL allows you to define distinct users and assign specific permissions at various levels.

Privilege Levels

• Global: Applies to all databases on a server. This is the highest and most general level of privilege.
• Database: Applies to individual databases and, by extension, all contents within them.
• Table: Applies to individual tables and all columns within those tables.
• Column: Applies to a specific column within a table.
• Routine: Applies to stored procedures, which may consist of multiple SQL queries.

Revoke Privileges

Use the REVOKE statement to remove previously granted permissions.

Create User

While earlier versions relied solely on the GRANT statement, modern MySQL uses the CREATE USER command. After creating a user, you must use a GRANT statement to assign privileges.

You can create a user and grant privileges simultaneously using GRANT. To create an unprivileged user, use the following command:

mysql> GRANT USAGE ON *.* TO 'anonymous' IDENTIFIED BY 'password';

Granting Privileges

To perform actions beyond consulting system variables, a user must be granted specific privileges. For example, to grant a user the ability to select data from a specific table, use the GRANT statement. To grant the user 'anonymous' the privilege to execute SELECT statements on the 'people' table in the 'test' database:

mysql> USE test;
mysql> GRANT SELECT ON test.people TO 'anonymous';

To grant global privileges, use ON *.* to indicate that permissions apply to all tables in all databases. To grant privileges on a specific database, use ON dbname.*.

Common Privileges

• ALL: Grants all available privileges.
• CREATE: Allows the creation of new tables.
• DELETE: Allows the use of the DELETE statement.
• DROP: Allows the deletion of tables.
• INSERT: Allows the insertion of data into tables.
• UPDATE: Allows the use of the UPDATE statement.