数据库安全与权限管理详解1. 权限管理基础1.1 用户创建-- 创建用户 CREATE USER app_userlocalhost IDENTIFIED BY strong_password; -- 创建可远程访问的用户 CREATE USER app_user% IDENTIFIED BY strong_password;1.2 权限授予-- 授予SELECT权限 GRANT SELECT ON mydb.* TO app_userlocalhost; -- 授予读写权限 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_userlocalhost; -- 授予所有权限 GRANT ALL PRIVILEGES ON mydb.* TO app_userlocalhost; -- 授予管理员权限 GRANT CREATE USER ON *.* TO adminlocalhost;1.3 权限撤销REVOKE DELETE ON mydb.* FROM app_userlocalhost;2. 角色管理2.1 创建角色-- 创建角色 CREATE ROLE app_readonly, app_readwrite, app_admin; -- 授予角色权限 GRANT SELECT ON mydb.* TO app_readonly; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_readwrite; GRANT ALL PRIVILEGES ON mydb.* TO app_admin;2.2 授予角色给用户-- 授予角色 GRANT app_readwrite TO app_userlocalhost; -- 设置默认角色 SET DEFAULT ROLE app_readwrite FOR app_userlocalhost;3. 行级安全3.1 MySQL行级权限-- 创建视图实现行级权限 CREATE VIEW user_orders AS SELECT * FROM orders WHERE user_id CURRENT_USER();3.2 PostgreSQL行级安全-- 启用行级安全 ALTER TABLE orders ENABLE ROW LEVEL SECURITY; -- 创建策略 CREATE POLICY user_orders ON orders FOR SELECT USING (user_id current_user_id()); CREATE POLICY user_orders_insert ON orders FOR INSERT WITH CHECK (user_id current_user_id());4. SQL注入防护4.1 使用参数化查询// 错误示例SQL注入 query : fmt.Sprintf(SELECT * FROM users WHERE name %s, userInput) // 正确示例参数化查询 query : SELECT * FROM users WHERE name ? row : db.QueryRowContext(ctx, query, userInput)4.2 ORM框架// 使用GORM var user User db.Where(name ?, userInput).First(user) // 使用参数化查询 db.Raw(SELECT * FROM users WHERE name ?, userInput).Scan(user)5. 敏感数据保护5.1 数据加密-- AES加密 INSERT INTO users (name, password) VALUES (John, AES_ENCRYPT(password, encryption_key)); SELECT AES_DECRYPT(password, encryption_key) FROM users WHERE name John;5.2 脱敏显示-- 邮箱脱敏 SELECT CONCAT(LEFT(email, 2), ***, SUBSTRING(email, INSTR(email, ))) FROM users; -- 手机号脱敏 SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) FROM users;5.3 Go语言加密实现import golang.org/x/crypto/bcrypt func HashPassword(password string) (string, error) { bytes, err : bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost) return string(bytes), err } func CheckPassword(password, hash string) bool { err : bcrypt.CompareHashAndPassword([]byte(hash), []byte(password)) return err nil }6. 审计日志6.1 启用审计-- MySQL Enterprise Audit INSTALL PLUGIN audit_log SONAME audit_log.so; ALTER TABLE mysql.general_log ADD COLUMN user_host VARCHAR(60);6.2 自定义审计表CREATE TABLE audit_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT, action VARCHAR(50), table_name VARCHAR(100), old_value TEXT, new_value TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ip_address VARCHAR(50) );6.3 触发器审计CREATE TRIGGER audit_users_update AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO audit_log (user_id, action, table_name, old_value, new_value) VALUES (OLD.id, UPDATE, users, OLD.name, NEW.name); END;7. 网络安全7.1 SSL连接# MySQL启用SSL [mysqld] ssl-ca /path/to/ca.pem ssl-cert /path/to/server-cert.pem ssl-key /path/to/server-key.pem require-secure-transport on7.2 Go语言SSL连接import crypto/tls rootCert, _ : ioutil.ReadFile(/path/to/ca.pem) certPool : x509.NewCertPool() certPool.AppendCertsFromPEM(rootCert) conn, err : mysql.DialTCP(tcp, nil, localhost:3306, user, password, dbname, tls.Config{ RootCAs: certPool, }, )8. 数据库防火墙8.1 SQL过滤-- 禁止删除操作 DELETE FROM users WHERE id 1; -- Error: Operation DELETE is not allowed -- 配置SQL防火墙规则 SET GLOBAL sql_mode NO_ENGINE_SUBSTITUTION;9. 备份与恢复9.1 物理备份# 使用xtrabackup xtrabackup --backup --target-dir/backup/full xtrabackup --prepare --target-dir/backup/full xtrabackup --copy-back --target-dir/backup/full9.2 逻辑备份# 使用mysqldump mysqldump -u root -p dbname backup.sql # 恢复 mysql -u root -p dbname backup.sql9.3 定时备份# crontab配置 0 2 * * * /usr/bin/mysqldump -u root -ppassword dbname /backup/dbname_$(date \%Y\%m\%d).sql10. 总结数据库安全是系统安全的重要组成部分需要从权限管理、SQL注入防护、数据加密、审计日志、网络安全等多个维度进行防护。合理的安全措施可以有效保护数据资产防止未授权访问和数据泄露。