Blog.csdn.net

Create a user

CREATE USER 'usernamexxx'@'hostxxx' IDENTIFIED BY 'passwordxxx';
Copy the code

Description:

  • Hostxxx: Specifies the host on which the user can log in. If the user is a local user, use localhost. If the user wants to log in from any remote host, use the wildcard character %
  • Passwordxxx: indicates the login password of the user. The password can be blank. If the password is blank, the user can log in to the server without the password

Example:

CREATE USER 'jack'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'rose'@'192.168.38.110 _' IDENDIFIED BY '123456';
CREATE USER 'rose'@The '%' IDENTIFIED BY '123456';
CREATE USER 'rose'@The '%' IDENTIFIED BY ' ';
CREATE USER 'rose'@The '%';
Copy the code

Note:

After a user is created, the user can only connect to the database server but does not have the permission to operate the database server.

authorization

GRANT privilegesxxx ON databasenamexxx.tablenamexxx TO 'usernamexxx'@'hostxxx'
Copy the code

Description:

  • Privilegesxxx: the operation of the user permissions, such as SELECT, INSERT, UPDATE, etc., if you want to grant permissions is used by ALL

  • Databasenamexxx: indicates the databasename

  • Tablenamexxx: Indicates the name of the table, which can be denoted by * if the user is to be granted appropriate operation rights on all databases and tables, as shown in.

Example:

GRANT SELECT.INSERT ON DbXXX.user TO 'jack'@The '%';
GRANT ALL ON *.* TO 'jack'@The '%';
GRANT ALL ON DbXXX.* TO 'jack'@The '%';
Copy the code

Note:

  1. After authorization, you need to connect to MySQL again to obtain the corresponding permission.

  2. The user who is authorized by using the preceding command cannot authorize other users. To enable this user to be authorized, run the following command:

GRANT privilegesxxx ON databasenamexxx.tablenamexxx TO 'usernamexxx'@'hostxxx' WITH GRANT OPTION;
Copy the code

Set and change user passwords

SET PASSWORD FOR 'usernamexxx'@'hostxxx' = PASSWORD('newpasswordxxx');
Copy the code

If the user is currently logged in:

SET PASSWORD = PASSWORD("newpasswordxxx");
Copy the code

Example:

SET PASSWORD FOR 'jack'@The '%' = PASSWORD("123456");
Copy the code

Revoking user Rights

REVOKE privilegexxx ON databasenamexxx.tablenamexxx FROM 'usernamexxx'@'hostxxx';
Copy the code

Example:

REVOKE SELECT ON *.* FROM 'jack'@The '%';
Copy the code

Note:

GRANT SELECT ON test.user TO ‘jack’@’%’; REVOKE SELECT ON. FROM ‘jack’@’%’; This command does not undo the user’s SELECT operation on the user table in the test database. GRANT SELECT ON. TO ‘jack’@’%’; REVOKE SELECT ON test.user FROM ‘jack’@’%’; This command cannot revoke the Select permission of a user on the user table in the test database. GRANTS FOR ‘jack’@’%’; Look at it.

Delete user

DROP USER 'usernamexxx'@'hostxxx';
Copy the code