数据库安全与权限管理

数据库安全与权限管理

1. 技术分析

1.1 数据库安全概述

数据库安全是保护数据资产的关键：

安全威胁 未授权访问: 密码泄露 SQL注入: 恶意SQL 数据泄露: 敏感信息暴露 数据篡改: 非法修改 安全措施: 访问控制 加密存储 审计日志

1.2 权限管理

权限级别 全局权限: ALL PRIVILEGES 数据库级别: 特定数据库 表级别: 特定表 列级别: 特定列 权限类型: 读权限: SELECT 写权限: INSERT, UPDATE, DELETE 管理权限: CREATE, DROP, GRANT

1.3 安全原则

2. 核心功能实现

2.1 用户管理

-- 创建用户 CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password'; -- 创建带过期时间的用户 CREATE USER 'temp_user'@'%' IDENTIFIED BY 'password' WITH EXPIRE INTERVAL 30 DAY; -- 修改用户密码 ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password'; -- 锁定用户 ALTER USER 'suspicious_user'@'localhost' ACCOUNT LOCK; -- 删除用户 DROP USER 'old_user'@'localhost';

2.2 权限管理

-- 授予数据库权限 GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'app_user'@'localhost'; -- 授予表级权限 GRANT SELECT ON mydatabase.users TO 'read_only_user'@'%'; -- 授予列级权限 GRANT SELECT (id, name) ON mydatabase.users TO 'limited_user'@'localhost'; -- 授予管理权限 GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; -- 撤销权限 REVOKE DELETE ON mydatabase.orders FROM 'app_user'@'localhost'; -- 查看权限 SHOW GRANTS FOR 'app_user'@'localhost';

2.3 安全工具

class DatabaseSecurityManager: def __init__(self, connection): self.connection = connection def create_user(self, username, password, host='localhost', privileges=None): cursor = self.connection.cursor() cursor.execute(f"CREATE USER '{username}'@'{host}' IDENTIFIED BY '{password}'") if privileges: for db, privs in privileges.items(): priv_str = ', '.join(privs) cursor.execute(f"GRANT {priv_str} ON {db} TO '{username}'@'{host}'") self.connection.commit() def audit_user_actions(self, username=None): cursor = self.connection.cursor() if username: query = f"SELECT * FROM audit_log WHERE user = '{username}'" else: query = "SELECT * FROM audit_log ORDER BY timestamp DESC LIMIT 100" cursor.execute(query) return cursor.fetchall() def check_weak_passwords(self): weak_patterns = ['password', '123456', 'qwerty'] cursor = self.connection.cursor() cursor.execute("SELECT user, host FROM mysql.user") weak_users = [] for user, host in cursor.fetchall(): cursor.execute(f"SELECT authentication_string FROM mysql.user WHERE user = '{user}' AND host = '{host}'") hash_val = cursor.fetchone()[0] for pattern in weak_patterns: if pattern.lower() in user.lower(): weak_users.append(user) break return weak_users def validate_permissions(self): cursor = self.connection.cursor() cursor.execute(""" SELECT user, host, privilege_type, table_name FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE privilege_type = 'ALL' """) overprivileged = cursor.fetchall() return overprivileged

3. 性能对比

3.1 认证方式对比

3.2 加密方式对比

3.3 审计日志对比

4. 最佳实践

4.1 安全策略

class SecurityPolicy: def __init__(self): pass def password_policy(self): return { 'min_length': 12, 'require_special_char': True, 'require_number': True, 'expiry_days': 90 } def access_policy(self): return { 'allow_remote_root': False, 'max_connections': 100, 'idle_timeout': 300 }

4.2 SQL注入防护

class SQLInjectionProtector: def __init__(self): pass def sanitize_input(self, input_string): dangerous_patterns = ["'", "\"", ";", "--", "UNION"] for pattern in dangerous_patterns: input_string = input_string.replace(pattern, "") return input_string def use_parameterized_queries(self, query, params): return {'query': query, 'params': params}

5. 总结

数据库安全是系统安全的重要组成部分：

1. 用户管理：创建、修改、删除用户
2. 权限控制：最小权限原则
3. 加密存储：保护敏感数据
4. 审计日志：追踪操作

对比数据如下：

• SSL/TLS是生产环境的必备
• AES-256适合敏感数据加密
• 最小权限原则是安全基础
• 密码策略应强制复杂密码要求