Detailed introduction to the sample code of mysql5.7 to create user authorization, delete user and revoke authorization

This article mainly introducesmysql5.7 Creating user authorizationDeletingThe method of user revoking authorization is very good and has reference value. Friends who need it can refer to it

1. Create user:

Command:

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

Description: username - the username you will create, host - specify the On which host can the user log in? If it is a local user, localhost can be used. If you want the user to log in from any remote host, you can use the wildcard character %. password - the user's login password. 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:

Command:

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

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

Example:

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 the user to be able to authorize, use the following command:

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

Permission information Tables with user, db, host, tables_priv and columns_priv are stored in MySQL database (that is, in a database named mysql).

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 password

Command:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用
SET PASSWORD = PASSWORD("newpassword");

Example:

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

4. Revoke user permissions

Command:

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

Description: privilege, databasename, tablename - same Authorization part.

Example:

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

Note: If you authorize user 'pig'@'%' like this (or similar): GRANT SELECT ON test.user TO 'pig'@'%', then using the REVOKE SELECT ON *.* FROM 'pig'@'%'; command cannot revoke the user's access to the user in the test database SELECT operation of the table. On the contrary, if the authorization is GRANT SELECT ON *.* TO 'pig'@'%'; then REVOKE SELECT ON test.user FROM 'pig'@'% ';The command cannot revoke the user's Select permission on the user table in the test database.

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

##5. Delete user

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 is the detailed content of Detailed introduction to the sample code of mysql5.7 to create user authorization, delete user and revoke authorization. For more information, please follow other related articles on the PHP Chinese website!