MySQL許可權管理

標籤：存在   tab   empty   重新整理   out   sock   超級使用者   mes   方法   

資料庫的許可權管理一直是很麻煩的一件事情，因為許可權是很分散的，不容易查看，也不容易修改的完善。但是MySQL的許可權管理讓人眼前一亮的感覺。因為什麼都是那麼一目瞭然。

1# 查看許可權
比如，我們想要看看MySQL的root使用者，擁有什麼許可權：

([email protected])[(none)]> select user();+----------------+| user()         |+----------------+| [email protected] |+----------------+1 row in set (0.00 sec)([email protected])[(none)]> show grants;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                                              |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*A0F874BC7F54EE086FCE60A37CE7887D8B31086B‘ WITH GRANT OPTION || GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION                                                                           |+----------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

一條show grants解決了問題，並且連grant語句都給我們了。這樣就是說，我們稍微修修改改就可用重造出另一個和root一樣的超級使用者了。

其中第一條：

GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*A0F874BC7F54EE086FCE60A37CE7887D8B31086B‘ WITH GRANT OPTION 

不光光grant 了 . 上的ALL PRIVILEGES 給‘root‘@‘localhost‘ ，甚至還有驗證密碼和 WITH 資訊。實際上這條語句是可用拿來建立這個使用者的。這也是一個另類的建立使用者的方法。

查看他人的許可權：

([email protected])[(none)]> show grants for test1    -> ;+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD ‘*CFA887C680E792C2DCF622D56FB809E3F8BE63CC‘ |+------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

這裡看到使用者‘test1‘@‘%‘只有一條許可權，這條許可權也是預設的建立使用者語句。

2# 授予許可權：
使用者必然是需要使用資料庫的。所以如果使用者只有usage這個沒用的許可權的話，這個使用者就不需要存在了。
文法：

([email protected])[(none)]> help grantName: ‘GRANT‘Description:Syntax:GRANT    priv_type [(column_list)]      [, priv_type [(column_list)]] ...    ON [object_type] priv_level    TO user_specification [, user_specification] ...    [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]    [WITH {GRANT OPTION | resource_option} ...]GRANT PROXY ON user_specification                                  #這個代理也是語句也是單獨存在    TO user_specification [, user_specification] ...    [WITH GRANT OPTION]object_type: {                                                                  #物件類型    TABLE  | FUNCTION  | PROCEDURE}priv_level: {                                                               #許可權的等級分類    *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_name}user_specification:                                     #使用者    user [ auth_option ]auth_option: {                                            #驗證資訊    IDENTIFIED BY ‘auth_string‘  | IDENTIFIED BY PASSWORD ‘hash_string‘  | IDENTIFIED WITH auth_plugin  | IDENTIFIED WITH auth_plugin AS ‘hash_string‘}tsl_option: {                                        #SSL類型    SSL  | X509  | CIPHER ‘cipher‘  | ISSUER ‘issuer‘  | SUBJECT ‘subject‘}resource_option: {                                      #資源使用定義  | MAX_QUERIES_PER_HOUR count  | MAX_UPDATES_PER_HOUR count  | MAX_CONNECTIONS_PER_HOUR count  | MAX_USER_CONNECTIONS count}

使用者權限列表，見官方文檔：https://dev.mysql.com/doc/refman/5.6/en/grant.html

使用者test1當前是沒有任何許可權的。假設我們需要讓他訪問mysql.user表

([email protected])[(none)]> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed([email protected])[mysql]> select count(*) from user;+----------+| count(*) |+----------+|        4 |+----------+1 row in set (0.00 sec)([email protected])[mysql]>

建立一個新資料庫，建立一個新表，對這個表進行訪問以及控制:

([email protected])[mysql]> create database sample;Query OK, 1 row affected (0.00 sec)([email protected])[mysql]> use sample;Database changed([email protected])[sample]> show tables;Empty set (0.00 sec)([email protected])[sample]> create table smp (id int,name char(20));Query OK, 0 rows affected (0.07 sec)([email protected])[sample]> grant all privileges on sample.smp to test1;Query OK, 0 rows affected (0.00 sec)([email protected])[sample]> select User,Table_name,Table_priv from mysql.tables_priv;+-------+------------+----------------------------------------------------------------------------------------------+| User  | Table_name | Table_priv                                                                                   |+-------+------------+----------------------------------------------------------------------------------------------+| test1 | user       | Select                                                                                       || test1 | smp        | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |+-------+------------+----------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)([email protected])[sample]>

更多的怎刪改和刪除表

([email protected])[sample]> insert into smp values (1,‘abc‘);Query OK, 1 row affected (0.00 sec)([email protected])[sample]> select * from smp;+------+------+| id   | name |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)([email protected])[sample]> delete from smp;Query OK, 1 row affected (0.00 sec)([email protected])[sample]> select * from smp;Empty set (0.00 sec)([email protected])[sample]> drep table smp;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 ‘drep table smp‘ at line 1([email protected])[sample]> drop table smp;Query OK, 0 rows affected (0.00 sec)

drop table 是DDL, 這個時候table已經刪除了，再看看許可權：

([email protected])[sample]>  select User,Table_name,Table_priv from mysql.tables_priv;+-------+------------+----------------------------------------------------------------------------------------------+| User  | Table_name | Table_priv                                                                                   |+-------+------------+----------------------------------------------------------------------------------------------+| test1 | user       | Select                                                                                       || test1 | smp        | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |+-------+------------+----------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

許可權依然存在。說明刪除表是不會刪除使用者所擁有的對象許可權的。

試試看建回來：

([email protected])[sample]>  create table smp (id int,name char(20));Query OK, 0 rows affected (0.00 sec)([email protected])[sample]> create table smp1 (id int,name char(20));ERROR 1142 (42000): CREATE command denied to user ‘test1‘@‘localhost‘ for table ‘smp1‘([email protected])[sample]>

成功建回來。那麼是否可用在這個資料庫中建立另外一張表呢？不行。

對 sample. 層面授予許可權。

([email protected])[sample]> grant create on sample.* to test1;Query OK, 0 rows affected (0.00 sec)([email protected])[sample]> select User,Table_name,Table_priv from mysql.tables_priv;+-------+------------+----------------------------------------------------------------------------------------------+| User  | Table_name | Table_priv                                                                                   |+-------+------------+----------------------------------------------------------------------------------------------+| test1 | user       | Select                                                                                       || test1 | smp        | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |+-------+------------+----------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)([email protected])[sample]> show grants for test1;+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD ‘*CFA887C680E792C2DCF622D56FB809E3F8BE63CC‘ || GRANT 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)([email protected])[sample]> flush privileges;Query OK, 0 rows affected (0.00 sec)

這個時候，test1使用者有了CREATE ON sample. 。嘗試在資料庫中建立表對象。

([email protected])[sample]>  create table smp1 (id int,name char(20));ERROR 1142 (42000): CREATE command denied to user ‘test1‘@‘localhost‘ for table ‘smp1‘

失敗！很奇怪，已經有了許可權，還是失敗。嘗試重新登陸

([email protected])[sample]> exitBye[[email protected] ~]$ mysql -utest1 -S /data/mysqldata/3306/mysql.sock -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 25Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.([email protected])[(none)]>([email protected])[(none)]>([email protected])[(none)]> create table smp1 (id int,name char(20));ERROR 1046 (3D000): No database selected([email protected])[(none)]> use sample;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed([email protected])[sample]> create table smp1 (id int,name char(20));Query OK, 0 rows affected (0.01 sec)

重新登陸後建立表對象成功。說明普通使用者的許可權需要在登陸的時候重新整理。

MySQL許可權管理