如何理解MySQL管理基礎(chǔ)中的安全、訪問(wèn)控制和權(quán)限

如何理解MySQL管理基礎(chǔ)中的安全、訪問(wèn)控制和權(quán)限，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

MySQL權(quán)限系統(tǒng)確保所有的用戶只執(zhí)行允許的操作。當(dāng)連接MySQL服務(wù)器時(shí)，用戶身份由其所在的主機(jī)和使用的用戶名來(lái)決定。當(dāng)用戶在連接后發(fā)出請(qǐng)求時(shí)，系統(tǒng)根據(jù)其身份和要進(jìn)行的操作來(lái)授予權(quán)限。

1.MySQL授權(quán)表

 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)

注：“%”用作通配符，這里表示允許從任何主機(jī)以root用戶訪問(wèn)。除db表外的其它授權(quán)表中，空host值與“%”相同。

[@more@]MySQL授權(quán)表一般都包含兩類字段：范圍字段和權(quán)限字段。上面看到的user表的host、user、password屬于范圍字段，此外還有一些名稱以“_priv”結(jié)尾的權(quán)限字段，用于指定用戶擁有的權(quán)限。這里賦予用戶的權(quán)限適用于系統(tǒng)的每個(gè)數(shù)據(jù)庫(kù)，因此一般將它們都設(shè)置為N，而使用host和db表進(jìn)行更為精細(xì)的權(quán)限設(shè)置。其余字段則與SSL加密以及用戶資源限制有關(guān)。

 2)db和host表

   db表有三個(gè)范圍字段host、db和user，指定該條記錄是針對(duì)某用戶從某主機(jī)連接某數(shù)據(jù)庫(kù)而言的；其余的字段為權(quán)限字段。
   host有兩個(gè)范圍字段host、db，其余的字段為權(quán)限字段。如果db表中記錄的host字段留空，MySQL服務(wù)器在驗(yàn)證用戶權(quán)限時(shí)會(huì)從host表中獲取相應(yīng)的主機(jī)名。host表不受GRANT和REVOKE語(yǔ)句的影響。大多數(shù)MySQL安裝根本不需要使用該表。

 3)tables_priv和columns_priv表

   這兩個(gè)表可以分別限制對(duì)數(shù)據(jù)庫(kù)中具體的表和表中具體的列的訪問(wèn)。

   當(dāng)MySQL需要決定是否允許用戶執(zhí)行某一數(shù)據(jù)庫(kù)操作時(shí)，首先要查看user表中該用戶是否具有足夠的權(quán)限，如果沒(méi)有，再查看db和host表。一些管理操作，如RELOAD、PROCESS涉及整個(gè)系統(tǒng)，只有user表中有相應(yīng)的列。
   可以手工修改授權(quán)表的內(nèi)容來(lái)進(jìn)行權(quán)限設(shè)置（但tables_priv和columns_priv表不建議這樣做），修改后需要使用FLUSH PRIVILEGES語(yǔ)句、mysqladmin flush-privileges或mysqladmin reload命令重新裝載授權(quán)表。

2.授予和回收權(quán)限

   通常使用GRANT和REVOKE命令授予和回收用戶的權(quán)限，下面通過(guò)一些例子來(lái)演示它們的用法。
   首先，創(chuàng)建一個(gè)從本機(jī)連接MySQL服務(wù)器的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用戶連接服務(wù)器，但還沒(méi)有訪問(wèn)數(shù)據(jù)庫(kù)的權(quán)限。

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ù)庫(kù)上的查詢權(quán)限：

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ù)庫(kù)中的內(nèi)容：

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)

   接下來(lái)，再創(chuàng)建一個(gè)從遠(yuǎn)程主機(jī)192.168.7.100連接MySQL服務(wù)器的ggyy用戶，授予查詢ggyy數(shù)據(jù)庫(kù)中user1表的權(quán)限。使用GRANT命令可以在授權(quán)時(shí)創(chuàng)建被授權(quán)的用戶：

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)


   可以看到，由于授予的是表的權(quán)限，host表中沒(méi)有相應(yīng)記錄，需要到tables_priv表中查看。
   在遠(yuǎn)程主機(jī)上訪問(wèn)數(shù)據(jù)庫(kù)：

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)


   要回收權(quán)限使用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表相結(jié)合的方式管理數(shù)據(jù)庫(kù)的權(quán)限，例如：

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用戶在本機(jī)上訪問(wèn)數(shù)據(jù)庫(kù)：

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表中的權(quán)限列相當(dāng)于“總開(kāi)關(guān)”，host表中的權(quán)限列相當(dāng)于“分開(kāi)關(guān)”，只有兩個(gè)“開(kāi)關(guān)”都打開(kāi)了，用戶才擁有相應(yīng)操作的權(quán)限。

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表一起確定的權(quán)限，使用SHOW GRANTS命令看不到，也無(wú)法使用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'

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。