Mysql 简明教程

MySQL - Grant Privileges

正如我们前面了解的，在安装 MySQL 之后，root 用户立即连接到服务器（使用密码）。此用户可用的权限是默认的。使用 root 帐户访问 MySQL 的用户有足够的权限对数据执行基本操作。但是，在特殊情况下，用户必须手动请求主机授予权限。

The MySQL Grant Privileges

MySQL 提供了多条 SQL 语句，允许或限制用户管理权限，以与存储在数据库中的数据进行交互。它们列在下面 −

GRANT statement
REVOKE statement

在本教程中，让我们详细了解 GRANT 语句。

The MySQL GRANT Statement

MySQL GRANT 语句用于向 MySQL 用户帐户分配各种权限或角色。但要注意，您不能在单个 GRANT 语句中同时分配权限和角色。要使用此语句向用户授予权限，您需要具有 GRANT OPTION 权限。

Syntax

以下为 MySQL GRANT 语句的语法——

GRANT
privilege1, privilege2, privilege3...
ON object_type
TO user_or_role1, user_or_role2, user_or_role3...
[WITH GRANT OPTION]
[AS user
  [WITH ROLE
    DEFAULT
    | NONE
    | ALL
    | ALL EXCEPT role [, role ] ...
    | role [, role ] ...
   ]
]

Example

假设我们已经使用 CREATE USER 语句在 MySQL 中创建了一个名为 'test_user'@'localhost' 的用户——

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

以下是上面代码的输出： -

Query OK, 0 rows affected (0.23 sec)

现在，让我们创建一个数据库——

CREATE DATABASE test_database;

输出结果如下：

Query OK, 0 rows 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' 授予对上述创建表格的 SELECT 权限——

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

执行上面的代码后，我们得到以下输出： -

Query OK, 0 rows affected (0.31 sec)

Verification

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

SHOW GRANTS FOR 'test_user'@'localhost';

我们获得的输出如下所示——

Granting Various Privileges

我们知道 MySQL GRANT 语句允许为用户帐户授予广泛的权限。下面列出可以使用 GRANT 语句授予的一些常用权限——

Privileges

Description

ALTER

允许用户使用 ALTER TABLE 语句修改表结构。

CREATE

授予创建新对象（如表和数据库）的能力。

DELETE

允许用户从表中删除行。

INSERT

允许用户将新记录插入表中。

SELECT

提供对表的读取访问，允许用户检索数据。

UPDATE

允许用户修改表中的现有数据。

SHOW DATABASES

授予查看可用数据库列表的能力。

CREATE USER

允许用户创建新的 MySQL 用户帐户。

GRANT OPTION

向用户提供向其他用户授予权限的权限。

SUPER

Grants high-level administrative privileges.

SHUTDOWN

允许用户关闭 MySQL 服务器。

REPLICATION CLIENT

提供访问复制相关信息的权限。

REPLICATION SLAVE

使用户能够用作复制从属服务器。

FILE

授予在服务器文件系统上读写文件的权限。

CREATE VIEW

允许用户创建新的数据库视图。

CREATE TEMPORARY TABLES

允许创建临时表。

EXECUTE

使用户能够执行存储过程和函数。

TRIGGER

提供了创建和管理触发器的能力。

EVENT

授予创建和管理事件的能力。

SHOW VIEW

允许用户查看视图的定义。

INDEX

使用户能够在表上创建和删除索引。

PROXY

具备代理或模拟其他用户的能力。

要向用户授予所有可用的权限，您需要在 GRANT 语句中使用“ALL”关键字 −

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

执行上面的代码后，我们得到以下输出： -

Query OK, 0 rows affected (0.13 sec)

Granting Privileges on Stored Routines

要在 MySQL 中向存储例程（例如表、过程或函数）授予权限，您需要在 ON 子句后指定对象类型 (PROCEDURE 或 FUNCTION)，后跟例程名称。

您可以在这些存储例程上授予 ALTER ROUTINE、CREATE ROUTINE、EXECUTE 和 GRANT OPTION 权限。

Example

假设我们在当前数据库中已使用以下命令创建了存储过程和存储函数，并将其命名为“sample” −

//Creating a procedure
DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT 'This is a sample procedure';
   END//
Query OK, 0 rows affected (0.29 sec)

//Creating a function
CREATE FUNCTION sample()
   RETURNS VARCHAR(120)
   DETERMINISTIC
   BEGIN
      DECLARE val VARCHAR(120);
      SET val = 'This is a sample function';
      return val;
   END//
DELIMITER ;

以下是所获得的输出 −

Query OK, 0 rows affected (0.34 sec)

创建这些存储例程后，您可以授予名为 'test_user'@'localhost' 的用户对上面创建的过程的 ALTER ROUTINE 和 EXECUTE 权限，如下所示 −

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

下面显示了产生的输出：

Query OK, 0 rows affected (0.24 sec)

现在，以下查询将向名为 'test_user'@'localhost' 的用户授予对上面创建的函数的 ALTER ROUTINE 和 EXECUTE 权限。

GRANT ALTER ROUTINE, EXECUTE ON
FUNCTION test_database.sample TO 'test_user'@'localhost';

以下是以上查询的输出：

Query OK, 0 rows affected (0.15 sec)

Privileges to Multiple Users

您可以向多个用户授予权限。为此，您需要通过逗号分隔对象或用户的名称。

Example

假设我们已使用 CREATE 语句创建了一个名为“sample”的表和三个用户帐户，如下所示。

Creating a table −

CREATE TABLE sample (data VARCHAR(255));

我们将获得如下所示的输出 -

Query OK, 0 rows affected (3.55 sec)

现在，让我们创建用户帐户。

Creating User 'test_user1' −

CREATE USER test_user1 IDENTIFIED BY 'testpassword';

获得的输出如下 −

Query OK, 0 rows affected (0.77 sec)

Creating User 'test_user2' −

CREATE USER test_user2 IDENTIFIED BY 'testpassword';

下面是生成的输出−

Query OK, 0 rows affected (0.28 sec)

创建第 3 个用户 −

Creating User 'test_user3' −

CREATE USER test_user3 IDENTIFIED BY 'testpassword';

我们得到了如下输出 −

Query OK, 0 rows affected (0.82 sec)

使用单个 GRANT 语句后执行查询可向所有三个用户（“test_user1”、“test_user2”和“test_user3”）授予对表“sample1”、“sample2”和“sample3”的 SELECT、INSERT 和 UPDATE 权限。

GRANT SELECT, INSERT, UPDATE ON
TABLE sample TO test_user1, test_user2, test_user3;

Output

执行上面的代码后，我们得到以下输出： -

Query OK, 0 rows affected (0.82 sec)

Global Privileges

您可以授予全局权限（适用于用户所有数据库的权限），而不是指定表、过程或函数。为此，您需要在 ON 子句后使用 . 。

Example

以下查询向名为“test_user”'@'localhost' 的用户授予所有数据库的 SELECT、INSERT 和 UPDATE 权限 −

GRANT SELECT, INSERT, UPDATE ON *.* TO 'test_user'@'localhost';

Output

以下是所获得的输出 −

Query OK, 0 rows affected (0.43 sec)

Example

同样，以下查询向“test_user”'@'localhost 授予所有数据库的所有权限 −

GRANT ALL ON *.* TO 'test_user'@'localhost';

Output

下面显示了产生的输出：

Query OK, 0 rows affected (0.41 sec)

Database Level Privileges

您可以通过在 ON 子句后指定数据库名称后接“.*”来向数据库中的所有对象授予权限。

Example

以下查询向名为 test 的数据库中的所有对象授予名为“test_user”'@'localhost' 的用户 SELECT、INSERT 和 UPDATE 权限 −

GRANT SELECT, INSERT, UPDATE
ON test.* TO 'test_user'@'localhost';

Output

以下是上面代码的输出： -

Query OK, 0 rows affected (0.34 sec)

Example

同样，以下查询向“test_user”'@'localhost 授予所有数据库的所有权限 −

GRANT ALL ON test.* TO 'test_user'@'localhost';

Output

上述代码的输出如下：

Query OK, 0 rows affected (0.54 sec)

Column Level Privileges

您可以向用户授予表中的特定栏位的权限。为此，您需要在权限后指定栏位名称。

Example

假设我们使用 CREATE 查询创建名为 Employee 的表，如下所示 −

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

下面显示了产生的输出：

Query OK, 0 rows affected (6.47 sec)

以下查询向名为“test_user”'@'localhost' 的用户授予对 Employee 表的 ID 栏位的 SELECT 权限，以及对 Name 和 Phone 栏位的 INSERT 和 UPDATE 权限 −

GRANT SELECT (ID), INSERT (Name, Phone)
ON Employee TO 'test_user'@'localhost';

获得的输出如下 −

Query OK, 0 rows affected (0.54 sec)

Proxy User Privileges

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

Example

假设我们使用 CREATE 语句创建名为 sample_user, proxy_user 的用户，如下所示 −

CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';

以下是所获得的输出 −

Query OK, 0 rows affected (0.52 sec)

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

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)

Granting Roles

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

Example

让我们首先创建一个名为 TestRole_ReadOnly 的角色。

CREATE ROLE 'TestRole_ReadOnly';

以下是所获得的输出 −

Query OK, 0 rows affected (0.13 sec)

现在，让我们使用 GRANT 语句授予创建的角色只读特权，以便访问数据库中的所有对象 -

GRANT SELECT ON * . * TO 'TestRole_ReadOnly';

此 GRANT 语句的输出应该是 -

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)

Granting Privileges Using a Client Program

现在，让我们看看如何使用客户端程序向 MySQL 用户授予特权。

Syntax

以下是语法 −

Example

以下是这些程序 −