How to Grant Privileges for a User in MySQL in your VPS server?

• 15th Jun 2024
• by: Danny Foo
• note: 0 Comments

Step 1: Open the SSH terminal with Bitvise SSH client into your VPS server as root.

Step 2: Log into MySQL

Step 3 : After the user is created, make sure that the permissions are granted before logging in. Those permissions include:

• Create: User can create databases.
• Select: User can search the database for needed information.
• Update: User can update table rows.
• Insert: User can insert data into tables.
• Delete: User can delete rows and columns.
• Drop: User can delete whole databases.
• Grant Option: User is able to manage privileges for other users.
• All privileges: User has full access to the database.

Step 4: To create a user with all privileges to a single database

To create a user with all privileges to a single database, use this command.
GRANT ALL PRIVILEGES ON database_name.* TO 'testuser'@'localhost';

Step 5 : To Show Privileges for a User in MySQL

Use the following statement to check the privileges for a specific user:
SHOW GRANTS FOR @;

Example:

Conclusion

You now know how to use the command line to create database user and to grant privileges to that user.