# 公民基本信息表
CREATE TABLE CITIZEN (
CITIZEN_ID VARCHAR(32) NOT NULL,
ID_CARD VARCHAR(18) NOT NULL,
NAME VARCHAR(100) NOT NULL,
GENDER VARCHAR(10),
BIRTH_DATE DATE,
NATIONALITY VARCHAR(50),
ETHNICITY VARCHAR(50),
MARITAL_STATUS VARCHAR(10),
EDUCATION VARCHAR(20),
OCCUPATION VARCHAR(100),
ADDRESS VARCHAR(200),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
HOUSEHOLD_REGISTER VARCHAR(200),
PHOTO BLOB,
DATA_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CREATE_USER VARCHAR(32),
UPDATE_USER VARCHAR(32),
CONSTRAINT PK_CITIZEN PRIMARY KEY (CITIZEN_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_CITIZEN_IDCARD ON CITIZEN(ID_CARD);
CREATE INDEX IDX_CITIZEN_NAME ON CITIZEN(NAME);
CREATE INDEX IDX_CITIZEN_STATUS ON CITIZEN(DATA_STATUS);

# 家庭关系表
CREATE TABLE FAMILY_RELATION (
RELATION_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
CITIZEN_ID VARCHAR(32) NOT NULL,
RELATIVE_ID VARCHAR(32) NOT NULL,
RELATION_TYPE VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CREATE_USER VARCHAR(32),
CONSTRAINT PK_FAMILY_RELATION PRIMARY KEY (RELATION_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_FAMILY_CITIZEN ON FAMILY_RELATION(CITIZEN_ID);
CREATE INDEX IDX_FAMILY_RELATIVE ON FAMILY_RELATION(RELATIVE_ID);

# 业务申请表
CREATE TABLE BUSINESS_APPLICATION (
APPLICATION_ID VARCHAR(32) NOT NULL,
BUSINESS_TYPE VARCHAR(20) NOT NULL,
CITIZEN_ID VARCHAR(32),
ORGANIZATION_ID VARCHAR(32),
APPLICATION_CONTENT CLOB,
ATTACHMENT_COUNT INTEGER,
APPLICATION_DATE DATE NOT NULL,
APPLICATION_STATUS VARCHAR(20) NOT NULL,
CURRENT_NODE VARCHAR(50),
CREATE_DEPT VARCHAR(50),
CREATE_USER VARCHAR(32),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_USER VARCHAR(32),
CONSTRAINT PK_BUSINESS_APPLICATION PRIMARY KEY (APPLICATION_ID)
) ORGANIZE BY ROW;

# 业务审批记录表
CREATE TABLE APPROVAL_RECORD (
RECORD_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
APPLICATION_ID VARCHAR(32) NOT NULL,
NODE_NAME VARCHAR(50) NOT NULL,
APPROVER_ID VARCHAR(32),
APPROVER_NAME VARCHAR(50),
APPROVER_DEPT VARCHAR(50),
APPROVAL_ACTION VARCHAR(20) NOT NULL,
APPROVAL_COMMENT VARCHAR(500),
APPROVAL_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_APPROVAL_RECORD PRIMARY KEY (RECORD_ID)
) ORGANIZE BY ROW;

# 附件表
CREATE TABLE ATTACHMENT (
ATTACHMENT_ID VARCHAR(32) NOT NULL,
BUSINESS_ID VARCHAR(32) NOT NULL,
BUSINESS_TYPE VARCHAR(20) NOT NULL,
FILE_NAME VARCHAR(200) NOT NULL,
FILE_TYPE VARCHAR(50),
FILE_SIZE BIGINT,
FILE_CONTENT BLOB,
FILE_PATH VARCHAR(500),
UPLOAD_USER VARCHAR(32),
UPLOAD_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ATTACHMENT PRIMARY KEY (ATTACHMENT_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_APPLICATION_TYPE_STATUS ON BUSINESS_APPLICATION(BUSINESS_TYPE, APPLICATION_STATUS, APPLICATION_DATE DESC);
CREATE INDEX IDX_APPLICATION_CITIZEN ON BUSINESS_APPLICATION(CITIZEN_ID, APPLICATION_DATE DESC);
CREATE INDEX IDX_APPROVAL_APPLICATION ON APPROVAL_RECORD(APPLICATION_ID, APPROVAL_TIME DESC);
CREATE INDEX IDX_ATTACHMENT_BUSINESS ON ATTACHMENT(BUSINESS_ID, BUSINESS_TYPE);

# 部门表
CREATE TABLE DEPARTMENT (
DEPT_ID VARCHAR(20) NOT NULL,
DEPT_NAME VARCHAR(100) NOT NULL,
PARENT_DEPT_ID VARCHAR(20),
DEPT_LEVEL INTEGER,
DEPT_TYPE VARCHAR(20),
DEPT_STATUS VARCHAR(10) NOT NULL,
SORT_ORDER INTEGER,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPT_ID)
) ORGANIZE BY ROW;

# 用户表
CREATE TABLE USER (
USER_ID VARCHAR(32) NOT NULL,
USERNAME VARCHAR(50) NOT NULL,
PASSWORD VARCHAR(100) NOT NULL,
REAL_NAME VARCHAR(50),
ID_CARD VARCHAR(18),
GENDER VARCHAR(10),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
DEPT_ID VARCHAR(20),
USER_STATUS VARCHAR(10) NOT NULL,
LAST_LOGIN_TIME TIMESTAMP,
PASSWORD_EXPIRE_TIME TIMESTAMP,
ACCOUNT_LOCK_TIME TIMESTAMP,
LOGIN_FAIL_COUNT INTEGER DEFAULT 0,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER PRIMARY KEY (USER_ID)
) ORGANIZE BY ROW;

# 角色表
CREATE TABLE ROLE (
ROLE_ID VARCHAR(20) NOT NULL,
ROLE_NAME VARCHAR(50) NOT NULL,
ROLE_DESC VARCHAR(200),
ROLE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ROLE PRIMARY KEY (ROLE_ID)
) ORGANIZE BY ROW;

# 用户角色关联表
CREATE TABLE USER_ROLE (
USER_ID VARCHAR(32) NOT NULL,
ROLE_ID VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER_ROLE PRIMARY KEY (USER_ID, ROLE_ID)
) ORGANIZE BY ROW;

# 权限表
CREATE TABLE PERMISSION (
PERMISSION_ID VARCHAR(50) NOT NULL,
PERMISSION_NAME VARCHAR(100) NOT NULL,
PERMISSION_TYPE VARCHAR(20) NOT NULL,
RESOURCE_TYPE VARCHAR(20),
RESOURCE_ID VARCHAR(50),
PERMISSION_ACTION VARCHAR(20),
PERMISSION_DESC VARCHAR(200),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PERMISSION PRIMARY KEY (PERMISSION_ID)
) ORGANIZE BY ROW;

# 角色权限关联表
CREATE TABLE ROLE_PERMISSION (
ROLE_ID VARCHAR(20) NOT NULL,
PERMISSION_ID VARCHAR(50) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ROLE_PERMISSION PRIMARY KEY (ROLE_ID, PERMISSION_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_USER_USERNAME ON USER(USERNAME);
CREATE INDEX IDX_USER_DEPT ON USER(DEPT_ID, USER_STATUS);
CREATE INDEX IDX_USER_ROLE_USER ON USER_ROLE(USER_ID);
CREATE INDEX IDX_USER_ROLE_ROLE ON USER_ROLE(ROLE_ID);
CREATE INDEX IDX_ROLE_PERMISSION_ROLE ON ROLE_PERMISSION(ROLE_ID);
CREATE INDEX IDX_ROLE_PERMISSION_PERMISSION ON ROLE_PERMISSION(PERMISSION_ID);

# 配置实例级权限
UPDATE DATABASE MANAGER CONFIGURATION USING
SYSADM_GROUP db2admin
SYSCTRL_GROUP db2ctrl
SYSMAINT_GROUP db2maint
SYSMON_GROUP db2mon;

# 配置数据库安全
UPDATE DATABASE CONFIGURATION FOR fgedb USING
AUTHENTICATION SERVER
TRUST_ALLCLNTS NO
TRUST_CLNTAUTH CLIENT
DFT_SCHEMA_USAGE RESTRICT;

# 启用数据库安全插件
db2set DB2AUTH=OSAUTHDB;

# 创建安全策略
CREATE SECURITY LABEL COMPONENT DEPT_LEVEL
ARRAY [‘PUBLIC’, ‘INTERNAL’, ‘CONFIDENTIAL’, ‘SECRET’];

CREATE SECURITY POLICY GOV_SECURITY_POLICY
COMPONENTS DEPT_LEVEL
WITH DB2LBACRULES;

ALTER TABLE CITIZEN ADD COLUMN SECURITY_LABEL DB2SECURITYLABEL;
ALTER TABLE CITIZEN ADD SECURITY POLICY GOV_SECURITY_POLICY;

# 设置用户安全标签
GRANT SECURITY LABEL GOV_SECURITY_POLICY.DEPT_LEVEL[‘INTERNAL’]
TO USER db2user1;

GRANT SECURITY LABEL GOV_SECURITY_POLICY.DEPT_LEVEL[‘CONFIDENTIAL’]
TO USER db2admin;

# 更新数据安全标签
UPDATE CITIZEN SET SECURITY_LABEL =
SECLABEL_TO_CHAR(GOV_SECURITY_POLICY, ‘DEPT_LEVEL[“CONFIDENTIAL”]’);

# 创建数据访问控制视图
CREATE OR REPLACE VIEW V_CITIZEN_ACCESS AS
SELECT
CITIZEN_ID,
ID_CARD,
NAME,
GENDER,
BIRTH_DATE,
CASE
WHEN CURRENT USER IN (‘db2admin’, ‘manager’) THEN PHONE
ELSE ‘********’
END AS PHONE,
CASE
WHEN CURRENT USER IN (‘db2admin’, ‘manager’) THEN ADDRESS
ELSE ‘********’
END AS ADDRESS,
DATA_STATUS
FROM CITIZEN;

# 只允许访问视图，不允许直接访问表
REVOKE ALL ON CITIZEN FROM PUBLIC;
GRANT SELECT ON V_CITIZEN_ACCESS TO PUBLIC;

# 创建数据库用户
CREATE USER db2user1 WITHOUT PROTOCOL;
CREATE USER db2user2 WITHOUT PROTOCOL;

# 创建角色
CREATE ROLE clerk;
CREATE ROLE manager;
CREATE ROLE auditor;

# 授予角色权限
GRANT SELECT, INSERT, UPDATE ON BUSINESS_APPLICATION TO ROLE clerk;
GRANT SELECT, INSERT, UPDATE, DELETE ON BUSINESS_APPLICATION TO ROLE manager;
GRANT SELECT ON ALL TABLES IN SCHEMA fgedu TO ROLE auditor;
GRANT EXECUTE ON PACKAGE fgedu.* TO ROLE clerk;
GRANT EXECUTE ON PACKAGE fgedu.* TO ROLE manager;

# 将用户加入角色
GRANT ROLE clerk TO USER db2user1;
GRANT ROLE manager TO USER db2user2;
GRANT ROLE auditor TO USER db2audit;

# 表级别权限控制
REVOKE ALL ON CITIZEN FROM PUBLIC;
GRANT SELECT(CITIZEN_ID, NAME, GENDER) ON CITIZEN TO clerk;
GRANT SELECT, INSERT, UPDATE ON CITIZEN TO manager;

# 存储过程权限
CREATE OR REPLACE PROCEDURE SP_QUERY_CITIZEN(
IN p_id_card VARCHAR(18)
)
RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT CITIZEN_ID, NAME, GENDER, BIRTH_DATE
FROM CITIZEN
WHERE ID_CARD = p_id_card;

OPEN c1;
END;

GRANT EXECUTE ON PROCEDURE SP_QUERY_CITIZEN TO clerk;

# 行级别权限控制
CREATE OR REPLACE VIEW V_DEPT_APPLICATION AS
SELECT * FROM BUSINESS_APPLICATION
WHERE CREATE_DEPT IN (
SELECT DEPT_ID FROM DEPARTMENT
START WITH DEPT_ID = (SELECT DEPT_ID FROM USER WHERE USER_ID = CURRENT USER)
CONNECT BY PARENT_DEPT_ID = PRIOR DEPT_ID
);

GRANT SELECT, INSERT, UPDATE ON V_DEPT_APPLICATION TO clerk;

# 创建审计策略
CREATE AUDIT POLICY GOV_AUDIT_POLICY
CATEGORIES AUDIT STATUS BOTH,
CHECKING STATUS BOTH,
OBJMNT STATUS BOTH,
SECMAINT STATUS BOTH,
SYSADMIN STATUS BOTH,
VALIDATE STATUS BOTH,
CONTEXT STATUS BOTH,
EXECUTE STATUS BOTH
ERROR TYPE NORMAL;

# 应用审计策略
AUDIT TABLE CITIZEN USING POLICY GOV_AUDIT_POLICY;
AUDIT TABLE BUSINESS_APPLICATION USING POLICY GOV_AUDIT_POLICY;
AUDIT TABLE USER USING POLICY GOV_AUDIT_POLICY;

# 审计特定用户
AUDIT USER db2user1 USING POLICY GOV_AUDIT_POLICY;

# 查看审计配置
SELECT
POLICYNAME,
AUDITSTATUS,
CHECKINGSTATUS,
OBJMNTSTATUS,
SECMAINTSTATUS,
SYSADMINSTATUS
FROM SYSCAT.AUDITPOLICIES;

# 创建审计日志表
CREATE TABLE AUDIT_LOG (
LOG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
EVENT_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
EVENT_TYPE VARCHAR(50) NOT NULL,
USER_ID VARCHAR(32),
CLIENT_IP VARCHAR(50),
TABLE_NAME VARCHAR(100),
OPERATION_TYPE VARCHAR(20),
SQL_TEXT CLOB,
OLD_VALUE CLOB,
NEW_VALUE CLOB,
CONSTRAINT PK_AUDIT_LOG PRIMARY KEY (LOG_ID)
) PARTITION BY RANGE (EVENT_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 MONTH
);

# 创建触发器记录数据变更
CREATE OR REPLACE TRIGGER TRG_CITIZEN_AUDIT_UPDATE
AFTER UPDATE ON CITIZEN
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT_LOG (
EVENT_TYPE, USER_ID, TABLE_NAME, OPERATION_TYPE, OLD_VALUE, NEW_VALUE
) VALUES (
‘DATA_CHANGE’, CURRENT USER, ‘CITIZEN’, ‘UPDATE’,
XMLSERIALIZE(XMLELEMENT(NAME “old”,
XMLELEMENT(NAME “CITIZEN_ID”, O.CITIZEN_ID),
XMLELEMENT(NAME “NAME”, O.NAME),
XMLELEMENT(NAME “PHONE”, O.PHONE)
) AS CLOB),
XMLSERIALIZE(XMLELEMENT(NAME “new”,
XMLELEMENT(NAME “CITIZEN_ID”, N.CITIZEN_ID),
XMLELEMENT(NAME “NAME”, N.NAME),
XMLELEMENT(NAME “PHONE”, N.PHONE)
) AS CLOB)
);
END;

CREATE OR REPLACE TRIGGER TRG_CITIZEN_AUDIT_INSERT
AFTER INSERT ON CITIZEN
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT_LOG (
EVENT_TYPE, USER_ID, TABLE_NAME, OPERATION_TYPE, NEW_VALUE
) VALUES (
‘DATA_CHANGE’, CURRENT USER, ‘CITIZEN’, ‘INSERT’,
XMLSERIALIZE(XMLELEMENT(NAME “new”,
XMLELEMENT(NAME “CITIZEN_ID”, N.CITIZEN_ID),
XMLELEMENT(NAME “NAME”, N.NAME),
XMLELEMENT(NAME “ID_CARD”, N.ID_CARD)
) AS CLOB)
);
END;

# 透明数据加密
CREATE LARGE TABLESPACE ENCRYPTED_TS
PAGESIZE 16K
MANAGED BY AUTOMATIC STORAGE
USING STOGROUP IBMSTOGROUP
EXTENTSIZE 32
PREFETCHSIZE 64
BUFFERPOOL BP16K
ENCRYPT USING ‘AES256’;

# 创建密钥管理
CREATE MASTER KEY ENCRYPTION BY PASSWORD ‘MasterKeyPass@2024’;

# 在加密表空间创建表
CREATE TABLE SENSITIVE_DATA (
ID VARCHAR(32) NOT NULL,
ID_CARD_ENCRYPT VARCHAR(100),
PHONE_ENCRYPT VARCHAR(100),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SENSITIVE_DATA PRIMARY KEY (ID)
) IN ENCRYPTED_TS;

# 使用ENCRYPT_T函数加密数据
INSERT INTO SENSITIVE_DATA (ID, ID_CARD_ENCRYPT, PHONE_ENCRYPT)
VALUES (
‘001’,
ENCRYPT_T(‘110101199001011234’, ‘EncryptKey@2024’),
ENCRYPT_T(‘13800138000’, ‘EncryptKey@2024’)
);

# 解密数据
SELECT
ID,
DECRYPT_CHAR(ID_CARD_ENCRYPT, ‘EncryptKey@2024’) AS ID_CARD,
DECRYPT_CHAR(PHONE_ENCRYPT, ‘EncryptKey@2024’) AS PHONE
FROM SENSITIVE_DATA;

# SSL/TLS配置
# 生成证书
gsk8capicmd_64 -keydb -create -db “server.kdb” -pw “ServerPass@2024” -stash
gsk8capicmd_64 -cert -create -db “server.kdb” -pw “ServerPass@2024” \
-label “DB2ServerCert” -dn “CN=db2server.fgedu.net.cn,O=FGedu,C=CN” \
-size 2048 -sigalg SHA256_WITH_RSA
gsk8capicmd_64 -cert -extract -db “server.kdb” -pw “ServerPass@2024” \
-label “DB2ServerCert” -target “server.arm” -format ascii

# 更新DBM配置
UPDATE DATABASE MANAGER CONFIGURATION USING
SSL_SVR_KEYDB /home/db2inst1/ssl/server.kdb
SSL_SVR_STASH /home/db2inst1/ssl/server.sth
SSL_SVR_LABEL DB2ServerCert
SSL_VERSIONS TLSV12;

# 配置数据库SSL
UPDATE DATABASE CONFIGURATION FOR fgedb USING
SSL_VERSIONS TLSV12;