MySQL用戶與權(quán)限管理介紹

這篇文章主要介紹“MySQL用戶與權(quán)限管理介紹”，在日常操作中，相信很多人在MySQL用戶與權(quán)限管理介紹問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL用戶與權(quán)限管理介紹”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

    MySQL權(quán)限系統(tǒng)的主要功能是證實(shí)連接到一臺給定主機(jī)的用戶，并且賦予該用戶在數(shù)據(jù)庫上的相關(guān)DML、DQL權(quán)限。MySQL存取控制包含2個(gè)階段，一是服務(wù)器檢查是否允許連接；二是假定能連接，服務(wù)器檢查發(fā)出的每個(gè)請求?？词欠裼凶銐虻臋?quán)限實(shí)施它。本文主要描述MySQL權(quán)限系統(tǒng)相關(guān)的用戶創(chuàng)建、授權(quán)、撤銷權(quán)限等等。

1、獲取有關(guān)權(quán)限管理的幫助

root@localhost[(none)]> help Account Management
For more information, type 'help ', where  is one of the following
topics:
You asked for help about help category: "Account Management"
  CREATE USER
  DROP USER
  GRANT
  RENAME USER
  REVOKE
  SET PASSWORD

2、創(chuàng)建mysql數(shù)據(jù)庫用戶

--創(chuàng)建用戶的語法
root@localhost[(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命令會創(chuàng)建一個(gè)新帳戶，同時(shí)也可以為其指定密碼。該命令將添加一條記錄到user表。
該命令僅僅授予usage權(quán)限。需要再使用grant命令進(jìn)行進(jìn)一步授權(quán)。也可以使用grant命令直接來創(chuàng)建賬戶見后續(xù)的相關(guān)演示。
下面是mysql官方手冊對usage的解釋。
The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

--當(dāng)前演示環(huán)境
root@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.5.39-log |
+---------------+------------+

--創(chuàng)建新用戶(未指定密碼)
root@localhost[(none)]> create user 'fred'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--指定密碼創(chuàng)建新用戶,%表示任意，即frank可以從任意主機(jī)訪問數(shù)據(jù)庫
root@localhost[(none)]> create user 'frank'@'%' identified by 'frank';
Query OK, 0 rows affected (0.00 sec)

--查看剛剛添加的賬戶
root@localhost[(none)]> select host,user,password from mysql.user where user like 'fr%';
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| %         | frank | *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| localhost | fred  |                                           |
+-----------+-------+-------------------------------------------+

3、使用grant授予權(quán)限

--grant命令語法
root@localhost[mysql]> help grant
Name: '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
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

user_specification:
    user
    [
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
        IDENTIFIED BY [PASSWORD] 'password'
    ]

如何授權(quán)
  a、需要指定授予哪些權(quán)限
  b、權(quán)限應(yīng)用在那些對象上(全局，特定對象等)
  c、授予給哪個(gè)帳戶
  d、可以指定密碼(可選項(xiàng),用此方式會自動創(chuàng)建用戶)

授權(quán)權(quán)限的范圍：
  ON　*.*
  ON  db_name.*
  ON  db_name.table_name
  ON  db_name.table_name.column_name
  ON  db_name.routine_name

--權(quán)限一覽表，我們直接查詢r(jià)oot賬戶所有的權(quán)限，如下
--mysql的權(quán)限相對于Oracle而言，相對簡單，而且也沒有涉及到角色方面的定義與配置
root@localhost[(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: Y
Create_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提示符為user@hostname[(dbname)]，不同的帳戶，不同的主機(jī)登錄會顯示不同。
--其次，不同的提示符下所代表的用戶身份及權(quán)限。
--查看當(dāng)前的連接用戶
root@localhost[(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+

--查看當(dāng)前帳戶的權(quán)限
root@localhost[(none)]> show grants;  --該賬戶用于最高權(quán)限，帶有WITH GRANT OPTION
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

SUSE11b:~ # mysql -ufred -p
Enter password:

fred@localhost[(none)]> show grants;
+------------------------------------------+
| Grants for fred@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'fred'@'localhost' |
+------------------------------------------+

--下面使用root賬戶給fred賦予權(quán)限all privileges
root@localhost[(none)]> grant all privileges on *.* to 'fred'@'localhost';
Query OK, 0 rows affected (0.01 sec)

root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

fred@localhost[(none)]> show grants;
+---------------------------------------------------+
| Grants for fred@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'fred'@'localhost' |
+---------------------------------------------------+

fred@localhost[(none)]> use tempdb

fred@localhost[tempdb]> create table tb_isam(id int,value varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.10 sec)

fred@localhost[tempdb]> insert into tb_isam values (1,'jack'),(2,'robin');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

fred@localhost[tempdb]> commit;

--下面的授權(quán)收到了錯誤提示，不能授權(quán)
fred@localhost[tempdb]> grant select on tempdb.* to 'frank'@'%';
ERROR 1044 (42000): Access denied for user 'fred'@'localhost' to database 'tempdb'

--下面從root session來給之前創(chuàng)建的frank授權(quán)
--授予frank在數(shù)據(jù)庫tempdb上所有對象的select權(quán)限
root@localhost[(none)]> grant select on tempdb.* to 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)

--更新cache中的權(quán)限
root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--從另外的主機(jī)使用frank賬戶登錄
suse11a:~ # mysql -ufrank -p -h272.16.6.89
Enter password:

--此時(shí)frank，此時(shí)已經(jīng)可以訪問了tempdb上的表tb_isam
frank@172.16.6.89[(none)]> select * from tempdb.tb_isam;
+------+-------+
| id   | value |
+------+-------+
|    1 | jack  |
|    2 | robin |
+------+-------+

frank@172.16.6.89[(none)]> show grants;
+------------------------------------------------------------------------------------------------------+
| Grants for frank@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
| GRANT SELECT ON `tempdb`.* TO 'frank'@'%'          --可以看到多出了select權(quán)限                          |
+------------------------------------------------------------------------------------------------------+

--下面是一個(gè)授予最大權(quán)限的例子，授予的同時(shí)會自動創(chuàng)建用戶，由于我們沒有設(shè)置密碼，所以password列查詢結(jié)果為空
root@localhost[(none)]> grant all privileges on *.* to 'jack'@'localhost'; 
Query OK, 0 rows affected (0.00 sec)    --第一個(gè)*號代表任意數(shù)據(jù)庫，第二個(gè)*號代表數(shù)據(jù)庫上的任意對象

root@localhost[(none)]> select user,host,Password from mysql.user where user='jack';
+------+-----------+----------+
| user | host      | Password |
+------+-----------+----------+
| jack | localhost |          |
+------+-----------+----------+

suse11b:~ # mysql -ujack -p -h localhost
Enter password:

jack@localhost[(none)]> show grants for current_user; --該方式等同于show grants，查看自身權(quán)限
+---------------------------------------------------+
| Grants for jack@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost' |
+---------------------------------------------------+

--在當(dāng)前session下查看其它用戶的權(quán)限，注，當(dāng)前session登陸的用戶也需要有權(quán)限才能查看其它用戶權(quán)限
jack@localhost[(none)]> show grants for 'frank'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for frank@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
| GRANT SELECT ON `tempdb`.* TO 'frank'@'%'                                                            |
+------------------------------------------------------------------------------------------------------+

--下面演示基于對象列級別的授權(quán)
--首先revoke之前的select權(quán)限
root@localhost[(none)]> revoke select on tempdb.* from 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)

fred@localhost[tempdb]> create table tb_user as select * from mysql.user;
Query OK, 9 rows affected (0.15 sec)
Records: 9  Duplicates: 0  Warnings: 0

fred@localhost[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' --授權(quán)失敗

--下面使用root來授權(quán)
root@localhost[(none)]> grant select(user,host),update(host) on tempdb.tb_user to 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--下面檢查一下frank所擁有的權(quán)限
root@localhost[(none)]> show grants for 'frank';
+------------------------------------------------------------------------------------------------------+
| Grants for frank@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
| GRANT SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` TO 'frank'@'%'                        |
+------------------------------------------------------------------------------------------------------+

--下面使用frank身份來驗(yàn)證所授予的權(quán)限
frank@172.16.6.89[(none)]> desc tempdb.tb_user;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Host  | char(60) | NO   |     |         |       |
| User  | char(16) | NO   |     |         |       |
+-------+----------+------+-----+---------+-------+

frank@172.16.6.89[(none)]> select * from tempdb.tb_user;   --訪問時(shí)不支持通配符，必須指定列名
ERROR 1142 (42000): SELECT command denied to user 'frank'@'suse11a.site' for table 'tb_user'

frank@172.16.6.89[(none)]> select host,user from tempdb.tb_user where user='frank';
+------+-------+
| host | user  |
+------+-------+
| %    | frank |
+------+-------+

--需要注意的是，如果你的對象創(chuàng)建在test相關(guān)數(shù)據(jù)庫下，權(quán)限限制可能會失效。
--下面這個(gè)查詢用于查看db的授權(quán)表
root@localhost[(none)]> select host,db,user from mysql.db;
+------+---------+------+
| host | db      | user |
+------+---------+------+
| %    | test    |      |
| %    | test\_% |      |
+------+---------+------+

--根據(jù)前面的權(quán)限授予,列host可以被更新，而列user不行，如下面的2條SQL語句執(zhí)行的結(jié)果
frank@172.16.6.89[(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

frank@172.16.6.89[(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'

--關(guān)于WITH GRANT OPTION
root@localhost[(none)]> show grants;   --注意root下有WITH GRANT OPTION
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

root@localhost[(none)]> show grants for 'jack'@'localhost'; --注意jack下沒有WITH GRANT OPTION
+---------------------------------------------------+       --這就是前面為什么用戶自身創(chuàng)建的對象而無法授權(quán)的問題
| Grants for jack@localhost                         |     
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost' |
+---------------------------------------------------+

4、撤銷權(quán)限

撤銷權(quán)限使用的是revoke關(guān)鍵字，撤銷與授權(quán)的權(quán)限方式基本類似，
其次有哪些權(quán)限可以授予，相應(yīng)地就有哪些權(quán)限可以撤銷，原來的to子句呢則變成了from子句。
如下面的示例
mysql> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from 'frank'@'%'; 
mysql> revoke all privileges, grant option from 'frank'@'%';

root@localhost[(none)]> revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb_user` from 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> revoke all privileges, grant option from 'frank'@'%';
Query OK, 0 rows affected (0.01 sec)

root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> show grants for 'frank';  --查看revoke之后僅擁有最基本權(quán)限
+------------------------------------------------------------------------------------------------------+
| Grants for frank@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'frank'@'%' IDENTIFIED BY PASSWORD '*63DAA25989C7E01EB96570FA4DBE154711BEB361' |
+------------------------------------------------------------------------------------------------------+

5、刪除及重命名賬戶

使用drop user命令刪除用戶
--查看當(dāng)前系統(tǒng)中已存在的用戶
root@localhost[(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命令刪除用戶
root@localhost[(none)]> drop user 'frank'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> drop user 'fred'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> select user,host,Password from mysql.user where user like 'fr%';
Empty set (0.00 sec)

--如何重命名帳戶，使用rename user命令
root@localhost[(none)]> rename user 'jack'@'localhost' to 'jason'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> select user,host,Password from mysql.user where user like 'j%';
+-------+-----------+----------+
| user  | host      | Password |
+-------+-----------+----------+
| jason | localhost |          |
+-------+-----------+----------+

--對于用戶的刪除也可以直接從mysql.user進(jìn)行刪除相應(yīng)的記錄，但不推薦直接操作MySQL系統(tǒng)表

到此，關(guān)于“MySQL用戶與權(quán)限管理介紹”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！