68 lines
5.3 KiB
SQL
Executable File
68 lines
5.3 KiB
SQL
Executable File
-- Additive only. Preserve all seven tables and the legacy iam_local_identity ledger on rollback.
|
|
-- Apply with configured table prefix (default zyt_). No existing roles/accounts are changed.
|
|
CREATE TABLE IF NOT EXISTS zyt_iam_v2_source_session (
|
|
session_id CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
|
|
admin_id INT UNSIGNED NOT NULL, business_session_id INT UNSIGNED NOT NULL,
|
|
token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
account_stamp CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
auth_method VARCHAR(16) NOT NULL, auth_time INT UNSIGNED NOT NULL,
|
|
version INT UNSIGNED NOT NULL DEFAULT 1, revoked TINYINT NOT NULL DEFAULT 0,
|
|
binding_version BIGINT UNSIGNED NOT NULL DEFAULT 0, employee_id VARCHAR(128) NOT NULL DEFAULT '',
|
|
expires_at INT UNSIGNED NOT NULL, INDEX idx_admin (admin_id), INDEX idx_expiry (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS zyt_iam_v2_proof (
|
|
proof_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
|
|
purpose VARCHAR(32) NOT NULL, session_id CHAR(64) NOT NULL, payload TEXT NOT NULL,
|
|
expires_at INT UNSIGNED NOT NULL, consumed TINYINT NOT NULL DEFAULT 0, INDEX idx_expiry(expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS zyt_iam_v2_binding (
|
|
admin_id INT UNSIGNED PRIMARY KEY, employee_id VARCHAR(128) NOT NULL DEFAULT '',
|
|
subject VARCHAR(255) NOT NULL DEFAULT '', state VARCHAR(32) NOT NULL,
|
|
version BIGINT UNSIGNED NOT NULL DEFAULT 0, payload MEDIUMTEXT NOT NULL,
|
|
checked_at INT UNSIGNED NOT NULL DEFAULT 0, INDEX idx_employee(employee_id), INDEX idx_subject(subject)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS zyt_iam_v2_resource (
|
|
resource_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
|
|
kind VARCHAR(32) NOT NULL, external_id VARCHAR(128) NOT NULL, actor_id INT UNSIGNED NOT NULL,
|
|
payload MEDIUMTEXT NOT NULL, created_at INT UNSIGNED NOT NULL, INDEX idx_actor(actor_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS zyt_iam_v2_flow (
|
|
flow_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
|
|
kind VARCHAR(32) NOT NULL, payload TEXT NOT NULL, expires_at INT UNSIGNED NOT NULL,
|
|
consumed TINYINT NOT NULL DEFAULT 0, INDEX idx_expiry(expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS zyt_iam_v2_event (
|
|
event_id VARCHAR(128) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY, digest CHAR(64) NOT NULL, received_at INT UNSIGNED NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS zyt_iam_v2_target_session (
|
|
token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
|
|
admin_id INT UNSIGNED NOT NULL, employee_id VARCHAR(128) NOT NULL,
|
|
source_application_id VARCHAR(64) NOT NULL, source_binding_version BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
checked_at INT UNSIGNED NOT NULL DEFAULT 0, INDEX idx_admin(admin_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
START TRANSACTION;
|
|
SET @iam_parent := (SELECT pid FROM zyt_system_menu WHERE component='permission/admin/index' LIMIT 1);
|
|
INSERT INTO zyt_system_menu (pid,type,name,icon,sort,perms,paths,component,selected,params,is_cache,is_show,is_disable,create_time,update_time)
|
|
SELECT COALESCE(@iam_parent,0),'C','IAM关联管理','el-icon-Connection',85,'iamV2/read','identity-management','permission/identity/index','','',0,1,0,UNIX_TIMESTAMP(),UNIX_TIMESTAMP()
|
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM zyt_system_menu WHERE perms='iamV2/read');
|
|
SET @iam_menu := (SELECT id FROM zyt_system_menu WHERE perms='iamV2/read' LIMIT 1);
|
|
INSERT INTO zyt_system_menu (pid,type,name,icon,sort,perms,paths,component,selected,params,is_cache,is_show,is_disable,create_time,update_time)
|
|
SELECT @iam_menu,'A','绑定IAM','',0,'iamV2/bind','','','','',0,1,0,UNIX_TIMESTAMP(),UNIX_TIMESTAMP()
|
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM zyt_system_menu WHERE perms='iamV2/bind');
|
|
INSERT INTO zyt_system_menu (pid,type,name,icon,sort,perms,paths,component,selected,params,is_cache,is_show,is_disable,create_time,update_time)
|
|
SELECT @iam_menu,'A','创建并关联IAM','',0,'iamV2/create','','','','',0,1,0,UNIX_TIMESTAMP(),UNIX_TIMESTAMP()
|
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM zyt_system_menu WHERE perms='iamV2/create');
|
|
INSERT INTO zyt_system_menu (pid,type,name,icon,sort,perms,paths,component,selected,params,is_cache,is_show,is_disable,create_time,update_time)
|
|
SELECT @iam_menu,'A','解除IAM关联','',0,'iamV2/unbind','','','','',0,1,0,UNIX_TIMESTAMP(),UNIX_TIMESTAMP()
|
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM zyt_system_menu WHERE perms='iamV2/unbind');
|
|
INSERT INTO zyt_system_menu (pid,type,name,icon,sort,perms,paths,component,selected,params,is_cache,is_show,is_disable,create_time,update_time)
|
|
SELECT @iam_menu,'A','更换IAM关联','',0,'iamV2/rebind','','','','',0,1,0,UNIX_TIMESTAMP(),UNIX_TIMESTAMP()
|
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM zyt_system_menu WHERE perms='iamV2/rebind');
|
|
INSERT INTO zyt_system_menu (pid,type,name,icon,sort,perms,paths,component,selected,params,is_cache,is_show,is_disable,create_time,update_time)
|
|
SELECT @iam_menu,'A','批量IAM任务','',0,'iamV2/batch','','','','',0,1,0,UNIX_TIMESTAMP(),UNIX_TIMESTAMP()
|
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM zyt_system_menu WHERE perms='iamV2/batch');
|
|
COMMIT;
|
|
-- Deliberately no system_role_menu grants: assign capabilities explicitly after review.
|