How to add users in mysql

Methods to add users: 1. Use the "CREATE USER user IDENTIFIED BY 'password';" statement; 2. Use the "GRANT priv_type ON database.table TO user IDENTIFIED BY 'password';" statement.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

When MySQL is installed, a user named root will be created by default. 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.

• Use the CREATE USER statement to create a user

• Add a user to the mysql.user table

• Use the GRANT statement to create users

The following explains 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 ] 'password' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

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.

3) 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.

2) 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" is the hash value of test1. Next, create user test1. The SQL statement and execution process are as follows:

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 with the password "test1".

2. Use the GRANT statement to create a new user

Although CREATE USER can create ordinary users, this method is inconvenient to grant user permissions. So MySQL provides the GRANT statement.

The basic grammatical form of using the GRANT statement to create a user is as follows:

GRANT priv_type ON database.table TO <用户> [IDENTIFIED BY [PASSWORD] 'password']

Among them:

• priv_typeThe parameter represents the new user's Permissions;

• database.tableThe parameter indicates the permission scope of the new user, that is, he can only use his own permissions on the specified database and table;

• The parameter specifies the account of the new user, which consists of the user name and host name;

• IDENTIFIED BY Thekeyword is used to set the password; the

• passwordparameter represents the password of the new user.

Example 3

The following uses the GRANT statement to create a user named test3. The host name is localhost and the password is test3. This user has SELECT permissions on all tables in all databases. The SQL statement and execution process are as follows:

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 语句修改密码、更改权限。

【相关推荐：mysql视频教程】

The above is the detailed content of How to add users in mysql. For more information, please follow other related articles on the PHP Chinese website!