Mysql5.7 create user authorization delete user revocation authorization mysql5.7 cancel

Mysql5.7 create user authorization delete user revocation authorization mysql5.7 cancel

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 '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:

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 'pig'@'%';    GRANT ALL ON *.* TO 'pig'@'%';

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;

Permission information is stored in the MySQL database 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. Set and change user passwords

Command:

Set password for 'username' @ 'host' = PASSWORD ('newpassword'); set password = PASSWORD ("newpassword") FOR the current login user ");

Example:

SET PASSWORD FOR 'pig'@'%' = 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 'pig' @ '% ';

Note: If you authorize the user 'pig' @ '%' like this (or similar ):GRANT SELECT ON test.user TO 'pig'@'%',In useREVOKE SELECT ON *.* FROM 'pig'@'%';The command does not allow the user to revoke the SELECT Operation on the user table in the test database.GRANT SELECT ON *.* TO 'pig'@'%';ThenREVOKE SELECT ON test.user FROM 'pig'@'%'; The command cannot revoke the user's Select permission on the user table in the test database.

You can use commands for specific information.SHOW GRANTS FOR 'pig'@'%'; View.

5. delete a user

Command:

DROP USER 'username'@'host';

6. View user 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 section describes how to grant the mysql5.7 user permission to delete a user and revoke the authorization. I hope this will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!