What is the statement to add mysql username and password?

Statements for adding user names and passwords in mysql: 1. CREATE USER statement, you can create an ordinary user and set the corresponding password; 2. INSERT statement, you can create a new ordinary user and set the corresponding password; 3. GRANT statement, create a new user, and set the corresponding password and user permissions.

(Recommended tutorial: mysql video tutorial)

When MySQL is installed, a file named root will be created by default. User, this user has super privileges and can control the entire MySQL server.

In the daily management and operation of MySQL, in order to prevent someone from maliciously using the root user to control the database, we usually create some users with appropriate permissions and use the root user as little or as little as possible to log in to the system. to ensure secure access to data.

MySQL provides the following 3 methods to create users, add user names and passwords.

• Use the CREATE USER statement to create a user and add a username and password.

• Add a user in the mysql.user table, add user name and password.

• Use the GRANT statement to create a user and add a username and password.

The following will explain these three methods in detail based on examples.

1. Use the CREATE USER statement to create a user

You can use the CREATE USER statement to create a MySQL user and set the corresponding password. The basic syntax format is as follows:

CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] &#39;password&#39; ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] &#39;password&#39; ]]

Parameter description is as follows:

1) User

specifies the creation of a user account, the format is user_name'@'host_name . Here user_name is the user name, and host_name is the host name, which is the name of the host used by the user to connect to MySQL. If only the user name is given without specifying the host name during the creation process, the host name defaults to "%", which represents a group of hosts, that is, permissions are open to all hosts.

2) IDENTIFIED BY clause

is used to specify the user password. New users do not need to have an initial password. If the user does not have a password, this clause can be omitted.

3) PASSWORD 'password'

PASSWORD means using a hash value to set the password. This parameter is optional. If the password is a plain string, there is no need to use the PASSWORD keyword. 'password' represents the password used by the user to log in and needs to be enclosed in single quotes.

You should pay attention to the following points when using the CREATE USER statement:

• The CREATE USER statement does not need to specify an initial password. However, from a security perspective, this approach is not recommended.

• To use the CREATE USER statement, you must have the INSERT permission of the mysql database or the global CREATE USER permission.

• After using the CREATE USER statement to create a user, MySQL will add a new record in the user table of the mysql database.

• The CREATE USER statement can create multiple users at the same time. Multiple users are separated by commas.

Newly created users have very few permissions and they can only perform operations that do not require permissions. For example, log in to MySQL, use the SHOW statement to query the list of all storage engines and character sets, etc. If two users have the same username but different hostnames, MySQL treats them as two users and allows the two users to be assigned different sets of permissions.

Example 1

Use CREATE USER to create a user, the username is test1, the password is test1, and the host name is localhost. The SQL statement and execution process are as follows.

mysql> CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';
Query OK, 1 rows affected (0.06 sec)

The results show that the test1 user was successfully created.

In practical applications, we should avoid specifying passwords in clear text. We can use the PASSWORD keyword to set the password using the hash value of the password.

Example 2

In MySQL, you can use the password() function to obtain the hash value of the password. The SQL statement and execution process for viewing the test1 hash value are as follows:

mysql> SELECT password('test1');
+-------------------------------------------+
| password('test1')                         |
+-------------------------------------------+
| *06C0BF5B64ECE2F648B5F048A71903906BA08E5C |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
“*06C0BF5B64ECE2F648B5F048A71903906BA08E5C”就是 test1 的哈希值。下面创建用户 test1，SQL 语句和执行过程如下：
mysql> CREATE USER 'test1'@'localhost'IDENTIFIED BY PASSWORD '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';
Query OK, 0 rows affected, 1 warning (0.00 sec)

After successful execution, you can log in using the password "test1".

2. Use the INSERT statement to create a new user

You can use the INSERT statement to add user information to the mysql.user table, but you must have INSERT permission on the mysql.user table. Usually the INSERT statement only adds the values ​​of the three fields Host, User and authentication_string.

The password field in the user table of MySQL 5.7 has changed from Password to authentication_string. If you are using a version before MySQL 5.7, just replace the authentication_string field with Password.

The code to create a user using the INSERT statement is as follows:

INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('hostname', 'username', PASSWORD('password'), '', '', '');

Since the three fields ssl_cipher, x509_issuer and x509_subject in the user table of the mysql database do not have default values, new records are inserted into the user table. , be sure to set the values ​​of these three fields, otherwise the INSERT statement will not be executed.

Example 3

The following uses the INSERT statement to create a user named test2. The host name is localhost and the password is also test2. The SQL statement and execution process are as follows:

mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('localhost', 'test2', PASSWORD('test2'), '', '', '');
Query OK, 1 row affected, 1 warning (0.02 sec)

结果显示，新建用户成功。但是这时如果通过该账户登录 MySQL 服务器，不会登录成功，因为 test2 用户还没有生效。

可以使用 FLUSH 命令让用户生效，命令如下：

FLUSH PRIVILEGES;

使用以上命令可以让 MySQL 刷新系统权限相关表。执行 FLUSH 命令需要 RELOAD 权限。

注意：user 表中的 User 和 Host 字段区分大小写，创建用户时要指定正确的用户名称或主机名。

3. 使用GRANT语句新建用户

虽然 CREATE USER 和 INSERT INTO 语句都可以创建普通用户，但是这两种方式不便授予用户权限。于是 MySQL 提供了 GRANT 语句。

使用 GRANT 语句创建用户的基本语法形式如下:

GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']

其中：

• priv_type 参数表示新用户的权限；

• database.table 参数表示新用户的权限范围，即只能在指定的数据库和表上使用自己的权限；

• user 参数指定新用户的账号，由用户名和主机名构成；

• IDENTIFIED BY 关键字用来设置密码；

• password 参数表示新用户的密码。

例 4

下面使用 GRANT 语句创建名为 test3 的用户，主机名为 localhost，密码为 test3。该用户对所有数据库的所有表都有 SELECT 权限。SQL 语句和执行过程如下：

mysql> GRANT SELECT ON*.* TO 'test3'@localhost IDENTIFIED BY 'test3';
Query OK, 0 rows affected, 1 warning (0.01 sec)

其中，“*.*” 表示所有数据库下的所有表。结果显示创建用户成功，且 test3 用户对所有表都有查询（SELECT）权限。

技巧：GRANT 语句是 MySQL 中一个非常重要的语句，它可以用来创建用户、修改用户密码和设置用户权限。教程后面会详细介绍如何使用 GRANT 语句修改密码、更改权限。

The above is the detailed content of What is the statement to add mysql username and password?. For more information, please follow other related articles on the PHP Chinese website!