溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本文主要給大家簡單講講mysql.db數(shù)據(jù)庫層權(quán)限該如何設(shè)置，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望mysql.db數(shù)據(jù)庫層權(quán)限該如何設(shè)置這篇文章可以給大家?guī)硪恍嶋H幫助。
1# 數(shù)據(jù)庫層權(quán)限記錄位置
表級別的權(quán)限記錄在mysql.tables_priv表中。
(root@localhost)[mysql]> (root@localhost)[mysql]> desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | char(77) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)然而關(guān)于數(shù)據(jù)庫層面的權(quán)限記錄在mysql.db表中
(root@localhost)[mysql]> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
(root@localhost)[mysql]> select * from db\G
*************************** 1. row ***************************
Host: %
Db: sample
User: test1
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: Y
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)這條對應(yīng)的grant語句是：
(root@localhost)[mysql]> show grants for test1; +------------------------------------------------------------------------------------------------------+ | Grants for test1@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' | | GRANT SELECT, CREATE ON `sample`.* TO 'test1'@'%' | | GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%' | | GRANT SELECT ON `mysql`.`user` TO 'test1'@'%' | +------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
第二條：
GRANT SELECT, CREATE ON sample.* TO 'test1'@'%'
嘗試再增加權(quán)限：
(root@localhost)[mysql]> grant all privileges on sample.* to test1; Query OK, 0 rows affected (0.00 sec) (root@localhost)[mysql]> (root@localhost)[mysql]> (root@localhost)[mysql]> select * from db\G *************************** 1. row *************************** Host: % Db: sample User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y 1 row in set (0.00 sec)
授予all privileges權(quán)限。注意點是grant option并不包含在all privileges里面。可以用with子句
(root@localhost)[mysql]> grant all privileges on sample.* to test1 with grant option; Query OK, 0 rows affected (0.00 sec) (root@localhost)[mysql]> select * from db\G *************************** 1. row *************************** Host: % Db: sample User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y 1 row in set (0.00 sec)
回收all privileges權(quán)限, 錯誤寫法，revoke并不能帶with grant option來回收grant option
(root@localhost)[mysql]> revoke all privileges on sample.* from test1 with grant option; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grant option' at line 1 (root@localhost)[mysql]>
這樣寫還是不對：
revoke all privileges, grant option on sample.* from test1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on sample.* from test1' at line 1
分開寫就可以了：
(root@localhost)[mysql]> revoke all privileges on sample.* from test1; Query OK, 0 rows affected (0.00 sec) (root@localhost)[mysql]> revoke grant option on sample.* from test1; Query OK, 0 rows affected (0.00 sec) (root@localhost)[mysql]>
grant option在授予的時候是用with子句，回收的時候需要單獨回收。
2#一般用戶可訪問的數(shù)據(jù)庫：
在test1用戶下，查看可以訪問的數(shù)據(jù)庫：
(test1@localhost)[(none)]> show databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | sample | +--------------------+ 3 rows in set (0.00 sec) (test1@localhost)[(none)]> (test1@localhost)[(none)]> (test1@localhost)[(none)]> (test1@localhost)[(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (test1@localhost)[mysql]> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | user | #由于只有user表上被授予了select權(quán)限，所以show tables只能看到一個表 +-----------------+ 1 row in set (0.00 sec) (test1@localhost)[mysql]> show grants; +---------------------------------------------------------------------+ | Grants for test1@% | +---------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD <secret> | | GRANT ALL PRIVILEGES ON `sample`.* TO 'test1'@'%' WITH GRANT OPTION | | GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%' | | GRANT SELECT ON `mysql`.`user` TO 'test1'@'%' | +---------------------------------------------------------------------+ 4 rows in set (0.00 sec)-----------------------------------------------------------------+ | Grants for test1@% | +-------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD <secret> | | GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%' | | GRANT SELECT ON `mysql`.`user` TO 'test1'@'%' | +-------------------------------------------------------------------+ 3 rows in set (0.00 sec) (test1@localhost)[mysql]>
sample數(shù)據(jù)庫是被授予的all privileges,所以全庫的表對象都是可用唄test1用戶訪問。至于mysql，只有user表上有一個select權(quán)限，所以也被歸類在可用訪問的數(shù)據(jù)庫之列，但是實際上用show tables只能看到一個表。
3# 數(shù)據(jù)庫層權(quán)限的從無到有，給一個用戶授予權(quán)限后，db表的變化：
a# 建立一個新的數(shù)據(jù)庫sample2
(root@localhost)[(none)]> create database sample2; Query OK, 1 row affected (0.00 sec)
這個時候查看用戶test1所擁有的數(shù)據(jù)庫權(quán)限，是只有sample數(shù)據(jù)庫。MYSQL數(shù)據(jù)庫并不顯示。
(root@localhost)[mysql]> select * from db\G *************************** 1. row *************************** Host: % Db: sample User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y 1 row in set (0.00 sec) (root@localhost)[mysql]> #這個時候test1用戶去嘗試訪問sample2數(shù)據(jù)庫也是失敗的： (test1@localhost)[sample]> use sample2 ERROR 1044 (42000): Access denied for user 'test1'@'%' to database 'sample2' (test1@localhost)[sample]>
運行授權(quán)語句，對test1受援sample2的select權(quán)限，這個時候mysql.db數(shù)據(jù)庫中出現(xiàn)了2行數(shù)據(jù)，多了一行關(guān)于sample2的記錄：
(root@localhost)[mysql]> select * from db\G *************************** 1. row *************************** Host: % Db: sample2 User: test1 Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N *************************** 2. row *************************** Host: % Db: sample User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y 2 rows in set (0.00 sec)
從記錄可用看到test1用戶對sample2數(shù)據(jù)庫擁有select權(quán)限。也就是訪問權(quán)限，表示里面的對象都具有。但是里面是沒有表的。
(test1@localhost)[(none)]> use sample2; Database changed (test1@localhost)[sample2]> show tables; Empty set (0.00 sec)
#并且test1用戶可用select出smp表的數(shù)據(jù)。 (test1@localhost)[sample2]> select * from smp; +------+------+ | id | name | +------+------+ | 1 | aaa | +------+------+ 1 row in set (0.00 sec) # 然而test1用戶并不顯示的擁有任在sample2上的表權(quán)限。所謂的權(quán)限的繼承。 (test1@localhost)[information_schema]> select * from TABLE_PRIVILEGES where TABLE_SCHEMA='sample2'; Empty set (0.00 sec)
#奇怪，并沒有給表smp授權(quán)select，受援的是select on sample2.*。嘗試單獨對表收回select 權(quán)限：
(root@localhost)[sample2]> revoke select on sample2.smp from test1; ERROR 1147 (42000): There is no such grant defined for user 'test1' on host '%' on table 'smp' #以失敗告終。顯示的revoke并不能收回隱式繼承來的權(quán)限。 (test1@localhost)[sample2]> show tables; +-------------------+ | Tables_in_sample2 | +-------------------+ | smp | +-------------------+ 1 row in set (0.00 sec)
mysql.db數(shù)據(jù)庫層權(quán)限該如何設(shè)置就先給大家講到這里，對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼