mysql 5.7 Create User Authorization Delete User Revocation Authorization

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 the server.

Examples:

 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'

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

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;

The permission information is stored in an MySQL database (i.e. in a database named mysql) with 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 Administration

shutdown Shutdown_priv Server Administration

process Process_priv Server Administration

file File_priv File Access on Server

3. Set and change user passwords

Command:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword'); If it is used by the current login user 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-Same license section.

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

Note: If you authorize the user 'pig' @ '%' like this (or something like that): GRANT SELECT ON test.user TO 'pig'@'%', You are using the REVOKE SELECT ON *.* FROM 'pig'@'%'; Command does not undo the user's SELECT operation on the user table in the test database GRANT SELECT ON *.* 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.

Specific information can be used in the command SHOW GRANTS FOR 'pig'@'%'; Check.

Step 5 Delete users

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)