Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to write the sql statement about adding a mysql user name and password. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Add user name and password to 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 rights.

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 and add usernames and passwords.

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

Add a user to the mysql.user table and add a user name and password.

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

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.

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

3) 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 the execution process is 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".

two。 Create a new user using the INSERT statement

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

The password field in the user table of MySQL 5.7has changed from Password to authentication_string. If you are using a version prior to MySQL 5.7, 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 there are no default values for ssl_cipher, x509_issuer, and x509_subject in the user table of the mysql database, the values of these three fields must be set when inserting new records into the user table, otherwise the INSERT statement will not be executed.

Example 3

Let's use the INSERT statement to create a user named test2 with a hostname of localhost and a password of test2. The SQL statement and execution procedure 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)

The results show that the new user is successful. However, if you log in to the MySQL server through this account, the login will not be successful, because the test2 user has not yet taken effect.

You can use the FLUSH command to give effect to the user, as follows:

FLUSH PRIVILEGES

Use the above command to let MySQL refresh the system permission related table. RELOAD permission is required to execute the FLUSH command.

Note: the User and Host fields in the user table are case sensitive, and the correct user name or hostname should be specified when creating the user.

3. Create a new user using the Grant statement

Although both CREATE USER and INSERT INTO statements can create ordinary users, it is not convenient to grant user permissions in both ways. 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 user [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

The user 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 4

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.

This is the end of the sql statement about adding a mysql user name and password. I hope the above can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

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.