all-project/docs/设计文档/database/oss/minio-security-implementation.sql

-- =====================================================
-- MinIO文件存储安全实施脚本
-- 创建时间: 2025-01-27
-- 说明: 将MinIO从public访问改为private + 预签名URL访问 + 访问密码
-- =====================================================

-- 1. 更新OSS配置，将访问策略改为私有
UPDATE sys_oss_config 
SET access_policy =  '0' -- 桶权限类型(0=private 1=public 2=custom)
WHERE config_key = 'minio';

-- 2. 创建文件权限表
CREATE TABLE IF NOT EXISTS sys_file_permission (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    oss_id BIGINT NOT NULL COMMENT '文件ID',
    user_id BIGINT COMMENT '用户ID（为空表示角色权限）',
    role_id BIGINT COMMENT '角色ID（为空表示用户权限）',
    permission_type VARCHAR(20) NOT NULL COMMENT '权限类型：READ, WRITE, DELETE',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    create_by BIGINT COMMENT '创建者',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    update_by BIGINT COMMENT '更新者',
    deleted TINYINT DEFAULT 0 COMMENT '删除标志（0代表存在 1代表删除）',
    tenant_id BIGINT DEFAULT 0 COMMENT '租户编号'
) COMMENT '文件权限表';

-- 3. 创建文件访问日志表
CREATE TABLE IF NOT EXISTS sys_file_access_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    oss_id BIGINT NOT NULL COMMENT '文件ID',
    user_id BIGINT NOT NULL COMMENT '访问用户ID',
    access_type VARCHAR(20) NOT NULL COMMENT '访问类型：DOWNLOAD, PREVIEW',
    access_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '访问时间',
    ip_address VARCHAR(50) COMMENT '访问IP',
    user_agent VARCHAR(500) COMMENT '用户代理',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT '文件访问日志表';

-- 4. 创建bucket访问密码表
CREATE TABLE IF NOT EXISTS sys_bucket_access_password (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    bucket_name VARCHAR(100) NOT NULL COMMENT 'bucket名称',
    access_password VARCHAR(255) NOT NULL COMMENT '访问密码（加密存储）',
    password_salt VARCHAR(100) NOT NULL COMMENT '密码盐值',
    is_active TINYINT DEFAULT 1 COMMENT '是否启用（1启用 0禁用）',
    expire_time DATETIME COMMENT '密码过期时间',
    max_access_count INT DEFAULT 0 COMMENT '最大访问次数（0表示无限制）',
    current_access_count INT DEFAULT 0 COMMENT '当前访问次数',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    create_by BIGINT COMMENT '创建者',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    update_by BIGINT COMMENT '更新者',
    deleted TINYINT DEFAULT 0 COMMENT '删除标志（0代表存在 1代表删除）',
    tenant_id BIGINT DEFAULT 0 COMMENT '租户编号',
    UNIQUE KEY uk_bucket_name (bucket_name)
) COMMENT 'bucket访问密码表';

-- 5. 创建bucket访问记录表
CREATE TABLE IF NOT EXISTS sys_bucket_access_record (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    bucket_name VARCHAR(100) NOT NULL COMMENT 'bucket名称',
    access_password_id BIGINT NOT NULL COMMENT '访问密码ID',
    access_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '访问时间',
    access_ip VARCHAR(50) COMMENT '访问IP',
    access_user_agent VARCHAR(500) COMMENT '用户代理',
    access_result TINYINT DEFAULT 1 COMMENT '访问结果（1成功 0失败）',
    fail_reason VARCHAR(500) COMMENT '失败原因',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT 'bucket访问记录表';

-- 6. 创建索引
CREATE INDEX idx_file_permission_oss_id ON sys_file_permission(oss_id);
CREATE INDEX idx_file_permission_user_id ON sys_file_permission(user_id);
CREATE INDEX idx_file_permission_role_id ON sys_file_permission(role_id);
CREATE INDEX idx_file_access_log_oss_id ON sys_file_access_log(oss_id);
CREATE INDEX idx_file_access_log_user_id ON sys_file_access_log(user_id);
CREATE INDEX idx_file_access_log_access_time ON sys_file_access_log(access_time);
CREATE INDEX idx_bucket_access_password_bucket_name ON sys_bucket_access_password(bucket_name);
CREATE INDEX idx_bucket_access_record_bucket_name ON sys_bucket_access_record(bucket_name);
CREATE INDEX idx_bucket_access_record_access_time ON sys_bucket_access_record(access_time);

-- 7. 插入默认bucket访问密码（示例：ruoyi bucket）
INSERT INTO sys_bucket_access_password (bucket_name, access_password, password_salt, is_active, max_access_count, create_by, tenant_id) VALUES 
('ruoyi', 'e10adc3949ba59abbe56e057f20f883e', 'ruoyi_salt_2025', 1, 0, 1, 0);

-- 8. 插入默认权限数据（可选）
-- 为现有文件添加默认权限，允许所有登录用户读取
INSERT INTO sys_file_permission (oss_id, permission_type, create_by, tenant_id)
SELECT 
    oss_id,
    'READ',
    1,
    0
FROM sys_oss 
-- WHERE deleted = 0;

-- 9. 验证更新结果
SELECT 
    config_key,
    access_policy,
    bucket_name,
    endpoint
FROM sys_oss_config 
WHERE config_key = 'minio';

-- 10. 显示表结构
DESCRIBE sys_file_permission;
DESCRIBE sys_file_access_log;
DESCRIBE sys_bucket_access_password;
DESCRIBE sys_bucket_access_record;