# 租户表
CREATE TABLE TENANT (
TENANT_ID VARCHAR(32) NOT NULL,
TENANT_CODE VARCHAR(50) NOT NULL,
TENANT_NAME VARCHAR(200) NOT NULL,
TENANT_TYPE VARCHAR(20),
CONTACT_NAME VARCHAR(100),
CONTACT_PHONE VARCHAR(20),
CONTACT_EMAIL VARCHAR(100),
DB_SCHEMA VARCHAR(50),
MAX_USERS INTEGER,
MAX_STORAGE BIGINT,
EXPIRY_DATE DATE,
TENANT_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TENANT PRIMARY KEY (TENANT_ID)
) ORGANIZE BY ROW;

# 共享用户表（带租户ID）
CREATE TABLE SHARED_USER (
USER_ID VARCHAR(32) NOT NULL,
TENANT_ID VARCHAR(32) NOT NULL,
USERNAME VARCHAR(100) NOT NULL,
PASSWORD VARCHAR(200) NOT NULL,
EMAIL VARCHAR(100),
PHONE VARCHAR(20),
USER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SHARED_USER PRIMARY KEY (USER_ID)
) ORGANIZE BY ROW;

# 共享订单表（带租户ID）
CREATE TABLE SHARED_ORDER (
ORDER_ID VARCHAR(32) NOT NULL,
TENANT_ID VARCHAR(32) NOT NULL,
ORDER_NO VARCHAR(50) NOT NULL,
USER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
ORDER_STATUS VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SHARED_ORDER PRIMARY KEY (ORDER_ID)
) ORGANIZE BY ROW;

# 创建索引（必须包含TENANT_ID）
CREATE UNIQUE INDEX IDX_SHARED_USER_TENANT ON SHARED_USER(TENANT_ID, USERNAME);
CREATE INDEX IDX_SHARED_ORDER_TENANT ON SHARED_ORDER(TENANT_ID, CREATE_TIME DESC);

# 创建视图（自动过滤租户数据）
CREATE VIEW V_SHARED_USER AS
SELECT * FROM SHARED_USER WHERE TENANT_ID = CURRENT CLIENT_ACCTNG;

CREATE VIEW V_SHARED_ORDER AS
SELECT * FROM SHARED_ORDER WHERE TENANT_ID = CURRENT CLIENT_ACCTNG;

# 设置租户上下文
SET CLIENT_ACCTNG = ‘TENANT001’;

# 查询数据（通过视图自动过滤）
SELECT * FROM V_SHARED_USER;
SELECT * FROM V_SHARED_ORDER;

# 创建租户用户
CREATE USER tenant001_user PASSWORD ‘password’;
GRANT CONNECT ON DATABASE TO tenant001_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON SHARED_USER TO tenant001_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON SHARED_ORDER TO tenant001_user;
GRANT SELECT ON V_SHARED_USER TO tenant001_user;
GRANT SELECT ON V_SHARED_ORDER TO tenant001_user;

# 创建触发器（自动设置租户ID）
CREATE OR REPLACE TRIGGER TRG_SHARED_USER_TENANT
BEFORE INSERT ON SHARED_USER
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.TENANT_ID IS NULL)
SET N.TENANT_ID = CURRENT CLIENT_ACCTNG;

CREATE OR REPLACE TRIGGER TRG_SHARED_ORDER_TENANT
BEFORE INSERT ON SHARED_ORDER
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.TENANT_ID IS NULL)
SET N.TENANT_ID = CURRENT CLIENT_ACCTNG;

# 创建租户Schema
CREATE SCHEMA TENANT001;
CREATE SCHEMA TENANT002;
CREATE SCHEMA TENANT003;

# 在租户Schema中创建表
CREATE TABLE TENANT001.USER (
USER_ID VARCHAR(32) NOT NULL,
USERNAME VARCHAR(100) NOT NULL,
PASSWORD VARCHAR(200) NOT NULL,
EMAIL VARCHAR(100),
PHONE VARCHAR(20),
USER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TENANT001_USER PRIMARY KEY (USER_ID)
) ORGANIZE BY ROW;

CREATE TABLE TENANT001.ORDER (
ORDER_ID VARCHAR(32) NOT NULL,
ORDER_NO VARCHAR(50) NOT NULL,
USER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
ORDER_STATUS VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TENANT001_ORDER PRIMARY KEY (ORDER_ID)
) ORGANIZE BY ROW;

# 为其他租户创建相同的表结构
CREATE TABLE TENANT002.USER LIKE TENANT001.USER;
CREATE TABLE TENANT002.ORDER LIKE TENANT001.ORDER;

CREATE TABLE TENANT003.USER LIKE TENANT001.USER;
CREATE TABLE TENANT003.ORDER LIKE TENANT001.ORDER;

# 创建租户用户并授权
CREATE USER tenant001_user PASSWORD ‘password’;
GRANT CONNECT ON DATABASE TO tenant001_user;
GRANT USE OF SCHEMA TENANT001 TO tenant001_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TENANT001.USER TO tenant001_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TENANT001.ORDER TO tenant001_user;
ALTER USER tenant001_user SET CURRENT_SCHEMA = TENANT001;

# 创建存储过程：创建新租户
CREATE OR REPLACE PROCEDURE SP_CREATE_TENANT(
IN p_tenant_id VARCHAR(32),
IN p_tenant_name VARCHAR(200),
IN p_password VARCHAR(200),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_sql VARCHAR(2000);

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘租户创建成功’;

EXECUTE IMMEDIATE ‘CREATE SCHEMA ‘ || p_tenant_id;

EXECUTE IMMEDIATE ‘CREATE TABLE ‘ || p_tenant_id || ‘.USER LIKE TENANT001.USER’;
EXECUTE IMMEDIATE ‘CREATE TABLE ‘ || p_tenant_id || ‘.ORDER LIKE TENANT001.ORDER’;

EXECUTE IMMEDIATE ‘CREATE USER ‘ || p_tenant_id || ‘_user PASSWORD ”’ || p_password || ””;

EXECUTE IMMEDIATE ‘GRANT CONNECT ON DATABASE TO ‘ || p_tenant_id || ‘_user’;
EXECUTE IMMEDIATE ‘GRANT USE OF SCHEMA ‘ || p_tenant_id || ‘ TO ‘ || p_tenant_id || ‘_user’;
EXECUTE IMMEDIATE ‘GRANT SELECT, INSERT, UPDATE, DELETE ON ‘ || p_tenant_id || ‘.USER TO ‘ || p_tenant_id || ‘_user’;
EXECUTE IMMEDIATE ‘GRANT SELECT, INSERT, UPDATE, DELETE ON ‘ || p_tenant_id || ‘.ORDER TO ‘ || p_tenant_id || ‘_user’;
EXECUTE IMMEDIATE ‘ALTER USER ‘ || p_tenant_id || ‘_user SET CURRENT_SCHEMA = ‘ || p_tenant_id;

INSERT INTO TENANT (
TENANT_ID, TENANT_CODE, TENANT_NAME, DB_SCHEMA, TENANT_STATUS
) VALUES (
p_tenant_id, p_tenant_id, p_tenant_name, p_tenant_id, ‘ACTIVE’
);

END;

# 创建角色
CREATE ROLE TENANT_ADMIN;
CREATE ROLE TENANT_USER;

# 授权角色
GRANT SELECT, INSERT, UPDATE, DELETE ON SHARED_USER TO TENANT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON SHARED_ORDER TO TENANT_USER;
GRANT SELECT ON TENANT TO TENANT_ADMIN;

# 授予角色给用户
GRANT TENANT_USER TO tenant001_user;
GRANT TENANT_ADMIN TO admin_user;

# 创建LBAC（基于标签的访问控制）
CREATE SECURITY POLICY TENANT_POLICY;

CREATE SECURITY LABEL TENANT_POLICY.TENANT001
COMPONENT TENANT ‘TENANT001’;

CREATE SECURITY LABEL TENANT_POLICY.TENANT002
COMPONENT TENANT ‘TENANT002’;

# 应用LBAC到表
ALTER TABLE SHARED_USER
ADD COLUMN TENANT_LABEL DB2SECURITYLABEL;

ALTER TABLE SHARED_USER
ADD SECURITY POLICY TENANT_POLICY;

# 授予用户安全标签
GRANT SECURITY LABEL TENANT_POLICY.TENANT001
TO USER tenant001_user FOR ALL ACCESS;

# 行权限控制（视图方式）
CREATE VIEW V_SHARED_USER_TENANT001 AS
SELECT * FROM SHARED_USER WHERE TENANT_ID = ‘TENANT001’
WITH CHECK OPTION;

CREATE VIEW V_SHARED_USER_TENANT002 AS
SELECT * FROM SHARED_USER WHERE TENANT_ID = ‘TENANT002’
WITH CHECK OPTION;

# 只授予视图权限，不授予基表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON V_SHARED_USER_TENANT001 TO tenant001_user;

# 创建独立的表空间
CREATE TABLESPACE TENANT001_TS
PAGESIZE 8K
MANAGED BY DATABASE
USING (FILE ‘/db2data/tenant001/tenant001_ts.dms’ 100M);

CREATE TABLESPACE TENANT002_TS
PAGESIZE 8K
MANAGED BY DATABASE
USING (FILE ‘/db2data/tenant002/tenant002_ts.dms’ 100M);

# 在租户表空间中创建表
CREATE TABLE TENANT001.USER (
…
) IN TENANT001_TS;

# 配置WLM（工作负载管理器）
CREATE SERVICE CLASS TENANT001_SC
SOFT CPU SHARES 100
SOFT MEMORY SHARES 100;

CREATE SERVICE CLASS TENANT002_SC
SOFT CPU SHARES 100
SOFT MEMORY SHARES 100;

CREATE WORKLOAD TENANT001_WL
APPLNAME(‘TENANT001_APP’)
SERVICE CLASS TENANT001_SC;

CREATE WORKLOAD TENANT002_WL
APPLNAME(‘TENANT002_APP’)
SERVICE CLASS TENANT002_SC;

# 监控租户资源使用
SELECT
SUBSTR(TABSCHEMA, 1, 20) AS TABSCHEMA,
SUBSTR(TABNAME, 1, 30) AS TABNAME,
SUM(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE) AS TOTAL_SIZE_KB
FROM SYSIBMADM.ADMINTABINFO
WHERE TABSCHEMA LIKE ‘TENANT%’
GROUP BY TABSCHEMA, TABNAME
ORDER BY TABSCHEMA, TOTAL_SIZE_KB DESC;

SELECT
SUBSTR(WORKLOAD_NAME, 1, 20) AS WORKLOAD_NAME,
SUM(CPU_USAGE_TIME) AS TOTAL_CPU_TIME,
SUM(ROWS_READ) AS TOTAL_ROWS_READ,
SUM(ROWS_WRITTEN) AS TOTAL_ROWS_WRITTEN
FROM SYSIBMADM.WORKLOAD_OCCURRENCE_METRICS
WHERE WORKLOAD_NAME LIKE ‘TENANT%’
GROUP BY WORKLOAD_NAME;