天天看點
傳回

MySQL-5.7權限詳解

本文介紹MySQL-5.7權限詳解

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	代表沒有任何權限，隻能登陸
MySQL-5.7權限詳解

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)
tables_priv user db columns_priv privileges grant
上一篇: MySQL-版本及服務介紹
下一篇: Oracle Database 19c 技術架構（一）

繼續閱讀