MySQL Creating User Authorizing User Revoking User Rights Changing User Password Deleting User of Practical Skills

  • 2021-07-22 11:42:48
  • OfStack

MySQL Create User and Authorize and Revoke User Rights

Running environment: MySQL 5.0

1. Create a user

Command:


CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username-you will create the user name, host-specifies the user on which host can log in, if the local user can use localhost, if you want the user can log in from any remote host, you can use wildcard%. password-the user's login password, password can be empty, if empty, the user can not need a password to log in to mysql server.

Examples:


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'@'%';

2. Authorization

Command:


GRANT privileges ON databasename.tablename TO 'username'@'host';

Explanation: privileges-User's operation rights, such as SELECT, INSERT, UPDATE, etc. (See the last side of this article for a detailed list). If you want to grant the rights, use ALL.; databasename-database name, tablename-table name, which can be represented if you want to grant the user appropriate permissions on all databases and tables, such as.

Examples:


GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON .* TO 'pig'@'%';

Note: A user authorized with the above command cannot authorize other users. If you want this user to authorize, use 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 it is the current login user, use SET PASSWORD = PASSWORD ("newpassword");

Examples:


SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

Step 4 Revoke user rights

Command:


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

Description: privilege, databasename, tablename-the same license section.

Example: REVOKE SELECT ON. FROM 'pig' @ '%';

Note: You are using REVOKE SELECT ON. FROM pig @ '%' if you authorize user 'GRANT ON test. user TO' pig '@'% 'when you authorize user' pig '@'% '; Command does not undo the user's SELECT operation on the user table in the test database. On the contrary, if ES90SELECT ON is authorized. TO 'pig' @ '%'; Then
REVOKE SELECT ON test. user FROM 'pig' @ '%'; Command also does not revoke the user's Select permission on the user table in the test database.

For specific information, use the command SHOW GRANTS FOR 'pig' @ '%'; Check.

Step 5 Delete users

Command:


DROP USER 'username'@'host';

Related articles: