after logon trigger禁止生产用户手工登录

创建触发器禁止生产用户通过PLSQL/DEV、SQLPlus等工具手工登录到数据库
[code]CREATE OR REPLACE TRIGGER "SYS"."TR_LOGIN_PRODUCEEM"
AFTER logon ON DATABASE
DECLARE
CURSOR cSession IS
select username,
sid,
Serial#,
OSUSER,
AUDSID,
ACTION,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
TERMINAL,
module,
PROGRAM
from v$session
where audsid = userenv('sessionid');

BEGIN

for sess in cSession

loop
if sess.USERNAME in ('USERNAME',
'USERNAME') then
if (upper(sess.PROGRAM) like 'PLSQLDEV%' or
upper(sess.PROGRAM) like 'SQLPLUS%') then
raise_application_error(-20003,
sess.USERNAME || ' from ' ||
SYS_CONTEXT('USERENV', 'IP_ADDRESS') ||
'拒绝登录');
end if;
end if;
end loop;
END TR_LOGIN_PRODUCEEM;

ALTER TRIGGER "SYS"."TR_LOGIN_PRODUCEEM" ENABLE;[/code]
另这个触发器对ADMINISTER DATABASE TRIGGER权限的用户无效
[code]SQL> SELECT grantee
2 FROM dba_sys_privs
3 WHERE PRIVILEGE = 'ADMINISTER DATABASE TRIGGER';

GRANTEE
------------------------------
DBA
IMP_FULL_DATABASE[/code]
即：除了DBA还有个IMP_FULL_DATABASE角色可以绕过trigger