mysql授权

1.dml权限

mysql> grant select,delete,update,insert,create on netcentre.* to 'ln_sale'@'%' identified by 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show grants for 'ln_sale'@'%';
+--------------------------------------------------------------------------------+
| Grants for ln_sale@%                                                           |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ln_sale'@'%'                                            |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `netcentre`.* TO 'ln_sale'@'%' |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2. 查看information_schema权限

mysql> GRANT PROCESS, SUPER ON *.* TO 'infomn'@'%' IDENTIFIED BY 'infomn';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show grants for 'infomn'@'%';
+---------------------------------------------+
| Grants for infomn@%                         |
+---------------------------------------------+
| GRANT PROCESS, SUPER ON *.* TO 'infomn'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)

3.dml，函数，存储过程权限

mysql> grant alter routine,Create routine,execute,select,delete,update,insert,create on netcentre.* to 'ln_common'@'%' identified by 'password';                                                                 
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show grants for 'ln_common'@'%';
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ln_common'@'%'                                                                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `netcentre`.* TO 'ln_common'@'%' |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4.普通dba权限

mysql> grant all privileges on *.* to 'test'@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select * from mysql.user where user='test'\G;
*************************** 1. row ***************************Host: %User: testSelect_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: Y
Create_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29password_expired: Npassword_last_changed: 2023-08-05 00:04:37password_lifetime: NULLaccount_locked: N
1 row in set (0.00 sec)

5. dba权限

mysql> select * from mysql.user where user='root' and host='localhost'\G;
*************************** 1. row ***************************Host: localhostUser: rootSelect_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv: YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: Y
Create_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9password_expired: Npassword_last_changed: 2023-06-07 01:40:36password_lifetime: NULLaccount_locked: N
1 row in set (0.00 sec)ERROR: 
No query specified

6.回收权限

mysql> revoke all on *.* from 'test'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'test'@'%'\G;
*************************** 1. row ***************************
Grants for test@%: GRANT USAGE ON *.* TO 'test'@'%'
1 row in set (0.00 sec)ERROR: 
No query specifiedmysql> select * from mysql.user where user='test' and host='%'\G;
*************************** 1. row ***************************Host: %User: testSelect_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NShow_db_priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: N
Create_tablespace_priv: Nssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29password_expired: Npassword_last_changed: 2023-08-05 00:04:37password_lifetime: NULLaccount_locked: N
1 row in set (0.00 sec)