trigger 이용한 db 접속 제어, 로그 남기기

trigger 를 이용해 db 접속 제어를 해보자..

----------------------------------------------------------------------------------
ORACLE 8I SYSTEM EVENT TRIGGER ( ORACLE 8.1.6 )
Oracle 8i에서는 LOGON 이나 SHUTDOWN 과 같은 시스템 상의 변화를 추적할 수 있는 system event를 제공한다.
다음은 system event 발생 시 가용한 function 목록과활용 예제이다. (Oracle 8.1.6 기준 자료 입니다.)
----------------------------------------------------------------------------------
1. System Defined Event Attributes
 다음은 시스템에서 제공하는 function ( attribute )들의 목록이다.
 아래에 나열된 function은 데이터베이스 생성시 CATPROC.SQL이 실행되면서 생성이 되며,
 SYS 소유이나 public synonym 형태로 제공되어 모든 사용자가 이용할 수 있다. 
 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 

2. 이벤트종류
 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

----------------------------------------------------------------------------------
참고 사이트
http://blog.naver.com/966138?Redirect=Log&logNo=70002128497
오라클 기술지원 게시판
----------------------------------------------------------------------------------


CREATE OR REPLACE TRIGGER LOGON_TRACE
AFTER LOGON ON database

DECLARE

      ls_osuser           varchar2(100);
      ls_machine          varchar2(100);
      ls_terminal         varchar2(100);
      ls_program          varchar2(100);

BEGIN

      select osuser, machine, terminal, program
        into ls_osuser, ls_machine, ls_terminal, ls_program
        from v$session
       where sid = ( select sid from v$mystat where rownum = 1 );

       -- program start
       if (ls_program <> 'JDBC'         ) then

          RAISE_APPLICATION_ERROR (-20001 , 'IP '|| ORA_CLIENT_IP_ADDRESS || ' or ' || ls_program || ' is not allowed to connect database as ' || user);

       elsif (ls_program = 'plsqldev.exe' ) then

          insert into xxx.logtable (dbuser, osuser, machine, terminal, program, ip, logon_dt)
          values (user, ls_osuser, ls_machine, ls_terminal, ls_program, ORA_CLIENT_IP_ADDRESS, sysdate);

       end if;

       if (ORA_CLIENT_IP_ADDRESS = 'xxx.xxx.xxx.xxx' ) then

          insert into xxx.logtable (dbuser, osuser, machine, terminal, program, ip, logon_dt)
          values (user, ls_osuser, ls_machine, ls_terminal, ls_program, ORA_CLIENT_IP_ADDRESS, sysdate);

       else
           RAISE_APPLICATION_ERROR (-20001 , 'IP '|| ORA_CLIENT_IP_ADDRESS || ' or ' || ls_program || ' is not allowed to connect database as ' || user);

       end if;
       -- program end

END;