数据库安全与权限管理详解

数据库安全与权限管理详解

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 = on

7.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/full

9.2 逻辑备份

# 使用mysqldump
mysqldump -u root -p dbname > backup.sql

# 恢复
mysql -u root -p dbname < backup.sql

9.3 定时备份

# crontab配置
0 2 * * * /usr/bin/mysqldump -u root -p'password' dbname > /backup/dbname_$(date +\%Y\%m\%d).sql

10. 总结

数据库安全是系统安全的重要组成部分，需要从权限管理、SQL注入防护、数据加密、审计日志、网络安全等多个维度进行防护。合理的安全措施可以有效保护数据资产，防止未授权访问和数据泄露。