Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to increase users in mysql". In daily operation, I believe many people have doubts about how to increase users in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to increase users in mysql". Next, please follow the editor to study!

Ways to add users: 1, use "CREATE USER user IDENTIFIED BY 'password';" statement; 2, use "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 is created by default, which has super privileges to control the entire MySQL server.

In the daily management and operation of MySQL, in order to avoid malicious use of root users to control the database, we usually create some users with appropriate permissions to log on to the system with root users as much as possible, so as to ensure the secure access of data.

MySQL provides the following three ways to create users.

Create a user using a CREATE USER statement

Add users to the mysql.user table

Create a user using a GRANT statement

These three methods are explained in detail according to the examples below.

1. Create a user using a CREATE USER statement

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

CREATE USER [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD]' password']]

The parameters are described as follows:

1) user

Specify to create a user account in the format of user_name'@'host_name. Here user_name is the user name and host_name hostname, that is, the name of the host that the user uses to connect to MySQL. If only the user name is given and no hostname is specified during the creation process, the hostname defaults to "%", which means a group of hosts, that is, permissions are open to all hosts.

3) IDENTIFIED BY clause

Used to specify the user password. A new user may not have an initial password. If the user does not set a password, this clause can be omitted.

2) PASSWORD 'password'

PASSWORD means to set the password with a hash value, which is optional. If the password is an ordinary string, the PASSWORD keyword is not required.' Password' indicates the password the user uses when logging in, which needs to be enclosed in single quotation marks.

The following points should be noted when using CREATE USER statements:

The CREATE USER statement may not specify the initial password. But from a security point of view, this approach is not recommended.

You must have INSERT permission or global CREATE USER permission for the mysql database to use the CREATE USER statement.

After you create a user using the CREATE USER statement, MySQL adds a new record to the user table in the mysql database.

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

Newly created users have very few permissions, and they can only perform operations that do not require permissions. Such as logging into MySQL, using SHOW statements to query the list of all storage engines and character sets, and so on. If two users have the same user name but different host names, MySQL treats them as two users and allows different permission sets to be assigned to the two users.

Example 1

Use CREATE USER to create a user with the user name test1, password test1, and hostname localhost. The SQL statement and execution procedure 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 created successfully.

In practical application, we should avoid specifying a password in clear text, and we can use the hash value of the password to set the password through the PASSWORD keyword.

Example 2

In MySQL, you can use the password () function to get 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. The following user test1,SQL statement is created and executed as follows:

Mysql > CREATE USER 'test1'@'localhost'IDENTIFIED BY PASSWORD' * 06C0BF5B64ECE2F648B5F048A71903906BA08E5C inquiry OK, 0 rows affected, 1 warning (0 sec)

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

two。 Create a new user using the Grant statement

Although CREATE USER can create ordinary users, it is not convenient to grant user permissions in this way. So MySQL provides the GRANT statement.

The basic syntax for creating a user using the GRANT statement is as follows:

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

Where:

The priv_type parameter indicates the permissions of the new user

The database.table parameter indicates the scope of permissions of the new user, that is, you can only use your own permissions on the specified database and table

Parameter specifies the account number of the new user, which consists of user name and host name

IDENTIFIED BY keyword is used to set the password

The password parameter represents the password of the new user.

Example 3

Let's use the GRANT statement to create a user named test3 with a host name of localhost and a password of test3. The user has SELECT permissions on all tables in all databases. The SQL statement and execution procedure are as follows:

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

Where "*. *" represents all tables under all databases. The results show that the user was created successfully and that the test3 user has query (SELECT) permissions on all tables.

Tip: the GRANT statement is a very important statement in MySQL, which can be used to create users, change user passwords, and set user permissions. Later in the tutorial, you will learn more about how to use GRANT statements to change passwords and permissions.

At this point, the study on "how to increase the number of users in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.