Mysql 简明教程

MySQL - REVOKE Statement

前面, 我们讨论了 root 用户如何在安装后通过默认权限访问 MySQL 服务器。这些权限足以对数据执行基本操作。然而, 在某些特殊情况下, 用户可能需要向服务器主机请求取消某些权限。要做到这一点, 我们使用 MySQL REVOKE 语句。

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

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

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

Syntax

以下是 MySQL REVOKE 语句的语法 −

Following is the syntax of the MySQL REVOKE Statement −

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

Example

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

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)

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

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)

将 grants privileges 查询应用于上面创建的表格, 给 'test_user'@'localhost −

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)

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

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 −

现在, 您可以使用 REVOKE statement 撤销上述授予的权限, 如下所示 −

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

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

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';

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

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

Revoking All Privileges

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

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

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

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)

此外，我们在数据库中创建一个名为“sample”的表 −

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)

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

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)

同样，以下查询向用户“sample_user”@“localhost”授予对表“sample”的 SELECT、INSERT 和 UPDATE 权限 −

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)

您可以使用 SHOW GRANTS 语句验证授予用户的全部权限清单 −

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 −

最后，要 revoke all the privileges 授予的“sample_user”@'localhost'，可以使用以下语句 −

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

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

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

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

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)

现在，我们正在创建一个表“Employee” −

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)

以下查询向用户 sample_user 授予对上述创建表上的 SELECT 和 INSERT 权限 −

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)

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

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)

您可以使用 REVOKE PROXY 语句撤消代理权限，如下所示 −

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

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

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

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

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)

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

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)

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

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

我们还可以使用客户端程序从 MySQL 用户那儿收回权限。

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

Syntax

以下是各种编程语言中吊销 MySQL 权限的语法 −

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

Example

以下是该操作在各种编程语言中的实现 −

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