MySQL - REVOKE 语句

前面，我们讨论了 root 用户如何在安装后获得具有默认权限的 MySQL 服务器的访问权限。这些权限足以对数据执行基本操作。但是，在某些特殊情况下，用户可能需要请求服务器主机撤销某些权限。为此，我们使用 MySQL REVOKE 语句。

MySQL REVOKE 语句

MySQL REVOKE 语句用于从用户中删除某些管理权限或角色。它撤销先前授予的权限。

语法

以下是 MySQL REVOKE 语句的语法：

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

示例

假设我们使用 CREATE USER 语句在 MySQL 中创建了一个名为 'test_user'@'localhost' 的用户，如下所示：

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

生成的输出如下：

Query OK, 0 rows affected (0.23 sec)

现在，让我们创建一个名为 'test_database' 的数据库：

CREATE DATABASE test_database;

生成的输出如下：

Query OK, 1 row affected (0.56 sec)

接下来，我们将使用已创建的数据库：

USE test_database;

我们得到如下所示的输出：

Database changed

现在，让我们在数据库中创建一个表：

CREATE TABLE MyTable(data VARCHAR(255));

获得的输出如下：

Query OK, 0 rows affected (0.67 sec)

以下查询将权限授予到上面创建的表到用户 'test_user'@'localhost'：

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

执行上述代码后，我们将得到以下输出：

Query OK, 0 rows affected (0.31 sec)

您可以使用 SHOW GRANTS 语句验证已授予的权限：

SHOW GRANTS FOR 'test_user'@'localhost';

我们得到的输出如下所示：

现在，您可以使用如下所示的REVOKE 语句撤销上述已授予的权限：

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

我们得到如下所示的输出：

Query OK, 0 rows affected (0.25 sec)

验证

我们可以使用如下所示的 SHOW GRANTS 语句验证 SELECT 权限是否已被撤销：

SHOW GRANTS FOR 'test_user'@'localhost';

我们可以看到输出不再列出 SELECT 权限，这表明它已被撤销：

撤销所有权限

如果用户对用户具有多个权限，则可以使用 MySQL 中的 REVOKE ALL 语句一次撤销所有这些权限。

语法

以下是 MySQL 中撤销所有权限的语法：

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

示例

假设我们创建了一个用户，如下所示：

CREATE USER 'sample_user'@'localhost';

生成的输出如下：

Query OK, 0 rows affected (0.18 sec)

我们还创建了一个过程，如下所示：

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

获得的输出如下：

Query OK, 0 rows affected (0.29 sec)

此外，我们在数据库中创建一个名为 'sample' 的表：

CREATE TABLE sample(data INT);

我们得到如下所示的输出：

Query OK, 0 rows affected (0.68 sec)

现在，以下查询将 ALTER ROUTINE、EXECUTE 权限授予到上面创建的过程给名为 'sample_user'@'localhost' 的用户。

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

上述代码的输出如下所示：

Query OK, 0 rows affected (0.20 sec)

类似地，以下查询将 SELECT、INSERT 和 UPDATE 权限授予到表 'sample' 给用户 'sample_user'@'localhost'：

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

产生的结果是：

Query OK, 0 rows affected (0.14 sec)

您可以使用 SHOW GRANTS 语句验证用户的所有已授予权限列表：

SHOW GRANTS FOR 'sample_user'@'localhost';

获得的结果如下：

最后，要撤销授予 'sample_user'@'localhost' 的所有权限，可以使用以下语句：

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

产生的结果是：

Query OK, 0 rows affected (0.30 sec)

验证

撤销权限后，您可以再次检查用户的权限：

SHOW GRANTS FOR 'sample_user'@'localhost';

下面的输出确认所有权限已被撤销：

撤销代理权限

您可以通过授予 PROXY 权限来使一个用户成为另一个用户的代理。如果您这样做，则两个用户都具有相同的权限。

示例

假设我们使用 CREATE 语句在 MySQL 中创建了名为 sample_user、proxy_user 的用户：

CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';

获得的输出如下：

Query OK, 0 rows affected (0.52 sec)

现在，我们正在创建表 'Employee'：

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

我们得到如下所示的输出：

Query OK, 0 rows affected (6.47 sec)

以下查询将 SELECT 和 INSERT 权限授予到上面创建的表给用户 sample_user：

GRANT SELECT, INSERT ON Emp TO sample_user;

获得的输出如下：

Query OK, 0 rows affected (0.28 sec)

现在，我们可以使用如下所示的 GRANT 语句将代理权限分配给用户 proxy_user：

GRANT PROXY ON sample_user TO proxy_user;

产生的结果是：

Query OK, 0 rows affected (1.61 sec)

您可以使用如下所示的 REVOKE PROXY 语句撤销代理权限：

REVOKE PROXY ON sample_user FROM proxy_user;

我们得到以下结果：

Query OK, 0 rows affected (0.33 sec)

撤销角色

MySQL 中的角色是一组具有名称的权限。您可以使用 CREATE ROLE 语句在 MySQL 中创建一个或多个角色。如果您使用不带 ON 子句的 GRANT 语句，则可以授予角色而不是权限。

示例

以下查询创建一个名为 TestRole_ReadOnly 的角色：

CREATE ROLE 'TestRole_ReadOnly';

以下是上述代码的输出：

Query OK, 0 rows affected (0.13 sec)

现在，让我们使用 GRANT 语句将只读权限授予已创建的角色：

GRANT SELECT ON * . * TO 'TestRole_ReadOnly';

获得的结果为：

Query OK, 0 rows affected (0.14 sec)

然后，您可以按如下方式将创建的角色授予用户：

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

上述代码的输出如下：

Query OK, 0 rows affected (0.14 sec)

接下来，您可以将“TestRole_ReadOnly”角色授予“newuser”@“localhost”：

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

我们得到以下结果：

Query OK, 0 rows affected (0.13 sec)

以下查询将从用户那里撤销角色：

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

执行上述代码后，我们将得到以下输出：

Query OK, 0 rows affected (1.23 sec)

使用客户端程序撤销权限

我们还可以使用客户端程序从 MySQL 用户撤销权限。

语法

以下是使用各种编程语言撤销 MySQL 权限的语法：

$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);

示例

以下是各种编程语言中此操作的实现：

$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://127.0.0.1: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.