How to Grant Privileges in MySQL Database Server

To grant privileges in a MySQL database server, you need to use the GRANT statement. This statement allows you to grant specific permissions to a user, such as creating or modifying tables, inserting or updating data, and more. In this guide, we’ll show you how to grant privileges in a MySQL database server.

Step 1: Log in to MySQL Server

Log in to the MySQL server using a user account with administrative privileges, typically the “root” user. Open a terminal and enter the following command:

mysql -u root -p

Enter the root password when prompted. You should now be logged in to the MySQL server.

[root@server ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 214
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Step 2: Grant Privileges

To grant privileges, use the GRANT statement with the appropriate privileges, user, and database. The general syntax is:

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

Here are some examples of granting privileges:

Grant all privileges to a user for a specific database:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

This command grants all privileges to the specified user for the specified database on the local server.

Grant specific privileges to a user for a specific database:

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

This command grants the specified user SELECT, INSERT, and UPDATE privileges for the specified database on the local server.

Grant all privileges to a user for all databases:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

This command grants all privileges to the specified user for all databases on the local server.

Note: Replace database_name, table_name, username, and host with the appropriate values for your setup.

For example:

mysql> GRANT ALL PRIVILEGES ON wp1db.* to wp1user@localhost ;

Step 3: Apply Changes

After granting the desired privileges, you need to apply the changes by running the following command:

FLUSH PRIVILEGES;

Step 4: Verify Changes

mysql> SHOW GRANTS FOR 'wp1user'@'localhost';
+---------------------------------------------------------------------------------------+
| Grants for wp1user@localhost                                                          |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wp1user'@'localhost' IDENTIFIED BY PASSWORD '67ca6cf16fbdbef2' |
| GRANT ALL PRIVILEGES ON `wp1db`.* TO 'wp1user'@'localhost'                            |
+---------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

This command ensures that the MySQL server reloads the privilege settings and applies the changes immediately.

Commands Mentioned:

• mysql -u root -p – Log in to the MySQL server with the specified username and password prompt.
• GRANT – Grant specific privileges to a user for a database, table, or the entire server.
• FLUSH PRIVILEGES – Reload the privilege settings on the MySQL server and apply the changes made using the GRANT command.

Conclusion

You have now learned how to grant privileges in a MySQL database server. Make sure to grant only the necessary privileges to each user to maintain a secure and well-organized database environment.

If you have any questions, comments, or suggestions for improvement, please feel free to share your thoughts in the comments section below. Your feedback is invaluable to us, and it helps us create better and more informative content for our users.