数据库安全与权限管理详解
数据库安全与权限管理详解
1. 权限管理基础
1.1 用户创建
-- 创建用户 CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password'; -- 创建可远程访问的用户 CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';1.2 权限授予
-- 授予SELECT权限 GRANT SELECT ON mydb.* TO 'app_user'@'localhost'; -- 授予读写权限 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost'; -- 授予所有权限 GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost'; -- 授予管理员权限 GRANT CREATE USER ON *.* TO 'admin'@'localhost';1.3 权限撤销
REVOKE DELETE ON mydb.* FROM 'app_user'@'localhost';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_user'@'localhost'; -- 设置默认角色 SET DEFAULT ROLE 'app_readwrite' FOR 'app_user'@'localhost';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 -p'password' dbname > /backup/dbname_$(date +\%Y\%m\%d).sql10. 总结
数据库安全是系统安全的重要组成部分，需要从权限管理、SQL注入防护、数据加密、审计日志、网络安全等多个维度进行防护。合理的安全措施可以有效保护数据资产，防止未授权访问和数据泄露。