-- 基本语法
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例
CREATE USER 'app'@'localhost' IDENTIFIED BY 'AppPass123!';
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'AppPass123!';
CREATE USER 'app'@'%' IDENTIFIED BY 'AppPass123!';

-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';

-- 或
SET PASSWORD FOR 'username'@'host' = 'NewPassword123!';

-- 重命名用户
RENAME USER 'old_user'@'host' TO 'new_user'@'host';

-- 锁定/解锁用户
ALTER USER 'username'@'host' ACCOUNT LOCK;
ALTER USER 'username'@'host' ACCOUNT UNLOCK;

DROP USER IF EXISTS 'username'@'host';
DROP USER 'user1'@'host', 'user2'@'host';

-- 查看所有用户
SELECT User, Host, Account_locked FROM mysql.user;

-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';
SHOW GRANTS FOR CURRENT_USER;

-- 基本语法
GRANT privilege_type ON db_name.table_name TO 'username'@'host';

-- 常用权限
-- ALL PRIVILEGES: 所有权限
-- SELECT, INSERT, UPDATE, DELETE: 数据操作
-- CREATE, DROP, ALTER: 数据定义
-- INDEX, REFERENCES: 索引和约束

-- 示例：授予数据库所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'localhost';

-- 示例：授予特定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'localhost';
GRANT SELECT ON mydb.readonly_table TO 'reader'@'%';

-- 示例：授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE mydb.my_procedure TO 'app'@'localhost';

-- 示例：授予列级权限
GRANT SELECT (id, name, email), UPDATE (name, email) ON mydb.users TO 'app'@'localhost';

REVOKE privilege_type ON db_name.table_name FROM 'username'@'host';

-- 示例
REVOKE DELETE ON mydb.* FROM 'app'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'localhost';

FLUSH PRIVILEGES;

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

-- 配置参数
-- validate_password.length: 最小长度
-- validate_password.mixed_case_count: 大小写字母数
-- validate_password.number_count: 数字个数
-- validate_password.special_char_count: 特殊字符数
-- validate_password.policy: 策略级别

-- 临时设置
SET GLOBAL validate_password.length = 8;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 0;

-- 配置文件设置
[mysqld]
validate_password.length = 8
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.policy = LOW

-- 只允许本地连接
CREATE USER 'app'@'localhost';

-- 允许特定 IP
CREATE USER 'app'@'192.168.1.100';
CREATE USER 'app'@'192.168.1.%';     -- IP段
CREATE USER 'app'@'192.168.%.%';     -- B类网段

-- 禁止远程 root 登录
CREATE USER 'root'@'localhost';
DROP USER 'root'@'%';

-- 检查 SSL 状态
SHOW VARIABLES LIKE 'have_ssl';
SHOW VARIABLES LIKE 'ssl_%';

-- 强制 SSL 连接
[mysqld]
require_secure_transport = ON
ssl_ca = /path/to/ca.pem
ssl_cert = /path/to/server-cert.pem
ssl_key = /path/to/server-key.pem

# CentOS/RHEL
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload

# 只允许特定 IP 访问 3306 端口
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port port="3306" protocol="tcp" accept'

# Ubuntu/Debian
ufw allow from 192.168.1.0/24 to any port 3306

-- 使用 AES 加密
INSERT INTO secrets (name, data) VALUES ('api_key', AES_ENCRYPT('secret_value', 'encryption_key'));
SELECT AES_DECRYPT(data, 'encryption_key') FROM secrets WHERE name = 'api_key';

-- MySQL 8.0 内置函数
INSERT INTO secrets (name, data) VALUES ('api_key', TO_BASE64(AES_ENCRYPT('secret_value', SHA2('encryption_key', 256))));

-- 创建视图隐藏敏感字段
CREATE VIEW user_public AS
SELECT id, username, email,
       CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS phone,
       CONCAT(LEFT(id_card, 6), '********', RIGHT(id_card, 4)) AS id_card
FROM users;

-- 授予视图权限
GRANT SELECT ON mydb.user_public TO 'app'@'localhost';

-- 创建安全视图
CREATE VIEW my_orders AS
SELECT * FROM orders WHERE user_id = CURRENT_USER_ID();

-- 或使用存储过程
CREATE PROCEDURE get_my_orders()
BEGIN
    SELECT * FROM orders WHERE user_id = CONNECTION_ID();
END;

-- 启用通用查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';

-- my.cnf 配置
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/query.log
log_output = FILE

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

-- 配置
[mysqld]
audit_log = ON
audit_log_file = /var/log/mysql/audit.log
audit_log_policy = ALL
audit_log_rotate_on_size = 100M

-- 查看审计日志
SELECT * FROM mysql.audit_log;

# 1. 初始化安全配置
mysql_secure_installation

# 2. 禁止 load_file 和 load data
SET GLOBAL local_infile = 0;

# 3. 禁用 symbol_links
symbolic-links = 0

# 4. 禁止使用 mysql 命令历史
rm -f ~/.mysql_history
ln -s /dev/null ~/.mysql_history

# 5. 修改默认端口
[mysqld]
port = 3307

# 6. 禁止 root 远程登录
# 在 mysql.user 表中删除 root@'%' 记录

# 7. 定期更新密码
ALTER USER 'app'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

# 8. 最小权限原则
# 只授予应用程序需要的最小权限

✅ 用户管理：创建、修改、删除用户
✅ 权限管理：GRANT/REVOKE 精细控制
✅ 密码策略：强密码要求和定期更换
✅ 网络安全：SSL/TLS、防火墙、IP限制
✅ 敏感数据：AES加密、数据脱敏
✅ 审计日志：记录所有操作行为