1.MySQL權限級別 （1）全局性管理權限 作用于整個MySQL實例級別 *.*代表所有數據庫的權限 ~~~ mysql> grant all on *.* to 'test'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> grant select, insert on *.* to 'test'@'%'; Query OK, 0 rows affected (0.00 sec) ~~~ （2）數據庫級別權限 作用于某個指定的數據庫上或所有的數據庫上 ~~~ mysql> grant all on test.* to 'test'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> grant select, insert on test.* to 'test'@'%'; Query OK, 0 rows affected (0.00 sec) ~~~ （3）數據庫對象級別權限 作用于指定的數據庫對象上（表、視圖等）或所有數據庫對象上 ~~~ mysql> grant select, insert on test.orders to 'test'@'localhost'; Query OK, 0 rows affected (0.07 sec) mysql> grant select(order_date), insert(order_id,customer_name) on test.orders_1 to 'test'@'localhost'; Query OK, 0 rows affected (0.01 sec) ~~~ 權限存儲在mysql庫的user、db、tables_priv、columns_priv、procs_priv這幾個系統表中，待MySQL實例啟動后加載到內存中。 2.查看權限 （1）查看所有用戶 ~~~ mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; +------------------------------------+ | query | +------------------------------------+ | User: 'mysql.session'@'localhost'; | | User: 'mysql.sys'@'localhost'; | | User: 'root'@'localhost'; | +------------------------------------+ 3 rows in set (0.01 sec) ~~~ （2）查看用戶權限 ~~~ mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) ~~~ （3）對比root用戶在幾個權限系統表中的數據 ~~~ mysql> select * from mysql.user where user='root' and host='localhost'; +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | N | 2017-11-18 18:21:57 | NULL | N | +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ mysql> select * from mysql.db where user='root' and host='localhost'; Empty set (0.01 sec) mysql> select * from mysql.tables_priv where user='root' and host='localhost'; Empty set (0.00 sec) mysql> select * from mysql.columns_priv where user='root' and host='localhost'; Empty set (0.00 sec) mysql> select * from mysql.procs_priv where user='root' and host='localhost'; Empty set (0.00 sec) ~~~ （4）權限認證中的大小敏感 字段user，password，authencation_string，db，table_name大小寫敏感 字段host，column_name，routine_name大小寫不敏感 3.MySQL支持的權限 ALL或ALL PRIVILEGES 代表指定權限等級的所有權限。 ALTER 允許使用ALTER TABLE來改變表的結構，ALTER TABLE同時也需要CREATE和INSERT權限。重命名一個表需要對舊表具有ALTER和DROP權限，對新表具有CREATE和INSERT權限。 ALTER ROUTINE 允許改變和刪除存儲過程和函數 CREATE 允許創建新的數據庫和表 CREATE ROUTINE 允許創建存儲過程和包 CREATE TABLESPACE 允許創建、更改和刪除表空間和日志文件組 CREATE TEMPORARY TABLES 允許創建臨時表 CREATE USER 允許更改、創建、刪除、重命名用戶和收回所有權限 CREATE VIEW 允許創建視圖 DELETE 允許從數據庫的表中刪除行 DROP 允許刪除數據庫、表和視圖 EVENT 允許在事件調度里面創建、更改、刪除和查看事件 EXECUETE 允許執行存儲過程和包 FILE 允許在服務器的主機上通過LOAD DATA INFILE、SELECT ... INTO OUTFILE和LOAD_FILE()函數讀寫文件 GRANT OPTION 允許向其他用戶授予或移除權限 INDEX 允許創建和刪除索引 INSERT 允許向數據庫的表中插入行 LOCK TABLE 允許執行LOCK TABLES語句來鎖定表 PROCESS 允許顯示在服務器上執行的線程信息，即被會話所執行的語句信息。這個權限允許你執行SHOW PROCESSLIST和mysqladmin processlist命令來查看線程，同時這個權限也允許你執行SHOW ENGINE命令 PROXY 允許用戶冒充成為另外一個用戶 REFERENCES 允許創建外鍵 RELOAD 允許使用FLUSH語句 REPLICATION CLIENT 允許執行SHOW MASTER STATUS,SHOW SLAVE STATUS和SHOW BINARY LOGS命令 REPLICATION SLAVE 允許SLAVE服務器連接到當前服務器來作為他們的主服務器 SELECT 允許從數據庫中查詢表 SHOW DATABASES 允許賬戶執行SHOW DATABASE語句來查看數據庫。沒有這個權限的賬戶只能看到他們具有權限的數據庫。 SHOW VIEW 允許執行SHOW CREATE VIEW語句 SHUTDOWN 允許執行SHUTDOWN語句和mysqladmin shutdown已經mysql_shutdown() C API函數 SUPER 允許用戶執行CHANGE MASTER TO,KILL或mysqladmin kill命令來殺掉其他用戶的線程，允許執行PURGE BINARY LOGS命令，通過SET GLOBAL來設置系統參數，執行mysqladmin debug命令，開啟和關閉日志，即使read_only參數開啟也可以執行update語句，打開和關閉從服務器上面的復制，允許在連接數達到max_connections的情況下連接到服務器。 TRIGGER 允許操作觸發器 UPDATE 允許更新數據庫中的表 USAGE 代表沒有任何權限，只能登陸 4.系統權限表 User表：存放用戶賬戶信息以及全局級別（所有數據庫）權限，決定了來自哪些主機的哪些用戶可以訪問數據庫實例，如果有全局權限則意味著對所有數據庫都有此權限； DB表：存放數據庫級別的權限，決定了來自哪些主機的哪些用戶可以訪問此數據庫； Tables_priv表：存放表級別的權限，決定了來自哪些主機的哪些用戶可以訪問此數據庫的這個表； Columns_priv表：存放列級別的權限，決定了來自哪些主機的哪些用戶可以訪問此數據庫的這個表的這個字段； Procs_priv表：存放存儲過程和函數級別的權限。 5.授權方式詳解 GRANT命令用來建立新用戶，指定用戶口令并增加用戶權限 mysql> GRANT <privileges> ON <what> TO <user> [IDENTIFIED BY "<password>"] [WITH GRANT OPTION]; 參數說明： 1.privileges是一個用逗號分隔的你想要賦予的MySQL用戶權限的列表。 你可以指定的權限可以分為三種類型： 數據庫/數據表/數據列權限（Alter、Create、Delete....） 全局管理MySQL用戶權限（file、PROCESS、reload、shutdown） 特別的權限（all、usage） 2.user表中host列的值的意義 % 匹配所有主機 localhost localhost不會被解析成IP地址，直接通過UNIXsocket連接 127.0.0.1 會通過TCP/IP協議連接，并且只能在本機訪問； ::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1 3.WITH GRANT OPTION 權限傳遞 A.如果帶了 with grant option ，那么用戶testuser1可以將select ,update權限傳遞給其他用戶( 如testuser2) grant select,update on bd_corp to testuser2 B.如果沒帶with grant option，那么用戶testuser1不能給testuser2授權 6.權限的生效 執行grant、revoke、set password、rename user命令修改權限后，MySQL會自動將修改后的權限信息同步加載到系統內存中； 如果執行insert、update、delete操作上述的系統權限表后，則必須在執行刷新命令才能同步到內存 如果是修改tables和colunms級別的權限，則客戶端的下次操作時新權限會生效； 如果是修改database級別的權限，則新權限在客戶端執行use database命令后生效 --skip-grant-tables可以跳過所有系統權限表而允許所有用戶登錄 7.創建用戶 ~~~ mysql> create user 'test'@'localhost' identified by 'mysql'; Query OK, 0 rows affected (0.00 sec) mysql> grant select on *.* to 'test'@'localhost' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> show create user 'test'@'localhost'\G *************************** 1. row *************************** CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK 1 row in set (0.00 sec) ~~~ 8.回收權限 revoke跟grant的語法差不多，只需要把關鍵字 “to” 換成 “from” 即可； ~~~ mysql> show grants for 'test'@'localhost'; +---------------------------------------------------------------------+ | Grants for test@localhost | +---------------------------------------------------------------------+ | GRANT SELECT, DELETE ON *.* TO 'test'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> revoke delete on *.* from 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +-------------------------------------------------------------+ | Grants for test@localhost | +-------------------------------------------------------------+ | GRANT SELECT ON *.* TO 'test'@'localhost' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec) ~~~ 9.刪除用戶 ~~~ mysql> drop user 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) ~~~ 也可以這樣 ~~~ mysql> delete from user where user='test1'; mysql> flush privileges; ~~~ 查看用戶所有權限：`show grants for test;` 查看用戶創建信息：`show create user test;`