We have already learned how to give access right from grant privileges to a user account. Now, we are going to learn about revoke privileges from a user account. MySQL provides REVOKE statements to remove privileges from a user account.
The ‘REVOKE’ function is used in MySQL to revoke all or specified access privileges from a user. This function can be used to revoke one particular access and multiple or all accesses at once, in a single query. It can remove the privileges from different database objects like an entire database, a table or at the column level. A user must have the ‘CREATE USER’ or ‘UPDATE’ privileges, as required, in order to use the ‘REVOKE’ function. This function simply takes away the access privileges from a user, keeping the user account safe at the MySQL.user system table. The output of the REVOKE query is always a message that says ‘Query OK. 0 row(s) affected’.Before discussing further on the REVOKE function, it would be desirable to have a strong understanding of the GRANT function and the scope and privileges of accesses in MySQL.
The revoke statement enables system administrators to revoke privileges and roles to the MySQL user accounts so that they cannot use the assigned permission on the database in the past.
The following are the basic syntax of using the REVOKE statement:
REVOKE privilege_name(s) ON object FROM user_account_name;
In this syntax:
- First, specify a list of comma-separated privileges that you want to revoke from a user account after the
- Second, specify the object type and privilege level of the privileges after the
ONkeyword; check it out the
GRANTstatement for more information on privilege level.
- Third, specify one or more user accounts from which you want to revoke the privileges in the
Note that to execute this form of
REVOKE statement, you must have
GRANT OPTION privilege or you must have the privileges that you are revoking.
In the above syntax, we can have the following parameters:
|privilege_name(s)||It specifies the access rights or grant privilege that we want to revoke from user accounts.|
|object||It determines the privilege level on which the access rights are being granted. It means granting privilege to the table; then the object should be the name of the table.|
|user_account_name||It determines the account name of the user from which we want to revoke the access rights.|
How do I revoke privileges in MySQL?
The syntax for the revoking privileges on a function or procedure in MySQL is: REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user; EXECUTE. The ability to execute the function or procedure is being revoked.
MySQL supports the following privilege levels:
|Global||REVOKE ALL, GRANT OPTION FROM john@localhost;||It applies to remove all access rights from the user on MySQL server.|
|Database||REVOKE ALL ON mydb.*|
|It applies to revoke all privileges from objects in the current database.|
|It applies to revoke privileges from all columns in a specified table.|
|Column||REVOKE SELECT (col1), INSERT (col1, col2), UPDATE (col2) ON mydb.mytable|
|It applies to revoke privileges from a single column of a table.|
|Stored Routine||REVOKE EXECUTE ON PROCEDURE/FUNCTION mydb.myprocedure|
|It applies to revoke all privileges from stored routines (procedure and functions).|
|Proxy||REVOKE PROXY ON root|
|It enables us to revoke the proxy user.|
REVOKE Statement Example
Let us understand the REVOKE privileges through the example. First, we need to create a new user named “john@localhost” using the following statement:
mysql> CREATE USER john@localhost IDENTIFIED BY 'jtp12345';
Next, assign all privileges to all databases in the current server to john@localhost, using the below statement:
mysql> <strong>GRANT</strong> ALL <strong>ON</strong> mystudentdb.* <strong>TO</strong> john@localhost;
Next, execute the SHOW GRANT statement to verify the privileges. In the output, we can see that all privileges are assigned to all databases in the current server to john@localhost.
If we want to revoke all privileges assign to the user, execute the following statement:
mysql> REVOKE ALL, GRANT OPTION FROM john@localhost;
We will get the output below where we can see that a user can log in to the database without any privileges.
REVOKE selected privilege from a user account
Suppose we have provided grant privilege of SELECT, INSERT, and UPDATE command on mystudentdb to the user with the following statement:
mysql> GRANT SELECT, UPDATE, INSERT ON mystudentdb.* TO john@localhost;
Next, display the GRANT privilege with the following statement:
mysql> SHOW GRANTS FOR john@localhost;
Finally, execute the REVOKE statement to remove UPDATE and INSERT privilege with the below statement:
mysql> REVOKE UPDATE, INSERT ON mystudentdb.* FROM john@localhost;
It will give the below output where only SELECT privilege is left.
REVOKE Proxy User Example
First, we need to grant the proxy privilege to the user whom you want using the following statement:
mysql> GRANT PROXY ON 'peter@javatpoint' TO 'john'@'localhost' WITH GRANT OPTION;
Next, display the GRANT privilege with the given statement:
mysql> SHOW GRANTS FOR 'john'@'localhost';
Finally, execute the REVOKE statement to remove proxy privilege from the user with the below statement:
mysql> REVOKE PROXY ON 'peter@javatpoint' FROM 'john'@'localhost';
It will give the below output where proxy privilege is revoked successfully.
Revoking Privileges from Stored Routine Example
Here, the revoke privileges are applied to procedures and functions where we can revoke the privileges from the user who has a execute privilege in the past. Let us understand it with the example. Suppose we have a function calculatesalary and want to grant EXECUTE privilege to a user john, run the following query:
mysql> GRANT EXECUTE ON FUNCTION calculatesalary TO john@localhost;
If there is a need to revoke the EXECUTE privilege to the users, we must run the below command:
mysql> REVOKE EXECUTE ON FUNCTION calculatesalary TO john@localhost;
We can revoke privileges from the below list on which privileges can be applied.
- CREATE: It enables the user account to create databases and tables.
- DROP: It allows the user account to drop databases and tables.
- DELETE: It enables the user account to delete rows from a specific table.
- INSERT: It allows the user account to insert rows into a specific table.
- SELECT: It enables the user account to read a database.
- UPDATE: It enables the user account to update table rows.
When the MySQL
REVOKE command takes effect
The effect of
REVOKE statement depends on the privilege level:
The changes take effect when the user account connects to the MySQL Server in the subsequent sessions. The changes are not applied to all currently connected users.
The changes take effect after the next
Table and column levels
The changes take effect on all subsequent queries.