-- 创建本地用户
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'App!Str0ngPass123';

-- 创建允许远程访问的用户
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'App!Str0ngPass123';

-- 创建允许任意主机访问的用户（慎用）
CREATE USER 'appuser'@'%' IDENTIFIED BY 'App!Str0ngPass123';

-- MySQL 8.0 默认认证插件
CREATE USER 'appuser'@'%'
    IDENTIFIED WITH caching_sha2_password BY 'App!Str0ngPass123'
    PASSWORD EXPIRE INTERVAL 90 DAY       -- 90天过期
    FAILED_LOGIN_ATTEMPTS 3               -- 3次失败锁定
    PASSWORD_LOCK_TIME 2;                 -- 锁定2天

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

-- 设置密码策略
SET GLOBAL validate_password.policy = MEDIUM;    -- LOW/MEDIUM/STRONG
SET GLOBAL validate_password.length = 12;        -- 最短12位
SET GLOBAL validate_password.mixed_case_count = 1; -- 至少1个大小写
SET GLOBAL validate_password.number_count = 1;    -- 至少1个数字
SET GLOBAL validate_password.special_char_count = 1; -- 至少1个特殊字符

-- 修改密码
ALTER USER 'appuser'@'%' IDENTIFIED BY 'New!Pass123456';

-- 修改密码认证插件（兼容旧客户端）
ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'New!Pass123456';

-- 查看所有用户
SELECT User, Host, plugin, authentication_string, password_lifetime
FROM mysql.user ORDER BY User;

-- 重命名用户
RENAME USER 'oldname'@'%' TO 'newname'@'%';

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

-- 锁定/解锁用户
ALTER USER 'appuser'@'%' ACCOUNT LOCK;
ALTER USER 'appuser'@'%' ACCOUNT UNLOCK;

-- 授予单库读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

-- 授予只读权限
GRANT SELECT ON myapp.* TO 'readonly'@'%';

-- 授予指定表权限
GRANT SELECT, INSERT ON myapp.users TO 'usermgr'@'%';

-- 授予指定列权限
GRANT SELECT (username, email, created_at) ON myapp.users TO 'limited'@'%';

-- 授予全局权限
GRANT PROCESS, REPLICATION SLAVE ON *.* TO 'monitor'@'%';

-- 授予带 GRANT OPTION 的权限
GRANT ALL PRIVILEGES ON myapp.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 查看权限
SHOW GRANTS FOR 'appuser'@'%';
SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE LIKE "'appuser'%";

-- 撤销权限
REVOKE DELETE ON myapp.* FROM 'appuser'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM 'appuser'@'%';

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

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

-- 将角色授予用户
GRANT 'app_read' TO 'report_user'@'%';
GRANT 'app_read', 'app_write' TO 'appuser'@'%';
GRANT 'app_admin' TO 'dba'@'%';

-- 设置默认角色
SET DEFAULT ROLE 'app_read', 'app_write' TO 'appuser'@'%';

-- 查看角色
SELECT * FROM mysql.role_edges;
SHOW GRANTS FOR 'appuser'@'%' USING 'app_read', 'app_write';

-- 开启通用日志（记录所有 SQL，生产慎用）
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- 查看日志位置
SHOW VARIABLES LIKE 'general_log%';

# MySQL Enterprise Audit（企业版）
# 或使用开源替代：MariaDB Audit Plugin

-- 安装审计插件
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 配置审计策略
SET GLOBAL audit_log_policy = ALL;        -- ALL/LOGINS/QUERIES/NONE
SET GLOBAL audit_log_format = JSON;       -- OLD/NEW/JSON/CSV

-- 查看当前连接
SELECT * FROM information_schema.PROCESSLIST;

-- 查看历史连接（Performance Schema）
SELECT 
    USER, HOST, 
    COUNT_STAR AS total_connections,
    SUM_ROWS_EXAMINED AS total_rows_examined
FROM performance_schema.events_statements_summary_by_user_by_event_name
GROUP BY USER, HOST;

-- 查看 SSL 状态
SHOW VARIABLES LIKE '%ssl%';

-- 创建要求 SSL 的用户
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'Pass123!' REQUIRE SSL;

-- 连接时使用 SSL
mysql -u secure_user -p --ssl-mode=REQUIRED

-- 查看当前连接的 SSL 状态
SHOW STATUS LIKE 'Ssl_cipher';

-- InnoDB 表空间加密（MySQL 8.0+）
-- 1. 配置密钥环
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

-- 2. 创建加密表
CREATE TABLE sensitive_data (
    id BIGINT PRIMARY KEY,
    ssn VARCHAR(20)
) ENCRYPTION='Y';

-- 3. 加密已有表
ALTER TABLE users ENCRYPTION='Y';

-- AES 加密/解密
-- 加密
INSERT INTO users (username, email, phone)
VALUES ('zhangsan', 
        HEX(AES_ENCRYPT('[email protected]', 'secret_key')),
        HEX(AES_ENCRYPT('13800000001', 'secret_key')));

-- 解密
SELECT username,
       AES_DECRYPT(UNHEX(email), 'secret_key') AS email,
       AES_DECRYPT(UNHEX(phone), 'secret_key') AS phone
FROM users;

-- ❌ 不安全：直接拼接 SQL
-- 应用代码：SELECT * FROM users WHERE username = '${input}'
-- 输入：' OR '1'='1
-- 实际执行：SELECT * FROM users WHERE username = '' OR '1'='1'
-- 结果：返回所有用户

-- ✅ 使用参数化查询（Prepared Statement）
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
SET @username = 'zhangsan';
EXECUTE stmt USING @username;
DEALLOCATE PREPARE stmt;

-- ✅ 应用层使用 ORM 或参数化查询
-- Java (JDBC): PreparedStatement
-- Python: cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
-- Go: db.Query("SELECT * FROM users WHERE username = ?", username)
-- PHP: PDO prepared statements

-- 1. 最小权限原则
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app'@'%';
-- 不要给 DROP, ALTER, GRANT 等权限

-- 2. 输入验证
-- 应用层对所有用户输入进行验证和转义

-- 3. 关闭 local_infile
SET GLOBAL local_infile = OFF;

-- 4. 使用 Web 应用防火墙（WAF）