forked from lunanting/cangku
79 lines
3.9 KiB
SQL
79 lines
3.9 KiB
SQL
-- CommerceInsight Phase 1 permissions schema
|
|
-- Target database: shujuku2_system
|
|
-- Purpose:
|
|
-- 1. Board-level permissions
|
|
-- 2. Board-level data scopes
|
|
-- 3. Stable user-to-owner bindings
|
|
-- 4. Optional field-level permissions for V1.5
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
USE shujuku2_system;
|
|
|
|
CREATE TABLE IF NOT EXISTS board_permissions (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
page_key VARCHAR(100) NOT NULL,
|
|
board_key VARCHAR(100) NOT NULL,
|
|
subject_type ENUM('role', 'department', 'user') NOT NULL,
|
|
subject_id VARCHAR(100) NOT NULL,
|
|
allowed TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_board_permissions_subject (page_key, board_key, subject_type, subject_id),
|
|
KEY idx_board_permissions_board (page_key, board_key),
|
|
KEY idx_board_permissions_subject (subject_type, subject_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Board-level permissions';
|
|
|
|
CREATE TABLE IF NOT EXISTS board_data_scopes (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
page_key VARCHAR(100) NOT NULL,
|
|
board_key VARCHAR(100) NOT NULL,
|
|
subject_type ENUM('role', 'department', 'user') NOT NULL,
|
|
subject_id VARCHAR(100) NOT NULL,
|
|
scope_type ENUM('all', 'self', 'department', 'self_and_department', 'custom_departments') NOT NULL DEFAULT 'department',
|
|
scope_value_json JSON NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_board_data_scopes_subject (page_key, board_key, subject_type, subject_id),
|
|
KEY idx_board_data_scopes_board (page_key, board_key),
|
|
KEY idx_board_data_scopes_subject (subject_type, subject_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Board-level data scopes';
|
|
|
|
CREATE TABLE IF NOT EXISTS user_owner_bindings (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id BIGINT NOT NULL,
|
|
binding_type ENUM('owner', 'oper', 'dept') NOT NULL,
|
|
binding_value VARCHAR(100) NOT NULL,
|
|
is_primary TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_user_owner_binding (user_id, binding_type, binding_value),
|
|
KEY idx_user_owner_bindings_user (user_id),
|
|
KEY idx_user_owner_bindings_lookup (binding_type, binding_value)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Stable mapping between system users and business owners';
|
|
|
|
CREATE TABLE IF NOT EXISTS board_field_permissions (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
page_key VARCHAR(100) NOT NULL,
|
|
board_key VARCHAR(100) NOT NULL,
|
|
field_key VARCHAR(100) NOT NULL,
|
|
subject_type ENUM('role', 'department', 'user') NOT NULL,
|
|
subject_id VARCHAR(100) NOT NULL,
|
|
visible TINYINT(1) NOT NULL DEFAULT 1,
|
|
exportable TINYINT(1) NOT NULL DEFAULT 0,
|
|
mask_rule VARCHAR(50) NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uk_board_field_permissions_subject (page_key, board_key, field_key, subject_type, subject_id),
|
|
KEY idx_board_field_permissions_board (page_key, board_key),
|
|
KEY idx_board_field_permissions_field (field_key),
|
|
KEY idx_board_field_permissions_subject (subject_type, subject_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Field-level permissions for V1.5';
|
|
|
|
-- Optional bootstrap examples:
|
|
-- INSERT INTO board_permissions (page_key, board_key, subject_type, subject_id, allowed)
|
|
-- VALUES ('tmall', 'overview', 'role', 'admin', 1);
|
|
--
|
|
-- INSERT INTO board_data_scopes (page_key, board_key, subject_type, subject_id, scope_type, scope_value_json)
|
|
-- VALUES ('tmall', 'overview', 'department', '12', 'custom_departments', JSON_ARRAY('Sales', 'E-Commerce'));
|