MySQL Grant Privilege

GRANT statement in MySQL

The GRANT statement is used to grant user account privileges to a user. The syntax of the GRANT command is-  

GRANT privilege(s) ON privilege_level TO user_account_name;

where, 

privilege(s) specify the access rights being granted. If we want to grant multiple privileges, we use comma (,) to separate them.
privilege_level specifies the level of privilege on which the access rights are being granted.
user_account_name specifies the user's account to which we are granting the privilege.

Note: If we want to grant privileges to all the users present in the MySQL server, then we have to use *@localhost.

Before getting further, let’s discuss how to see a user’s privilege.

SHOW GRANTS Command

The SHOW GRANTS command is to see a user’s privilege. The syntax of the SHOW GRANTS command is-

SHOW GRANTS FOR user_account_name;

For example, the following query will check the privileges assigned to the user account ninja@localhost.

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

The word USAGE in the output means the user ‘ninja’ can only log into the MySQL server but has no privileges.

Now, we will assign privileges to the user ‘ninja’. Before getting there, let us know the list of privileges assigned to a user.

Privileges

Below is the list of privileges or access rights assigned to a user.

• SELECT: By granting the SELECT privilege, the user can run the SELECT statements on a table and view the result set. This privilege can be applied only at the Global, Database, Table and Column levels.
• INSERT: The user can insert records in a table by granting the INSERT privilege. This privilege can be applied only at the Global, Database, Table and Column levels.
• DELETE: By granting the DELETE privilege, the user can delete the records from a table. This privilege can be applied only at the Global, Database and Table levels.
• CREATE: By granting the CREATE privilege, the user can create databases and tables. This privilege can be applied only at the Global, Database and Table levels.
• ALTER: By granting the ALTER privilege, the user can modify the tables. This privilege can be applied only at the Global, Database and Table levels.
• UPDATE: By granting the UPDATE privilege, the user can modify the data in the tables. This privilege can be applied only at the Global, Database, Table and Column levels.
• DROP: By granting the DROP privilege, the user can drop a table. This privilege can be applied only at the Global, Database and Table levels.
• INDEX: By granting the INDEX privilege, the user can create indexes on a table. This privilege can be applied only at the Global, Database and Table levels.
• ALL: By granting the ALL privilege, the user can access all the privileges except the GRANT privilege. This privilege can be applied at all levels.
• GRANT: By granting the GRANT privilege, the user can add or change access rights. This privilege can be applied at all levels.

Now, we shall discuss the privilege levels on which we can grant access rights to a user.

Privilege Levels

MySQL supports the following privilege levels for granting access rights to a user.

We will discuss these privilege levels one by one.

GLOBAL

If we specify the privilege level as GLOBAL to a user account, that user gets access to all the databases and tables present in the current MySQL server. The user can query data from all the tables of each database. We need to specify the *.* syntax for applying the GLOBAL privilege level.

Below is the syntax for using the GLOBAL privilege level.

GRANT privilege(s) ON *.* TO user_account_name;

where,

privilege(s) are the names of the privileges separated by commas.

user_account_name is the user's account to which we are granting the privilege.

For example, we will grant the ALL privilege to the user ‘ninja’ at the GLOBAL privilege level using the following statement.

mysql> GRANT ALL ON *.* TO ninja@localhost;

The user ‘ninja’ can now perform all operations on all the databases' tables except the GRANT privilege.
 

DATABASE

If we specify the privilege level as Database to a user account, then that user gets access to all the tables of the mentioned database. The user can only query the data of the tables of the said database. We need to specify the database_name.* syntax for applying the DATABASE privilege level. Here, database_name is the name of the database for which we want to grant access.

Below is the syntax for using the DATABASE privilege level.

GRANT privilege(s) ON database_name.* TO user_account_name;

where,

privilege(s) are the names of the privileges separated by commas.

database_name is the name of the database.

user_account_name is the user's account to which we are granting the privilege.

For example, we will grant the INSERT privilege to the user ‘ninja’ for the employee database at the DATABASE privilege level using the following statement.

mysql> GRANT INSERT ON employee.* TO ninja@localhost;

The user ‘ninja’ can now insert records into all the table’s of the employee database.
 

TABLE

If we specify the privilege level as TABLE to a user account, that user gets access to all the columns of the mentioned table of a database. The user can only query data from the said table. We need to specify the database_name.table_name syntax for applying the TABLE privilege level. Here, database_name is the name of the database, and table_name is the table's name for which we want to grant access.

Below is the syntax for using the TABLE privilege level.

GRANT privilege(s) ON database_name.table_name TO user_account_name;

where,

privilege(s) are the names of the privileges separated by commas.

database_name is the name of the database.

table_name is the name of the table present in the mentioned database.

user_account_name is the user's account to which we are granting the privilege.

For example, we will grant the DELETE privilege to the user ‘ninja’ for the salary table of the employee database at the TABLE privilege level using the following statement.

mysql> GRANT DELETE ON employee.salary TO ninja@localhost;

The user ‘ninja’ can now delete records of the salary table of the employee database.
 

COLUMN

If we specify the privilege level as COLUMN to a user account, then that user gets access to the selected columns of the mentioned table of a database. The user can only query data from those chosen columns. We need to specify the name of the column(s) enclosed in parentheses for each privilege for applying the COLUMN privilege level. 

Below is the syntax for using the COLUMN privilege level.

GRANT privilege(s)(col1,col2,...) ON database_name.table_name TO user_account_name;

where,

privilege(s) are the names of the privileges separated by commas.

col1,col2,... are the names of the columns present in the mentioned table. 

database_name is the name of the database.

table_name is the name of the table present in the mentioned database.

user_account_name is the user's account to which we are granting the privilege.

For example, consider a table salary of the employee database. The salary table has five columns - 

sal_id (salary id), 

emp_id (employee id), 

emp_name (employee name), 

amount (salary amount) and 

type (salary type, i.e., quarterly, monthly, annually etc.). 

We will grant some COLUMN level privileges like the SELECT privilege to the user ‘ninja’ for the columns sal_id and emp_id, the INSERT privilege for the columns emp_name and amount and the UPDATE privilege for the column type of the salary table of the employee database using the following statement.

mysql> GRANT SELECT (sal_id, emp_id), INSERT (emp_name, amount), UPDATE (type) ON employee.salary TO ninja@localhost;

The user ‘ninja’ can now select data of the columns sal_id and emp_id, insert records in the columns emp_name and amount and update the data of the column type of the salary table of the employee database.
 

STORED ROUTINE

If we specify the privilege level as STORED ROUTINE to a user account, that user can access the procedures and functions stored in the current database. At this privilege level, we can grant privileges like CREATE ROUTINE, ALTER ROUTINE, EXECUTE and GRANT OPTION. 

• CREATE ROUTINE: This privilege allows users to create stored procedures and functions.
• ALTER ROUTINE: This privilege allows users to alter and drop stored procedures and functions.
• EXECUTE: This privilege allows a user to execute stored procedures and functions.
• GRANT OPTION: This privilege allows a user to grant and revoke privileges.

We need to specify the name of the procedure or function for which we want to apply the STORED ROUTINE privilege level. 

Below is the syntax for using the STORED ROUTINE privilege level.

GRANT privilege(s) ON PROCEDURE/FUNCTION procedure_name/function_name TO user_account_name;

where,

privilege(s) are the names of the privileges separated by commas.

procedure_name/function_name is the name of the procedure or function present in the current database.

user_account_name is the user's account to which we are granting the privilege.

For example, suppose we have a procedure called check_type, and we want to GRANT the EXECUTE privilege to the user ‘ninja’. We will use the following command-

mysql> GRANT EXECUTE ON PROCEDURE check_type TO ninja@localhost;

The user ‘ninja’ can now execute the procedure check_type.

Let’s consider one more example.

Suppose we have a function called calculate_salary, and we want to GRANT the EXECUTE privilege to the user ‘ninja’. We will use the following command-

mysql> GRANT EXECUTE ON FUNCTION calculate_salary TO ninja@localhost;

The user ‘ninja’ can now execute the function calculate_salary.
 

PROXY

The PROXY privilege level allows one user to proxy other users. In other words, the proxy user can use the privileges available to one user.

Below is the syntax for using the PROXY privilege level.

GRANT PROXY ON proxied_user_account_name TO proxy_user_account_name;

where,

proxied_user_account_name is the user's account of which we are granting the privileges.

proxy_user_account_name is the user's account to which we are granting the privilege.

For example, we will grant all the privileges available to the root to the user ‘ninja’ using the following command.

mysql> GRANT PROXY ON root TO ninja@localhost;

The user ‘ninja’ now has all the privileges of the root.

FAQs

1. How can we create a user in MySQL?
    We can create a user in MySQL using the following command-

CREATE USER user_account_name IDENTIFIED BY ‘password’;

2. How can we check a user’s privileges in MySQL?
    We can use the SHOW GRANTS command to check a user’s privileges in MySQL. The syntax of the SHOW 
    GRANTS command is-

SHOW GRANTS FOR user_account_name;

3. What are the different privileges assigned to a user in MySQL?
    The different privileges that can be assigned to a user are - select, insert, delete, create, alter, update, drop, 
    index, all, grant, create routine, alter routine, execute, grant option.

4. What are the different privilege levels we can grant privileges to a user in MySQL?
    The different privilege levels we can grant privileges to a user are- global, database, table, column, stored 
    routine and proxy.

5. Can we assign all privileges at all privilege levels in MySQL? 

• The ALL and GRANT privilege can be applied at all levels.
• The SELECT, INSERT and UPDATE privileges can be applied only at the Global, Database, Table and Column levels.
• The DELETE, CREATE, ALTER, DROP, and INDEX privileges can be applied only at the Global, Database and Table level.

Key Takeaways

In this blog, we talked about how to grant privileges in MySQL. There are various privileges that can be assigned at different levels. We discussed all these privileges and privilege levels through syntaxes and examples. 

Recommended Readings:

• MySQL Case Statement

Attention Ninja!!!!! Don’t stop here. Start your DBMS journey with the DBMS course on Coding Ninjas. To master SQL, solve our Top 100 SQL Problems asked in various company interviews.

Happy Learning!