MySQL - REVOKE Statement

Earlier, we discussed how a root user gets access to a MySQL server with default privileges after installation. These privileges are sufficient for performing basic operations on the data. However, in some special situations, users might need to request the server's host to take away certain privileges. To do so, we use the MySQL REVOKE statement.

The MySQ REVOKE statement

The MySQL REVOKE statement is used to remove certain administrative privileges or roles from users. It revokes permissions that were previously granted.

Syntax

Following is the syntax of the MySQL REVOKE Statement −

REVOKE privileges
   ON database_name.table_name
   FROM 'user'@'host';

Example

Assume we have created a user named 'test_user'@'localhost' in MySQL using the CREATE USER statement as shown below −

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';

Following is the output produced −

Query OK, 0 rows affected (0.23 sec)

Now, let us create a database named 'test_database' −

CREATE DATABASE test_database;

The output produced is as follows −

Query OK, 1 row affected (0.56 sec)

Next, we will use the created database −

USE test_database;

We get the output as shown below −

Database changed

Now, let us create a table in the database −

CREATE TABLE MyTable(data VARCHAR(255));

The output obtained is as follows −

Query OK, 0 rows affected (0.67 sec)

Following query grants privileges on the table created above to the user 'test_user'@'localhost −

GRANT SELECT ON test_database.MyTable TO 'test_user'@'localhost';

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.31 sec)

You can verify the granted privileges using the SHOW GRANTS statements −

SHOW GRANTS FOR 'test_user'@'localhost';

The output we get is as shown below −

Now, you can revoke the above granted privilege using the REVOKE statement as shown below −

REVOKE SELECT ON test_database.MyTable FROM 'test_user'@'localhost';

We get the output as follows −

Query OK, 0 rows affected (0.25 sec)

Verification

We can verify whether the SELECT privilege has been revoked or not using the SHOW GRANTS statements as shown below −

SHOW GRANTS FOR 'test_user'@'localhost';

We can see that the output no longer lists the SELECT privilege, indicating that it has been revoked −

Revoking All Privileges

If a user has multiple privileges with a user, you can revoke all those privileges at once using the REVOKE ALL statement in MySQL.

Syntax

Following is the syntax to revoke all privileges in MySQL −

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';

Example

Assume we have created a user as follows −

CREATE USER 'sample_user'@'localhost';

Following is the output produced −

Query OK, 0 rows affected (0.18 sec)

We also create a procedure as shown below −

DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT 'This is a sample procedure';
   END//
DELIMITER ;

The output obtained is as follows −

Query OK, 0 rows affected (0.29 sec)

Additionally, we create a table named 'sample' in a database −

CREATE TABLE sample(data INT);

We get the output as shown below −

Query OK, 0 rows affected (0.68 sec)

Now, the following queries grants ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named 'sample_user'@'localhost'.

GRANT ALTER ROUTINE, EXECUTE 
ON PROCEDURE test_database.sample TO 'sample_user'@'localhost';

Output of the above code is as shown below −

Query OK, 0 rows affected (0.20 sec)

Similarly, following query grants SELECT, INSERT and UPDATE privileges on the table 'sample' to the user 'sample_user'@'localhost −

GRANT SELECT, INSERT, UPDATE 
ON test.sample TO 'sample_user'@'localhost';

The result produced is −

Query OK, 0 rows affected (0.14 sec)

You can verify the list of all privileges granted for the user using the SHOW GRANTS statement −

SHOW GRANTS FOR 'sample_user'@'localhost';

The result obtained is as follows −

Finally, to revoke all the privileges granted to 'sample_user'@'localhost', you can use the following statement −

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sample_user'@'localhost';

The result produced is −

Query OK, 0 rows affected (0.30 sec)

Verification

After revoking privileges, you can check the user's grants again −

SHOW GRANTS FOR 'sample_user'@'localhost';

The output below confirms that all privileges have been revoked −

Revoking Proxy Privilege

You can make one user as a proxy of another by granting the PROXY privilege to it. If you do so, both users have the same privileges.

Example

Assume we have created users named sample_user, proxy_user in MySQL using the CREATE statement −

CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';

Following is the output obtained −

Query OK, 0 rows affected (0.52 sec)

Now, we are creating a table 'Employee' −

CREATE TABLE Employee (
ID INT, Name VARCHAR(15), Phone INT, SAL INT);

We get the output as shown below −

Query OK, 0 rows affected (6.47 sec)

Following query grants SELECT and INSERT privileges on the table created above, to the user sample_user −

GRANT SELECT, INSERT ON Emp TO sample_user;

The output obtained is as follows −

Query OK, 0 rows affected (0.28 sec)

Now, we can assign proxy privileges to the user proxy_user using the GRANT statement as shown below −

GRANT PROXY ON sample_user TO proxy_user;

The result produced is −

Query OK, 0 rows affected (1.61 sec)

You can revoke a proxy privilege using the REVOKE PROXY statement as shown below −

REVOKE PROXY ON sample_user FROM proxy_user;

We get the following result −

Query OK, 0 rows affected (0.33 sec)

Revoking a Role

A role in MySQL is a set of privileges with name. You can create one or more roles in MySQL using the CREATE ROLE statement. If you use the GRANT statement without the ON clause, you can grant a role instead of privileges.

Example

Following query creates a role named TestRole_ReadOnly −

CREATE ROLE 'TestRole_ReadOnly';

Following is the output of the above code −

Query OK, 0 rows affected (0.13 sec)

Now, let us grant read only privilege to the created role using the GRANT statement −

GRANT SELECT ON * . * TO 'TestRole_ReadOnly';

The result obtained is −

Query OK, 0 rows affected (0.14 sec)

Then, you can GRANT the created role to a user as follows −

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Output of the above code is as follows −

Query OK, 0 rows affected (0.14 sec)

Next, you can grant the 'TestRole_ReadOnly' role to the 'newuser'@'localhost'−

GRANT 'TestRole_ReadOnly' TO 'newuser'@'localhost';

We get the following result −

Query OK, 0 rows affected (0.13 sec)

Following query revokes the role from the user −

REVOKE 'TestRole_ReadOnly' FROM 'newuser'@'localhost';

After executing the above code, we get the following output −

Query OK, 0 rows affected (1.23 sec)

Revoking Privileges Using a Client Program

We can also revoke privileges from a MySQL user using a client program.

Syntax

Following are the syntaxes to revoke MySQL Privileges in various programming languages −

$sql = "REVOKE ALL, GRANT OPTION FROM user_name";
$mysqli->query($sql);

sql= "REVOKE privilege_name(s) ON object FROM user_account_name";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_NAME";
statement.execute(sql);

sql = f"REVOKE {privileges} ON your_database.* FROM '{username_to_revoke}'@'localhost'";
cursorObj.execute(sql);

Example

Following are the implementations of this operation in various programming languages −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = "REVOKE ALL, GRANT OPTION FROM Sarika";
if($result = $mysqli->query($sql)){
   printf("Revoke privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Revoke privileges executed successfully...!

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});
  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';"
  con.query(sql);

  sql = "CREATE DATABASE test_database;"
  con.query(sql);

  sql = "USE test_database;"
  con.query(sql);

  sql = "CREATE TABLE MyTable(data VARCHAR(255));"
  con.query(sql);

  sql = "GRANT SELECT ON test_database.MyTable TO 'test_user'@'localhost';"
  con.query(sql);

  sql = "SHOW GRANTS FOR 'test_user'@'localhost';";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log("**Granted privileges:**");
    console.log(result);
    console.log("--------------------------");
  });
  sql = "REVOKE SELECT ON test_database.MyTable FROM 'test_user'@'localhost';"
  con.query(sql);

  sql = "SHOW GRANTS FOR 'test_user'@'localhost';";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log("**Grants after revoking:**");
    console.log(result);
  });
});

Connected!
--------------------------
**Granted privileges:**
[
  {
    'Grants for test_user@localhost': 'GRANT USAGE ON *.* TO `test_user`@`localhost`'
  },
  {
    'Grants for test_user@localhost': 'GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost`'
  }
]
--------------------------
**Grants after revoking:**
[
  {
    'Grants for test_user@localhost': 'GRANT USAGE ON *.* TO `test_user`@`localhost`'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class RevokePriv {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM Vivek";
            st.execute(sql);
            System.out.println("You revoked all the privileges form user 'Vivek'");    
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

You revoked all the privileges form user 'Vivek'

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)
username_to_revoke = 'newUser'
# privileges we want to revoke
privileges = 'SELECT, INSERT, UPDATE'
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"REVOKE {privileges} ON your_database.* FROM '{username_to_revoke}'@'localhost'")
print(f"Privileges revoked from the user '{username_to_revoke}' successfully.")
cursorObj.close()
connection.close()

Privileges revoked from the user 'newUser' successfully.