User Management in MySQL

Managing users and their privileges is an essential aspect of MySQL administration. This involves creating, modifying, and removing users, as well as managing their access rights to databases and tables.

1. Creating Users

You can create a new user in MySQL using the CREATE USER command. A user account in MySQL is identified by a combination of the username and host (where the user can connect from).

Syntax:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Example:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'john_password';

This creates a user named john who can connect to the MySQL server from the local machine (localhost) using the password john_password.

2. Granting Permissions

Once a user is created, you need to assign permissions that control what the user can do in MySQL. permissions determine what actions a user can perform, such as selecting data, creating tables, or managing the entire database.

Syntax:

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

Example:

To give john permission to perform all actions on a specific database (employees):

GRANT ALL PRIVILEGES ON employees.* TO 'john'@'localhost';

To give only read (SELECT) access:

GRANT SELECT ON employees.* TO 'john'@'localhost';

After granting privileges, it’s good practice to reload the privileges with:

FLUSH PRIVILEGES;

3. Viewing User Rights

To view what rightsa user has been granted, you can use the SHOW GRANTS command.

Syntax:

SHOW GRANTS FOR 'username'@'host';

Example:

SHOW GRANTS FOR 'john'@'localhost';

Result:

GRANT ALL PRIVILEGES ON `employees`.* TO 'john'@'localhost'

4. Modifying User Passwords

You can change a user's password using the ALTER USER or SET PASSWORD commands.

Syntax:

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

Example:

ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_secure_password';

5. Deleting Users

To remove a user from MySQL, you can use the DROP USER command.

Syntax:

DROP USER 'username'@'host';

Example:

DROP USER 'john'@'localhost';

This will delete the john user and revoke all their privileges.

Common Privileges