這篇文章帶給大家的內容是介紹MySQL如何進行權限管理?有一定的參考價值,有需要的朋友可以參考一下,希望對你有幫助。
MySQL 的權限表在資料庫啟動的時候就載入內存,當用戶通過身份認證後,就在內存中進行相應權限的訪問,這樣,此用戶就可以在數據庫中做權限範圍內的各種操作了。 【影片教學推薦:MySQL 教學】
在權限存取的兩個過程中,系統會用到「mysql”資料庫(安裝MySQL 時被創建,資料庫名稱叫做「mysql」) 中user、host 和db 這3個最重要的權限表。
在這 3 個表中,最重要的表示 user 表,其次是 db 表,host 表在大多數情況下並未使用。
user 中的欄位主要分為 4 個部分:使用者列、權限列、安全性列和資源控制列。
通常用的最多的是使用者列和權限列,其中權限列又分為普通權限和管理權限。普通權限用於資料庫的操作,例如 select_priv
、super_priv
等。
當使用者進行連線時,權限表的存取程序有以下兩個程序:
#先從user 資料表中的host、user 和password 這3 個欄位中判斷連線的IP、使用者名稱、和密碼是否存在於表中,如果存在,則通過身份驗證,否則拒絕連線。
如果經過驗證、則依照下列權限表的順序得到資料庫權限:user -> db -> tables_priv -> columns_priv。
在這幾個權限表中,權限範圍依序遞減,全域權限覆寫局部權限。上面的第一階段好理解,以下以一個例子來詳細解釋第二階段。
為了方便測試,需要修改變數sql_mode
// sql_mode 默认值中有 NO_AUTO_CREATE_USER (防止GRANT自动创建新用户,除非还指定了密码) SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
MySQL [mysql]> grant select on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ...
MySQL [mysql]> select * from db where user='zj' \G ; Empty set (0.00 sec)
可以發現,user 表的select_priv 欄位是“Y”,而db 表中並沒有記錄,也就是說,對所有資料庫都具有相同的權限的使用者並不需要記錄到db 表,而只需要將user 表中的select_priv 改為「Y」 即可。換句話說,user 表中的每個權限都代表了對所有資料庫都有權限。
MySQL [mysql]> revoke select on *.* from zj@localhost; Query OK, 0 rows affected, 1 warning (0.02 sec) MySQL [mysql]> grant select on t2.* to zj@localhost; Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL [mysql]> select * from user where user='zj' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ... MySQL [mysql]> select * from db where user='zj' \G; *************************** 1. row *************************** Host: localhost Db: t2 User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N
這時發現,user 表中的 select_priv 變成 “N” ,而 db 表中增加了 db 為 t2 的一筆記錄。也就是說,當只授予部分資料庫某些權限時,user 表中的對應權限列會保持 “N”,而將特定的資料庫權限寫入 db 表。 table 和 column 的權限機制和 db 類似。
從上例可以看出,當使用者通過權限認證,進行權限分配時,將按照user -> db -> tables_priv -> columns_priv 的順序進行權限分配,即先檢查全域權限表user,如果user 中對應權限為“Y”,則此使用者對所有資料庫的權限都為“Y”,將不再檢查db、tables_priv 和columns_priv;如果為“N”,則到db 表中檢查此使用者對應的特定資料庫,並得到db 中為“Y”的權限;如果db 中相應權限為“N”,則再依次檢查tables_priv 和columns_priv 中的權限,如果所有的都為“N”,則判斷為不具備權限。
主要包括帳號的創建,權限的變更和帳號的刪除。
使用grant 語法創建,範例:
MySQL [mysql]> grant all privileges on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y
可以發現,除了 grant_priv 權限外,所有權限在 user 表裡面都是 「Y」。
MySQL [(none)]> grant all privileges on *.* to zj@localhost with grant option; Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G ; *************************** 1. row *************************** Host: localhost User: zj 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 ...
MySQL [mysql]> grant all privileges on *.* to zj@localhost identified by '123' with grant option; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G ; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:29:42 password_lifetime: NULL
可以發現,密碼變成了一堆加密後的字串。
MySQL [mysql]> grant select ,insert, update,delete on t2.* to 'zj2'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [mysql]> select * from user where user='zj2' and host="%" \G; *************************** 1. row *************************** Host: % User: zj2 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:37:49 password_lifetime: NULL MySQL [mysql]> select * from db where user="zj2" and host='%' \G; *************************** 1. row *************************** Host: % Db: t2 User: zj2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: N Drop_priv: N ......
user 表中的權限都是“N”,db 表中增加的記錄權限則都是“Y”。一般的,只授予使用者適當的權限,而不會授予過多的權限。
本例中的 IP 限制為所有 IP 都可以連接,因此設定為 “*”,mysql 資料庫中是透過 user 表的 host 欄位來控制,host 可以是以下類型的賦值。
Host 值可以是主機名稱或IP號,或 “localhost” 指出本機。
可以在Host 列值使用通配符字元「%」 和「_」
Host 值「%」 匹配任何主機名,空Host 值等價於“%”,它們的意義與like 運算子的模式比對運算相同。
注意: mysql 数据库的 user 表中 host 的值为 “*” 或者空,表示所有外部 IP 都可以连接,但是不包括本地服务器 localhost,因此,如果要包括本地服务器,必须单独为 localhost 赋予权限。
MySQL [mysql]> grant super,process,file on *.* to 'zj3'@'%'; Query OK, 0 rows affected, 1 warning (0.00 sec)
因为这几个权限都是属于管理权限,因此不能够指定某个数据库,on 后面必须跟 “.”,下面语法将提示错误
MySQL [mysql]> grant super,process,file on t2.* to 'zj3'@'%'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
MySQL [mysql]> grant usage on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> exit Bye zj@bogon:~$ mysql -uzj4 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 78 Server version: 5.7.18-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.02 sec)
usage 权限只能用于数据库登录,不能执行任何操作
账号创建好后,可以通过如下命令查看权限:
show grants for user@host;
示例:
MySQL [(none)]> show grants for zj@localhost; +-------------------------------------------------------------------+ | Grants for zj@localhost | +-------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'zj'@'localhost' WITH GRANT OPTION | +-------------------------------------------------------------------+ 1 row in set (0.01 sec)
可以进行权限的新增和回收。和创建账号一样,权限变更也有两种方法:使用 grant(新增) 和 revoke (回收) 语句,或者更改权限表。
MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
MySQL [(none)]> grant select on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +------------------------------------------+ | Grants for zj4@localhost | +------------------------------------------+ | GRANT SELECT ON *.* TO 'zj4'@'localhost' | +------------------------------------------+ 1 row in set (0.00 sec)
MySQL [(none)]> show grants for 'zj4'@'localhost'; +--------------------------------------------------+ | Grants for zj4@localhost | +--------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO 'zj4'@'localhost' | +--------------------------------------------------+ 1 row in set (0.00 sec)
revoke 语句可以回收已经赋予的权限,对于上面的例子,这里决定要收回 zj4@localhost 上的 insert 和 select 权限:
MySQL [(none)]> revoke select,insert on *.* from zj4@localhost; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
usage 权限不能被回收,也就是说,revoke 用户并不能删除用户。
shell> mysqladmin -u user_name -h host_name password "123456"
mysql> set password for 'username'@'%' = password('pwd');
如果是更改自己的密码,可以省略 for 语句
mysql> set password=password('pwd');
mysql> grant usage on *.* to 'username'@'%' identified by 'pwd';
要彻底的删除账号,可以使用 drop user :
drop user zj@localhost;
创建 MySQL 账号时,还有一类选项称为账号资源限制
,这类选项的作用是限制每个账号实际具有的资源限制,这里的“资源”主要包括:
max_queries_per_hour count : 单个账号每小时执行的查询次数
max_upodates_per_hour count : 单个账号每小时执行的更新次数
max_connections_per_hour count : 单个账号每小时连接服务器的次数
max_user_connections count : 单个账号并发连接服务器的次数
##
4#
#
#
#
MySQL 的權限表在資料庫啟動的時候就載入內存,當用戶通過身份認證後,就在內存中進行相應權限的訪問,這樣,此使用者就可以在資料庫中做權限範圍內的各種操作了。 一、權限表的存取######在權限存取的兩個過程中,系統會用到“mysql” 資料庫(安裝MySQL 時被創建,資料庫名稱叫做“mysql” ) 中user、host 和db 這3個最重要的權限表。 ######在這 3 個表中,最重要的表示 user 表,其次是 db 表,host 表在大多數情況下並未使用。 ######user 中的欄位主要分為 4 個部分:使用者列、權限列、安全性列和資源控制列。 ######通常用的最多的是使用者列和權限列,其中權限列又分為普通權限和管理權限。普通權限用於資料庫的操作,例如 ###select_priv###、###super_priv### 等。 ######當使用者進行連線時,權限表的存取程序有以下兩個程序:#############先從user 資料表中的host、user 和password 這3 個欄位中判斷連線的IP、使用者名稱、和密碼是否存在於表中,如果存在,則通過身份驗證,否則拒絕連線。 ############如果經過驗證、則依照下列權限表的順序得到資料庫權限:user -> db -> tables_priv -> columns_priv。 ############在這幾個權限表中,權限範圍依序遞減,全域權限覆寫局部權限。上面的第一階段好理解,以下以一個例子來詳細解釋第二階段。 ######為了方便測試,需要修改變數sql_mode######// sql_mode 默认值中有 NO_AUTO_CREATE_USER (防止GRANT自动创建新用户,除非还指定了密码) SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';###1. 建立使用者zj@localhost,並賦予所有資料庫上的所有資料表的select 權限###
MySQL [mysql]> grant select on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ...### 2. 檢視db 表###
MySQL [mysql]> select * from db where user='zj' \G ; Empty set (0.00 sec)###可以發現,user 表的select_priv 欄位是“Y”,而db 表中並沒有記錄,也就是說,對所有資料庫都具有相同的權限的使用者並不需要記錄到db 表,而只需要將user 表中的select_priv 改為「Y」 即可。換句話說,user 表中的每個權限都代表了對所有資料庫都有權限。 ######3. 將 zj@localhost 上的權限改為只對 t2 資料庫上所有資料表的 select 權限。 ###
MySQL [mysql]> revoke select on *.* from zj@localhost; Query OK, 0 rows affected, 1 warning (0.02 sec) MySQL [mysql]> grant select on t2.* to zj@localhost; Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL [mysql]> select * from user where user='zj' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ... MySQL [mysql]> select * from db where user='zj' \G; *************************** 1. row *************************** Host: localhost Db: t2 User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N###這時發現,user 表中的 select_priv 變成 “N” ,而 db 表中增加了 db 為 t2 的一筆記錄。也就是說,當只授予部分資料庫某些權限時,user 表中的對應權限列會保持 “N”,而將特定的資料庫權限寫入 db 表。 table 和 column 的權限機制和 db 類似。 ######從上例可以看出,當使用者通過權限認證,進行權限分配時,將按照user -> db -> tables_priv -> columns_priv 的順序進行權限分配,即先檢查全域權限表user,如果user 中對應權限為“Y”,則此使用者對所有資料庫的權限都為“Y”,將不再檢查db、tables_priv 和columns_priv;如果為“N”,則到db 表中檢查此使用者對應的特定資料庫,並得到db 中為“Y”的權限;如果db 中相應權限為“N”,則再依次檢查tables_priv 和columns_priv 中的權限,如果所有的都為“N”,則判斷為不具備權限。 ######二、帳號管理######主要包括帳號的創建,權限的變更和帳號的刪除。 ######1. 建立帳號######使用grant 語法創建,範例:######(1) 建立使用者zj ,權限為可以在所有資料庫上執行所有權限,只能從本地進行連線。 ###
MySQL [mysql]> grant all privileges on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y###可以發現,除了 grant_priv 權限外,所有權限在 user 表裡面都是 「Y」。 ######(2) 在(1) 基礎上,增加對zj 的grant 權限###
MySQL [(none)]> grant all privileges on *.* to zj@localhost with grant option; Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G ; *************************** 1. row *************************** Host: localhost User: zj 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 ...###(3) 在(2) 基礎上,設定密碼為「123」###
MySQL [mysql]> grant all privileges on *.* to zj@localhost identified by '123' with grant option; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G ; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:29:42 password_lifetime: NULL# ##可以發現,密碼變成了一堆加密後的字串。 ###
MySQL [mysql]> grant select ,insert, update,delete on t2.* to 'zj2'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [mysql]> select * from user where user='zj2' and host="%" \G; *************************** 1. row *************************** Host: % User: zj2 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:37:49 password_lifetime: NULL MySQL [mysql]> select * from db where user="zj2" and host='%' \G; *************************** 1. row *************************** Host: % Db: t2 User: zj2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: N Drop_priv: N ......
user 表中的权限都是“N”,db 表中增加的记录权限则都是“Y”。一般的,只授予用户适当的权限,而不会授予过多的权限。
本例中的 IP 限制为所有 IP 都可以连接,因此设置为 “*”,mysql 数据库中是通过 user 表的 host 字段来进行控制,host 可以是以下类型的赋值。
Host 值可以是主机名或IP号,或 “localhost” 指出本地主机。
可以在 Host 列值使用通配符字符 “%” 和 “_”
Host 值 “%” 匹配任何主机名,空 Host 值等价于 “%”,它们的含义与 like 操作符的模式匹配操作相同。
注意: mysql 数据库的 user 表中 host 的值为 “*” 或者空,表示所有外部 IP 都可以连接,但是不包括本地服务器 localhost,因此,如果要包括本地服务器,必须单独为 localhost 赋予权限。
MySQL [mysql]> grant super,process,file on *.* to 'zj3'@'%'; Query OK, 0 rows affected, 1 warning (0.00 sec)
因为这几个权限都是属于管理权限,因此不能够指定某个数据库,on 后面必须跟 “.”,下面语法将提示错误
MySQL [mysql]> grant super,process,file on t2.* to 'zj3'@'%'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
MySQL [mysql]> grant usage on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> exit Bye zj@bogon:~$ mysql -uzj4 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 78 Server version: 5.7.18-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.02 sec)
usage 权限只能用于数据库登录,不能执行任何操作
账号创建好后,可以通过如下命令查看权限:
show grants for user@host;
示例:
MySQL [(none)]> show grants for zj@localhost; +-------------------------------------------------------------------+ | Grants for zj@localhost | +-------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'zj'@'localhost' WITH GRANT OPTION | +-------------------------------------------------------------------+ 1 row in set (0.01 sec)
可以进行权限的新增和回收。和创建账号一样,权限变更也有两种方法:使用 grant(新增) 和 revoke (回收) 语句,或者更改权限表。
MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
MySQL [(none)]> grant select on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +------------------------------------------+ | Grants for zj4@localhost | +------------------------------------------+ | GRANT SELECT ON *.* TO 'zj4'@'localhost' | +------------------------------------------+ 1 row in set (0.00 sec)
MySQL [(none)]> show grants for 'zj4'@'localhost'; +--------------------------------------------------+ | Grants for zj4@localhost | +--------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO 'zj4'@'localhost' | +--------------------------------------------------+ 1 row in set (0.00 sec)
revoke 语句可以回收已经赋予的权限,对于上面的例子,这里决定要收回 zj4@localhost 上的 insert 和 select 权限:
MySQL [(none)]> revoke select,insert on *.* from zj4@localhost; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
usage 权限不能被回收,也就是说,revoke 用户并不能删除用户。
shell> mysqladmin -u user_name -h host_name password "123456"
mysql> set password for 'username'@'%' = password('pwd');
如果是更改自己的密码,可以省略 for 语句
mysql> set password=password('pwd');
mysql> grant usage on *.* to 'username'@'%' identified by 'pwd';
要彻底的删除账号,可以使用 drop user :
drop user zj@localhost;
创建 MySQL 账号时,还有一类选项称为账号资源限制
,这类选项的作用是限制每个账号实际具有的资源限制,这里的“资源”主要包括:
max_queries_per_hour count : 单个账号每小时执行的查询次数
max_upodates_per_hour count : 单个账号每小时执行的更新次数
max_connections_per_hour count : 单个账号每小时连接服务器的次数
max_user_connections count : 单个账号并发连接服务器的次数
评论
以上是MySQL如何進行權限管理?的詳細內容。更多資訊請關注PHP中文網其他相關文章!