MySQL Revoke Privilege

GRANT Command

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.

We will grant all privileges on the database Employee to the user ‘ninja’ using the following command:

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

We can check the user ‘ninja’s’ privilege using the SHOW GRANTS Command. 

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

Now, we will revoke these privileges from the user ‘ninja’.

REVOKE Command

To revoke the privileges from a user, we use the REVOKE command. The syntax of the REVOKE command is as follows:

REVOKE privilege(s) ON privilege_level FROM user_account_name;

where, 

privilege(s) specify the access rights being revoked. If we want to revoke multiple privileges, we use comma (,) to separate them.

privilege_level specifies the level of privilege on which the access rights are being revoked.

user_account_name specifies the user's account to which we are revoking the privilege.

For example, to revoke all the privileges of the user ‘ninja’, we will execute the following command-

mysql> REVOKE ALL ON Employee.* FROM ninja@localhost; 

To check whether the user’s privileges are revoked or not, we will use the SHOW GRANTS Command.

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 discuss the list of privileges that can be revoked from a user.

Privileges

Below is the list of privileges or access rights revoked from a user.

SELECT: By revoking the SELECT privilege, the user cannot run the SELECT statements on a table and can’t view the result set. This privilege can be applied only at the Global, Database, Table and Column levels.

INSERT: The user cannot insert records in a table by revoking the INSERT privilege. This privilege can be applied only at the Global, Database, Table and Column levels.

DELETE: The user cannot delete the records from a table by revoking the DELETE privilege. This privilege can be applied only at the Global, Database and Table levels.

CREATE: The user cannot create databases and tables by revoking the CREATE privilege. This privilege can be applied only at the Global, Database and Table levels.

UPDATE: By revoking the UPDATE privilege, the user cannot modify the data in the tables. This privilege can be applied only at the Global, Database, Table and Column levels.

DROP: By revoking the DROP privilege, the user cannot drop a table. This privilege can be applied only at the Global, Database and Table levels.

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

Privilege Levels

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

We will discuss these privilege levels.

GLOBAL

If we specify the privilege level as GLOBAL to a user account, users' access rights to all the databases and tables present in the current MySQL server are removed. The user can query data from all the tables of each database.

Below is the syntax for revoking privileges at the GLOBAL privilege level.

REVOKE privilege(s) FROM user_account_name;

where,

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

user_account_name is the user's account from which we are removing the privilege.

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

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

We can check the privileges using the SHOW GRANTS command.

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

Now, we will remove all these privileges using the following command-

mysql> REVOKE ALL, GRANT OPTION FROM ninja@localhost;

The user ‘ninja’ currently has no privileges. 
 

DATABASE

If we specify the privilege level as DATABASE to a user account, then that user’S access rights to all the tables of the mentioned database are removed. The user cannot query the data from 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 remove access.

Below is the syntax for revoking privileges at the DATABASE privilege level.

REVOKE privilege(s) ON database_name.* FROM 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 from which we are removing the privilege.

For example, first, we will grant all the privileges to the user ‘ninja’ for the Employee database using the following statement.

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

We can check the privileges using the SHOW GRANTS command.

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

Now, we will remove all these privileges using the following command-

mysql> REVOKE ALL ON Employee.* FROM ninja@localhost;

The user ‘ninja’ currently has no privileges. 
 

TABLE

If we specify the privilege level as TABLE to a user account, then that user’s access rights to all the columns of the mentioned table of a database are removed. The user cannot 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 remove access.

Below is the syntax for revoking privileges at the TABLE privilege level.

REVOKE privilege(s) ON database_name.table_name FROM 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 from which we are removing the privilege.

For example, first, we will grant all the privileges to the user ‘ninja’ for the salary table of the Employee database using the following statement.

mysql> GRANT ALL ON Employee.salary TO ninja@localhost;

We can check the privileges using the SHOW GRANTS command.

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

Now, we will remove only the DELETE privilege using the following command-

mysql> REVOKE DELETE ON Employee.salary FROM ninja@localhost;

The user ‘ninja’ now has no DELETE privilege to delete the data of the salary table. 
 

COLUMN

If we specify the privilege level as COLUMN to a user account, then that user’s access rights to the selected columns of the mentioned table of a database are removed. The user cannot 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. Here, database_name is the name of the database, and table_name is the table's name for which we want to remove access.

Below is the syntax for revoking privileges at the COLUMN privilege level.

REVOKE privilege(s)(col1,col2,...) ON database_name.table_name FROM 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 from which we are removing 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;

We can check the privileges using the SHOW GRANTS command.

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

Now, we will remove all these SELECT, INSERT and UPDATE privileges using the following command-

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

The user ‘ninja’ now has no SELECT, INSERT and UPDATE privileges on the specified columns. 
 

STORED ROUTINE

If we specify the privilege level as STORED ROUTINE to a user account, then that user’s access rights to the procedures and functions stored in the current database are removed. 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 revoking privileges at the STORED ROUTINE privilege level.

REVOKE 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 from which we are removing 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;

We can check the privileges using the SHOW GRANTS command.

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

Now, we will remove this granted privilege using the following command-

mysql> REVOKE EXECUTE ON PROCEDURE check_type FROM ninja@localhost;

Now, the user ‘ninja’ has no right to execute the check_type procedure.
 

PROXY

The PROXY privilege level allows one user to proxy other users. If we revoke this privilege, the proxy user cannot use the privileges available to one user.

Below is the syntax for revoking privileges at the PROXY privilege level.

REVOKE PROXY ON proxied_user_account_name FROM proxy_user_account_name;

where,

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

proxy_user_account_name is the user's account from which we are revoking the privilege.

For example, first, 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;

We can check the privileges using the SHOW GRANTS command.

mysql> SHOW GRANTS FOR ninja@localhost;

The output of the above query will be-

Now, we will remove this granted privilege using the following command-

mysql> REVOKE PROXY ON root FROM ninja@localhost;

The user ‘ninja’ now has no 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 revoked from a user in MySQL?
    The privileges that can be revoked from a user are - select, insert, delete, create, update, drop.
 

4. What are the different privilege levels at which we can revoke privileges from 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 remove all privileges at all privilege levels in MySQL?

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

Key Takeaways

In this blog, we talked about how to revoke privileges in MySQL. There are various privileges that can be revoked at different privilege 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!