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:
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:
1 2 3 4 5 |
mysql> mysql> GRANT DROP ON tutorials_space_db.* TO 'TestUser'@'localhost' ; Query OK, 0 rows affected (0.00 sec) mysql> 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:
1 2 3 4 |
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:
1 2 3 4 5 |
mysql> mysql> GRANT CREATE USER ON *.* TO 'TestUser'@'localhost' ; Query OK, 0 rows affected (0.00 sec) mysql> 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 :
1 2 3 4 5 6 7 8 9 |
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> |
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 🙂