Grant Specific Database Privileges to MySQL User

Securing a database environment requires granting users only the minimum necessary permissions they need to perform their tasks. This article provides a direct, step-by-step guide on how to grant specific database privileges to a MySQL user, from connecting to the server to applying and verifying the permissions.

Step 1: Log in to the MySQL Server

To manage privileges, you must log in to the MySQL server as a user with administrative rights (such as root). Open your terminal or command line and run the following command:

mysql -u root -p

Enter your administrative password when prompted.

Step 2: Understand the GRANT Syntax

MySQL uses the GRANT statement to assign permissions. The basic syntax for granting specific privileges is as follows:

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

• privilege_type: The specific action allowed (e.g., SELECT, INSERT, UPDATE, DELETE, CREATE, DROP).
• database_name.table_name: The database and table the privileges apply to. You can use an asterisk (*) as a wildcard to apply privileges to all databases or all tables (e.g., db_name.*).
• ‘username’@‘host’: The target MySQL account and the host from which they can connect (e.g., 'john'@'localhost' or 'john'@'%' for any host).

Step 3: Execute the GRANT Statement

Depending on your security requirements, choose one of the following scenarios to grant privileges.

Scenario A: Granting Read-Only Access

To allow a user to only read data from all tables within a specific database named sales_db:

GRANT SELECT ON sales_db.* TO 'reporter'@'localhost';

Scenario B: Granting Data Manipulation Access

To allow a user to read, insert, update, and delete data within a specific database:

GRANT SELECT, INSERT, UPDATE, DELETE ON sales_db.* TO 'editor'@'localhost';

Scenario C: Granting All Privileges on a Single Database

To grant full administrative control over a single database without granting global server privileges:

GRANT ALL PRIVILEGES ON sales_db.* TO 'db_admin'@'localhost';

Step 4: Apply the Changes

After executing the GRANT statements, you must reload the grant tables to ensure the new permissions take effect immediately:

FLUSH PRIVILEGES;

Step 5: Verify the Privileges

To confirm that the privileges were successfully assigned, run the SHOW GRANTS command for the specific user:

SHOW GRANTS FOR 'editor'@'localhost';

This command will output a list of all active permission rules associated with that user account.