# 配置密码策略
UPDATE DATABASE MANAGER CONFIGURATION USING CLPWFILE /db2/security/password.policy;

# 密码复杂度要求
# – 最小长度：8位
# – 包含大小写字母
# – 包含数字
# – 包含特殊字符

# 创建密码验证函数
CREATE OR REPLACE FUNCTION FN_VALIDATE_PASSWORD(p_password VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
BEGIN
DECLARE v_length INTEGER;
DECLARE v_has_upper INTEGER;
DECLARE v_has_lower INTEGER;
DECLARE v_has_digit INTEGER;
DECLARE v_has_special INTEGER;

SET v_length = LENGTH(p_password);
SET v_has_upper = CASE WHEN LOCATE(‘A’, p_password) > 0 OR
LOCATE(‘B’, p_password) > 0 OR
LOCATE(‘C’, p_password) > 0 THEN 1 ELSE 0 END;
SET v_has_lower = CASE WHEN LOCATE(‘a’, p_password) > 0 OR
LOCATE(‘b’, p_password) > 0 OR
LOCATE(‘c’, p_password) > 0 THEN 1 ELSE 0 END;
SET v_has_digit = CASE WHEN LOCATE(‘0’, p_password) > 0 OR
LOCATE(‘1’, p_password) > 0 OR
LOCATE(‘2’, p_password) > 0 THEN 1 ELSE 0 END;
SET v_has_special = CASE WHEN LOCATE(‘!’, p_password) > 0 OR
LOCATE(‘@’, p_password) > 0 OR
LOCATE(‘#’, p_password) > 0 THEN 1 ELSE 0 END;

IF v_length >= 8 AND v_has_upper = 1 AND v_has_lower = 1
AND v_has_digit = 1 AND v_has_special = 1 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

# 使用密码验证
SELECT FN_VALIDATE_PASSWORD(‘Abc123!@’) FROM SYSIBM.SYSDUMMY1;

# 配置密码过期时间
UPDATE DATABASE CONFIGURATION USING PWD_EXPIRY_DAYS 90;

# 查看密码过期配置
GET DATABASE CONFIGURATION | grep PWD_EXPIRY_DAYS;

# 创建密码历史表
CREATE TABLE PASSWORD_HISTORY (
USER_NAME VARCHAR(128) NOT NULL,
PASSWORD_HASH VARCHAR(256) NOT NULL,
CHANGE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PASSWORD_HISTORY PRIMARY KEY (USER_NAME, CHANGE_TIME)
);

# 创建密码修改触发器
CREATE TRIGGER TRG_PASSWORD_CHANGE
AFTER UPDATE OF PASSWORD ON USER_PASSWORD
REFERENCING NEW AS N
FOR EACH ROW
BEGIN
INSERT INTO PASSWORD_HISTORY (USER_NAME, PASSWORD_HASH)
VALUES (N.USER_NAME, N.PASSWORD_HASH);
END;

# 检查密码历史
SELECT COUNT(*)
FROM PASSWORD_HISTORY
WHERE USER_NAME = ‘APP_USER’
AND CHANGE_TIME > CURRENT DATE – 365 DAYS;

# 创建角色
CREATE ROLE APP_READONLY;
CREATE ROLE APP_READWRITE;
CREATE ROLE APP_ADMIN;

# 授予只读权限
GRANT SELECT ON TABLE ORDERS TO APP_READONLY;
GRANT SELECT ON TABLE CUSTOMER TO APP_READONLY;
GRANT SELECT ON TABLE PRODUCT TO APP_READONLY;

# 授予读写权限
GRANT SELECT, INSERT, UPDATE ON TABLE ORDERS TO APP_READWRITE;
GRANT SELECT, INSERT, UPDATE ON TABLE CUSTOMER TO APP_READWRITE;

# 授予管理权限
GRANT ALL ON TABLE ORDERS TO APP_ADMIN;
GRANT ALL ON TABLE CUSTOMER TO APP_ADMIN;

# 授予角色给用户
GRANT APP_READONLY TO app_readonly_user;
GRANT APP_READWRITE TO app_readwrite_user;
GRANT APP_ADMIN TO app_admin_user;

# 查看用户权限
SELECT
GRANTEE,
GRANTEETYPE,
TABSCHEMA,
TABNAME,
SELECTAUTH,
INSERTAUTH,
UPDATEAUTH,
DELETEAUTH
FROM SYSCAT.TABAUTH
WHERE GRANTEE = ‘APP_READONLY’;

# 创建行级访问控制表
CREATE TABLE SENSITIVE_DATA (
ID INTEGER NOT NULL,
DEPARTMENT VARCHAR(50) NOT NULL,
DATA_VALUE VARCHAR(500),
CONSTRAINT PK_SENSITIVE_DATA PRIMARY KEY (ID)
);

# 创建权限视图
CREATE VIEW V_SENSITIVE_DATA_DEPT AS
SELECT * FROM SENSITIVE_DATA
WHERE DEPARTMENT = CURRENT_USER;

# 授权视图
GRANT SELECT ON V_SENSITIVE_DATA_DEPT TO dept_user;

# 创建行级权限控制函数
CREATE OR REPLACE FUNCTION FN_CHECK_DEPARTMENT(p_dept VARCHAR(50))
RETURNS INTEGER
LANGUAGE SQL
BEGIN
IF p_dept = CURRENT_USER THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

# 创建行级权限触发器
CREATE TRIGGER TRG_CHECK_DEPARTMENT
NO CASCADE BEFORE INSERT OR UPDATE OR DELETE ON SENSITIVE_DATA
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
DECLARE v_allowed INTEGER;

IF INSERTING THEN
SET v_allowed = FN_CHECK_DEPARTMENT(N.DEPARTMENT);
ELSE
SET v_allowed = FN_CHECK_DEPARTMENT(O.DEPARTMENT);
END IF;

IF v_allowed = 0 THEN
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Access denied for this department’;
END IF;
END;

# 生成SSL证书
# 1. 创建证书请求
openssl req -new -newkey rsa:2048 -nodes -keyout db2server.key -out db2server.csr

# 2. 签名证书
openssl x509 -req -days 365 -in db2server.csr -signkey db2server.key -out db2server.crt

# 3. 创建证书库
gsk8capicmd_64 -keydb -create -db /db2/ssl/key.kdb -pw password -type kdb -stash

# 4. 导入证书
gsk8capicmd_64 -cert -import -db /db2/ssl/key.kdb -pw password -label db2server -file db2server.crt

# 配置SSL
UPDATE DATABASE MANAGER CONFIGURATION USING
SSL_SVR_KEYDB /db2/ssl/key.kdb
SSL_SVR_STASH /db2/ssl/key.sth
SSL_SVR_LABEL db2server
SSL_SVCENAME 50001;

# 重启实例
db2stop
db2start

# 客户端连接
db2 connect to FGEDB user db2inst1 using password
SSLCLIENT=true SSLServerCertificate=/db2/ssl/db2server.crt

# 查看SSL配置
GET DATABASE MANAGER CONFIGURATION | grep SSL;

# 配置防火墙规则
# 只允许特定IP访问
iptables -A INPUT -p tcp -s 192.168.1.0/24 –dport 50000 -j ACCEPT
iptables -A INPUT -p tcp –dport 50000 -j DROP

# 配置DB2连接限制
UPDATE DATABASE MANAGER CONFIGURATION USING
AUTHENTICATION SERVER_ENCRYPT
TRUST_ALLCLNTS NO
TRUST_CLNTAUTH SERVER;

# 创建允许连接的主机列表
UPDATE DATABASE MANAGER CONFIGURATION USING
SVT_ENCRYPT_AES ON;

# 配置连接超时
UPDATE DATABASE MANAGER CONFIGURATION USING
CONNECT_TIMEOUT 10
IDLE_TIMEOUT 600;

# 查看连接配置
GET DATABASE MANAGER CONFIGURATION;

# 监控连接
SELECT
AGENT_ID,
CLIENT_IPADDR,
APPL_NAME,
APPL_STATUS
FROM SYSIBMADM.APPLICATIONS
ORDER BY CLIENT_IPADDR;