Re: Help required for system database trigger

From: <kishanthakker_at_gmail.com>
Date: 16 Apr 2007 03:22:00 -0700
Message-ID: <1176718920.350877.104150@e65g2000hsc.googlegroups.com>

Friends,

I have tried to create trigger as follows, but the problem is if the condition is true (i.e. if the value of module is like 'SQL%') I INTEND TO 'LOGOFF' from the oracle. But I am unable to achieve this task. Can anyone tell me where I am going wrong.

First I have created one function

CREATE OR REPLACE FUNCTION chk_login(sessn number) RETURN char IS prog varchar2(70);
BEGIN
   SELECT module INTO prog
     FROM v$session
    WHERE audsid = sessn
      and rownum<=1;
  if prog like 'SQL%' then

          return('Y');
  else

          return('N');
  end if;
END; then following code is added in trigger.

CREATE OR REPLACE TRIGGER "LOGIN_AUDIT_TRIGGER" AFTER LOGON ON DATABASE
DECLARE
sess number(10);
ans char(1) := 'Y';
UNAME VARCHAR2(25);
BEGIN
IF sys_context('USERENV','BG_JOB_ID') is null THEN

   sess := sys_context('USERENV','SESSIONID');    ans := CHK_LOGIN(sess);
   SELECT username INTO uname
     FROM v$session
    WHERE audsid = sess
      and rownum<=1;

   if ans = 'Y' then

      IF UNAME <> 'SYSTEM' THEN
         raise_application_error(-20001,'****** Unauthorised Login
*******',FALSE);
      END IF;

   end if;
end if;
END; please help me to overcome the problem.

Thanx in advance

Kishan Thakker Received on Mon Apr 16 2007 - 05:22:00 CDT