Mysql 简明教程

MySQL - Grant Privileges

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

As we learnt earlier, a root user is connected to the server (using a password) immediately after installing MySQL. The privileges available to this user are default. The user accessing MySQL using root account has enough privileges to perform basic operations on the data. However, in exceptional cases, the user must manually request the host to grant privileges.

The MySQL Grant Privileges

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

MySQL provides several SQL statements to allow or restrict administrative privileges for users to interact with the data stored in the database. They are listed below −

  1. GRANT statement

  2. REVOKE statement

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

In this tutorial, let us learn about the GRANT statement in detail.

The MySQL GRANT Statement

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

The MySQL GRANT statement is used to assign various privileges or roles to MySQL user accounts. However, it’s important to note that you cannot assign both privileges and roles in a single GRANT statement. To grant privileges to users using this statement, you need to have the GRANT OPTION privilege.

Syntax

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

Following is the syntax of the MySQL GRANT Statement −

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' 的用户——

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

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

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

Following is the output of the above code −

Query OK, 0 rows affected (0.23 sec)

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

Now, let us create a database −

CREATE DATABASE test_database;

输出结果如下：

The output produced is as follows −

Query OK, 0 rows 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)

以下查询向用户 'test_user'@'localhost' 授予对上述创建表格的 SELECT 权限——

Following query grants SELECT 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)

Verification

您可以使用 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 −

Granting Various Privileges

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

We know that the MySQL GRANT statement allows a wide range of privileges to user accounts. Here is a list of some commonly used privileges that can be granted using the GRANT statement −

Privileges

Description

ALTER

Allows users to modify table structures using the ALTER TABLE statement.

CREATE

Grants the ability to create new objects such as tables and databases.

DELETE

Enables users to delete rows from tables.

INSERT

Allows users to insert new records into tables.

SELECT

Provides read access to tables, allowing users to retrieve data.

UPDATE

Allows users to modify existing data in tables.

SHOW DATABASES

Grants the ability to see a list of available databases.

CREATE USER

Allows users to create new MySQL user accounts.

GRANT OPTION

Provides users with the authority to grant privileges to other users.

SUPER

Grants high-level administrative privileges.

SHUTDOWN

Allows users to shut down the MySQL server.

REPLICATION CLIENT

Provides access to replication-related information.

REPLICATION SLAVE

Enables users to act as a replication slave server.

FILE

Grants permission to read and write files on the server’s file system.

CREATE VIEW

Allows users to create new database views.

CREATE TEMPORARY TABLES

Allows the creation of temporary tables.

EXECUTE

Enables users to execute stored procedures and functions.

TRIGGER

Provides the ability to create and manage triggers.

EVENT

Grants the ability to create and manage events.

SHOW VIEW

Allows users to see the definition of views.

INDEX

Enables users to create and drop indexes on tables.

PROXY

Provides the capability to proxy or impersonate other users.

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

To GRANT all the available privileges to a user, you need to use the 'ALL' keyword in the GRANT statement −

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

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

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

Query OK, 0 rows affected (0.13 sec)

Granting Privileges on Stored Routines

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

To grant privileges on stored routines, such as tables, procedures or functions, in MySQL, you need to specify the object type (PROCEDURE or FUNCTION) after the ON clause followed by the name of the routine.

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

You can grant ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges on these stored routines.

Example

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

Assume we have created a stored procedure and a stored function with the name 'sample' in the current database as follows −

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

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.34 sec)

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

After creating these stored routines, you can grant ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named 'test_user'@'localhost' as follows −

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

下面显示了产生的输出：

The output produced is as shown below −

Query OK, 0 rows affected (0.24 sec)

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

Now, the query below grants ALTER ROUTINE, EXECUTE privileges on the above created function to the user named 'test_user'@'localhost'.

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

以下是以上查询的输出：

Following is the output of the above query −

Query OK, 0 rows affected (0.15 sec)

Privileges to Multiple Users

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

You can grant privileges to multiple users. To do so, you need to provide the names of the objects or users separated by commas.

Example

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

Assume we have created a table named 'sample' and three user accounts using the CREATE statement as shown below.

Creating a table −

Creating a table −

CREATE TABLE sample (data VARCHAR(255));

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

We will get the output as shown below −

Query OK, 0 rows affected (3.55 sec)

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

Now, let us create the user accounts.

Creating User 'test_user1' −

Creating User 'test_user1' −

CREATE USER test_user1 IDENTIFIED BY 'testpassword';

获得的输出如下 −

The output obtained is as follows −

Query OK, 0 rows affected (0.77 sec)

Creating User 'test_user2' −

Creating User 'test_user2' −

CREATE USER test_user2 IDENTIFIED BY 'testpassword';

下面是生成的输出−

Following is the output produced −

Query OK, 0 rows affected (0.28 sec)

创建第 3 个用户 −

Creating the 3rd user −

Creating User 'test_user3' −

Creating User 'test_user3' −

CREATE USER test_user3 IDENTIFIED BY 'testpassword';

我们得到了如下输出 −

We get the output as follows −

Query OK, 0 rows affected (0.82 sec)

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

Following query grant SELECT, INSERT and UPDATE privileges on the tables 'sample1', 'sample2' and 'sample3' to to all three users ('test_user1', 'test_user2', and 'test_user3') using a single GRANT statement.

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

Output

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

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

Query OK, 0 rows affected (0.82 sec)

Global Privileges

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

Instead of specifying the table, procedure or a function you can grant global privileges: privileges that apply to all databases to a user. To do so, you need to use . after the ON clause.

Example

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

Following query grants SELECT, INSERT and UPDATE privileges on all databases to the user named 'test_user'@'localhost' −

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

Output

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.43 sec)

Example

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

Similarly, following query grants all privileges on all the databases to the 'test_user'@'localhost −

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

Output

下面显示了产生的输出：

The output produced is as shown below −

Query OK, 0 rows affected (0.41 sec)

Database Level Privileges

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

You can grant privileges to all the objects in a database by specifying the database name followed by ".*" after the ON clause.

Example

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

Following query grants SELECT, INSERT and UPDATE privileges on all objects in the database named test to the user 'test_user'@'localhost' −

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

Output

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

Following is the output of the above code −

Query OK, 0 rows affected (0.34 sec)

Example

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

Similarly, following query grants all privileges on all the databases to the 'test_user'@'localhost −

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

Output

上述代码的输出如下：

Output of the above code is as follows −

Query OK, 0 rows affected (0.54 sec)

Column Level Privileges

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

You can grant privileges on a specific column of a table to a user. To do so, you need to specify the column names after the privileges.

Example

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

Assume we have created a table named Employee using the CREATE query as −

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

下面显示了产生的输出：

The output produced is as shown below −

Query OK, 0 rows affected (6.47 sec)

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

Following query grants SELECT privilege to the user named 'test_user'@'localhost' on the ID column and INSERT and UPDATE privileges on the columns Name and Phone of the Employee table −

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

获得的输出如下 −

The output obtained is as follows −

Query OK, 0 rows affected (0.54 sec)

Proxy User Privileges

您可以通过向用户授予 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

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

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

CREATE USER sample_user, proxy_user IDENTIFIED BY 'testpassword';

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.52 sec)

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

The following query grants SELECT and INSERT privileges on the Employee table created above to the user sample_user −

GRANT SELECT, INSERT ON Emp TO sample_user;

我们得到了如下输出 −

We get the output as shown below −

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 output is as follows −

Query OK, 0 rows affected (1.61 sec)

Granting Roles

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

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 的角色。

Let us start by creating a role named TestRole_ReadOnly.

CREATE ROLE 'TestRole_ReadOnly';

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.13 sec)

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

Now, let us grant read only privilege to the created role using the GRANT statement for accessing all objects within the database −

GRANT SELECT ON * . * TO 'TestRole_ReadOnly';

此 GRANT 语句的输出应该是 -

The output of this GRANT statement should be −

Query OK, 0 rows affected (0.14 sec)

然后，您可以将创建的角色授予特定用户。首先，您需要像下面那样创建用户 -

Then, you can GRANT the created role to a specific user. First, you will need to create the user as shown below −

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

下面是生成的输出−

Following is the output produced −

Query OK, 0 rows affected (0.14 sec)

现在，您可以将“TestRole_ReadOnly”角色授予“newuser”@“localhost” -

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

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

获得的输出如下所示 −

The output obtained is as shown below −

Query OK, 0 rows affected (0.13 sec)

Granting Privileges Using a Client Program

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

Now, let us see how to grant privileges to a MySQL user using the client program.

Syntax

以下是语法 −

Following are the syntaxes −

Example

以下是这些程序 −

Following are the programs −