Mysql user Common Operations

Mysql user Common Operations

1. Create a user:
Command: create user 'username' @ 'host' identified by 'Password ';
Description: username-the username you will create, host-specifies the host on which the user can log on. If a local user can use localhost, if you want to allow the user to log on from any remote host, you can use the wildcard %. password-the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password.
Example: create user 'testuser' @ 'localhost' identified by '20140901 ';
Create user 'testuser' @ '192. 168.1.101 _ 'idendified BY '123 ';
Create user 'testuser' @ '%' identified by '201312 ';
Create user 'testuser' @ '%' identified '';
Create user 'testuser' @ '% ';
2. Authorization:

Command: GRANT privileges ON databasename. tablename TO '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: grant select, insert on test. user TO 'testuser' @ '% ';
Grant all on *. * TO 'testuser' @ '% ';

Note: The user authorized with the preceding command cannot authorize other users. to authorize the user, run the following command:
GRANT privileges ON databasename. tablename TO 'username' @ 'host' with grant option;

3. Set and change user passwords

Command: set password for 'username' @ 'host' = PASSWORD ('newpassword'); if the current login user uses set password = PASSWORD ("newpassword ");

Example: set password for 'testuser' @ '%' = PASSWORD ("123456 ");

Iv. Revoke User Permissions

Command: REVOKE privilege ON databasename. tablename FROM 'username' @ 'host ';

Note: privilege, databasename, tablename-same as the authorization section.

Example: revoke select on *. * FROM 'testuser' @ '% ';

Note: If you authorize the user 'pig' @ '%' like this (or similar): grant select on test. user TO 'testuser' @ '%', use revoke select on *. * FROM 'testuser' @ '%'; the command does not cancel the SELECT Operation on the user table in the test database. conversely, grant select on * is used for authorization *. * TO 'testuser' @ '%'; then revoke select on test. user FROM 'testuser' @ '%'; the command cannot revoke this user's Select permission on the user table in the test database.

FOR more information, run the show grants for 'testuser' @ '%' command.

5. delete a user

Command: drop user 'username' @ 'host ';