CREATE USER [IF NOT EXISTS] 
    'username'@'hostname' 
    [IDENTIFIED BY 'password']
    [IDENTIFIED WITH auth_plugin]
    [PASSWORD EXPIRE]
    [ACCOUNT LOCK | ACCOUNT UNLOCK]
    [REQUIRE SSL]
    [WITH resource_option];

-- 创建用户（MySQL 8.0默认使用caching_sha2_password）
CREATE USER 'dba'@'%' IDENTIFIED BY 'qwe@123';

-- 使用mysql_native_password插件（兼容旧版本）
CREATE USER 'dba'@'%' IDENTIFIED WITH mysql_native_password BY 'qwe@123';

-- 使用caching_sha2_password插件（MySQL 8.0默认）
CREATE USER 'dba'@'%' IDENTIFIED WITH caching_sha2_password BY 'qwe@123';

-- 创建用户，密码立即过期
CREATE USER 'dba'@'%' IDENTIFIED BY 'qwe@123' PASSWORD EXPIRE;

-- 创建用户，密码90天后过期
CREATE USER 'dba'@'%' IDENTIFIED BY 'qwe@123' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建用户，账户锁定
CREATE USER 'dba'@'%' IDENTIFIED BY 'qwe@123' ACCOUNT LOCK;

-- 创建用户，要求SSL连接
CREATE USER 'dba'@'%' IDENTIFIED BY 'qwe@123' REQUIRE SSL;

-- 创建用户，设置资源限制
CREATE USER 'dba'@'%' IDENTIFIED BY 'qwe@123'
WITH 
    MAX_QUERIES_PER_HOUR 1000
    MAX_UPDATES_PER_HOUR 500
    MAX_CONNECTIONS_PER_HOUR 100
    MAX_USER_CONNECTIONS 10;

-- 推荐的用户创建方式
CREATE USER 'dba'@'%' 
IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!@#'
PASSWORD EXPIRE INTERVAL 90 DAY
REQUIRE SSL
WITH 
    MAX_QUERIES_PER_HOUR 10000
    MAX_CONNECTIONS_PER_HOUR 1000;

-- 修改用户密码
ALTER USER 'dba'@'%' IDENTIFIED BY 'NewPassword123!@#';

-- 修改用户密码并指定认证插件
ALTER USER 'dba'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPassword123!@#';

-- 修改密码过期策略
ALTER USER 'dba'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 立即过期密码
ALTER USER 'dba'@'%' PASSWORD EXPIRE;

-- 账户锁定/解锁
ALTER USER 'dba'@'%' ACCOUNT LOCK;
ALTER USER 'dba'@'%' ACCOUNT UNLOCK;

-- 要求SSL连接
ALTER USER 'dba'@'%' REQUIRE SSL;

-- 修改资源限制
ALTER USER 'dba'@'%'
WITH MAX_QUERIES_PER_HOUR 2000;

-- 重命名用户
RENAME USER 'dba'@'%' TO 'dba_admin'@'%';

-- 删除用户
DROP USER 'dba'@'%';

-- 如果用户不存在也不报错
DROP USER IF EXISTS 'dba'@'%';

-- 删除多个用户
DROP USER 'dba'@'%', 'appuser'@'localhost';

-- 查看所有用户
SELECT user, host, plugin, authentication_string 
FROM mysql.user;

-- 查看用户详细信息
SELECT 
    user,
    host,
    plugin,
    password_expired,
    password_last_changed,
    password_lifetime,
    account_locked,
    password_require_current
FROM mysql.user;

-- 查看用户创建语句（MySQL 8.0新特性）
SHOW CREATE USER 'dba'@'%';

CREATE USER 'dba'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$...' 
REQUIRE SSL PASSWORD EXPIRE INTERVAL 90 DAY 
ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT 
PASSWORD REUSE INTERVAL DEFAULT 
PASSWORD REQUIRE CURRENT DEFAULT;

GRANT privilege_type [(column_list)]
    [, privilege_type [(column_list)]] ...
ON [object_type] privilege_level
TO user [auth_option] [, user [auth_option]] ...
[WITH GRANT OPTION]
[AS user [WITH ROLE role]];

-- 授予所有数据库的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' WITH GRANT OPTION;

-- 授予testdb数据库的所有权限
GRANT ALL PRIVILEGES ON testdb.* TO 'dba'@'%';

-- 授予多个数据库的权限
GRANT SELECT, INSERT ON testdb.* TO 'appuser'@'%';
GRANT SELECT ON prod_db.* TO 'readonly'@'%';

-- 授予testdb.users表的所有权限
GRANT ALL PRIVILEGES ON testdb.users TO 'dba'@'%';

-- 授予特定表的特定权限
GRANT SELECT, INSERT, UPDATE ON testdb.users TO 'appuser'@'%';

-- 授予特定列的权限
GRANT SELECT (id, name), UPDATE (name) ON testdb.users TO 'appuser'@'%';

-- 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE testdb.get_user TO 'appuser'@'%';

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION testdb.calculate_total TO 'appuser'@'%';

-- 创建DBA用户并授予所有权限
CREATE USER 'dba'@'%' IDENTIFIED BY 'qwe@123';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 创建应用用户，授予特定数据库的读写权限
CREATE USER 'appuser'@'%' IDENTIFIED BY 'AppPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

-- 创建只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly123!';
GRANT SELECT ON *.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

-- 创建备份用户
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Backup123!';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

-- 创建监控用户
CREATE USER 'monitor'@'%' IDENTIFIED BY 'Monitor123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* TO 'monitor'@'%';
GRANT SELECT ON sys.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

-- 授予权限并允许该用户授予权限给其他用户
GRANT ALL PRIVILEGES ON testdb.* TO 'dba'@'%' WITH GRANT OPTION;

-- 刷新权限表，使权限立即生效
FLUSH PRIVILEGES;

REVOKE privilege_type [(column_list)]
    [, privilege_type [(column_list)]] ...
ON [object_type] privilege_level
FROM user [, user] ...;

-- 回收GRANT OPTION
REVOKE GRANT OPTION ON privilege_level FROM user;

-- 回收所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'dba'@'%';
FLUSH PRIVILEGES;

-- 回收特定数据库的权限
REVOKE ALL PRIVILEGES ON testdb.* FROM 'appuser'@'%';

-- 回收特定表的权限
REVOKE SELECT, INSERT ON testdb.users FROM 'appuser'@'%';

-- 回收特定权限
REVOKE DELETE ON testdb.* FROM 'appuser'@'%';
FLUSH PRIVILEGES;

-- 回收授予权限的能力
REVOKE GRANT OPTION ON *.* FROM 'dba'@'%';
FLUSH PRIVILEGES;

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'dba'@'%';

+---------------------------------------------------+
| Grants for dba@%                                  |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `dba`@`%`         |
| WITH GRANT OPTION                                 |
+---------------------------------------------------+

-- 查看用户创建语句（MySQL 8.0）
SHOW CREATE USER 'dba'@'%';

+------------------------------------------------------------------+
| CREATE USER for dba@%                                            |
+------------------------------------------------------------------+
| CREATE USER 'dba'@'%' IDENTIFIED WITH 'caching_sha2_password'    |
| AS '$A$005$...' REQUIRE SSL PASSWORD EXPIRE INTERVAL 90 DAY     |
| ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT                         |
| PASSWORD REUSE INTERVAL DEFAULT                                  |
| PASSWORD REQUIRE CURRENT DEFAULT                                |
+------------------------------------------------------------------+

-- 查看所有用户
SELECT user, host, plugin, authentication_string 
FROM mysql.user 
WHERE user = 'dba';

-- 查看用户全局权限
SELECT * FROM mysql.user WHERE user = 'dba' AND host = '%';

-- 查看用户数据库权限
SELECT * FROM mysql.db WHERE user = 'dba' AND host = '%';

-- 查看用户表权限
SELECT * FROM mysql.tables_priv WHERE user = 'dba' AND host = '%';

-- 查看用户列权限
SELECT * FROM mysql.columns_priv WHERE user = 'dba' AND host = '%';

-- 查看用户所有权限的完整脚本
SELECT 
    CONCAT('用户: ', user, '@', host) AS user_info,
    CONCAT('认证插件: ', plugin) AS auth_plugin,
    CONCAT('账户锁定: ', IF(account_locked = 'Y', '是', '否')) AS account_status,
    CONCAT('密码过期: ', IF(password_expired = 'Y', '是', '否')) AS password_status
FROM mysql.user 
WHERE user = 'dba' AND host = '%';

-- 查看用户权限详情
SELECT 
    '全局权限' AS privilege_level,
    CONCAT(user, '@', host) AS user_host,
    CASE 
        WHEN Select_priv = 'Y' THEN 'SELECT '
        ELSE ''
    END ||
    CASE 
        WHEN Insert_priv = 'Y' THEN 'INSERT '
        ELSE ''
    END ||
    CASE 
        WHEN Update_priv = 'Y' THEN 'UPDATE '
        ELSE ''
    END ||
    CASE 
        WHEN Delete_priv = 'Y' THEN 'DELETE '
        ELSE ''
    END AS privileges
FROM mysql.user 
WHERE user = 'dba' AND host = '%';

-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 创建角色并指定主机
CREATE ROLE 'dba'@'localhost', 'readonly'@'%';

-- 授予角色权限
GRANT SELECT ON appdb.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_write';
GRANT ALL PRIVILEGES ON appdb.* TO 'app_admin';

-- 授予角色给用户
GRANT 'app_read' TO 'appuser1'@'%';
GRANT 'app_write' TO 'appuser2'@'%';
GRANT 'app_admin' TO 'dba'@'%';

-- 授予多个角色
GRANT 'app_read', 'app_write' TO 'appuser3'@'%';

-- 查看当前用户角色
SELECT CURRENT_ROLE();

-- 激活角色（会话级别）
SET ROLE 'app_read';

-- 激活所有角色
SET ROLE ALL;

-- 激活默认角色（永久）
ALTER USER 'appuser1'@'%' DEFAULT ROLE 'app_read';

-- 激活所有默认角色
ALTER USER 'appuser1'@'%' DEFAULT ROLE ALL;

-- 查看所有角色
SELECT * FROM mysql.roles_mapping;

-- 查看用户角色
SHOW GRANTS FOR 'appuser1'@'%' USING 'app_read';

-- 查看角色权限
SHOW GRANTS FOR 'app_read';

-- 从用户回收角色
REVOKE 'app_read' FROM 'appuser1'@'%';

-- 回收角色权限
REVOKE SELECT ON appdb.* FROM 'app_read';

-- 删除角色
DROP ROLE 'app_read';

-- 1. 创建标准角色
CREATE ROLE 'db_readonly', 'db_readwrite', 'db_admin';

-- 2. 授予角色权限
GRANT SELECT ON *.* TO 'db_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'db_readwrite';
GRANT ALL PRIVILEGES ON appdb.* TO 'db_admin';

-- 3. 授予角色给用户并设置默认角色
CREATE USER 'appuser'@'%' IDENTIFIED BY 'Password123!';
GRANT 'db_readwrite' TO 'appuser'@'%';
ALTER USER 'appuser'@'%' DEFAULT ROLE 'db_readwrite';

-- 4. 验证角色权限
SHOW GRANTS FOR 'appuser'@'%' USING 'db_readwrite';

-- 检查组件是否安装
SELECT * FROM mysql.component WHERE component = 'validate_password';

-- 安装组件
INSTALL COMPONENT 'file://component_validate_password';

-- 查看组件状态
SHOW VARIABLES LIKE 'validate_password%';

-- 查看密码策略配置
SHOW VARIABLES LIKE 'validate_password%';

-- 设置密码最小长度
SET GLOBAL validate_password.length = 12;

-- 设置密码策略
SET GLOBAL validate_password.policy = STRONG;

-- 设置大小写字母数量
SET GLOBAL validate_password.mixed_case_count = 2;

-- 设置数字数量
SET GLOBAL validate_password.number_count = 2;

-- 设置特殊字符数量
SET GLOBAL validate_password.special_char_count = 2;

-- 检查密码是否包含用户名
SET GLOBAL validate_password.check_user_name = ON;

-- 查看全局密码过期策略
SHOW VARIABLES LIKE 'default_password_lifetime';

-- 设置全局密码有效期（天）
SET GLOBAL default_password_lifetime = 90;

-- 设置为永不过期
SET GLOBAL default_password_lifetime = 0;

-- 创建用户时设置密码过期
CREATE USER 'dba'@'%' IDENTIFIED BY 'Password123!'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 修改用户密码过期策略
ALTER USER 'dba'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 立即过期密码
ALTER USER 'dba'@'%' PASSWORD EXPIRE;

-- 查看用户密码信息
SELECT 
    user, 
    host, 
    password_last_changed, 
    password_expired,
    password_lifetime
FROM mysql.user 
WHERE user = 'dba';

-- 查看密码历史配置
SHOW VARIABLES LIKE 'password_history%';
SHOW VARIABLES LIKE 'password_reuse_interval%';

-- 设置密码历史数量（不能重复使用最近N个密码）
SET GLOBAL password_history = 5;

-- 设置密码重用间隔（天）
SET GLOBAL password_reuse_interval = 365;

-- 用户级别设置
ALTER USER 'dba'@'%' PASSWORD HISTORY 5;
ALTER USER 'dba'@'%' PASSWORD REUSE INTERVAL 365 DAY;

-- 设置修改密码时需要提供当前密码
SET GLOBAL password_require_current = ON;

-- 用户级别设置
ALTER USER 'dba'@'%' PASSWORD REQUIRE CURRENT;

-- 创建用户时指定认证插件
CREATE USER 'dba'@'%' IDENTIFIED WITH caching_sha2_password BY 'Password123!';

-- 修改用户认证插件
ALTER USER 'dba'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123!';

-- 查看用户认证插件
SELECT user, host, plugin FROM mysql.user WHERE user = 'dba';

-- 查看支持的认证插件
SELECT * FROM mysql.plugin WHERE name LIKE '%password%';

-- 查看SSL支持
SHOW VARIABLES LIKE '%ssl%';

-- 查看SSL状态
SHOW STATUS LIKE 'Ssl%';

-- 创建用户时要求SSL
CREATE USER 'dba'@'%' IDENTIFIED BY 'Password123!' REQUIRE SSL;

-- 修改用户要求SSL
ALTER USER 'dba'@'%' REQUIRE SSL;

-- 要求SSL并指定证书
ALTER USER 'dba'@'%' 
REQUIRE SSL 
REQUIRE ISSUER '/C=US/ST=CA/L=San Francisco/O=MySQL/CN=CA'
REQUIRE SUBJECT '/C=US/ST=CA/L=San Francisco/O=MySQL/CN=client';

-- 查看当前连接SSL状态
STATUS;

-- 或
SHOW STATUS LIKE 'Ssl_cipher';

-- 1. 为应用创建专用用户，只授予必要权限
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'%';

-- 2. 为只读查询创建只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly123!';
GRANT SELECT ON appdb.* TO 'readonly'@'%';

-- 3. 为备份创建专用用户
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Backup123!';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

-- 1. 创建标准角色
CREATE ROLE 'db_readonly', 'db_readwrite', 'db_admin';

-- 2. 授予角色权限
GRANT SELECT ON *.* TO 'db_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'db_readwrite';
GRANT ALL PRIVILEGES ON appdb.* TO 'db_admin';

-- 3. 授予角色给用户
CREATE USER 'appuser'@'%' IDENTIFIED BY 'Password123!';
GRANT 'db_readwrite' TO 'appuser'@'%';
ALTER USER 'appuser'@'%' DEFAULT ROLE 'db_readwrite';

-- 1. 配置强密码策略
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.mixed_case_count = 2;
SET GLOBAL validate_password.number_count = 2;
SET GLOBAL validate_password.special_char_count = 2;

-- 2. 设置密码过期策略
SET GLOBAL default_password_lifetime = 90;

-- 3. 设置密码历史
SET GLOBAL password_history = 5;
SET GLOBAL password_reuse_interval = 365;

-- 1. 定期检查账户状态
SELECT user, host, account_locked, password_expired 
FROM mysql.user;

-- 2. 锁定不使用的账户
ALTER USER 'olduser'@'%' ACCOUNT LOCK;

-- 3. 删除多余账户
DROP USER IF EXISTS 'unused_user'@'%';

-- 4. 重命名默认账户
RENAME USER 'root'@'localhost' TO 'admin'@'localhost';

-- 1. 定期查看用户权限
SELECT user, host, 
    CASE WHEN Select_priv = 'Y' THEN 'SELECT ' ELSE '' END ||
    CASE WHEN Insert_priv = 'Y' THEN 'INSERT ' ELSE '' END ||
    CASE WHEN Update_priv = 'Y' THEN 'UPDATE ' ELSE '' END ||
    CASE WHEN Delete_priv = 'Y' THEN 'DELETE ' ELSE '' END AS privileges
FROM mysql.user;

-- 2. 查看用户角色
SELECT user, host, default_role 
FROM mysql.user 
WHERE default_role IS NOT NULL;

-- 3. 记录权限变更（建议使用审计插件）

-- 检查所有用户权限
SELECT 
    CONCAT(user, '@', host) AS user_host,
    plugin AS auth_plugin,
    IF(account_locked = 'Y', '锁定', '正常') AS account_status,
    IF(password_expired = 'Y', '已过期', '正常') AS password_status,
    IFNULL(password_lifetime, '永不过期') AS password_lifetime
FROM mysql.user
ORDER BY user, host;

-- 导出用户创建语句
SELECT CONCAT('CREATE USER ', QUOTE(user), '@', QUOTE(host), 
    ' IDENTIFIED WITH ', QUOTE(plugin), 
    IF(password_expired = 'Y', ' PASSWORD EXPIRE', ''),
    IF(account_locked = 'Y', ' ACCOUNT LOCK', ' ACCOUNT UNLOCK'),
    ';') AS create_user_sql
FROM mysql.user
WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');

-- 1. 刷新权限表
FLUSH PRIVILEGES;

-- 2. 检查用户是否激活角色
SELECT CURRENT_ROLE();
SET ROLE ALL;

-- 3. 检查权限是否正确授予
SHOW GRANTS FOR 'user'@'host';

-- 1. 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 2. 调整密码策略（临时）
SET GLOBAL validate_password.policy = LOW;

-- 3. 使用符合策略的强密码
CREATE USER 'dba'@'%' IDENTIFIED BY 'StrongPassword123!@#';

-- 1. 修改用户认证插件
ALTER USER 'dba'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123!';

-- 2. 或升级客户端到支持caching_sha2_password的版本

-- 1. 激活角色
SET ROLE 'role_name';

-- 2. 设置默认角色
ALTER USER 'user'@'%' DEFAULT ROLE 'role_name';

-- 3. 检查角色权限
SHOW GRANTS FOR 'user'@'%' USING 'role_name';

-- 1. 检查是否有多个权限来源（直接权限+角色权限）
SHOW GRANTS FOR 'user'@'%';

-- 2. 回收所有相关权限
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%';
REVOKE 'role_name' FROM 'user'@'%';

-- 3. 刷新权限
FLUSH PRIVILEGES;