MySQL 使用者與許可權管理

標籤：database   mysql   dba   資料庫   sql   

    MySQL許可權系統的主要功能是證實串連到一台給定主機的使用者，並且賦予該使用者在資料庫上的相關DML,DQL許可權。MySQL存取控制包含2個階段，一是伺服器檢查是否允許你串連；二是假定你能串連，伺服器檢查你發出的每個請求。看你是否有足夠的許可權實施它。本文主要描述MySQL許可權系統相關的使用者建立、授權、撤銷許可權等等。

1、擷取有關許可權管理的協助

[email protected][(none)]> help Account ManagementFor more information, type ‘help <item>‘, where <item> is one of the followingtopics:You asked for help about help category: "Account Management"   CREATE USER   DROP USER   GRANT   RENAME USER   REVOKE   SET PASSWORD

2、建立mysql資料庫使用者

--建立使用者的文法[email protected][(none)]> help create user;Name: ‘CREATE USER‘Description:Syntax:CREATE USER user_specification [, user_specification] ...user_specification:    user    [      | IDENTIFIED WITH auth_plugin [AS ‘auth_string‘]        IDENTIFIED BY [PASSWORD] ‘password‘    ]create user命令會建立一個新帳戶，同時也可以為其指定密碼。該命令將添加一條記錄到user表。該命令僅僅授予usage許可權。需要再使用grant命令進行進一步授權。也可以使用grant命令直接來建立賬戶見後續的相關示範。下面是mysql官方手冊對usage的解釋。The USAGE privilege specifier stands for “no privileges.” It is used at the global level withGRANT to modify account attributes such as resource limits or SSL characteristics without affectingexisting account privileges.--當前示範環境[email protected][(none)]> show variables like ‘version‘;+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.5.39-log |+---------------+------------+--建立新使用者(未指定密碼)[email protected][(none)]> create user ‘fred‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)--指定密碼建立新使用者,%表示任意，即frank可以從任意主機訪問資料庫[email protected][(none)]> create user ‘frank‘@‘%‘ identified by ‘frank‘;Query OK, 0 rows affected (0.00 sec)--查看剛剛添加的賬戶[email protected][(none)]> select host,user,password from mysql.user where user like ‘fr%‘;+-----------+-------+-------------------------------------------+| host      | user  | password                                  |+-----------+-------+-------------------------------------------+| %         | frank | *63DAA25989C7E01EB96570FA4DBE154711BEB361 || localhost | fred  |                                           |+-----------+-------+-------------------------------------------+

3、使用grant授予許可權

--grant命令文法[email protected][mysql]> 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 | ssl_option [[AND] ssl_option] ...}]    [WITH with_option ...]GRANT PROXY ON user_specification    TO user_specification [, user_specification] ...    [WITH GRANT OPTION]object_type:    TABLE  | FUNCTION  | PROCEDUREpriv_level:    *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_nameuser_specification:    user    [      | IDENTIFIED WITH auth_plugin [AS ‘auth_string‘]        IDENTIFIED BY [PASSWORD] ‘password‘    ]如何授權  a、需要指定授予哪些許可權  b、許可權應用在那些對象上(全域，特定對象等)  c、授予給哪個帳戶  d、可以指定密碼(可選項,用此方式會自動建立使用者)授權許可權的範圍：  ON　*.*  ON  db_name.*  ON  db_name.table_name  ON  db_name.table_name.column_name  ON  db_name.routine_name--許可權一覽表，我們直接查詢root賬戶所有的許可權，如下--mysql的許可權相對於oracle而言，相對簡單，而且也沒有涉及到角色方面的定義與配置[email protected][(none)]> select * from mysql.user where user=‘root‘ and host=‘localhost‘\G *************************** 1. row ***************************                  Host: localhost                  User: root              Password:            Select_priv: Y           Insert_priv: Y           Update_priv: Y           Delete_priv: Y           Create_priv: Y             Drop_priv: Y           Reload_priv: Y         Shutdown_priv: Y          Process_priv: Y             File_priv: Y            Grant_priv: Y       References_priv: Y            Index_priv: Y            Alter_priv: Y          Show_db_priv: Y            Super_priv: Y Create_tmp_table_priv: Y      Lock_tables_priv: Y          Execute_priv: Y       Repl_slave_priv: Y      Repl_client_priv: Y      Create_view_priv: Y        Show_view_priv: Y   Create_routine_priv: Y    Alter_routine_priv: Y      Create_user_priv: Y            Event_priv: Y          Trigger_priv: YCreate_tablespace_priv: Y              ssl_type:             ssl_cipher:            x509_issuer:           x509_subject:          max_questions: 0           max_updates: 0       max_connections: 0  max_user_connections: 0                plugin:  authentication_string: 1 row in set (0.00 sec)--說明，本文中描述的mysql提示符為[email protected][(dbname)]，不同的帳戶，不同的主機登入會顯示不同。--其次，不同的提示符下所代表的使用者身份及許可權。--查看當前的串連使用者[email protected][(none)]> select current_user();+----------------+| current_user() |+----------------+| [email protected] |+----------------+--查看當前帳戶的許可權[email protected][(none)]> show grants;  --該賬戶用於最高許可權，帶有WITH GRANT OPTION+---------------------------------------------------------------------+| Grants for [email protected]                                           |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ WITH GRANT OPTION || GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION        |+---------------------------------------------------------------------+suse11b:~ # mysql -ufred -pEnter password: [email protected][(none)]> show grants;+------------------------------------------+| Grants for [email protected]                |+------------------------------------------+| GRANT USAGE ON *.* TO ‘fred‘@‘localhost‘ |+------------------------------------------+--下面使用root賬戶給fred賦予許可權all privileges[email protected][(none)]> grant all privileges on *.* to ‘fred‘@‘localhost‘;Query OK, 0 rows affected (0.01 sec)[email protected][(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec)[email protected][(none)]> show grants;+---------------------------------------------------+| Grants for [email protected]                         |+---------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘fred‘@‘localhost‘ |+---------------------------------------------------+[email protected][(none)]> use tempdb[email protected][tempdb]> create table tb_isam(id int,value varchar(20)) engine=myisam;Query OK, 0 rows affected (0.10 sec)[email protected][tempdb]> insert into tb_isam values (1,‘jack‘),(2,‘robin‘);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0[email protected][tempdb]> commit;--下面的授權收到了錯誤提示，不能授權[email protected][tempdb]> grant select on tempdb.* to ‘frank‘@‘%‘;ERROR 1044 (42000): Access denied for user ‘fred‘@‘localhost‘ to database ‘tempdb‘--下面從root session來給之前建立的frank授權--授予frank在資料庫tempdb上所有對象的select許可權[email protected][(none)]> grant select on tempdb.* to ‘frank‘@‘%‘;Query OK, 0 rows affected (0.00 sec)--更新cache中的許可權[email protected][(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec)--從另外的主機使用frank賬戶登入suse11a:~ # mysql -ufrank -p -h172.16.6.89Enter password: --此時frank，此時已經可以訪問了tempdb上的表tb_isam[email protected][(none)]> select * from tempdb.tb_isam;+------+-------+| id   | value |+------+-------+|    1 | jack  ||    2 | robin |+------+-------+[email protected][(none)]> show grants;+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘frank‘@‘%‘ IDENTIFIED BY PASSWORD ‘*63DAA25989C7E01EB96570FA4DBE154711BEB361‘ || GRANT SELECT ON `tempdb`.* TO ‘frank‘@‘%‘          --可以看到多出了select許可權                         |+------------------------------------------------------------------------------------------------------+--下面是一個授予最大許可權的例子，授予的同時會自動建立使用者，由於我們沒有設定密碼，所以password列查詢結果為空白[email protected][(none)]> grant all privileges on *.* to ‘jack‘@‘localhost‘;   Query OK, 0 rows affected (0.00 sec)    --第一個*號代表任意資料庫，第二個*號代表資料庫上的任意對象[email protected][(none)]> select user,host,Password from mysql.user where user=‘jack‘;+------+-----------+----------+| user | host      | Password |+------+-----------+----------+| jack | localhost |          |+------+-----------+----------+suse11b:~ # mysql -ujack -p -h localhostEnter password: [email protected][(none)]> show grants for current_user; --該方式等同於show grants，查看自身許可權+---------------------------------------------------+| Grants for [email protected]                         |+---------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘jack‘@‘localhost‘ |+---------------------------------------------------+--在當前session下查看其它使用者的許可權，注，當前session登陸的使用者也需要有許可權才能查看其它使用者權限[email protected][(none)]> show grants for ‘frank‘@‘%‘;+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘frank‘@‘%‘ IDENTIFIED BY PASSWORD ‘*63DAA25989C7E01EB96570FA4DBE154711BEB361‘ || GRANT SELECT ON `tempdb`.* TO ‘frank‘@‘%‘                                                            |+------------------------------------------------------------------------------------------------------+--下面示範基於對象列層級的授權--首先revoke之前的select許可權[email protected][(none)]> revoke select on tempdb.* from ‘frank‘@‘%‘;Query OK, 0 rows affected (0.00 sec)[email protected][tempdb]> create table tb_user as select * from mysql.user;Query OK, 9 rows affected (0.15 sec)Records: 9  Duplicates: 0  Warnings: 0[email protected][tempdb]> grant select(user,host),update(host) on tempdb.tb_user to ‘frank‘@‘%‘;ERROR 1142 (42000): GRANT command denied to user ‘fred‘@‘localhost‘ for table ‘tb_user‘ --授權失敗--下面使用root來授權[email protected][(none)]> grant select(user,host),update(host) on tempdb.tb_user to ‘frank‘@‘%‘;Query OK, 0 rows affected (0.00 sec)[email protected][(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec)--下面檢查一下frank所擁有的許可權[email protected][(none)]> show grants for ‘frank‘;+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘frank‘@‘%‘ IDENTIFIED BY PASSWORD ‘*63DAA25989C7E01EB96570FA4DBE154711BEB361‘ || GRANT SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` TO ‘frank‘@‘%‘                        |+------------------------------------------------------------------------------------------------------+--下面使用frank身份來驗證所授與權限[email protected][(none)]> desc tempdb.tb_user;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| Host  | char(60) | NO   |     |         |       || User  | char(16) | NO   |     |         |       |+-------+----------+------+-----+---------+-------+[email protected][(none)]> select * from tempdb.tb_user;   --訪問時不支援萬用字元，必須指定列名 ERROR 1142 (42000): SELECT command denied to user ‘frank‘@‘suse11a.site‘ for table ‘tb_user‘[email protected][(none)]> select host,user from tempdb.tb_user where user=‘frank‘;+------+-------+| host | user  |+------+-------+| %    | frank |+------+-------+--需要注意的是，如果你的對象建立在test相關資料庫下，許可權限制可能會失效。--下面這個查詢用於查看db的授權表[email protected][(none)]> select host,db,user from mysql.db;+------+---------+------+| host | db      | user |+------+---------+------+| %    | test    |      || %    | test\_% |      |+------+---------+------+--根據前面的許可權授予,列host可以被更新，而列user不行，如下面的2條SQL語句執行的結果[email protected][(none)]> update tempdb.tb_user set host=‘localhost‘ where user=‘frank‘;Query OK, 1 row affected (0.12 sec)Rows matched: 1  Changed: 1  Warnings: 0[email protected][(none)]> update tempdb.tb_user set user=‘jason‘ where user=‘jack‘;ERROR 1143 (42000): UPDATE command denied to user ‘frank‘@‘suse11a.site‘ for column ‘user‘ in table ‘tb_user‘--關於WITH GRANT OPTION[email protected][(none)]> show grants;   --注意root下有WITH GRANT OPTION+---------------------------------------------------------------------+| Grants for [email protected]                                           |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ WITH GRANT OPTION || GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION        |+---------------------------------------------------------------------+[email protected][(none)]> show grants for ‘jack‘@‘localhost‘; --注意jack下沒有WITH GRANT OPTION+---------------------------------------------------+       --這就是前面為什麼使用者自身建立的對象而無法授權的問題| Grants for [email protected]                         |       +---------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO ‘jack‘@‘localhost‘ |+---------------------------------------------------+         

4、撤銷許可權

撤銷許可權使用的是revoke關鍵字，撤銷與授權的許可權方式基本類似，其次有哪些許可權可以授予，相應地就有哪些許可權可以撤銷，原來的to子句呢則變成了from子句。如下面的樣本mysql> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from ‘frank‘@‘%‘;  mysql> revoke all privileges, grant option from ‘frank‘@‘%‘;[email protected][(none)]> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from ‘frank‘@‘%‘; Query OK, 0 rows affected (0.00 sec)-- Author : Leshami-- Blog   : http://blog.csdn.net/leshami[email protected][(none)]> revoke all privileges, grant option from ‘frank‘@‘%‘;Query OK, 0 rows affected (0.01 sec)[email protected][(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec)[email protected][(none)]> show grants for ‘frank‘;  --查看revoke之後僅擁有最基本許可權+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘frank‘@‘%‘ IDENTIFIED BY PASSWORD ‘*63DAA25989C7E01EB96570FA4DBE154711BEB361‘ |+------------------------------------------------------------------------------------------------------+

5、刪除及重新命名賬戶

使用drop user命令刪除使用者--查看當前系統中已存在的使用者[email protected][(none)]> select user,host,Password from mysql.user;+-------+-----------+-------------------------------------------+| user  | host      | Password                                  |+-------+-----------+-------------------------------------------+| root  | localhost |                                           || root  | suse11b   |                                           || root  | 127.0.0.1 |                                           || root  | ::1       |                                           ||       | localhost |                                           ||       | suse11b   |                                           || fred  | localhost |                                           || frank | %         | *63DAA25989C7E01EB96570FA4DBE154711BEB361 || jack  | localhost |                                           |+-------+-----------+-------------------------------------------+--使用drop user命令刪除使用者[email protected][(none)]> drop user ‘frank‘@‘%‘;Query OK, 0 rows affected (0.00 sec)[email protected][(none)]> drop user ‘fred‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)[email protected][(none)]> select user,host,Password from mysql.user where user like ‘fr%‘;Empty set (0.00 sec)--如何重新命名帳戶，使用rename user命令[email protected][(none)]> rename user ‘jack‘@‘localhost‘ to ‘jason‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)[email protected][(none)]> select user,host,Password from mysql.user where user like ‘j%‘;+-------+-----------+----------+| user  | host      | Password |+-------+-----------+----------+| jason | localhost |          |+-------+-----------+----------+--對於使用者的刪除也可以直接從mysql.user進行刪除相應的記錄，但不推薦直接操作mysql系統資料表

MySQL 使用者與許可權管理