1.mysql creates a read-only account
Create users through GRANT (create users and authorize them at the same time):
GRANT SElECT ON *.* TO 'reader'@'172.16.%.%' IDENTIFIED BY "reader123"*. * all libraries. If only one library is authorized, such as test, it is written as test*
reader is the user name
172.16%.% is the ip network segment that is allowed to connect to this database. Of course, if ip is not limited, it is written as%. Finally
Reader 123 is the connection password.
Permission specifier
Alter modify tables and indexes Create create database and table Delete delete existing records in the table Drop discards (deletes) databases and tables INDEX create or discard INDEX Insert inserts a new row into the table REFERENCE not used Records in the Select key Update modify existing table record FILE read or write files on the server PROCESS view the thread information executed in the server or kill the thread RELOAD reloads the authorization table or clears the log, host cache, or table cache. SHUTDOWN shut down the server ALL owned; synonyms ALL PRIVILEGES USAGE special "no permission" permission
To view creation records and permissions:
*************************** 1. row ***************************
Host: 172.16.%.%
User: reader
Password: *C40C2140D0511910F6142739820C4B7878E3F603
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
2 rows in set (0.00 sec)2. Recycling authority
view user permission
mysql> show grants for 'reader'@'172.16.%.%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for reader@172.16.%.% |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'reader'@'172.16.%.%' IDENTIFIED BY PASSWORD '*C40C2140D0511910F6142739820C4B7878E3F603' |
| GRANT SELECT ON `test`.* TO 'reader'@'172.16.%.%' |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)The same user can view permissions according to different authorization ranges:
mysql> show grants for 'reader'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for reader@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'reader'@'%' IDENTIFIED BY PASSWORD '*C40C2140D0511910F6142739820C4B7878E3F603' |
| GRANT SELECT ON `test`.* TO 'reader'@'%' |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)Recall authority
mysql> revoke SELECT ON `test`.* from 'fundreader'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'reader'@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for reader@% |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'reader'@'%' IDENTIFIED BY PASSWORD '*C40C2140D0511910F6142739820C4B7878E3F603' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
You can see that the permission recycling is completed
Reclaim grant permissions
mysql> show grants for 'fundread';
+---------------------------------------------------------------------------------------------------------+
| Grants for fundread@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fundread'@'%' IDENTIFIED BY PASSWORD '*27A6D9517F68EAB764F6150D85E78C827BE23FB6' |
| GRANT USAGE ON `test`.* TO 'fundread'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke grant option on `test`.* from 'fundread'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'fundread';
+---------------------------------------------------------------------------------------------------------+
| Grants for fundread@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fundread'@'%' IDENTIFIED BY PASSWORD '*27A6D9517F68EAB764F6150D85E78C827BE23FB6' |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)3. Delete user
Delete users according to different authorization scope
mysql> delete from user where user='reader' and host='host';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='reader' and host='172.16.%.%';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='reader' and host='%';
Query OK, 1 row affected (0.00 sec)