CREATE VIEW GARBAGE(text) AS SELECT CAST(NULL AS CLOB(20M)) FROM SYSIBM.SYSDUMMY1 WHERE 1 = 0$ CREATE TRIGGER INSERT_GARBAGE INSTEAD OF INSERT ON GARBAGE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC END$ INSERT INTO GARBAGE VALUES 'unrecoverable document'$ 哦，我们在这里为自己做了一个漂亮的垃圾箱。鉴于此，只允许对视图拥有 CONTROL 特权的用户、视图定义者、SYSADM 或 DBADM 才能在视图上创建 INSTEAD OF 触发器。 透明加密 在介绍了 INSTEAD OF 触发器这种最简单的应用之后，让我们讨论更常见的类型，也就是使用 INSTEAD OF 触发器在视图中为加密表达式提供“逆”操作。 CREATE TABLE USERS (user VARCHAR(20), system VARCHAR(30), login VARCHAR(20), password VARCHAR(40) FOR BIT DATA)$ 上面的表包含了不同系统上用户的标识和加密密码。下面的视图假定在用户提供了正确凭证的前提下，对与当前用户相关的行进行解密。 CREATE VIEW MY_LOGINS(system, login, password) AS SELECT system, login, decrypt_char(password) FROM USERS AS u WHERE u.user = USER$ 为了通过这个视图进行更新或插入，我们需要定义 INSTEAD OF 触发器，该触发器对用户提供的密码进行加密以将其存储在基本表中。 CREATE TRIGGER INSERT_MY_LOGINS INSTEAD OF INSERT ON MY_LOGINS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL INSERT INTO USERS VALUES(USER, n.system, n.login, encrypt(password))$ CREATE TRIGGER UPDATE_MY_LOGINS INSTEAD OF UPDATE ON MY_LOGINS REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DB2SQL UPDATE USERS U SET system = n.system, login = n.login, password = encrypt(n.password) WHERE system = o.system AND login = o.login AND U.user = USER$ 加密数据通过 ENCRYPTION PASSWORD 特殊寄存器受到保护。 SET ENCRYPTION PASSWORD = 'unbreakable'$ 现在把我们的机密信息填入表中： INSERT INTO MY_LOGINS VALUES('AFS', 'srielau', 'mydogsname'), ('Linux', 'root', 'oopsIforgot'), ('IIUG', 'Rielau', '123456789')$ SELECT * FROM MY_LOGINS WHERE system = 'Linux'$ SYSTEM LOGIN PASSWORD ------ ------- ------------ Linux root oopsIforgot 在基本表上密码是加过密的。没有上面设置的密码，任何人（即使是 DB2 支持人员）都不能取回信息。 SELECT * FROM USERS U ORDER BY U.user, system, login$ USER SYSTEM LOGIN PASSWORD -------- ------- ------- ------------- SRIELAU AFS srielau 0x........... SRIELAU IIUG Rielau 0x........... SRIELAU Linux root 0x........... 可以更新视图中的 password 或任何其它字段。 UPDATE MY_LOGINS SET password = 'mycatsname' WHERE system = 'AFS' AND login = 'srielau'$ 但是，首先有一点，视图是可删除的。因此我们不用 INSTEAD OF DELETE 触发器就可以删除各项。 DELETE FROM my_logins WHERE SYSTEM = 'AFS'$ 有关加密的更多详细情况，请参阅 http://www7b.boulder.ibm.com/dmdd/library/techarticle/benfield/0108benfield.html。 让我们用一个较复杂的安全性示例来增加点复杂性。 组级和行级安全性 经理可以查看其雇员的工资，但是不能查看其同级经理或那些经理所管辖的那些雇员的工资。二线经理可以看到其直接以及间接报告的工资。 让我们对这样的模式进行建模，然后定义 INSTEAD OF 触发器，以允许经理可以更新其管辖雇员的信息。 CREATE TABLE PROFILES (empid INT, name VARCHAR(20), sqlid VARCHAR(18), mgrid INT, salary DECIMAL(9,2), ismgr CHAR(1))$ 我们想填入一些数据，在本例中是一个四层的层次结构。我们是名叫“MySelf”的二线经理。 INSERT INTO PROFILES VALUES(0001, 'SuperBoss', 'sboss', NULL, 500000, 'Y'), (1001, 'BigBoss', 'bboss', 0001, 200000, 'Y'), (1002, 'MySelf', USER, 0001, 250000, 'Y'), (2001, 'FirstLine', 'fline', 1001, 100000, 'Y'), (2002, 'MiddleMen', 'mmen', 1001, 110000, 'Y'), (2003, 'Yeti', 'yeti', 1002, 90000, 'Y'), (2004, 'BigFoot', 'bfoot', 1002, 80000, 'N'), (3001, 'TinyToon', 'ttoon', 2001, 50000, 'N'), (3002, 'Mouse', 'Mouse', 2001, 40000, 'N'), (3003, 'Whatsisname','wname', 2002, 45000, 'N'), (3004, 'Hasnoclue', 'hclue', 2002, 38000, 'N'), (3005, 'Doesallwork','dwork', 2003, 15000, 'N')$ 递归视图允许我们确定所有雇员的工资，包括我们自己的工资。 CREATE VIEW my_emps(empid, level, salary) AS WITH rec(empid, level, salary) AS (SELECT empid, 0, salary FROM PROFILES WHERE sqlid = USER UNION ALL SELECT P.empid, level-1, P.salary FROM PROFILES P, REC R WHERE level > -100 AND R.EMPID = P.MGRID) SELECT empid, level, salary FROM rec$ 下面的视图将是个接口，被任何人用来读取雇员信息。我们考虑了雇员标识（empid）、雇员姓名（name）、他们经理的姓名（name）、他们的 SQLID（sqlid）以及他们是否是经理（ismgr）和公共信息。雇员只能看到他们自己的工资及其下属的工资。 CREATE VIEW PROFILES_V(empid, name, mgrname, salary, sqlid, ismgr) AS SELECT P.empid, P.name, (SELECT name FROM PROFILES M WHERE M.empid = P.mgrid), ME.salary, P.sqlid, P.ismgr FROM PROFILES P LEFT OUTER JOIN MY_EMPS ME ON ME.empid = P.empid$ 根据我们早先的分析，我们知道这个视图是不可删除的，因此也是不可更新或插入的。为了保持封装性，我们需要定义一组完整的 INSTEAD OF 触发器： CREATE TRIGGER INSERT_PROFILES_V INSTEAD OF INSERT ON PROFILES_V REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE mgrid INT; DECLARE ismgr CHAR(1); SET (mgrid, ismgr) = (SELECT empid, ismgr FROM PROFILES WHERE name = n.mgrname); IF mgrid NOT IN (SELECT empid FROM my_emps) OR ismgr = 'N' THEN SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = 'Not Authorized!'; END IF; INSERT INTO PROFILES VALUES(n.empid, n.name, n.sqlid, mgrid, n.salary, n.ismgr); END$ 这个触发器强制执行这条业务规则：假如用户自己是经理，那么他们只能插入作为其下属的雇员。 下面的 INSTEAD OF 更新触发器强制执行这样一条业务规则：只能更新下属
