SELECT User, Host FROM mysql.user;

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 創(chuàng)建可以從本地連接的用戶
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
-- 創(chuàng)建可以從任何主機(jī)連接的用戶(不推薦，存在安全風(fēng)險(xiǎn))
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'AnotherStrongPassword!';
-- 創(chuàng)建可以從特定IP段連接的用戶
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPassword456';

-- 設(shè)置密碼過期策略
CREATE USER 'temp_user'@'localhost' 
IDENTIFIED BY 'temp_pass' 
PASSWORD EXPIRE INTERVAL 90 DAY;
-- 鎖定新創(chuàng)建的用戶
CREATE USER 'locked_user'@'localhost' 
IDENTIFIED BY 'locked_pass' 
ACCOUNT LOCK;

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

-- 授予特定數(shù)據(jù)庫的所有權(quán)限
GRANT ALL PRIVILEGES ON mydb.* TO 'dev_user'@'localhost';
-- 授予特定表的SELECT, INSERT, UPDATE權(quán)限
GRANT SELECT, INSERT, UPDATE ON mydb.customers TO 'app_user'@'192.168.1.%';
-- 授予創(chuàng)建臨時(shí)表的權(quán)限
GRANT CREATE TEMPORARY TABLES ON *.* TO 'report_user'@'localhost';
-- 授予執(zhí)行存儲過程的權(quán)限
GRANT EXECUTE ON PROCEDURE mydb.update_stats TO 'proc_user'@'localhost';
-- 授予所有數(shù)據(jù)庫的只讀權(quán)限
GRANT SELECT ON *.* TO 'readonly_user'@'%';

GRANT ALL PRIVILEGES ON mydb.* 
TO 'admin_user'@'localhost' 
WITH GRANT OPTION;

FLUSH PRIVILEGES;

-- 查看特定用戶的權(quán)限
SHOW GRANTS FOR 'username'@'host';
-- 例如
SHOW GRANTS FOR 'dev_user'@'localhost';

-- 基本語法
REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';
-- 示例：撤銷INSERT權(quán)限
REVOKE INSERT ON mydb.* FROM 'app_user'@'192.168.1.%';
-- 撤銷所有權(quán)限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

-- MySQL 5.7.6及以上版本
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
-- 舊版本語法
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

-- 鎖定用戶
ALTER USER 'username'@'host' ACCOUNT LOCK;
-- 解鎖用戶
ALTER USER 'username'@'host' ACCOUNT UNLOCK;

DROP USER 'username'@'host';
-- 示例
DROP USER 'old_user'@'localhost';

-- 創(chuàng)建角色
CREATE ROLE 'read_only', 'app_developer';
-- 授予角色權(quán)限
GRANT SELECT ON *.* TO 'read_only';
GRANT ALL ON app_db.* TO 'app_developer';
-- 將角色授予用戶
GRANT 'read_only' TO 'report_user'@'localhost';
GRANT 'app_developer' TO 'dev_user'@'localhost';
-- 激活角色
SET DEFAULT ROLE ALL TO 'dev_user'@'localhost';

-- 設(shè)置全局密碼策略
SET GLOBAL validate_password.policy = STRONG;
-- 創(chuàng)建用戶時(shí)指定密碼策略
CREATE USER 'secure_user'@'localhost' 
IDENTIFIED WITH 'mysql_native_password' BY 'Complex@Password123' 
PASSWORD REQUIRE CURRENT;