Create user and authorization in Mysql, create user authorization in Mysql

Create user and authorization in Mysql, create user authorization in Mysql

System Management Account logon to the system greatly reduces the security of the system. Therefore, to use computers more securely, we usually create a new user to log on to the system. MySQL is no exception. Create a user and authorization Implementation Method in MySQL. For friends who are new to mysql, refer!

Create

There are three methods for creating a user in Mysql: The INSERTUSER table method, the CREATEUSER 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 CREATEUSER command

Script: CREATEUSER 'username' @ 'host' [IDENTIFIEDBY 'Password'] Where the PASSword is optional;

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:

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

Example: mysql> grantselect, insert, update, delete, create, dropontest.hrtojohn@192.168.10.1identifiedby '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> grantallprivilegesontest. * tojoe@192.168.10.1identifiedby '123 ';

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> grantallprivilegeson *. * tojohn@192.168.10.1identifiedby '123 ';

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> grantallprivilegeson *. * tojohn @ localhostidentifiedby '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> insertintouser (host, user, password) values ('%', 'john', password ('123 '));

View permissions:

Showgrantsfor your user;

Showgrantsforroot @ 'localhost ';

Showgrantsforwebgametest@10.3.18.158;

Showcreatedatabasedbname; you can see some parameters used to create a database.

Showcreatetabletickets; you can see some parameters used in table creation.

Revoke permissions:

Revokeallon *. * fromdba @ localhost;

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

Mysql> flushprivileges;

Although there are three ways to create a user, individuals prefer the second method, one step in place, simple and clear; the other two methods only help to understand the principles of the database;

Authorization:

Command: GRANTprivilegesONdatabasename. tablenameTO 'username' @ 'host'

Pri: privileges-operation permissions of users, such as SELECT, INSERT, and UPDATE (for detailed list, see the end of this Article ). use ALL .; databasename-Database Name, tablename-table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, as shown in *. *.

Example: GRANTSELECT, INSERTONtest. userTO 'pig' @ '% ';

GRANTALLON *. * TO 'pig' @ '% ';

Note: The user authorized with the preceding command cannot authorize other users. to authorize the user, run the following command:

GRANTprivilegesONdatabasename. tablenameTO 'username' @ 'host' WITHGRANTOPTION;