Mysql 简明教程

MySQL - Create Users

在 MySQL 中，你可以创建多个用户帐户以访问数据库，每个帐户都有特定的身份验证详细信息，如密码。可以使用 SQL 语句为这些用户授予特定权限，如 CREATE USER 用于在创建新用户时的身份验证，以及 GRANT 和 REVOKE 分别用于分配和移除管理权限。

In MySQL, you can create multiple user accounts to access the database, each with specific authentication detail such as password. These users can be granted specific privileges using SQL statements like CREATE USER for authentication when creating a new user, and GRANT and REVOKE for assigning and removing administrative privileges, respectively.

The MySQL CREATE USERS Statement

我们可以使用 MySQL 中的 CREATE USER 语句创建新用户帐户。要执行此语句，当前帐户必须具有 CREATE USER 权限或 MySQL 系统架构的 INSERT 权限。

We can create a new user account using the CREATE USER Statement in MySQL. To execute this statement, the current account must have the CREATE USER privilege or the INSERT privilege for the MySQL system schema.

Syntax

以下是 MySQL CREATE USER 语句的语法 -

Following is the syntax of the MySQL CREATE USER statement −

CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'password';

其中，

Where,

user_name is the name of the user you need to create.
hostname specifies the host from which the user can connect.
password is the user’s password.

Example

在以下查询中，我们创建了一个名为“sample”的用户，该用户只能从“localhost”主机连接，并将其密码设置为“123456”。确保你已使用具有管理员权限（root）的用户登录 -

In the following query, we are creating a user named 'sample' who can only connect from the 'localhost' host and sets their password as '123456'. Make sure that you have logged in with a user with admin privileges (root) −

CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';

Output

输出将显示为：

The output will be displayed as −

Query OK, 0 rows affected (0.12 sec)

Verification

你可以使用以下查询验证用户列表 −

You can verify the list of users using the following query −

SELECT USER FROM MySQL.USER;

表将显示如下 -

The table will be displayed as shown below −

Granting Privileges in MySQL

你可以使用 GRANT ALL 语句授予已创建用户所有权限。这让你可以为用户授予访问数据库、表格的特定权限，以及在其中执行诸如 SELECT、INSERT 或 DELETE 等操作的权限。

You can grant all privileges to the created user using the GRANT ALL statement. This allows you to give specific permissions to users for actions like accessing databases, tables, and performing operations, such as SELECT, INSERT, or DELETE, on them.

Syntax

以下是授予 MySQL 中所有权限的语法 -

Following is the syntax to grant all privileges in MySQL −

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Example

以下查询授予用户“sample”在从“localhost”主机连接时对任何数据库或表执行任何操作的完全权限，从而在本地完全控制 MySQL 服务器 -

The following query grants the user 'sample' full privileges to perform any action on any database or table when connecting from the 'localhost' host, giving complete control over the MySQL server locally −

GRANT ALL PRIVILEGES ON * . * TO 'sample'@'localhost';

Output

输出将显示为：

The output will be displayed as −

Query OK, 0 rows affected (0.02 sec)

Logging as a Different User

要在 MySQL 中以不同的用户身份登录，如果你已经登录，你应该首先退出当前 MySQL 会话，然后在系统的命令提示符或终端中执行命令 -u user_name -p ，而不是在 MySQL 外壳中执行。

To log in as a different user in MySQL, you should first exit the current MySQL session if you are already logged in and then execute the command -u user_name -p in your system’s command prompt or terminal, not within the MySQL shell itself.

Example

在此，我们执行 -u sample -p 命令。运行该命令后，系统将提示你输入指定用户的密码。输入正确的密码以按如下所示登录 -

Here, we are executing the -u sample -p command. After running the command, you will be prompted to enter the password for the specified user. Enter the correct password to log in as shown below −

mysql -u sample -p
Enter password: ******

Output

这将使你以样本用户登录，并具有适当的权限和许可，如下所示 -

This will log you in as the sample user with the appropriate privileges and permissions as shown below −

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

The Expire Clause

如果你使用 expire 子句，旧密码（当前密码）将立即过期，用户需要在首次连接时选择新密码。

If you use the expire clause, the old password (current password) will expire immediately and the user need to choose new password at first connection.

Example

在此，我们首先移除现有用户“sample'@'localhost' -

Here, we are first removing the existing user 'sample'@'localhost' −

DROP user sample@localhost;

我们现在创建一个新用户“sample'@'localhost'，密码为“MyPassword”，同时立即使密码过期，强制用户在首次登录时设置新密码 -

We are now creating a new user 'sample'@'localhost' with the password 'MyPassword' while immediately expiring the password, forcing the user to set a new password upon the first login −

CREATE USER 'sample'@'localhost'
IDENTIFIED BY 'MyPassword' PASSWORD EXPIRE;

现在，如果你以新创建的用户身份登录，将生成一个错误。因此，要以新创建的用户身份登录，请在 MySQL 目录的 bin 文件夹中浏览命令提示符并执行以下命令 -

Now, if you log in as a newly created user, an error will be generated. So, to login as newly created user, open command prompt browse through bin folder of the MySQL directory and execute the following command −

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -u sample@localhost -p
Enter password: **********

此时执行任何 MySQL 命令都会触发以下错误消息 -

Any MySQL command execution at this point will trigger an error message as shown below −

select now();

获得的输出如下所示 −

The output obtained is as shown below −

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

密码已过期，因此会生成上述错误消息。要解决此问题，我们需要使用以下命令更改（重置）密码：

Since the password is expired, the above error message is generated. To make this right we need to change (reset) the password using the following command −

SET PASSWORD='passwordtest';

下面是生成的输出−

Following is the output produced −

Query OK, 0 rows affected (0.34 sec)

您还可以设置 EXPIRE 子句的间隔以实施定期密码更改，如下所示：

You can also set an interval for the EXPIRE clause to implement periodic password changes as shown below −

DROP user sample@localhost;
CREATE USER 'sample'@'localhost'
   IDENTIFIED BY 'MyPassword'
   PASSWORD EXPIRE INTERVAL 25 DAY
   FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

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

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

Query OK, 0 rows affected (0.20 sec)

User Comment

在 MySQL 中创建用户时，可以使用 COMMENT 子句为用户添加注释。这提供了有关用户的附加信息或上下文。

You can add comments to the user while creating a user in MySQL using the COMMENT clause. This provides additional information or context about the user.

Example

在以下示例中，我们首先删除现有的 'sample'@'localhost' 用户。然后，我们创建一个新的 'sample'@'localhost' 用户，同时添加一个注释来描述用户：

In the following example, we are first removing the existing 'sample'@'localhost' user. Then, we are creating a new 'sample'@'localhost' user while adding a comment to describe the user −

drop user sample@localhost;
CREATE USER 'sample'@'localhost' COMMENT 'Sample information';

Output

获得的结果如下所示 −

The result obtained is as shown below −

Query OK, 0 rows affected (0.10 sec)

Verification

您可以使用以下 SELECT 查询验证属性和注释信息：

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER='sample' AND HOST='localhost';

生成的结果如下所示 −

The result produced is as shown below −

User Attribute

在 MySQL 中为用户创建用户帐户时，可以使用 ATTRIBUTE 子句为用户添加属性。这些属性可以存储有关用户的附加信息。

You can add attributes to a user in MySQL using the ATTRIBUTE clause when creating a user account. These attributes can store additional information about the user.

Example

在其中，我们首先删除现有的 'sample@localhost' 用户。然后，我们创建一个新的 'sample'@'localhost' 用户，将属性 'attr1' 和 'attr2' 分别设置为 'val1' 和 'val2'，并将其与用户帐户相关联：

In here, we are first removing the existing 'sample@localhost' user. Then, we are creating a new 'sample'@'localhost' user with attributes 'attr1' and 'attr2' set to 'val1' and 'val2,' respectively, associated with the user account −

DROP user sample@localhost;
CREATE USER 'sample'@'localhost'
ATTRIBUTE '{"attr1": "val1", "attr2": "val2"}';

获得的结果如下所示 −

The result obtained is as shown below −

Output

Query OK, 0 rows affected (0.09 sec)

Verification

您可以使用以下 SELECT 查询验证属性和注释信息：

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER='sample' AND HOST='localhost';

获得的结果如下所示 −

The result obtained is as shown below −

The IF NOT EXISTS Clause

如果您尝试创建具有现有名称的用户，则会生成错误。为防止此错误并确保仅在用户不存在的情况下创建用户，您可以使用“IF NOT EXISTS”子句。

If you try to create a user with an existing name, an error will be generated. To prevent this error and ensure the user is created only if it does not already exist, you can use the "IF NOT EXISTS" clause.

Example

在下面的示例中，我们正在创建一个名为 'sample@localhost' 的用户，而不使用“IF NOT EXISTS”子句：

In the example below we are creating a user 'sample@localhost' without the "IF NOT EXISTS" clause −

CREATE USER 'sample@localhost';

我们可以在下面的输出中看到生成了一个错误：

We can see in the below output that an error is generated −

ERROR 1396 (HY000): Operation CREATE USER failed for 'sample@localhost'@'%'

但是，如果我们在 CREATE 语句中使用“IF NOT EXISTS”子句，则将会创建一个新用户，如果具有给定名称的用户已经存在，则将忽略查询：

However, if we use the "IF NOT EXISTS" clause along with the CREATE statement, a new user will be created, and if a user with the given name already exists, the query will be ignored −

CREATE USER IF NOT EXISTS 'sample@localhost';

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Creating User Using a Client Program

除了使用 MySQL 查询在 MySQL 数据库中创建用户之外，还可以使用客户端程序创建。

In addition to creating a user into MySQL Database using the MySQL query, we can also create using a client program.

Syntax

以下是语法，用于在各种编程语言中创建 MySQL 用户：

Following are the syntaxes to create a MySQL user in various programming languages −

Example

以下是用于在 MySQL 中创建用户的客户端程序：

Following are the client programs to create an user in MySQL −