I will be highly obliged if any one can help me on

Creating Database trigger for event of database login.

We have Oracle9i Enterprise Edition Release 9.2.0.4.0 -
ported on Redhat Linux ES rel. 4.

My SPECIFIC Requirement is

I want to trap the 'log in' event which is NOT initiated by
Oracle forms, Oracle reports and Oracle Plsql.

i.e. any 'login' other than these programs from windows98/windowsXP/
Windows2000 should be restricted.

I am unable to find any database function which returns this program
names.

some hint on the matter will really be helpful.

Thanx in Advance

Kishan Thakker

On 9 Apr 2007 04:22:31 -0700, "kishanthakker@gmail.com"
<kishanthakker@gmail.com> wrote:

>
>I will be highly obliged if any one can help me on
>
>Creating Database trigger for event of database login.
>
>We have Oracle9i Enterprise Edition Release 9.2.0.4.0 -
>ported on Redhat Linux ES rel. 4.
>
>My SPECIFIC Requirement is
>
>I want to trap the 'log in' event which is NOT initiated by
>Oracle forms, Oracle reports and Oracle Plsql.
>
>i.e. any 'login' other than these programs from windows98/windowsXP/
>Windows2000 should be restricted.
>
>I am unable to find any database function which returns this program
>names.
>
>some hint on the matter will really be helpful.
>
>Thanx in Advance
>
>Kishan Thakker


convert the code to a stored procedure, owned by system,
and just issue
select program
from v$session where sid in (select sid from v$mystat)
or better still get the session sid from the sys_context function, and
you are there.
I would assume sys_context is capable of getting the program column
from v$session, but I leave it to you to look up the sys_context
function in the sql reference manual.

--
Sybrand Bakker
Senior Oracle DBA

sybrandb@hccnet.nl schrieb am 09.04.2007 in
<v4ck13t8alv7fhmfj09ne0avlr0kp04rpq@4ax.com>:

> On 9 Apr 2007 04:22:31 -0700, "kishanthakker@gmail.com"
> <kishanthakker@gmail.com> wrote:

>>
>> I will be highly obliged if any one can help me on
>>
>> Creating Database trigger for event of database login.
>>
>> We have Oracle9i Enterprise Edition Release 9.2.0.4.0 -
>> ported on Redhat Linux ES rel. 4.
>>
>> My SPECIFIC Requirement is
>>
>> I want to trap the 'log in' event which is NOT initiated by
>> Oracle forms, Oracle reports and Oracle Plsql.
>>
>> i.e. any 'login' other than these programs from windows98/windowsXP/
>> Windows2000 should be restricted.
>>
>> I am unable to find any database function which returns this program
>> names.
>>
>> some hint on the matter will really be helpful.
>>
>> Thanx in Advance
>>
>> Kishan Thakker


> convert the code to a stored procedure, owned by system,
> and just issue
> select program
> from v$session where sid in (select sid from v$mystat)
> or better still get the session sid from the sys_context function, and
> you are there.
> I would assume sys_context is capable of getting the program column
> from v$session, but I leave it to you to look up the sys_context
> function in the sql reference manual.

Additional: If a user has the role "DBA" the trigger will not be
executed if user logs in. sys_context will help, but I do not know, what
grant is to give to users for this.

Andreas Mosmann


--
wenn email, dann AndreasMosmann <bei> web <punkt> de

On Apr 9, 5:29 pm, sybra...@hccnet.nl wrote:
> On 9 Apr 2007 04:22:31 -0700, "kishanthak...@gmail.com"
>
>
>
> <kishanthak...@gmail.com> wrote:
>
> >I will be highly obliged if any one can help me on
>
> >Creating Database trigger for event of database login.
>
> >We have Oracle9i Enterprise Edition Release 9.2.0.4.0 -
> >ported on Redhat Linux ES rel. 4.
>
> >My SPECIFIC Requirement is
>
> >I want to trap the 'log in' event which is NOT initiated by
> >Oracle forms, Oracle reports and Oracle Plsql.
>
> >i.e. any 'login' other than these programs from windows98/windowsXP/
> >Windows2000 should be restricted.
>
> >I am unable to find any database function which returns this program
> >names.
>
> >some hint on the matter will really be helpful.
>
> >Thanx in Advance
>
> >Kishan Thakker
>
> convert the code to a stored procedure, owned by system,
> and just issue
> select program
> from v$session where sid in (select sid from v$mystat)
> or better still get the session sid from the sys_context function, and
> you are there.
> I would assume sys_context is capable of getting the program column
> from v$session, but I leave it to you to look up the sys_context
> function in the sql reference manual.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Dear Sybrand,

You are absolutely right. V$SESSION is not showing any thing in
'PROGRAM' column.
Instead 'MODULE' column is showing some details like 'T.O.A.D.' in
case I 'log in' from TOAD.
but is not showing any name if I 'log in' from Form or Reports.

any suggestion ?

regards

Kishan Thakker

kishanthakker@gmail.com schrieb am 10.04.2007 in
<1176201471.967815.242080@y5g2000hsa.googlegroups. com>:

> On Apr 9, 5:29 pm, sybra...@hccnet.nl wrote:
>> On 9 Apr 2007 04:22:31 -0700, "kishanthak...@gmail.com"
> Dear Sybrand,

> You are absolutely right. V$SESSION is not showing any thing in
> 'PROGRAM' column.
> Instead 'MODULE' column is showing some details like 'T.O.A.D.' in
> case I 'log in' from TOAD.
> but is not showing any name if I 'log in' from Form or Reports.

> any suggestion ?
As far as I remember you can ask for a special user_defined sys_context
and you can set this too.
So you could set a special SYS_CONTEXT in your application. All others
will not do this and you can blog them in your trigger by
begin
SELECT sys_context('USERENV','YOUR_SPECIAL_CONTEXT') INTO TestIt FROM
DUAL;
IF not TestIt='MyApplication' THEN
-- rais Exception, log it, whatever you want
END IF
END


Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

On 10 Apr 2007 03:37:52 -0700, "kishanthakker@gmail.com"
<kishanthakker@gmail.com> wrote:

>On Apr 9, 5:29 pm, sybra...@hccnet.nl wrote:
>> On 9 Apr 2007 04:22:31 -0700, "kishanthak...@gmail.com"
>>
>>
>>
>> <kishanthak...@gmail.com> wrote:
>>
>> >I will be highly obliged if any one can help me on
>>
>> >Creating Database trigger for event of database login.
>>
>> >We have Oracle9i Enterprise Edition Release 9.2.0.4.0 -
>> >ported on Redhat Linux ES rel. 4.
>>
>> >My SPECIFIC Requirement is
>>
>> >I want to trap the 'log in' event which is NOT initiated by
>> >Oracle forms, Oracle reports and Oracle Plsql.
>>
>> >i.e. any 'login' other than these programs from windows98/windowsXP/
>> >Windows2000 should be restricted.
>>
>> >I am unable to find any database function which returns this program
>> >names.
>>
>> >some hint on the matter will really be helpful.
>>
>> >Thanx in Advance
>>
>> >Kishan Thakker
>>
>> convert the code to a stored procedure, owned by system,
>> and just issue
>> select program
>> from v$session where sid in (select sid from v$mystat)
>> or better still get the session sid from the sys_context function, and
>> you are there.
>> I would assume sys_context is capable of getting the program column
>> from v$session, but I leave it to you to look up the sys_context
>> function in the sql reference manual.
>>
>> --
>> Sybrand Bakker
>> Senior Oracle DBA
>
>Dear Sybrand,
>
>You are absolutely right. V$SESSION is not showing any thing in
>'PROGRAM' column.
>Instead 'MODULE' column is showing some details like 'T.O.A.D.' in
>case I 'log in' from TOAD.
>but is not showing any name if I 'log in' from Form or Reports.
>
>any suggestion ?
>
>regards
>
>Kishan Thakker

Modern versions of Developer run from an application server.
You could simply set up sqlnet.ora on the server with tcp.valid_nodes
or set up connection manager to do the same.
In both cases you don't need a trigger anymore.

--

On Apr 10, 8:37 pm, Andreas Mosmann <mosm...@expires-30-04-2007.news-
group.org> wrote:
> kishanthak...@gmail.com schrieb am 10.04.2007 in
> <1176201471.967815.242...@y5g2000hsa.googlegroups. com>:
>
> > On Apr 9, 5:29 pm, sybra...@hccnet.nl wrote:
> >> On 9 Apr 2007 04:22:31 -0700, "kishanthak...@gmail.com"
> > Dear Sybrand,
> > You are absolutely right. V$SESSION is not showing any thing in
> > 'PROGRAM' column.
> > Instead 'MODULE' column is showing some details like 'T.O.A.D.' in
> > case I 'log in' from TOAD.
> > but is not showing any name if I 'log in' from Form or Reports.
> > any suggestion ?
>
> As far as I remember you can ask for a special user_defined sys_context
> and you can set this too.
> So you could set a special SYS_CONTEXT in your application. All others
> will not do this and you can blog them in your trigger by
> begin
> SELECT sys_context('USERENV','YOUR_SPECIAL_CONTEXT') INTO TestIt FROM
> DUAL;
> IF not TestIt='MyApplication' THEN
> -- rais Exception, log it, whatever you want
> END IF
> END
>
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

It seems this approach is more practical. by setting sys_context it I
can restrict the 'log on' from applications other than forms & reports
then my problem will be solved.

Thanx Andreas, I may bother you again If I dont get any help on
sys_context setting.

regards

Kishan Thakker

On Apr 10, 11:05 pm, sybra...@hccnet.nl wrote:
> On 10 Apr 2007 03:37:52 -0700, "kishanthak...@gmail.com"
>
>
>
> <kishanthak...@gmail.com> wrote:
> >On Apr 9, 5:29 pm, sybra...@hccnet.nl wrote:
> >> On 9 Apr 2007 04:22:31 -0700, "kishanthak...@gmail.com"
>
> >> <kishanthak...@gmail.com> wrote:
>
> >> >I will be highly obliged if any one can help me on
>
> >> >Creating Database trigger for event of database login.
>
> >> >We have Oracle9i Enterprise Edition Release 9.2.0.4.0 -
> >> >ported on Redhat Linux ES rel. 4.
>
> >> >My SPECIFIC Requirement is
>
> >> >I want to trap the 'log in' event which is NOT initiated by
> >> >Oracle forms, Oracle reports and Oracle Plsql.
>
> >> >i.e. any 'login' other than these programs from windows98/windowsXP/
> >> >Windows2000 should be restricted.
>
> >> >I am unable to find any database function which returns this program
> >> >names.
>
> >> >some hint on the matter will really be helpful.
>
> >> >Thanx in Advance
>
> >> >Kishan Thakker
>
> >> convert the code to a stored procedure, owned by system,
> >> and just issue
> >> select program
> >> from v$session where sid in (select sid from v$mystat)
> >> or better still get the session sid from the sys_context function, and
> >> you are there.
> >> I would assume sys_context is capable of getting the program column
> >> from v$session, but I leave it to you to look up the sys_context
> >> function in the sql reference manual.
>
> >> --
> >> Sybrand Bakker
> >> Senior Oracle DBA
>
> >Dear Sybrand,
>
> >You are absolutely right. V$SESSION is not showing any thing in
> >'PROGRAM' column.
> >Instead 'MODULE' column is showing some details like 'T.O.A.D.' in
> >case I 'log in' from TOAD.
> >but is not showing any name if I 'log in' from Form or Reports.
>
> >any suggestion ?
>
> >regards
>
> >Kishan Thakker
>
> Modern versions of Developer run from an application server.
> You could simply set up sqlnet.ora on the server with tcp.valid_nodes
> or set up connection manager to do the same.
> In both cases you don't need a trigger anymore.
>
> --

thanx for reply. to be more precise we are working in a environment
where middle tier application is not there. Its purely client server
environment. Hence dependency of trigger cannot be avoided.

any other solution if possible ?

Kishan Thakker

kishanthakker@gmail.com schrieb am 12.04.2007 in
<1176375601.970574.323390@o5g2000hsb.googlegroups. com>:

> Thanx Andreas, I may bother you again If I dont get any help on
> sys_context setting.
I never tried, so tell me, if it works. Look for
"CREATE CONTEXT" in "SQL REFERENCE" book.

> regards

> Kishan Thakker
HTH
Andreas Mosmann


--
wenn email, dann AndreasMosmann <bei> web <punkt> de

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