05触发器和权限管理课件

oracle触发器和权限管理oracle触发器和权限管理触发器触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑触发器触发器是当特定事件出现时自动执行的存储过程创建触发器的语法CREATE[ORREPLACE]TRIGGERtrigger_nameAFTER|BEFORE|INSTEADOF[INSERT][[OR]UPDATE[OFcolumn_list]][[OR]DELETE]ONtable_or_view_name[REFERENCING{OLD[AS]old/NEW[AS]new}][FOREACHROW][WHEN(condition)]pl/sql_block;创建触发器的语法CREATE[ORREPLACE]TR触发器的组成部分-1触发器由三部分组成：触发器语句（事件）定义激活触发器的DML事件和DDL事件触发器限制执行触发器的条件，该条件必须为真才能激活触发器触发器操作（主体）包含一些SQL语句和代码，它们在发出了触发器语句且触发限制的值为真时运行触发器的组成部分-1触发器由三部分组成：触发器的组成部分-2SQL>CREATEORREPLACETRIGGERtrig_sal AFTERUPDATEOFempsalONsalary_records

…触发器语句为salary_records表创建trig-sal触发器在更新emp_sal列之后激活触发器触发器限制SQL> … FOREACHROW WHEN(NEW.empsal>OLD.empsal) DECLARE Sal_diffNUMBER; … 只有在WHEN子句中的条件得到满足时，才激活trig_sal触发器触发器操作SQL>… BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff); END;如果WHEN子句中的条件得到满足，将执行BEGIN块中的代码触发器的组成部分-2SQL>CREATEORREPL触发器的组成部分-3Oracle数据库更新表保存更新激活触发器AFTER触发器的工作原理BEFORE触发器的工作原理更新表激活触发器保存更新Oracle数据库触发器的组成部分-3Oracle更新表保存更新激活触发器A创建触发器CREATEORREPLACETRIGGERaiu_itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF(:NEW.qty_hand=0)THENDBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');ELSEDBMS_OUTPUT.PUT_LINE(‘已插入记录');ENDIF;END;/创建触发器CREATEORREPLACETRIGGER触发器类型-1触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级触发器行级触发器INSTEADOF触发器触发器类型-1触发器的类型有：触发器类型模式(DDL)DML触发器类型-2DDL触发器数据库级触发器DML触发器语句级触发器行级触发器INSTEADOF触发器在模式中执行DDL语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用DML语句修改的视图触发器类型-2DDL触发器在模式中执行DDL语句时执行触发器类型-3行级触发器SQL>CREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20));SQL>CREATESEQUENCESEQ_TEST;SQL>CREATEORREPLACETRIGGERBI_TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST.NEXTVALINTO:NEW.IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(-20020,'不允许更新ID值！');ENDIF;END;/触发器类型-3行级触发器SQL>CREATETABLE触发器类型-4SQL>CREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(‘已更新ORDER_MASTER中的数据');ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(‘已删除ORDER_MASTER中的数据');ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(‘已在ORDER_MASTER中插入数据');ENDIF;END;/语句级触发器触发器类型-4SQL>CREATEORREPLACE触发器类型-5SQL>CREATEORREPLACETRIGGERupd_ord_viewINSTEADOFUPDATEONord_viewFOREACHROWBEGIN UPDATEorder_masterSETvencode=:NEW.vencode WHEREorderno=:NEW.orderno; DBMS_OUTPUT.PUT_LINE(‘已激活触发器');END;/INSTEADOF触发器触发器类型-5SQL>CREATEORREPLACE触发器类型-6SQL>CREATETABLEdropped_obj(obj_nameVARCHAR2(30),obj_typeVARCHAR2(20),drop_dateDATE);SQL>CREATEORREPLACETRIGGERlog_drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);END;/模式触发器触发器类型-6SQL>CREATETABLEdropp启用和禁用触发器删除触发器启用、禁用和删除触发器SQL>ALTERTRIGGERaiu_itemfileDISABLE;SQL>ALTERTRIGGERaiu_itemfileENABLE;SQL>DROPTRIGGERaiu_itemfile;启用和禁用触发器启用、禁用和删除触发器SQL>ALTER查看有关触发器的信息SQL>SELECTTRIGGER_NAMEFROMUSER_TRIGGERSWHERETABLE_NAME='EMP';SQL>SELECTTRIGGER_TYPE,TRIGGERING_EVENT,WHEN_CLAUSEFROMUSER_TRIGGERSWHERETRIGGER_NAME='BIU_EMP_DEPTNO';USER_TRIGGERS数据字典视图包含有关触发器的信息查看有关触发器的信息SQL>SELECTTRIGGER_权限权限是执行一种特殊类型的SQL语句或存取另一用户的对象的权力。有两类权限：系统权限和对象权限。系统权限：是执行一处特殊动作或者在对象类型上执行一种特殊动作的权利。系统权限可授权给用户或角色，一般，系统权限只授予管理人员和应用开发人员，终端用户不需要这些相关功能。对象权限：在指定的表、视图、序列、过程、函数或包上执行特殊动作的权利。权限权限是执行一种特殊类型的SQL语句或存取另一用户的对象的角色为相关权限的命名组，可授权给用户和角色。数据库角色包含下列功能：一个角色可授予系统权限或对象权限。一个角色可授权给其它角色，但不能循环授权。任何角色可授权给任何数据库用户。授权给用户的每一角色可以是可用的或者不可用的。一个间接授权角色对用户可显式地使其可用或不可用。在一个数据库中，每一个角色名必须唯一。角色名与用户不同，角色不包含在任何模式中，所以建立角色的用户被删除时不影响该角色。建立角色的目的为数据库应用管理权限和为用户组管理权限。相对应的角色称为应用角色和用户角色。应用角色是授予的运行数据库应用所需的全部权限。用户角色是为具有公开权限需求的一组数据库用户而建立的。用户权限管理是受应用角色或权限授权给用户角色所控制，然后将用户角色授权给相应的用户。角色为相关权限的命名组，可授权给用户和角色。数据库角色包含下利用角色对权限管理的优点ORACEL利用角色更容易地进行权限管理。有下列优点：减少权限管理，不要显式地将同一权限组授权给几个用户，只需将这权限组授给角色，然后将角色授权给每一用户。动态权限管理，如果一组权限需要改变，只需修改角色的权限，所有授给该角色的全部用户的安全域将自动地反映对角色所作的修改。权限的选择可用性，授权给用户的角色可选择地使其可用或不可用。应用可知性，当用户经用户名执行应用时，该数据库应用可查询字典，将自动地选择使角色可用或不可用。应用安全性，角色使用可由口令保护，应用可提供正确的口令使用角色，利用角色对权限管理的优点ORACEL利用角色更容易地进行权限创建角色-1使用CREATEROLE语句可以创建一个新的角色，执行该语句的用户必须具有CREATEROLE系统权限。在角色刚刚创建时，它并不具有任何权限，这时的角色是没有用处的。因此，在创建角色之后，通常会立即为它授予权限。例如：利用下面的语句创建了一个名为OPT_ROLE的角色，并且为它授予了一些对象权限和系统权限：CREATEROLEOPT_ROLE;GRANTSELECTONsal_historyTOOPT_ROLE;GRANTINSERT,UPDATEONmount_entryTOOPT_ROLE;GRANTCREATEVIEWTOOPT_ROLE;创建角色-1使用CREATEROLE语句可以创建一个新的角授予权限或角色-授予系统权限在GRANT关键字之后指定系统权限的名称，然后在TO关键字之后指定接受权限的用户名，即可将系统权限授予指定的用户。例如：利用下面的语句可以相关权限授予用户chenjie：GRANTCREATEUSER,ALTERUSER,DROPUSERTOchenjieWITHADMINOPTION;授予权限或角色-授予系统权限在GRANT关键字之后指定系统授予权限或角色-授予对象权限Oracle对象权限指用户在指定的表上进行特殊操作的权利。在GRANT关键字之后指定对象权限的名称，然后在ON关键字后指定对象名称，最后在TO关键字之后指定接受权限的用户名，即可将指定对象的对象权限授予指定的用户。使用一条GRANT语句可以同时授予用户多个对象权限，各个权限名称之间用逗号分隔。有三类对象权限可以授予表或视图中的字段，它们是分别是INSERT，UPDATE和REFERENCES对象例如：利用下面的语句可以将CUSTOMER表的SELECT和INSERT，UPDATE对象权限授予用户chenqian：GRANTSELECT,INSERT(CUSTOMER_ID,CUSTOMER_name), UPDATE(desc)ONCUSTOMERTOchenqianWITHGRANTOPTION;在授予对象权限时，可以使用一次关键字ALL或ALLPRIVILEGES将某个对象的所有对象权限全部授予指定的用户。授予权限或角色-授予对象权限Oracle对象权限指用户在指授予权限或角色-授予角色在GRANT关键字之后指定角色的名称，然后在TO关键字之后指定用户名，即可将角色授予指定的用户。Oracle数据库系统预先定义了CONNECT、RESOURCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE五个角色。CONNECT具有创建表、视图、序列等权限；RESOURCE具有创建过程、触发器、表、序列等权限、DBA具有全部系统权限；EXP_FULL_DATABASE、IMP_FULL_DATABASE具有卸出与装入数据库的权限。通过查询sys.dba_sys_privs可以了解每种角色拥有的权利。授予权限或角色-授予角色在GRANT关键字之后指定角色的名回收权限或角色使用REVOKE语句可以回收己经授予用户（或角色）的系统权限、对象权限与角色，执行回收权限操作的用户同时必须具有授予相同权限的能力。例如：利用下面的语句可以回收已经授予用户chenqian的SELECT和UPDATE对象权限：REVOKESELECT,UPDATEONCUSTOMERFROMchenqian;利用下面的语句可以回收已经授予用户chenjie的CREATEANYTABLE系统权限：REVOKECREATEANYTABLEFROMchenjie;利用下面的语句可以回收己经授予用户chenjie的OPT_ROLE角色：REVOKEOPT_ROLEFROMchenjie;在回收对象权限时，可以使用关键字ALL或ALLPRIVILEGES将某个对象的所有对象权限全部回收。例如：利用下面的语句可以回收己经授予用户chenqian的CUSTOMER表的所有对象权限：REVOKEALLONCUSTOMERFROMchenjie;回收权限或角色使用REVOKE语句可以回收己经授予用户（或角激活和禁用角色一个用户可以同时被授予多个角色，但是并不是所有的这些角色都同时起作用。角色可以处于两种状态：激活状态或禁用状态，禁用状态的角色所具有权限并不生效。当用户连接到数据库中时，只有他的默认角色（DefaultRole）处于激活状态。在ALTERUSER角色中使用DEFAULTROLE子句可以改变用户的默认角色。例如：如果要将用户所拥有的一个角色设置为默认角色，可以使用下面的语句：ALTERUSERchenjieDEFAULTROLEconnect,OPT_ROLE在用户会话的过程中，还可以使用SETROLE语句来激活或禁用他所拥有的角色。用户所同时激活的最大角色数目由初始化参数ENABLEDROLES决定（默认值为20）。如果角色在创建时使用了IDENTIFIEDBY子句，则在使用SETROLE语句激活角色时也需要在IDENTIFIEDBY子句中提供口令。如果要激活用户所拥有的所有角色，可以使用下面的语句：SETROLEALL;激活和禁用角色一个用户可以同时被授予多个角色，但是并不是所有总结子程序是命名的PL/SQL块，可带参数并可在需要时随时调用有两种类型的PL/SQL子程序，即过程和函数过程用户执行特定的任务，函数用于执行任务并返回值触发器是当特定事件出现时自动执行的存储过程触发器分为DML触发器、DDL触发器和数据库级触发器三种类型DML触发器的三种类型包括行级触发器、语句级触发器和INSTEADOF触发器总结子程序是命名的PL/SQL块，可带参数并可在需要时随oracle触发器和权限管理oracle触发器和权限管理触发器触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑触发器触发器是当特定事件出现时自动执行的存储过程创建触发器的语法CREATE[ORREPLACE]TRIGGERtrigger_nameAFTER|BEFORE|INSTEADOF[INSERT][[OR]UPDATE[OFcolumn_list]][[OR]DELETE]ONtable_or_view_name[REFERENCING{OLD[AS]old/NEW[AS]new}][FOREACHROW][WHEN(condition)]pl/sql_block;创建触发器的语法CREATE[ORREPLACE]TR触发器的组成部分-1触发器由三部分组成：触发器语句（事件）定义激活触发器的DML事件和DDL事件触发器限制执行触发器的条件，该条件必须为真才能激活触发器触发器操作（主体）包含一些SQL语句和代码，它们在发出了触发器语句且触发限制的值为真时运行触发器的组成部分-1触发器由三部分组成：触发器的组成部分-2SQL>CREATEORREPLACETRIGGERtrig_sal AFTERUPDATEOFempsalONsalary_records

…触发器语句为salary_records表创建trig-sal触发器在更新emp_sal列之后激活触发器触发器限制SQL> … FOREACHROW WHEN(NEW.empsal>OLD.empsal) DECLARE Sal_diffNUMBER; … 只有在WHEN子句中的条件得到满足时，才激活trig_sal触发器触发器操作SQL>… BEGIN sal_diff:=:NEW.empsal-:OLD.empsal; DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff); END;如果WHEN子句中的条件得到满足，将执行BEGIN块中的代码触发器的组成部分-2SQL>CREATEORREPL触发器的组成部分-3Oracle数据库更新表保存更新激活触发器AFTER触发器的工作原理BEFORE触发器的工作原理更新表激活触发器保存更新Oracle数据库触发器的组成部分-3Oracle更新表保存更新激活触发器A创建触发器CREATEORREPLACETRIGGERaiu_itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF(:NEW.qty_hand=0)THENDBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');ELSEDBMS_OUTPUT.PUT_LINE(‘已插入记录');ENDIF;END;/创建触发器CREATEORREPLACETRIGGER触发器类型-1触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级触发器行级触发器INSTEADOF触发器触发器类型-1触发器的类型有：触发器类型模式(DDL)DML触发器类型-2DDL触发器数据库级触发器DML触发器语句级触发器行级触发器INSTEADOF触发器在模式中执行DDL语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用DML语句修改的视图触发器类型-2DDL触发器在模式中执行DDL语句时执行触发器类型-3行级触发器SQL>CREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20));SQL>CREATESEQUENCESEQ_TEST;SQL>CREATEORREPLACETRIGGERBI_TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST.NEXTVALINTO:NEW.IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(-20020,'不允许更新ID值！');ENDIF;END;/触发器类型-3行级触发器SQL>CREATETABLE触发器类型-4SQL>CREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(‘已更新ORDER_MASTER中的数据');ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(‘已删除ORDER_MASTER中的数据');ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(‘已在ORDER_MASTER中插入数据');ENDIF;END;/语句级触发器触发器类型-4SQL>CREATEORREPLACE触发器类型-5SQL>CREATEORREPLACETRIGGERupd_ord_viewINSTEADOFUPDATEONord_viewFOREACHROWBEGIN UPDATEorder_masterSETvencode=:NEW.vencode WHEREorderno=:NEW.orderno; DBMS_OUTPUT.PUT_LINE(‘已激活触发器');END;/INSTEADOF触发器触发器类型-5SQL>CREATEORREPLACE触发器类型-6SQL>CREATETABLEdropped_obj(obj_nameVARCHAR2(30),obj_typeVARCHAR2(20),drop_dateDATE);SQL>CREATEORREPLACETRIGGERlog_drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);END;/模式触发器触发器类型-6SQL>CREATETABLEdropp启用和禁用触发器删除触发器启用、禁用和删除触发器SQL>ALTERTRIGGERaiu_itemfileDISABLE;SQL>ALTERTRIGGERaiu_itemfileENABLE;SQL>DROPTRIGGERaiu_itemfile;启用和禁用触发器启用、禁用和删除触发器SQL>ALTER查看有关触发器的信息SQL>SELECTTRIGGER_NAMEFROMUSER_TRIGGERSWHERETABLE_NAME='EMP';SQL>SELECTTRIGGER_TYPE,TRIGGERING_EVENT,WHEN_CLAUSEFROMUSER_TRIGGERSWHERETRIGGER_NAME='BIU_EMP_DEPTNO';USER_TRIGGERS数据字典视图包含有关触发器的信息查看有关触发器的信息SQL>SELECTTRIGGER_权限权限是执行一种特殊类型的SQL语句或存取另一用户的对象的权力。有两类权限：系统权限和对象权限。系统权限：是执行一处特殊动作或者在对象类型上执行一种特殊动作的权利。系统权限可授权给用户或角色，一般，系统权限只授予管理人员和应用开发人员，终端用户不需要这些相关功能。对象权限：在指定的表、视图、序列、过程、函数或包上执行特殊动作的权利。权限权限是执行一种特殊类型的SQL语句或存取另一用户的对象的角色为相关权限的命名组，可授权给用户和角色。数据库角色包含下列功能：一个角色可授予系统权限或对象权限。一个角色可授权给其它角色，但不能循环授权。任何角色可授权给任何数据库用户。授权给用户的每一角色可以是可用的或者不可用的。一个间接授权角色对用户可显式地使其可用或不可用。在一个数据库中，每一个角色名必须唯一。角色名与用户不同，角色不包含在任何模式中，所以建立角色的用户被删除时不影响该角色。建立角色的目的为数据库应用管理权限和为用户组管理权限。相对应的角色称为应用角色和用户角色。应用角色是授予的运行数据库应用所需的全部权限。用户角色是为具有公开权限需求的一组数据库用户而建立的。用户权限管理是受应用角色或权限授权给用户角色所控制，然后将用户角色授权给相应的用户。角色为相关权限的命名组，可授权给用户和角色。数据库角色包含下利用角色对权限管理的优点ORACEL利用角色更容易地进行权限管理。有下列优点：减少权限管理，不要显式地将同一权限组授权给几个用户，只需将这权限组授给角色，然后将角色授权给每一用户。动态权限管理，如果一组权限需要改变，只需修改角色的权限，所有授给该角色的全部用户的安全域将自动地反映对角色所作的修改。权限的选择可用性，授权给用户的角色可选择地使其可用或不可用。应用可知性，当用户经用户名执行应用时，该数据库应用可查询字典，将自动地选择使角色可用或不可用。应用安全性，角色使用可由口令保护，应用可提供正确的口令使用角色，利用角色对权限管理的优点ORACEL利用角色更容易地进行权限创建角色-1使用CREATEROLE语句可以创建一个新的角色，执行该语句的用户必须具有CREATEROLE系统权限。在角色刚刚创建时，它并不具有任何权限，这时的角色是没有用处的。因此，在创建角色之后，通常会立即为它授予权限。例如：利用下面的语句创建了一个名为OPT_ROLE的角色，并且为它授予了一些对象权限和系统权限：CREATEROLEOPT_ROLE;GRANTSELECTONsal_historyTOOPT_ROLE;GRANTINSERT,UPDATEONmount_entryTOOPT_ROLE;GRANTCREATEVIEWTOOPT_ROLE;创建角色-1使用CREATEROLE语句可以创建一个新的角授予权限或角色-授予系统权限在GRANT关键字之后指定系统权限的名称，然后在TO关键字之后指定接受权限的用户名，即可将系统权限授予指定的用户。例如：利用下面的语句可以相关权限授予用户chenjie：GRANTCREATEUSER,ALTERUSER,DROPUSERTOchenjieWITHADMINOPTION;授予权限或角色-授予系统权限在GRANT关键字之后指定系统授予权限或角色-授予对象权限Oracle对象权限指用户在指定的表上进行特殊操作的权利。在GRANT关键字之后指定对象权限的名称，然后在ON关键字后指定对象名称，最后在TO关键字之后指定接受权限的用户名，即可将指定对象的对象权限授予指定的用户。使用一条GRANT语句可以同时授予用户多个对象权限，各个权限名称之间用逗号分隔。有三类对象权限可以授予表或视图中的字段，它们是分别是INSERT，UPDATE和REFERENCES对象例如：利用下面的语句可以将CUSTOMER表的SELECT和INSERT，UPDATE对象权限授予用户chenqian：GRANTSELECT,INSERT(CUSTOMER_ID,CUSTOMER_name), UPDATE(desc)ONCUSTOMERTOchenqianWITHGRANTOPTION;在授予对象权限时，可以使用一次关键字ALL或ALLPRIVILEGES将某个对象的所有对象权限全部授予指定的用户。授予权限或角色-授予对象权限Oracle对象权限指用户在指授予权限或角色-授予角色在GRANT关键字之后指定角色的名称，然后在TO关键字之后指定用户名，即可将角色授予指定的用户。Oracle数据库系统预先定义了CONNECT、RESOU