如何理解MySQL管理基礎中的安全、訪問控制和權限
如何理解MySQL管理基礎中的安全、訪問控制和權限，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
創(chuàng)新互聯(lián)公司提供高防主機、云服務器、香港服務器、雙線服務器托管等
MySQL權限系統(tǒng)確保所有的用戶只執(zhí)行允許的操作。當連接MySQL服務器時，用戶身份由其所在的主機和使用的用戶名來決定。當用戶在連接后發(fā)出請求時，系統(tǒng)根據(jù)其身份和要進行的操作來授予權限。
1.MySQL授權表
1)user表
mysql> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *25B0A985780CE19647D8E41C2B50E7FB300EA6E8 |
| % | root | *25B0A985780CE19647D8E41C2B50E7FB300EA6E8 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)
注：“%”用作通配符，這里表示允許從任何主機以root用戶訪問。除db表外的其它授權表中，空host值與“%”相同。
[@more@]MySQL授權表一般都包含兩類字段：范圍字段和權限字段。上面看到的user表的host、user、password屬于范圍字段，此外還有一些名稱以“_priv”結尾的權限字段，用于指定用戶擁有的權限。這里賦予用戶的權限適用于系統(tǒng)的每個數(shù)據(jù)庫，因此一般將它們都設置為N，而使用host和db表進行更為精細的權限設置。其余字段則與SSL加密以及用戶資源限制有關。
2)db和host表
db表有三個范圍字段host、db和user，指定該條記錄是針對某用戶從某主機連接某數(shù)據(jù)庫而言的；其余的字段為權限字段。
host有兩個范圍字段host、db，其余的字段為權限字段。如果db表中記錄的host字段留空，MySQL服務器在驗證用戶權限時會從host表中獲取相應的主機名。host表不受GRANT和REVOKE語句的影響。大多數(shù)MySQL安裝根本不需要使用該表。
3)tables_priv和columns_priv表
這兩個表可以分別限制對數(shù)據(jù)庫中具體的表和表中具體的列的訪問。
當MySQL需要決定是否允許用戶執(zhí)行某一數(shù)據(jù)庫操作時，首先要查看user表中該用戶是否具有足夠的權限，如果沒有，再查看db和host表。一些管理操作，如RELOAD、PROCESS涉及整個系統(tǒng)，只有user表中有相應的列。
可以手工修改授權表的內容來進行權限設置（但tables_priv和columns_priv表不建議這樣做），修改后需要使用FLUSH PRIVILEGES語句、mysqladmin flush-privileges或mysqladmin reload命令重新裝載授權表。
2.授予和回收權限
通常使用GRANT和REVOKE命令授予和回收用戶的權限，下面通過一些例子來演示它們的用法。
首先，創(chuàng)建一個從本機連接MySQL服務器的ggyy用戶：
mysql> create user ggyy@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| % | root |
| localhost | ggyy |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)
現(xiàn)在可以使用ggyy用戶連接服務器，但還沒有訪問數(shù)據(jù)庫的權限。
C:>mysql -u ggyy -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 18
Server version: 5.1.34-community MySQL Community Server (GPL)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
下面授予ggyy用戶在ggyy數(shù)據(jù)庫上的查詢權限：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> grant select on ggyy.* to ggyy@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ggyy@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for ggyy@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD '*484FFAA42C12F40931C794D33A11B7F075B91467' |
| GRANT SELECT ON `ggyy`.* TO 'ggyy'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select host, db, user, select_priv, insert_priv, update_priv, delete_priv from mysql.db;
+-----------+------+------+-------------+-------------+-------------+-------------+
| host | db | user | select_priv | insert_priv | update_priv | delete_priv |
+-----------+------+------+-------------+-------------+-------------+-------------+
| localhost | ggyy | ggyy | Y | N | N | N |
+-----------+------+------+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)
mysql> select * from mysql.tables_priv;
Empty set (0.00 sec)
使用ggyy用戶查看ggyy數(shù)據(jù)庫中的內容：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| ggyy@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> use ggyy
Database changed
mysql> show tables;
+----------------+
| Tables_in_ggyy |
+----------------+
| blob_text_test |
| char_test |
| contact |
| date_time_test |
| float_test |
| groups1 |
| groups2 |
| int_test |
| members |
| members_temp |
| processes1 |
| test_crttb |
| test_crttb2 |
| test_crttb3 |
| test_crttb4 |
| test_crttb5 |
| ts_dt_test |
| users1 |
+----------------+
18 rows in set (0.00 sec)
mysql> select * from processes1 limit 1;
+------+-------+------+
| pid | pname | ppid |
+------+-------+------+
| 1 | init | 0 |
+------+-------+------+
1 row in set (0.00 sec)
接下來，再創(chuàng)建一個從遠程主機192.168.7.100連接MySQL服務器的ggyy用戶，授予查詢ggyy數(shù)據(jù)庫中user1表的權限。使用GRANT命令可以在授權時創(chuàng)建被授權的用戶：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> grant select on ggyy.users1 to ggyy@192.168.7.100 identified by 'ggyy';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ggyy@192.168.7.100;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for ggyy@192.168.7.100 |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ggyy'@'192.168.7.100' IDENTIFIED BY PASSWORD '*484FFAA42C12F40931C794D33A11B7F075B91467' |
| GRANT SELECT ON `ggyy`.`users1` TO 'ggyy'@'192.168.7.100' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select host, user from mysql.user;
+---------------+------+
| host | user |
+---------------+------+
| % | root |
| 192.168.7.100 | ggyy |
| localhost | ggyy |
| localhost | root |
+---------------+------+
4 rows in set (0.00 sec)
mysql> select host, db, user, select_priv, insert_priv, update_priv, delete_priv from mysql.db where host = '192.168.7.1
00';
Empty set (0.00 sec)
mysql> select * from mysql.tables_priv where host = '192.168.7.100';
+---------------+------+------+------------+----------------+---------------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+---------------+------+------+------------+----------------+---------------------+------------+-------------+
| 192.168.7.100 | ggyy | ggyy | users1 | root@localhost | 2010-01-17 14:02:42 | Select | |
+---------------+------+------+------------+----------------+---------------------+------------+-------------+
1 row in set (0.00 sec)
可以看到，由于授予的是表的權限，host表中沒有相應記錄，需要到tables_priv表中查看。
在遠程主機上訪問數(shù)據(jù)庫：
C:>mysql -h 192.168.7.101 -u ggyy -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 20
Server version: 5.1.34-community MySQL Community Server (GPL)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> select current_user();
+--------------------+
| current_user() |
+--------------------+
| ggyy@192.168.7.100 |
+--------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ggyy |
+--------------------+
2 rows in set (0.00 sec)
mysql> use ggyy
Database changed
mysql> show tables;
+----------------+
| Tables_in_ggyy |
+----------------+
| users1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from users1 limit 1;
+------+----------+------+
| uid | uname | gid |
+------+----------+------+
| 202 | fengsong | 200 |
+------+----------+------+
1 row in set (0.00 sec)
要回收權限使用REVOKE命令，例如：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> revoke select on ggyy.* from ggyy@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> revoke select on ggyy.users1 from ggyy@192.168.7.100;
Query OK, 0 rows affected (0.00 sec)
也可以采用db和host表相結合的方式管理數(shù)據(jù)庫的權限，例如：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> insert into mysql.db (db, user, select_priv, delete_priv) values ('ggyy', 'ggyy', 'Y', 'Y');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql.host (db, host, select_priv, insert_priv) values ('ggyy', 'localhost', 'Y', 'Y');
Query OK, 1 row affected (0.00 sec)
mysql> select host, db, user, select_priv, insert_priv, delete_priv from mysql.db where db = 'ggyy';
+------+------+------+-------------+-------------+-------------+
| host | db | user | select_priv | insert_priv | delete_priv |
+------+------+------+-------------+-------------+-------------+
| | ggyy | ggyy | Y | N | Y |
+------+------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
mysql> select host, db, select_priv, insert_priv, delete_priv from mysql.host where db = 'ggyy';
+-----------+------+-------------+-------------+-------------+
| host | db | select_priv | insert_priv | delete_priv |
+-----------+------+-------------+-------------+-------------+
| localhost | ggyy | Y | Y | N |
+-----------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
使用ggyy用戶在本機上訪問數(shù)據(jù)庫：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| ggyy@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ggyy |
+--------------------+
2 rows in set (0.00 sec)
mysql> use ggyy
Database changed
mysql> show tables;
+----------------+
| Tables_in_ggyy |
+----------------+
| blob_text_test |
| char_test |
| contact |
| date_time_test |
| float_test |
| groups1 |
| groups2 |
| int_test |
| members |
| members_temp |
| processes1 |
| test_crttb |
| test_crttb2 |
| test_crttb3 |
| test_crttb4 |
| test_crttb5 |
| ts_dt_test |
| users1 |
+----------------+
18 rows in set (0.00 sec)
mysql> select * from groups1 limit 1;
+------+-------+
| gid | gname |
+------+-------+
| 0 | root |
+------+-------+
1 row in set (0.00 sec)
mysql> insert into groups1 values (255, 'test');
ERROR 1142 (42000): INSERT command denied to user 'ggyy'@'localhost' for table 'groups1'
mysql> delete from groups1 where gname = 'root';
ERROR 1142 (42000): DELETE command denied to user 'ggyy'@'localhost' for table 'groups1'
db表中的權限列相當于“總開關”，host表中的權限列相當于“分開關”，只有兩個“開關”都打開了，用戶才擁有相應操作的權限。
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> update mysql.db set insert_priv = 'Y' where db = 'ggyy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update mysql.host set delete_priv = 'Y' where db = 'ggyy';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host, db, user, select_priv, insert_priv, delete_priv from mysql.db where db = 'ggyy';
+------+------+------+-------------+-------------+-------------+
| host | db | user | select_priv | insert_priv | delete_priv |
+------+------+------+-------------+-------------+-------------+
| | ggyy | ggyy | Y | Y | Y |
+------+------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
mysql> select host, db, select_priv, insert_priv, delete_priv from mysql.host where db = 'ggyy';
+-----------+------+-------------+-------------+-------------+
| host | db | select_priv | insert_priv | delete_priv |
+-----------+------+-------------+-------------+-------------+
| localhost | ggyy | Y | Y | Y |
+-----------+------+-------------+-------------+-------------+
1 row in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
再次使用ggyy用戶嘗試插入和刪除操作：
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| ggyy@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> insert into groups1 values (255, 'test');
Query OK, 1 row affected (0.00 sec)
mysql> delete from groups1 where gname = 'root';
Query OK, 1 rows affected (0.00 sec)
這種由db表和host表一起確定的權限，使用SHOW GRANTS命令看不到，也無法使用REVOKE命令回收。
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show grants for ggyy@localhost;
+-------------------------------------------------------------------------------------------------------------+
| Grants for ggyy@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD '*484FFAA42C12F40931C794D33A11B7F075B91467' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke select on ggyy.* from ggyy@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'ggyy' on host 'localhost'
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對創(chuàng)新互聯(lián)的支持。
分享名稱：如何理解MySQL管理基礎中的安全、訪問控制和權限
轉載注明：http://muchs.cn/article46/gheohg.html
成都網站建設公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、小程序開發(fā)、面包屑導航、企業(yè)網站制作、建站公司、網站收錄
聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)
- 上海網站設計公司和上海網站建設公司免費幫你維護更新網站 2020-11-09
- 網站設計公司：網站制作美工需要不斷提升設計水平 2017-02-14
- 上海網站建設公司、上海網站設計公司是一支高素質的團隊 2020-11-05
- 上海網站設計公司、上海網站建設公司為客戶免費提供源代碼 2020-11-06
- 上海網站設計公司、上海網站建設公司網站建設有哪些注意事項呢 2020-11-05
- 上海網站建設公司、上海網站設計公司給互聯(lián)網創(chuàng)造力什么價值 2020-11-08
- 深圳網站設計公司教你如何設計出漂亮的網頁 2021-09-15
- 上海網站建設公司和上海網站設計公司的各種建站風格 2020-11-08
- 行業(yè)發(fā)展力推上海網站設計公司、上海網站建設公司加速成長 2020-11-05
- 如何選擇一家好的網站設計公司 2016-11-13
- 上海網站設計公司、上海網站建設公司網站建設的性 2020-11-07
- 上海網站建設公司、上海網站設計公司你公司網站建設更好的選擇 2020-11-05