Re: Help required for system database trigger

On Apr 16, 8:14 pm, DA Morgan <damor..._at_psoug.org> wrote:
> kishanthak..._at_gmail.com wrote:
> > 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
>
> www.psoug.org
> click on Morgan's Library
> click on Wildcards
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Dear Daniel,

How 'Wild Card' solution will help to solve this problem?. I M unable to understand. Please Clarify. Received on Tue Apr 17 2007 - 06:10:52 CDT