mysql> select user,host from mysql.user;

mysql> use mysql;
mysql> select * from user where user='root' and host='localhost'\G;  #所有權限都是Y ，就是什么權限都有
mysql> select * from db where user='root' and host='localhost'\G;  # 沒有此條記錄
mysql> select * from tables_priv where user='root' and host='localhost';  # 沒有此條記錄
 
mysql> select * from columns_priv where user='root' and host='localhost'; # 沒有此條記錄
 
mysql> select * from procs_priv where user='root' and host='localhost'; # 沒有此條記錄

mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

User權限表結構中的特殊字段：

• Plugin,authentication_string字段存放用戶認證信息
• Password_expired設置成'Y'則表明允許DBA將此用戶的密碼設置成過期而且過期后要求用戶的使用者重置密碼（alter user/set password重置密碼）
• Password_last_changed作為一個時間戳字段代表密碼上次修改時間，執行create user/alter user/set password/grant等命令創建用戶或修改用戶密碼時此數值自動更新
• Password_lifetime代表從password_last_changed時間開始此密碼過期的天數
• Account_locked代表此用戶被鎖住，無法使用

在mysql 5.7 以前在user表有password 這個字段。

2.1.2 Tables_priv和columns_priv權限表結構

Tables_priv和columns_priv權限值

2.1.3 procs_priv權限表結構

• Routine_type是枚舉類型，代表是存儲過程還是函數
• Timestamp和grantor兩個字段暫時沒用

系統權限表字段長度限制表

權限認證中的大小寫敏感問題

• 字段user,password,authencation_string,db,table_name大小寫敏感
• 字段host,column_name,routine_name大小寫不敏感

2.2 用戶權限信息管理

2.2.1 查看用戶權限信息

查看MYSQL有哪些用戶

mysql> select user,host from mysql.user;

查看已經授權給用戶的權限信息
例如root

mysql> show grants for root@'localhost';

查看用戶的其他非授權信息

mysql> show create user root@'localhost';

2.2.2 用戶組成

MySQL的授權用戶由兩部分組成：用戶名和登錄主機名

• 表達用戶的語法為'user_name'@'host_name'
• 單引號不是必須，但如果其中包含特殊字符則是必須的
• ”@‘localhost'代表匿名登錄的用戶
• Host_name可以使主機名或者ipv4/ipv6的地址。 Localhost代表本機， 127.0.0.1代表ipv4本機地址， ::1代表ipv6的本機地址
• Host_name字段允許使用%和_兩個匹配字符，比如'%'代表所有主機， '%.mysql.com'代表

來自mysql.com這個域名下的所有主機， ‘192.168.1.%'代表所有來自192.168.1網段的主機

2.2.3 修改用戶權限

執行Grant,revoke,set password,rename user命令修改權限之后， MySQL會自動將修改后的權限信息同步加載到系統內存中

如果執行insert/update/delete操作上述的系統權限表之后，則必須再執行刷新權限命令才能同步到系統內存中，刷新權限命令包括：flush privileges/mysqladmin flush-privileges / mysqladmin reload

如果是修改tables和columns級別的權限，則客戶端的下次操作新權限就會生效

如果是修改database級別的權限，則新權限在客戶端執行use database命令后生效

如果是修改global級別的權限，則需要重新創建連接新權限才能生效

如果是修改global級別的權限，則需要重新創建連接新權限才能生效 (例如修改密碼)

2.2.4 創建 mysql 用戶

有兩種方式創建MySQL授權用戶

• 執行create user/grant命令（推薦方式）
• 通過insert語句直接操作MySQL系統權限表

# 創建finley 這只是創建用戶并沒有權限
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
# 把finley 變成管理員用戶
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH
GRANT OPTION;
#創建用戶并賦予RELOAD,PROCESS權限 ，在所有的庫和表上
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';
 
# 創建keme用戶，在test庫，temp表， 上的id列只有select 權限
mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';

2.2.4 回收 mysql 權限

通過revoke命令收回用戶權限,回收的時候看一下這個用戶有哪些權限然后回收
我對admin 用戶做測試

mysql> show grants for admin@'localhost';
mysql> select user,host from mysql.user;
mysql> revoke PROCESS ON *.* FROM admin@'localhost';

2.2.5 刪除 mysql 用戶

通過執行drop user命令刪除MySQL用戶
還可以通過系統權限表刪除（不建議）

mysql> drop user admin@'localhost';

2.2.6 設置MySQL用戶資源限制

通過設置全局變量max_user_connections可以限制所有用戶在同一時間連接MySQL實例的數量，但此參數無法對每個用戶區別對待，所以MySQL提供了對每個用戶的資源限制管理

MAX_QUERIES_PER_HOUR：一個用戶在一個小時內可以執行查詢的次數（基本包含所有語句）

MAX_UPDATES_PER_HOUR：一個用戶在一個小時內可以執行修改的次數（僅包含修改數據庫或表的語句）

MAX_CONNECTIONS_PER_HOUR：一個用戶在一個小時內可以連接MySQL的時間

MAX_USER_CONNECTIONS：一個用戶可以在同一時間連接MySQL實例的數量

從5.0.3版本開始，對用戶‘user'@‘%.example.com'的資源限制是指所有通過example.com域名主機連接user用戶的連接，而不是分別指從host1.example.com和host2.example.com主機過來的連接

2.2.7 修改 mysql 用戶密碼

修改用戶密碼的方式包括：

mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
shell> mysqladmin -u user_name -h host_name password "new_password"

創建用戶時指定密碼

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

修改當前會話本身用戶密碼的方式包括：

mysql> ALTER USER USER() IDENTIFIED BY 'mypass';
mysql> SET PASSWORD = PASSWORD('mypass');

2.2.8 設置MySQL用戶密碼過期策略

設置系統參數default_password_lifetime作用于所有的用戶賬戶

• default_password_lifetime=180 設置180天過期
• default_password_lifetime=0 設置密碼不過期

如果為每個用戶設置了密碼過期策略，則會覆蓋上述系統參數

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; 密碼不過期
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; 默認過期策略

手動強制某個用戶密碼過期

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

2.2.9 mysql 用戶 lock

通過執行create user/alter user命令中帶account lock/unlock子句設置用戶的lock狀態

Create user語句默認的用戶是unlock狀態

# 創建的時候給用戶鎖定
mysql> create user abc2@localhost identified by 'mysql' account lock;

Alter user語句默認不會修改用戶的lock/unlock狀態

# 修改用戶為unlock
mysql> alter user abc2@'localhost' account unlock;

當客戶端使用lock狀態的用戶登錄MySQL時，會收到如此報錯
Access denied for user ‘user_name'@'host_name'.
Account is locked.

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

到此這篇關于Mysql 用戶權限管理實現的文章就介紹到這了,更多相關Mysql 用戶權限管理內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！