How to manage user privileges?

To manage user privileges in MySQL, you can use the GRANT, REVOKE, and SHOW GRANTS commands. Here’s a brief overview of how to use these commands:

1. Granting Privileges

To grant privileges to a user, use the GRANT command. For example, to grant all privileges on a database named mydatabase to a user username:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'host';

Replace mydatabase with the name of your database.
Replace username with the user's name.
Replace host with the host from which the user connects (e.g., localhost).

2. Revoking Privileges

To revoke privileges from a user, use the REVOKE command. For example, to revoke all privileges on mydatabase from username:

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'username'@'host';

3. Viewing User Privileges

To see the privileges granted to a user, use the SHOW GRANTS command:

SHOW GRANTS FOR 'username'@'host';

4. Applying Changes

After making changes to user privileges, it’s a good practice to run:

FLUSH PRIVILEGES;

This command reloads the grant tables to ensure that the changes take effect immediately.

Example

Here’s a complete example:

-- Grant SELECT and INSERT privileges
GRANT SELECT, INSERT ON mydatabase.* TO 'username'@'localhost';

-- Revoke INSERT privilege
REVOKE INSERT ON mydatabase.* FROM 'username'@'localhost';

-- Show grants for the user
SHOW GRANTS FOR 'username'@'localhost';

These commands help you effectively manage user privileges in MySQL.