Grant Privileges to MySQL User Accounts

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;

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;

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.

Post Views: 162

CoderCMS