Re: Help required for system database trigger

kishanthakker_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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org