https://dev.mysql.com/doc/refman/5.7/en/grant.html
用戶管理和權(quán)限管理
mysql登錄驗(yàn)證是通過(guò)三個(gè)維度的：用戶名、密碼、ip
創(chuàng)建david，ip無(wú)限制，密碼為123
create user 'david'@'%' identified by '123';
創(chuàng)建david，ip限制為192.168.1開(kāi)頭，密碼為123
create user 'david'@'192.168.1.*' identified by '123';
查看當(dāng)前用戶的權(quán)限
show grants;
(root@localhost) [performance_schema]>show grants;
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.01 sec)
(david@localhost) [(none)]>show grants;
+-----------------------------------+
| Grants for david@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
查看特定用戶權(quán)限
(david@localhost) [(none)]>show grunts for 'david'@'%';
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 'grunts for 'david'@'%'' at line 1
(david@localhost) [(none)]>show grants for 'david'@'%';
+-----------------------------------+
| Grants for david@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
授權(quán)操作，將test.* 的select,update,insert,delete權(quán)限授予給 'david'@'%' 用戶
(root@localhost) [performance_schema]>grant select,update,insert,delete on test.* to 'david'@'%';
Query OK, 0 rows affected (0.00 sec)
test.*代表test下的所有表，*.*就是全局
注意：有一種將創(chuàng)建用戶和授權(quán)操作同時(shí)使用的操作，mysql不推薦這樣做，未來(lái)版本會(huì)將這種寫(xiě)法刪除。
(root@localhost) [performance_schema]>grant select,update,insert,delete on test.* to 'amy'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [performance_schema]>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'warning' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [performance_schema]>
修改用戶密碼
alter user 'david'@'%' identified by '456';
添加新的權(quán)限
grant create,index on test.* to 'david'@'%' ;
收回權(quán)限
revoke create,index on test.* from 'david'@'%';
注意：revoke all on . to 'david'@'%' ; 將david的所有權(quán)限回收并不代表將用戶刪除了
將自己的權(quán)限授予其它用戶的權(quán)限:with grant option 。
(root@localhost) [performance_schema]>grant select,update,insert,delete on test.* to 'david'@'%' with grant option ;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [performance_schema]>show grants for 'david'@'%';
+-----------------------------------------------------------------------------------+
| Grants for david@% |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'david'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
這樣我在david下就可以將自己的權(quán)限授予別人了
(david@localhost) [(none)]>grant select on test.* to 'amy'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql庫(kù)下四張保存權(quán)限的表
user 全局級(jí)別
db 庫(kù)級(jí)別
tables_priv 表級(jí)別
columns_priv 列級(jí)別
(root@localhost) [mysql]>show tables like 'user';
+------------------------+
| Tables_in_mysql (user) |
+------------------------+
| user |
+------------------------+
1 row in set (0.00 sec)
(root@localhost) [mysql]>show tables like 'db';
+----------------------+
| Tables_in_mysql (db) |
+----------------------+
| db |
+----------------------+
1 row in set (0.00 sec)
(root@localhost) [mysql]>show tables like 'tables_priv';
+-------------------------------+
| Tables_in_mysql (tables_priv) |
+-------------------------------+
| tables_priv |
+-------------------------------+
1 row in set (0.00 sec)
(root@localhost) [mysql]>show tables like 'columns_priv';
+--------------------------------+
| Tables_in_mysql (columns_priv) |
+--------------------------------+
| columns_priv |
+--------------------------------+
1 row in set (0.00 sec)
查詢david這個(gè)用戶在全局的權(quán)限
(root@localhost) [mysql]>select * from user where user='david'\G;
*************************** 1. row ***************************
Host: %
User: david
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *531E182E2F72080AB0740FE2F2D689DBE0146E04
password_expired: N
password_last_changed: 2021-04-18 18:33:09
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
可以看到全都是N ，說(shuō)名david在全局下沒(méi)有權(quán)限。
再來(lái)看david在庫(kù)級(jí)別的權(quán)限
(root@localhost) [mysql]>select * from db where user='david'\G;
*************************** 1. row ***************************
Host: %
Db: test
User: david
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
Grant_priv: Y
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)
ERROR:
No query specified
可以看到david在庫(kù)級(jí)別下存在Select_priv、Insert_priv、Update_priv、Delete_priv、Grant_priv的權(quán)限。說(shuō)名創(chuàng)建的普通用戶權(quán)限是在db級(jí)別的。
注意，強(qiáng)烈建議不要直接修改這四張表（user 、db、tables_priv 、columns_priv ）來(lái)達(dá)到授權(quán)目的，請(qǐng)使用grant命令！這樣做是存在一定風(fēng)險(xiǎn)的。
而root權(quán)限是保存到user表里的：
(root@localhost) [mysql]>select * from user where user='root'\G;
*************************** 1. row ***************************
Host: %
User: root
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: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2021-04-18 10:45:46
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
mysql密碼加密方式
簡(jiǎn)單查詢下mysql.user
(root@localhost) [mysql]>select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| david | % | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
| amy | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
authentication_string 字段保存了mysql密碼的加密后字符串，類似于md5，是一種單向的摘要算法。
內(nèi)部使用mysql函數(shù)：password()來(lái)實(shí)現(xiàn)，如下：
(root@localhost) [mysql]>select password('456')
-> ;
+-------------------------------------------+
| password('456') |
+-------------------------------------------+
| *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
資源限制
https://dev.mysql.com/doc/refman/5.7/en/user-resources.html
1、每小時(shí)執(zhí)行查詢次數(shù)
2、每小時(shí)更新次數(shù)
3、每小時(shí)最大連接數(shù)
4、用戶最大連接數(shù)
max_user_connections
max_connectios_per_hour 每小時(shí)內(nèi)連接數(shù)次數(shù)
max_queries_per_hour
max_updates_per_hour
david最多只能有一個(gè)用戶連接。注意已經(jīng)連上的用戶不納入記數(shù)。
alter user 'david'@'%' with max_user_connections 1;
[root@localhost ~]# mysql -udavid -p456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1226 (42000): User 'david' has exceeded the 'max_user_connections' resource (current value: 1)
基于角色的權(quán)限管理
https://dev.mysql.com/doc/refman/8.0/en/create-role.html
mysql8.0可以做到根據(jù)role角色分配權(quán)限。
1、創(chuàng)建角色;
(root@localhost) [(none)]>create role senior_dba,app_dev;
Query OK, 0 rows affected (0.01 sec)
刪除角色，角色被刪除后相應(yīng)授權(quán)用戶也會(huì)移除這個(gè)權(quán)限：
(root@localhost) [(none)]>DROP ROLE 'app_dev';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]>show grants for leon@'192.168.1.%';
+--------------------------------------------+
| Grants for leon@192.168.1.% |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `leon`@`192.168.1.%` |
+--------------------------------------------+
1 row in set (0.00 sec)
2、給角色授權(quán)
給senior_dba角色授最高權(quán)限;
給app_dev角色授予wp庫(kù)上的select,insert,update,delete 權(quán)限;
(root@localhost) [(none)]>grant all on *.* to senior_dba with grant option;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]>grant select,insert,update,delete on wp.* to app_dev;
Query OK, 0 rows affected (0.00 sec)
3、創(chuàng)建用戶
創(chuàng)建用戶tom@'192.168.1.%' 并給該用戶指定senior_dba 角色;
(root@localhost) [(none)]>create user tom@'192.168.1.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]>grant senior_dba to tom@'192.168.1.%';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]>
相反，這樣來(lái)解除用戶的角色：
REVOKE 'senior_dba' FROM leon@'192.168.1.%';
4、查看權(quán)限
注意show grants 語(yǔ)句查不出詳細(xì)權(quán)限,只能查到用戶的角色為senior_dba;后面再加上using senior_dba即可查看具體權(quán)限;
(root@localhost) [(none)]>show grants for tom@'192.168.1.%';
+-----------------------------------------------+
| Grants for tom@192.168.1.% |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `tom`@`192.168.1.%` |
| GRANT `senior_dba`@`%` TO `tom`@`192.168.1.%` |
+-----------------------------------------------+
2 rows in set (0.00 sec)
(root@localhost) [(none)]>show grants for tom@'192.168.1.%' using senior_dba;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for tom@192.168.1.% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `tom`@`192.168.1.%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `tom`@`192.168.1.%` WITH GRANT OPTION |
| GRANT `senior_dba`@`%` TO `tom`@`192.168.1.%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
基于角色的權(quán)限管理在有相同權(quán)限用戶特別多時(shí)非常有用，比如游戲行業(yè)。
