mysql5.7 create user authorization delete user revoke authorization

1. Create a user:

Order:

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

Description: username - the username you will create, host - specifies the host on which the user can log in. If it is a local user, you can use localhost. If you want the user to log in from any remote host, you can use a wildcard. Password - the login password of the user. The password can be empty. If it is empty, the user can log in to the server without a password.

example:

 CREATE USER 'dog'@'localhost' IDENTIFIED BY 'password';
    CREATE USER 'pig'@'192.168.1.100' IDENDIFIED BY 'password';
    CREATE USER 'pig'@'192.168.1.%' IDENDIFIED BY 'password';
    CREATE USER 'pig'@'%' IDENTIFIED BY 'password';
    CREATE USER 'pig'@'%' IDENTIFIED BY '';
    CREATE USER 'pig'@'%';

2. Authorization:

Order:

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

Note: privileges - user's operation privileges, such as SELECT, INSERT, UPDATE, etc. (see the end of this article for a detailed list). If you want to grant all privileges, use ALL.; databasename - database name, tablename - table name. If you want to grant the user corresponding operation privileges for all databases and tables, you can use * to represent it, such as *.*.

example:

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

Note: The user authorized by the above command cannot authorize other users. If you want to allow the user to authorize, use the following command:

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

Privilege information is stored in the MySQL database (that is, in a database named mysql) using the user, db, host, tables_priv, and columns_priv tables.

Permission column Context

select Select_priv table

insert Insert_priv table

update Update_priv table

delete Delete_priv table

index Index_priv table

alter Alter_priv table

create Create_priv database, table, or index

drop Drop_priv database or table

grant Grant_priv database or table

references References_priv database or table

reload Reload_priv Server management

shutdown Shutdown_priv Server Management

process Process_priv Server Management

file File_priv File access on the server

3. Setting and changing user password

Order:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword'); If it is the current logged in user, use SET PASSWORD = PASSWORD("newpassword");

example:

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

4. Revoke user permissions

Order:

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

Note: privilege, databasename, tablename - Same as the authorization part.

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

Note: If you GRANT SELECT ON test.user TO 'pig'@'%', then REVOKE SELECT ON *.* FROM 'pig'@'%'; GRANT SELECT ON *.* TO 'pig'@'%'; ';, then REVOKE SELECT ON test.user REVOKE SELECT ON test.user FROM 'pig'@'% '; command cannot revoke the user's Select permission on the user table in the test database.

Detailed information can be viewed using the command SHOW GRANTS FOR 'pig'@'%'; ;.

5. Delete User

Order:

DROP USER 'username'@'host';

6. Check the user's authorization

mysql> show grants for 'test01'@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test01@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test01'@'localhost' |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test001`.* TO 'test01'@'localhost' |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> show grants for 'test02'@'localhost'; 
+-------------------------------------------------------------+
| Grants for test02@localhost |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test02'@'localhost' |
| GRANT ALL PRIVILEGES ON `test001`.* TO 'test02'@'localhost' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

The above is what I introduced to you about MySQL 5.7 creating user authorization, deleting user authorization, and revoking authorization. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!