MySQL Create user and authorization

System Management Account login system will greatly reduce the security of the system, so in order to more secure use of the computer, usually we will create a new user to log into the system, MySQL is no exception. MySQL in the creation of user and authorization implementation method, for the first contact with MySQL friends can refer to the next!

Create

MySQL creates users in three ways: the Insertuser table method, the CreateUser method, and the Grant method.

I. Form of account name

Account Composition: User name + host (so duplicate user names can appear, unlike other databases)

User name: within 16 characters.

Host Name: You can use the hostname and IP address, or you can use a wildcard character

Wildcard Description: 172.18.10.% (IP address is 172.18. All IP addresses in paragraph 10 are accessible)

Second, create the user through the CreateUser command

Script: CREATEUSER ' username ' @ ' host ' [Identifiedby ' PASSword '] where the password is optional;

Note: This method is created by the user only to connect to the database permissions, need to follow-up authorization;

Iii. creating a user with the grant command

Personal habits generally use this method to create a user, grant will authorize the user when the database exists, but when the database does not exist, the user will be created and authorized. (indicates that the above step is superfluous)

Script:

Description: Priv represents permission Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process, File, etc. 14 permissions

Example: Mysql>grantselect,insert,update,delete,create,[email protected] ' 123 ';

Description: Give the host 192.168.10.1 user John the ability to perform operations such as Select,insert,update,delete,create,drop on the HR table of the database test, and set the password to 123.

Mysql>grantallprivilegesontest.*[email protected] ' 123 ';

Description: Give the host 192.168.10.1 user John the ability to perform all operations on the database test all tables and set the password to 123.

Mysql>grantallprivilegeson*.*[email protected] ' 123 ';

Description: Give the host 192.168.10.1 user John the ability to perform all operations on all tables in all databases and set the password to 123.

Mysql>grantallprivilegeson*.*[email protected] ' 123 ';

Description: User John assigns permissions to all operations on all tables in all databases and sets the password to 123.

Iv. inserting records directly into the Mysql.user table (this method is rarely used by individuals)

Because the database user information is saved in mysql.user this table, so directly to the table INSERT statement, you can complete the user's creation;

Mysql>insertintouser (Host,user,password) VALUES ('% ', ' John ', Password (' 123 '));

To view permissions:

Showgrantsfor your users;

[email protected] ' localhost ';

[Email protected];

Showcreatedatabasedbname; This can see some of the parameters used to create the database.

Showcreatetabletickets; You can see some of the parameters used to create the table

Revoke permissions:

Revokeallon*.*[email protected];

After the completion of the user's creation, please remember to refresh the System permissions table;

mysql>flushprivileges;

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

Authorized:

Command: Grantprivilegesondatabasename.tablenameto ' username ' @ ' host '

Description: privileges-The user's operation rights, such as select,insert,update, etc. (see the last side of the article for a detailed list). Use all if you want to grant the permission. databasename-the database name, tablename-table name, if you want to grant the user permission to operate on all databases and tables, the * representation, such as *. *.

Example: Grantselect,insertontest.userto ' pig ' @ '% ';

Grantallon*.*to ' pig ' @ '% ';

Note: A user authorized with the above command cannot authorize another user, and if you want the user to be authorized to do so, use the following command:

Grantprivilegesondatabasename.tablenameto ' username ' @ ' host ' withgrantoption;

MySQL Create user and authorization