以一个例子来说明logon trigger的使用问题。
如下只是一个举例，自己修改一下可以完成更多的功能。想完成如下的功能:
<1>如果你的logon trigger不是建在sys用户下，v_$session的查询权限需要授权给给建trigger的用户。
我选择在system用户下建：
SQL>connect / as sysdba; SQL>grant select on v_$session to system;
<2>logon trigger体
CREATE OR REPLACE TRIGGER QIUYB$LOGON AFTER LOGON ON DATABASE DECLARE V_PROGRAM VARCHAR2(48); V_MESSAGE VARCHAR2(1000); V_BAD_LOGON EXCEPTION; BEGIN SELECT PROGRAM INTO V_PROGRAM FROM V$SESSION WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID') AND rownum<2; IF USER IN ('QIUYB','HR') AND SYS_CONTEXT('USERENV','ip_address') LIKE '10.199.168.%' AND SYS_CONTEXT('USERENV','ip_address') LIKE '10.199.173.%' AND LOWER(v_program)<>'zhyz_report.exe' THEN RAISE V_BAD_LOGON; END IF; EXCEPTION WHEN v_bad_logon THEN v_message := 'Uh Uh Uh! - This user can not logon with this software!!'; RAISE_APPLICATION_ERROR(-20002, v_message); WHEN OTHERS THEN v_message := 'FATAL ERROR - QIUYB$LOGON TRIGGER- Please Contact Your DBA!!' || CHR(10) || SQLERRM; RAISE_APPLICATION_ERROR(-20003, v_message); END;
logon trigger对于dba权限的用户是没效力的，只会在alter.log中生成报错信息。
links from: http://qiuyb.itpub.net/post/8049/160310
Comments
There are currently no comments
New Comment