How to Grant Permissions to a MySQL User on Linux

The GRANT statement grants privileges to MySQL user accounts.

To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. GRANT additionally requires the SUPER privilege.

Contents

Step 1: Login to MySQL:

To begin, sign into MySQL with the following command:

[user@localhost $] mysql -u root -p
Enter password:

1 2	[user@localhost $] mysql -u root -p Enter password:

Enter the administrator password you set up during installation.

Step 2: Grant Permissions to MySQL User :

Here is a short list of commonly used permissions :

ALL : Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.
CREATE : Allow a user to create databases and tables.
DELETE : Allow a user to delete rows from a table.
DROP : Allow a user to drop databases and tables.
EXECUTE : Allow a user to execute stored routines.
GRANT OPTION : Allow a user to grant or remove another user’s privileges.
INSERT : Allow a user to insert rows from a table.
SELECT : Allow a user to select data from a database.
SHOW DATABASES : Allow a user to view a list of all databases.
SHOW VIEW : Enable use of SHOW CREATE VIEW. Levels: Global, database, table.
UPDATE : Allow a user to update rows in a table.
CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global.
CREATE VIEW : Enable views to be created or altered. Levels: Global, database, table.

Example 1: To grant TestUser the ability to drop tables in the specific database tutorials_space_db , use the DROP permission:

mysql>
mysql> GRANT DROP ON tutorials_space_db.* TO 'TestUser'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

mysql>

mysql> GRANT DROP ON tutorials_space_db.* TO 'TestUser'@'localhost' ;

Query OK, 0 rows affected (0.00 sec)

mysql>

Example 2: To grant CREATE permissions for all databases * and all tables * to the user TestUser (we created in the previous tutorial) use the CREATE permission:

mysql>
mysql> GRANT CREATE ON *.* TO 'TestUser'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql>

mysql>

mysql> GRANT CREATE ON *.* TO 'TestUser'@'localhost' ;

Query OK, 0 rows affected (0.00 sec)mysql>

mysql>

Example 3: To grant CREATE USER to User ‘TestUser’ use the CREATE USER permission:

mysql>
mysql> GRANT CREATE USER ON *.* TO 'TestUser'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

mysql>

mysql> GRANT CREATE USER ON *.* TO 'TestUser'@'localhost' ;

Query OK, 0 rows affected (0.00 sec)

mysql>

Step 3: View Grants for MySQL User :

After you’ve granted permissions to a MySQL user if you want to know and check them you can use the following command to check the grants for TestUser :

mysql> SHOW GRANTS FOR 'TestUser'@'localhost' ;
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for TestUser@localhost                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT <strong>CREATE</strong>, <strong>CREATE USER</strong> ON *.* TO 'TestUser'@'localhost' IDENTIFIED BY PASSWORD '*676243218923905CF94CB52A3C9D3EB30CE8E20D' |
| GRANT <strong>DROP</strong> ON `tutorials_space`.* TO 'TestUser'@'localhost'                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)mysql>
mysql>

mysql> SHOW GRANTS FOR 'TestUser'@'localhost' ;

+-------------------------------------------------------------------------------------------------------------------------------+

| Grants for TestUser@localhost |

+-------------------------------------------------------------------------------------------------------------------------------+

| GRANT CREATE, CREATE USER ON *.* TO 'TestUser'@'localhost' IDENTIFIED BY PASSWORD '*676243218923905CF94CB52A3C9D3EB30CE8E20D' |

| GRANT DROP ON `tutorials_space`.* TO 'TestUser'@'localhost' |

+-------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)mysql>

mysql>

Conclusion

IIn this post, i have explained hHow to Grant Permissions to a MySQL User on Linux.
If you have any questions or feedback, feel free to leave a comment.
As always, if you found this post useful, then click like and share it 🙂

194520cookie-checkHow to Grant Permissions to a MySQL User on Linux