1. Home
  2. MySQL Revoke Privilege

MySQL Revoke Privilege

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.

REVOKE Statement

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.

Syntax

The following are the basic syntax of using the REVOKE statement:

Parameter Explanation

In the above syntax, we can have the following parameters:

Parameter Name Descriptions
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.

Privilege Levels

MySQL supports the following privilege levels:

Privilege Level Syntax Descriptions
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.*
FROM john@localhost;		 It applies to revoke all privileges from objects in the current database.
Table REVOKE DELETE
ON mydb.employees
FROM john@localhsot;		 It applies to revoke privileges from all columns in a specified table.
Column REVOKE SELECT (col1), INSERT (col1, col2), UPDATE (col2) ON mydb.mytable
FROM john@localhost;		 It applies to revoke privileges from a single column of a table.
Stored Routine REVOKE EXECUTE ON PROCEDURE/FUNCTION mydb.myprocedure
FROM john@localhost;		 It applies to revoke all privileges from stored routines (procedure and functions).
Proxy REVOKE PROXY ON root
FROM peter@localhost;		 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:

Next, assign all privileges to all databases in the current server to john@localhost, using the below statement:

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.

MySQL Revoke Privilege

If we want to revoke all privileges assign to the user, execute the following statement:

We will get the output below where we can see that a user can log in to the database without any privileges.

MySQL Revoke Privilege

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:

Next, display the GRANT privilege with the following statement:

Finally, execute the REVOKE statement to remove UPDATE and INSERT privilege with the below statement:

It will give the below output where only SELECT privilege is left.

MySQL Revoke Privilege

REVOKE Proxy User Example

First, we need to grant the proxy privilege to the user whom you want using the following statement:

Next, display the GRANT privilege with the given statement:

Finally, execute the REVOKE statement to remove proxy privilege from the user with the below statement:

It will give the below output where proxy privilege is revoked successfully.

MySQL Revoke Privilege

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:

If there is a need to revoke the EXECUTE privilege to the users, we must run the below command:

We can revoke privileges from the below list on which privileges can be applied.

  1. CREATE: It enables the user account to create databases and tables.
  2. DROP: It allows the user account to drop databases and tables.
  3. DELETE: It enables the user account to delete rows from a specific table.
  4. INSERT: It allows the user account to insert rows into a specific table.
  5. SELECT: It enables the user account to read a database.
  6. UPDATE: It enables the user account to update table rows.
Next TopicMySQL if()




Hot Tutorials

Contact US

Email:[email protected]

MySQL Revoke Privilege
10/30