54 lines
2.3 KiB
MySQL
54 lines
2.3 KiB
MySQL
|
-- 权限 2.0：租户成员、资源申请、ACL 生命周期和审批动作
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS tenant_members (
|
|||
|
|
tenant_id TEXT NOT NULL,
|
|||
|
|
user_id TEXT NOT NULL,
|
|||
|
|
role TEXT NOT NULL DEFAULT 'member',
|
|||
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|||
|
|
invited_by TEXT,
|
|||
|
|
joined_at TEXT,
|
|||
|
|
expires_at TEXT,
|
|||
|
|
PRIMARY KEY (tenant_id, user_id)
|
|||
|
|
);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_members_user ON tenant_members(user_id, status);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_tenant_members_tenant ON tenant_members(tenant_id, status);
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS resource_access_requests (
|
|||
|
|
id TEXT PRIMARY KEY,
|
|||
|
|
tenant_id TEXT NOT NULL,
|
|||
|
|
resource_type TEXT NOT NULL,
|
|||
|
|
resource_id TEXT NOT NULL,
|
|||
|
|
applicant_id TEXT NOT NULL,
|
|||
|
|
principal_type TEXT NOT NULL DEFAULT 'user',
|
|||
|
|
principal_id TEXT NOT NULL,
|
|||
|
|
requested_permissions TEXT NOT NULL DEFAULT '[]',
|
|||
|
|
reason TEXT,
|
|||
|
|
approval_id TEXT,
|
|||
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|||
|
|
expires_at TEXT,
|
|||
|
|
created_at TEXT NOT NULL,
|
|||
|
|
decided_at TEXT,
|
|||
|
|
decided_by TEXT
|
|||
|
|
);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_resource_access_requests_resource
|
|||
|
|
ON resource_access_requests(resource_type, resource_id, status);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_resource_access_requests_applicant
|
|||
|
|
ON resource_access_requests(applicant_id, status);
|
|||
|
|
|
|||
|
|
ALTER TABLE acls ADD COLUMN IF NOT EXISTS tenant_id TEXT;
|
|||
|
|
ALTER TABLE acls ADD COLUMN IF NOT EXISTS granted_by TEXT;
|
|||
|
|
ALTER TABLE acls ADD COLUMN IF NOT EXISTS source_request_id TEXT;
|
|||
|
|
ALTER TABLE acls ADD COLUMN IF NOT EXISTS expires_at TEXT;
|
|||
|
|
ALTER TABLE acls ADD COLUMN IF NOT EXISTS revoked_at TEXT;
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_acls_tenant_active ON acls(tenant_id, revoked_at, expires_at);
|
|||
|
|
|
|||
|
|
ALTER TABLE approval_instances ADD COLUMN IF NOT EXISTS action TEXT;
|
|||
|
|
ALTER TABLE approval_instances ADD COLUMN IF NOT EXISTS requested_permissions TEXT NOT NULL DEFAULT '[]';
|
|||
|
|
ALTER TABLE approval_instances ADD COLUMN IF NOT EXISTS reason TEXT;
|
|||
|
|
ALTER TABLE approval_instances ADD COLUMN IF NOT EXISTS tenant_id TEXT;
|
|||
|
|
ALTER TABLE approval_instances ADD COLUMN IF NOT EXISTS expires_at TEXT;
|
|||
|
|
ALTER TABLE approval_instances ADD COLUMN IF NOT EXISTS decided_by TEXT;
|
|||
|
|
ALTER TABLE approval_instances ADD COLUMN IF NOT EXISTS decided_at TEXT;
|
|||
|
|
ALTER TABLE approval_steps ADD COLUMN IF NOT EXISTS approver_type TEXT NOT NULL DEFAULT 'user';
|
|||
|
|
|