Oracle 8.1.6 부터 system event trigger 란게 있습니다.
Oracle 8i에서는 LOGON 이나 SHUTDOWN 과 같은 시스템 상의 변화를 추적할 수 있는
system event를 제공한다.
1. Event의 종류
1) Resource Manager Event
STARTUP, SHUTDOWN, SERVERERROR
2) Client Event
AFTER LOGON, BEFORE LOGOFF, BEFORE CREATE, AFTER CREATE,
BEFORE ALTER, AFTER ALTER, BEFORE DROP, AFTER DROP,
BEFORE ANALYZE, AFTER ANALYZE, BEFORE ASSOCIATE STATISTICS,
AFTER ASSOCIATE STATISTICS, BEFORE AUDIT, AFTER AUDIT,
BEFORE NOAUDIT, AFTER NOAUDIT, BEFORE COMMENT, AFTER COMMENT,
BEFORE CREATE, AFTER CREATE, BEFORE DDL, AFTER DDL,
BEFORE DISASSOCIATE STATISTICS, AFTER DISASSOCIATE STATISTICS,
BEFORE GRANT, AFTER GRANT, BEFORE RENAME, AFTER RENAME,
BEFORE REVOKE, AFTER REVOKE, BEFORE TRUNCATE, AFTER TRUNCATE
2. System Defined Event Attributes
ora_client_ip_address, ora_database_name,
ora_des_encrypted_password,ora_dict_obj_name,
ora_dict_obj_name_list, ora_dict_obj_owner,
ora_dict_obj_owner_list, ora_dict_obj_type, ora_grantee,
ora_instance_num,ora_is_alter_column, ora_is_creating_nested_table,
ora_is_drop_column,ora_is_servererror, ora_login_user,
ora_privileges, ora_revokee,ora_server_error, ora_sysevent,
ora_with_grant_option
3.SYS_CONTEXT 함수
이 함수는 namespace와 관계되는 parameter의 값을 반환한다.
【형식】
SYS_CONTEXT('namespace','parameter' [,length])
【예제】
SQL> select sys_context('userenv','session_user') from dual;
SYS_CONTEXT('USERENV','SESSION_USER')
-------------------------------------
JIJOE
SQL> select sys_context('userenv','lang') from dual;
SYS_CONTEXT('USERENV','LANG')
-----------------------------
US
USERENV에서 사용될 parameter는 다음과 같다.
AUDITED_CURSORID AUTHENTICATION_DATA BG_JOB_ID
CLIENT_IDENTIFIER CLIENT_INFO CURRENT_SCHEMA
CURRENT_SCHEMAID CURRENT_SQL CURRENT_USER
CURRENT_USERID DB_DOMAIN DB_NAME
ENTRY_ID EXTERNAL_NAME FG_JOB_ID
GLOBAL_CONTEXT_MEMORY HOST INSTANCE
IP_ADDRESS ISDBA LANG
LANGUAGE NETWORK_PROTOCOL NLS_CALENDAR
NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_LANGUAGE
NLS_SORT NLS_TERRITORY OS_USER
PROXY_USER PROXY_USERID SESSION_USER
SESSION_USERID SESSIONID TERMINAL
[예제]
특정 USER에서 DDL 등의 COMMAND 실행을 제한하는 방법 - DDL EVENT TRIGGER
CREATE OR REPLACE TRIGGER ddl_prevent_trig
before drop or truncate ON database
begin
raise_application_error (-20102, 'Cannot execute DROP or TRUNCATE !!');
end;
/
오라클 ERP시스템에서 이 트리거를 적용하면 안된다.
회계쪽 모듈에서 내부적으로 임시테이블,인덱스를 생성,삭제하므로 concurrent program에서 에러가 발생한다.
몰라서 적용했다가 삭제한 경우가 있습니다.
[예제]
CREATE OR REPLACE TRIGGER ddl_history
after ddl ON database
begin
insert into ddl_history values
( sysdate,SYS_CONTEXT('USERENV','IP_ADDRESS',16),userenv('sessionid'),ora_login_user,
SYS_CONTEXT('USERENV','OS_USER',30),SYS_CONTEXT('USERENV','host',64),SYS_CONTEXT('USERENV','TERMINAL',30),
ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type, ora_sysevent);
end;
/