1、oracle触发器和权限管理触发器和权限管理触发器触发器l触发器是当特定事件出现时自动执行的存储触发器是当特定事件出现时自动执行的存储过程过程l特定事件可以是执行更新的特定事件可以是执行更新的DML语句和语句和DDL语句语句l触发器不能被显式调用触发器不能被显式调用l触发器的功能：触发器的功能：自动生成数据自动生成数据自定义复杂的安全权限自定义复杂的安全权限提供审计和日志记录提供审计和日志记录启用复杂的业务逻辑启用复杂的业务逻辑创建触发器的语法创建触发器的语法CREATE OR REPLACE TRIGGER trigger_nameAFTER|BEFORE|INSTEAD OFINSERT
2、OR UPDATE OF column_list OR DELETEON table_or_view_nameREFERENCING OLD AS old/NEW AS newFOR EACH ROWWHEN(condition)pl/sql_block;触发器的组成部分触发器的组成部分-1触发器由三部分组成：触发器由三部分组成：l触发器语句（事件）触发器语句（事件）定义激活触发器的定义激活触发器的 DML 事件和事件和 DDL 事件事件l触发器限制触发器限制执行触发器的条件，该条件必须为真才能激活执行触发器的条件，该条件必须为真才能激活触发器触发器l触发器操作（主体）触发器操作（主体）包含一
3、些包含一些 SQL 语句和代码，它们在发出了触语句和代码，它们在发出了触发器语句且触发限制的值为真时运行发器语句且触发限制的值为真时运行触发器的组成部分触发器的组成部分-2 SQL CREATE OR REPLACE TRIGGER trig_salAFTER UPDATE OF empsal ON salary_records触发器语句为 salary_records 表创建 trig-sal 触发器在更新 emp_sal 列之后激活触发器触发器限制SQL FOR EACH ROWWHEN(NEW.empsalOLD.empsal)DECLARESal_diff NUMBER;只有在WHEN
4、子句中的条件得到满足时，才激活trig_sal 触发器触发器操作SQL BEGIN sal_diff:=:NEW.empsal-:OLD.empsal;DBMS_OUTPUT.PUT_LINE(工资差额：sal_diff);END;如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码触发器的组成部分触发器的组成部分-3Oracle 数据库更新表表保存更新激活触发器触发器AFTER 触发器的工作原理BEFORE 触发器的工作原理更新表表激活触发器触发器保存更新Oracle 数据库创建触发器创建触发器CREATE OR REPLACE TRIGGER aiu_itemfileAFTER
5、INSERTON itemfileFOR EACH ROWBEGIN IF(:NEW.qty_hand=0)THEN DBMS_OUTPUT.PUT_LINE(警告：已插入记录，但数量为零);ELSE DBMS_OUTPUT.PUT_LINE(已插入记录);END IF;END;/触发器类型触发器类型-1l触发器的类型有：触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级触发器行级触发器INSTEAD OF触发器触发器类型触发器类型-2DDL 触发器数据库级触发器DML 触发器语句级触发器行级触发器INSTEAD OF 触发器在模式中执行 DDL 语句时执行在发生打
6、开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用 DML 语句修改的视图触发器类型触发器类型-3l行级触发器行级触发器SQL CREATE TABLE TEST_TRG (ID NUMBER,NAME VARCHAR2(20);SQL CREATE SEQUENCE SEQ_TEST;SQL CREATE OR REPLACE TRIGGER BI_TEST_TRGBEFORE INSERT OR UPDATE OF IDON TEST_TRGFOR EACH ROWBEGIN I
7、F INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO:NEW.ID FROM DUAL;ELSE RAISE_APPLICATION_ERROR(-20020,不允许更新ID值！);END IF;END;/触发器类型触发器类型-4SQL CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT OR UPDATE OR DELETE ON order_masterBEGIN IF UPDATING THEN DBMS_OUTPUT.PUT_LINE(已更新 ORDER_MASTER 中的数据);ELSIF DELETIN
8、G THEN DBMS_OUTPUT.PUT_LINE(已删除 ORDER_MASTER 中的数据);ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE(已在 ORDER_MASTER 中插入数据);END IF;END;/l语句级触发器语句级触发器触发器类型触发器类型-5SQL CREATE OR REPLACE TRIGGER upd_ord_view INSTEAD OF UPDATE ON ord_view FOR EACH ROW BEGIN UPDATE order_master SET vencode=:NEW.vencode WHERE orde
9、rno=:NEW.orderno;DBMS_OUTPUT.PUT_LINE(已激活触发器);END;/lINSTEAD OF 触发器触发器触发器类型触发器类型-6SQL CREATE TABLE dropped_obj(obj_name VARCHAR2(30),obj_type VARCHAR2(20),drop_date DATE);SQL CREATE OR REPLACE TRIGGER log_drop_objAFTER DROP ON SCHEMABEGIN INSERT INTO dropped_obj VALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_
10、TYPE,SYSDATE);END;/l模式触发器模式触发器l启用和禁用触发器启用和禁用触发器l删除触发器删除触发器启用、禁用和删除触发器启用、禁用和删除触发器SQL ALTER TRIGGER aiu_itemfile DISABLE;SQL ALTER TRIGGER aiu_itemfile ENABLE;SQL DROP TRIGGER aiu_itemfile;查看有关触发器的信息查看有关触发器的信息SQL SELECT TRIGGER_NAME FROM USER_TRIGGERSWHERE TABLE_NAME=EMP;SQL SELECT TRIGGER_TYPE,TRIGGE
11、RING_EVENT,WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME=BIU_EMP_DEPTNO;lUSER_TRIGGERS 数据字典视图包含有关数据字典视图包含有关触发器的信息触发器的信息权限权限l权限是执行一种特殊类型的SQL语句或存取另一用户的对象的权力。有两类权限：系统权限和对象权限。l系统权限：是执行一处特殊动作或者在对象类型上执行一种特殊动作的权利。系统权限可授权给用户或角色，一般，系统权限只授予管理人员和应用开发人员，终端用户不需要这些相关功能。l对象权限：在指定的表、视图、序列、过程、函数或包上执行特殊动作的权利。角色角色l为
12、相关权限的命名组，可授权给用户和角色。数据库角色包含下列功能：一个角色可授予系统权限或对象权限。一个角色可授权给其它角色，但不能循环授权。任何角色可授权给任何数据库用户。授权给用户的每一角色可以是可用的或者不可用的。一个间接授权角色对用户可显式地使其可用或不可用。l在一个数据库中，每一个角色名必须唯一。角色名与用户不同，角色不包含在任何模式中，所以建立角色的用户被删除时不影响该角色。l建立角色的目的建立角色的目的为数据库应用管理权限和为用户组管理权限。相对应的角色称为应用角色和用户角色。应用角色是授予的运行数据库应用所需的全部权限。用户角色是为具有公开权限需求的一组数据库用户而建立的。用户权限
13、管理是受应用角色或权限授权给用户角色所控制，然后将用户角色授权给相应的用户。利用角色对权限管理的优点利用角色对权限管理的优点lORACEL利用角色更容易地进行权限管理。有下列优点：减少权限管理，不要显式地将同一权限组授权给几个用户，只需将这权限组授给角色，然后将角色授权给每一用户。动态权限管理，如果一组权限需要改变，只需修改角色的权限，所有授给该角色的全部用户的安全域将自动地反映对角色所作的修改。权限的选择可用性，授权给用户的角色可选择地使其可用或不可用。应用可知性，当用户经用户名执行应用时，该数据库应用可查询字典，将自动地选择使角色可用或不可用。应用安全性，角色使用可由口令保护，应用可提供正
14、确的口令使用角色，创建角色创建角色-1l使用使用CREATE ROLE语句可以创建一个新的角色，语句可以创建一个新的角色，执行该语句的用户必须具有执行该语句的用户必须具有CREATE ROLE系统权系统权限。限。l在角色刚刚创建时，它并不具有任何权限，这时的在角色刚刚创建时，它并不具有任何权限，这时的角色是没有用处的。因此，在创建角色之后，通常角色是没有用处的。因此，在创建角色之后，通常会立即为它授予权限。例如：利用下面的语句创建会立即为它授予权限。例如：利用下面的语句创建了一个名为了一个名为OPT_ROLE的角色，并且为它授予了一的角色，并且为它授予了一些对象权限和系统权限：些对象权限和系统
15、权限：CREATE ROLE OPT_ROLE;GRANT SELECT ON sal_history TO OPT_ROLE;GRANT INSERT,UPDATE ON mount_entry TO OPT_ROLE;GRANT CREATE VIEW TO OPT_ROLE;授予权限或角色授予权限或角色-授予系统权限授予系统权限l在GRANT关键字之后指定系统权限的名称，然后在TO关键字之后指定接受权限的用户名，即可将系统权限授予指定的用户。l例如：利用下面的语句可以相关权限授予用户chenjie：GRANT CREATE USER,ALTER USER,DROP USER TO che
16、njie WITH ADMIN OPTION;授予权限或角色授予权限或角色-授予对象权限授予对象权限lOracle对象权限指用户在指定的表上进行特殊操作的权利。l 在GRANT关键字之后指定对象权限的名称，然后在ON关键字后指定对象名称，最后在TO关键字之后指定接受权限的用户名，即可将指定对象的对象权限授予指定的用户。l使用一条GRANT语句可以同时授予用户多个对象权限，各个权限名称之间用逗号分隔。l有三类对象权限可以授予表或视图中的字段，它们是分别是INSERT，UPDATE和REFERENCES对象l例如：利用下面的语句可以将CUSTOMER表的SELECT和INSERT，UPDATE对象
17、权限授予用户chenqian：GRANT SELECT,INSERT(CUSTOMER_ID,CUSTOMER_name),UPDATE(desc)ON CUSTOMER TO chenqian WITH GRANT OPTION;l在授予对象权限时，可以使用一次关键字ALL或ALL PRIVILEGES将某个对象的所有对象权限全部授予指定的用户。授予权限或角色授予权限或角色-授予角色授予角色l在GRANT关键字之后指定角色的名称，然后在TO关键字之后指定用户名，即可将角色授予指定的用户。Oracle数据库系统预先定义了CONNECT、RESOURCE、DBA、EXP_FULL_DATABAS
18、E、IMP_FULL_DATABASE五个角色。CONNECT具有创建表、视图、序列等权限；RESOURCE具有创建过程、触发器、表、序列等权限、DBA具有全部系统权限；EXP_FULL_DATABASE、IMP_FULL_DATABASE具有卸出与装入数据库的权限。l通过查询sys.dba_sys_privs可以了解每种角色拥有的权利。回收权限或角色回收权限或角色l使用使用REVOKE语句可以回收己经授予用户（或角色）的系统权限、对象语句可以回收己经授予用户（或角色）的系统权限、对象权限与角色，执行回收权限操作的用户同时必须具有授予相同权限的能力。权限与角色，执行回收权限操作的用户同时必须具
19、有授予相同权限的能力。l例如：利用下面的语句可以回收已经授予用户例如：利用下面的语句可以回收已经授予用户chenqian的的SELECT和和UPDATE对象权限：对象权限：REVOKE SELECT,UPDATE ON CUSTOMER FROM chenqian;l利用下面的语句可以回收已经授予用户利用下面的语句可以回收已经授予用户chenjie的的CREATE ANY TABLE系统权限系统权限：REVOKE CREATE ANY TABLE FROM chenjie;l利用下面的语句可以回收己经授予用户利用下面的语句可以回收己经授予用户chenjie的的OPT_ROLE角色：角色：REV
20、OKE OPT_ROLE FROM chenjie;l在回收对象权限时，可以使用关键字在回收对象权限时，可以使用关键字ALL或或ALL PRIVILEGES将某个对将某个对象的所有对象权限全部回收。象的所有对象权限全部回收。l例如：利用下面的语句可以回收己经授予用户例如：利用下面的语句可以回收己经授予用户chenqian的的CUSTOMER表表的所有对象权限：的所有对象权限：REVOKE ALL ON CUSTOMER FROM chenjie;激活和禁用角色激活和禁用角色l一个用户可以同时被授予多个角色，但是并不是所有的这些一个用户可以同时被授予多个角色，但是并不是所有的这些角色都同时起作用
21、。角色可以处于两种状态：激活状态或禁角色都同时起作用。角色可以处于两种状态：激活状态或禁用状态，禁用状态的角色所具有权限并不生效。用状态，禁用状态的角色所具有权限并不生效。l当用户连接到数据库中时，只有他的默认角色（当用户连接到数据库中时，只有他的默认角色（Default Role）处于激活状态。在）处于激活状态。在ALTER USER角色中使用角色中使用DEFAULT ROLE子句可以改变用户的默认角色。子句可以改变用户的默认角色。l例如：如果要将用户所拥有的一个角色设置为默认角色，可例如：如果要将用户所拥有的一个角色设置为默认角色，可以使用下面的语句：以使用下面的语句：ALTER USER
22、 chenjie DEFAULT ROLE connect,OPT_ROLEl在用户会话的过程中，还可以使用在用户会话的过程中，还可以使用SET ROLE语句来激活或语句来激活或禁用他所拥有的角色。用户所同时激活的最大角色数目由初禁用他所拥有的角色。用户所同时激活的最大角色数目由初始化参数始化参数ENABLED ROLES决定（默认值为决定（默认值为20）。如果角色）。如果角色在创建时使用了在创建时使用了IDENTIFIED BY子句，则在使用子句，则在使用SET ROLE语句激活角色时也需要在语句激活角色时也需要在IDENTIFIED BY子句中提供口令。子句中提供口令。l如果要激活用户所拥
23、有的所有角色，可以使用下面的语句：如果要激活用户所拥有的所有角色，可以使用下面的语句：SET ROLE ALL;总结总结l子程序是命名的子程序是命名的 PL/SQL 块，可带参数并可在需块，可带参数并可在需要时随时调用要时随时调用l有两种类型的有两种类型的PL/SQL子程序，即过程和函数子程序，即过程和函数l过程用户执行特定的任务，函数用于执行任务并过程用户执行特定的任务，函数用于执行任务并返回值返回值l触发器是当特定事件出现时自动执行的存储过程触发器是当特定事件出现时自动执行的存储过程 l触发器分为触发器分为 DML 触发器、触发器、DDL 触发器和数据库触发器和数据库级触发器三种类型级触发器三种类型lDML 触发器的三种类型包括行级触发器、语句级触发器的三种类型包括行级触发器、语句级触发器和触发器和 INSTEAD OF 触发器触发器
