Three methods for creating a user in MySQL

Three methods for creating a user in MySQL

There are three methods for creating a USER in mysql: The insert user table method, the create user method, and the GRANT method.

I. Account Name Composition

Account composition: User Name + host (So duplicate user names can appear, different from other databases)

User name: within 16 characters.

Host Name: You can use the host name and IP address, or use wildcards.

Wildcard Description: 172.18.10.% (all IP addresses in the 172.18.10 segment can be accessed)

2. CREATE a USER using the create user command

Script: create user 'username' @ 'host' [identified by 'Password'] Where the PASSWORD is optional;

Example: create user 'john' @ '192. 168.189.71 'identified by "192 ";

Create user 'john' @ '192. 192.% 'identified by "168.189 ";

Create user 'john' @ '% ';

Note: The user created by this method only has the permission to connect to the database and needs to be further authorized;

3. Create a user using the GRANT command

This method is generally used to create a user. When a database has a user, GRANT the user permission. However, when the database does not exist, the corresponding user is created and authorized. (This step is redundant)

Script:

GRANT <ALL | priv1, priv2,... privn> ON

[Object] [identified by 'Password']

[With grant option];

MAX_QUERIES_PER_HOUR count

MAX_UPDATES_PER_HOUR count

MAX_CONNECTIONS_PER_HOUR count

MAX_USER_CONNECTIONS count

Note: priv represents 14 permissions, including select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, and file.

Example: mysql> grant select, insert, update, delete, create, drop on test. hr to john@192.168.10.1 identified by '20140901 ';

Note: john, the host user with 192.168.10.1, is assigned the select, insert, update, delete, create, and drop permissions on the hr table of the database test, and set the password to 123.

Mysql> grant all privileges on test. * to joe@192.168.10.1 identified by '20140901 ';

Note: Assign the user john with host 192.168.10.1 the permission to perform all operations on all tables in the database test and set the password to 123.

Mysql> grant all privileges on *. * to john@192.168.10.1 identified by '20140901 ';

Note: Assign the user john with host 192.168.10.1 the permission to perform all operations on all tables in all databases and set the password to 123.

Mysql> grant all privileges on *. * to john @ localhost identified by '20140901 ';

Note: john is assigned the permission to perform all operations on all tables in all databases and sets the password to 123.

4. insert records directly to the mysql. user table (this method is rarely used)

Because the user information of the database is stored in the mysql. user table, you can directly insert statements into the table to create the user;

Mysql> insert into user (host, user, password) values ('%', 'john', password ('123 '));

5. After creating a user, refresh the system permission table;

Mysql> flush privileges;

Conclusion: Although there are three ways to create a user, individuals prefer the second method, which is simple and clear in one step;

The other two methods are only helpful for understanding the principles of the database;

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: