How to add users to mysql database

Two methods to add users: 1. Use the CREATE USER statement to create a new user and set the corresponding password, the syntax is "CREATE USER user IDENTIFIED BY [PASSWORD] 'password']". 2. Use the GRANT statement to create a new user, with the syntax "GRANT user authority ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']".

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.

Two ways to add (create) users to mysql database

• Use the CREATE USER statement to create users

• Use the GRANT statement to create a user

1. Use the MySQL CREATE USER statement to create a new 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'];

1) User

specifies the creation of a user account, in the format username'@'hostname. 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.

Note: The password must be in clear text. MySQL encrypts passwords before saving user accounts to the user table.

For example, to create a new user to connect to the MySQL database server with password dbadmin, use the following statement: localhostsecretCREATE USER

CREATE USER dbadmin@localhost 
IDENTIFIED BY 'secret';

To view the permissions of a user account, use the following SHOW GRANTS statement:

SHOW GRANTS FOR dbadmin@localhost;

The *.* in the result indicates that the user account dbadmin can only log in to the database server and has no other permissions. To grant permissions to a user, use the GRANT statement.

Please note that the part before the dot (.) represents the database, and the part after the dot (.) represents the table, for example, database.table

To allow a user account to connect from any host, use the percent (%) wildcard character, as shown in the following example:

CREATE USER superadmin@'%'IDENTIFIED BY 'secret';

Percent wildcard character % has the same effect as used in the LIKE operator, for example, to allow the mysqladmin user account to connect from any subdomain of the begtut.com host To the database server, use the percent wildcard character % as shown below:

CREATE USER mysqladmin@'%.begtut.com'IDENTIFIED by 'secret';

Please note that you can also use the underscore wildcard character _ in the CREATE USER statement.

If you omit the hostname part of the user account, MySQL will accept it and allow the user to connect from any host. For example, the following statement creates a new user account named remote that can connect to the database server from any host:

CREATE USER remote;

You can see that the remote user account is granted Permissions, as follows:

SHOW GRANTS FOR remote;

If you accidentally reference user account 'username@hostname', MySQL will create a user username@hostname name and allows users to connect from any host, which may not be what you expect.

例如，以下语句创建一个api@localhost可以从任何主机连接到MySQL数据库服务器的新用户。

CREATE USER 'api@localhost';

SHOW GRANTS FOR 'api@localhost';

如果您创建一个已存在的用户，MySQL将发出错误。例如，以下语句创建一个remote已存在的名为的用户帐户：

CREATE USER remote;

MySQL发出以下错误消息：

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

注意：CREATE USER 语句只是一个没有权限的新用户帐户。如果要向用户授予权限，请使用GRANT 语句。

2、使用MySQL GRANT 语句新建用户

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

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

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

其中：

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

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

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

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

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

示例：

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

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

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