MySQL new user and assign permissions under command line

To create a user:

' username '@'host'password';

Description: username– You will create the user name, host– specifies which host on which the user can log on, if localhost is available to local users, you can use the wildcard% if you want the user to be able to log on from any remote host. password– The user's login password, the password can be empty, if it is empty, the user can not require a password to log on to the server.

Example:

CREATE USER'Dog'@'localhost'Identified by'123456'; CREATE USER'Pig'@'192.168.1.101_'Idendified by'123456'; CREATE USER'Pig'@'%'Identified by'123456'; CREATE USER'Pig'@'%'Identified by"'; CREATE USER'Pig'@'%';

Authorized:

' username '@'host'

Description: privileges– user's operation permissions, 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 *. *.

' Pig '@'%'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:

' username '@'host' with GRANT OPTION;

setting and changing user passwords

' username '@'host' = PASSWORD ('newpassword');

If the user is currently logged in

SET PASSWORD = PASSWORD ("newpassword");

Revoke user Rights

' username '@'host';

Description: Privilege, DatabaseName, tablename– with the authorization section.

' Pig '@'%';

Note: If you are authorizing the user ' pig ' @ '% ' (or similar): Grant SELECT on Test.user to ' pig ' @ '% ', then use REVOKE Select On *. * from ' pig ' @ '% '; command It is not possible to revoke the user's select operation on the Users table in the test database.  Conversely, if the authorization is to use the grant SELECT On *. * to ' pig ' @ '% '; then REVOKE select on Test.user the ' pig ' @ '% '; the command cannot revoke the user's SELECT from the users table in the test database Permissions.

Specific information can be used with the command show GRANTS for ' pig ' @ '% '; View.

Delete User

DROP USER ' username ' @'host ';

A typical database-building table that builds the user process:

Create a user for a localhost connection and specify a password for MySQL> Create User'pcom'@'localhost'Identified by'aaa7b2249'; Query OK,0Rows Affected (0.00sec) Create database MySQL>CREATE DATABASE pcom default character set UTF8 collate utf8_bin; Query OK,1Row affected (0.00sec) to authorize local users, there is no need to specify the password MySQL> Grant all on pcom.* to'pcom'@'localhost'; Query OK,0Rows Affected (0.00sec) to authorize users under other IP addresses, note: You must specify a password here, otherwise you can access MySQL without a password> Grant all on pcom.* to'pcom'@'192.168.0.0/255.255.0.0'Identified by'aaa7b2249'; Query OK,0Rows Affected (0.00sec) similarly MySQL> Grant all on pcom.* to'pcom'@'172.20.0.0/255.255.0.0'Identified by'aaa7b2249'; Query OK,0Rows Affected (0.00Sec

Finally, be sure to refresh the database

Flush privileges;

MySQL new user and assign permissions under command line