In this tutorial, you will learn how to use the MySQL SHOW GRANT
and GRANT
statement to show and grant privileges to user accounts.
For how to create and manage MySQL user account, you can refer to previous articles for detail:
You need to connect to the MySQL server before running following MySQL statements. For how to connect to MySQL Server, you can refer to the previous article: Connect to MySQL Server Using Command Line.
Use following MySQL statement can show you current connect user:
mysql> select CURRENT_USER;
or
mysql> select CURRENT_USER();
The output is like as following
+--------------------+
| CURRENT_USER() |
+--------------------+
| codercms@localhost |
+--------------------+
1 row in set (0.00 sec)
MySQL SHOW GRANTS statement
SHOW GRANTS
statement returns all privileges and roles granted to an account user or role.
For example, codercms
account is connecting to MySQL server with command line, following commands will show its granted privileges:
mysql> show grants;
or
mysql> show grants for codercms@localhost;
Output:
+----------------------------------------------------------------+
| Grants for codercms@localhost |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `codercms`@`localhost` |
| GRANT ALL PRIVILEGES ON `codercms`.* TO `codercms`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL GRANT statement
The CREATE USER
statement creates one or more user accounts without privileges.
The new created accounts can login and connect to the MySQL Server, but cannot do any other operations such as choosing a database, query data from db tables.
To allow new created user accounts can work with database objects, you need to assign related privileges for these new created accounts by using GRANT
statement.
Syntax of GRANT statement
GRANT privilege [,privilege],..
ON privilege_level
TO account_name;
Privilege Level
MySQL supports following privilege levels:
- Global
- Database
- Table
- Column
- Stored Routine
- Proxy
Prerequisites
Before testing the grant statement, let’s connect to MySQL server with root
account and create a new user account codercms@localhost
by using following commands or statements.
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 164
Server version: 8.0.13 MySQL Community Server - GPL
mysql> create user codercms@localhost identified by 'abc123_';
Query OK, 0 rows affected (0.24 sec)
Check the new created user’s privileges, the user has no any other privileges except the MySQL server connection privilege.
mysql> show grants for codercms@localhost;
+----------------------------------------------+
| Grants for codercms@localhost |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `codercms`@`localhost` |
+----------------------------------------------+
1 row in set (0.01 sec)
Grant Global Privilege
Use *.*
pattern to assign global privilege to user codercms@localhost
, following statement will assign all DB privileges to the account (note do not do this in real life).
mysql> grant all on *.* to codercms@localhost;
Check the user’s grants after running above statement, you will see following output:
+--------------------------------------------------------------------------------+
| Grants for codercms@localhost +--------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `codercms`@`localhost` |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `codercms`@`localhost` |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Exit root
account and connect to MySQL server with codercms
account, then you can do any operations for any Databases.
Note we use following statement to remove all privilege for user account since we will practice other privilege level.
mysql> revoke all, grant option from codercms@localhost;
Query OK, 0 rows affected (0.05 sec)
Grant Database Level Privileges
Use syntax ON database_name.*
for granting privileges to operate all tables within the database. Following are the sample that grant the user can insert data to all tables in the database country_state_city.
mysql> grant INSERT on country_state_city.* to codercms@localhost;
Query OK, 0 rows affected (0.13 sec)
mysql> show grants for codercms@localhost;
+------------------------------------------------------------------+
| Grants for codercms@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `codercms`@`localhost` |
| GRANT INSERT ON `country_state_city`.* TO `codercms`@`localhost` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)
In case you want the user account have full privilege on the database, using following statement,
mysql> grant all on country_state_city.* to codercms@localhost;
Grant Table level Privileges
Use the ON database_name.table_name
syntax, apply for all columns within the specified db table.
mysql> grant select on country_state_city.states to codercms@localhost;
Query OK, 0 rows affected (0.08 sec)
Grant Column level privileges
If you want to apply to specified columns in a table. You have to specify the column or columns for each privilege, for example:
mysql> grant select (id,name,country_id,country_code), update(name) on states to codercms@localhost;
Query OK, 0 rows affected (0.06 sec)
Grant Stored Routine Privileges
Apply to stored procedures and stored functions, for example:
mysql> grant execute on procedure sp_get_country_summary to codercms@localhost;
Query OK, 0 rows affected (0.05 sec)
Grant Proxy user privileges
One user can be a proxy for another user, and the proxy user can get all privileges of the proxied user. For example:
mysql> grant proxy on root to codercms@localhost;
Query OK, 0 rows affected (0.03 sec)
Then codercms
has all privileges same with root
.
Summary
In this tutorial, you have learn that how to use the MySQL SHOW GRANT
and GRANT
statement to show and grant privileges to user accounts.