|
|
 |
 |
 |
 |
Help required for system database trigger
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, "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
-----------------------------------------------Reply-----------------------------------------------
sybra @hccnet.nl schrieb am 09.04.2007 in <v4ck13t8alv7fhmfj09ne0avlr0kp04 @4ax.com>:
> 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.
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
-----------------------------------------------Reply-----------------------------------------------
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
-----------------------------------------------Reply-----------------------------------------------
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
-----------------------------------------------Reply-----------------------------------------------
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. --
-----------------------------------------------Reply-----------------------------------------------
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
-----------------------------------------------Reply-----------------------------------------------
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
-----------------------------------------------Reply-----------------------------------------------
kishanthak @gmail.com schrieb am 12.04.2007 in <1176375601.970574.323 @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
-----------------------------------------------Reply-----------------------------------------------
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
-----------------------------------------------Reply-----------------------------------------------
kishanthak @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
click on Morgan's Library click on Wildcards -- Daniel A. Morgan University of Washington damor @x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org -----------------------------------------------Reply-----------------------------------------------
Is it just me or does this seem like a strange substitute for proper user/password management? -----------------------------------------------Reply-----------------------------------------------
On Apr 17, 3:03 pm, dombrooks <dombro @hotmail.com> wrote: > Is it just me or does this seem like a strange substitute for proper
> user/password management?
I think there is some mis-communication.... I intend to validate the NAME of the program module through which the
user is trying to connect oracle, not the USER name.
if user is trying to connect with 'TOAD' or 'SQL*Plus' then I intend
to REJECT the 'Log on' process. But if the user is loging thru 'Forms'
Or 'Reports' then the SAME user is a VALID user.
Kishan Thakker
-----------------------------------------------Reply-----------------------------------------------
On Apr 16, 8:14 pm, DA Morgan <damor @psoug.org> wrote:
> kishanthak @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@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.
-----------------------------------------------Reply-----------------------------------------------
kishanthak @gmail.com wrote: > Dear Daniel, > How 'Wild Card' solution will help to solve this problem?. I M unable
> to understand. Please Clarify.
I misunderstood your issue. See if this works on your system. conn / as sysdba
CREATE OR REPLACE TRIGGER kill_session
AFTER LOGON
ON DATABASE
DECLARE
cur_user dba_users.username%TYPE;
BEGIN
SELECT user
INTO cur_user
FROM dual;
IF cur_user = 'SCOTT' THEN
RAISE program_error;
END IF;
END kill_session;
/
conn scott/tiger
But before you do run this query:
col NAME format a30
col VALUE format a20
col DESCRIPTION format a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_system_trig_enabled';
You may well need:
ALTER SYSTEM SET "_system_trig_enabled" = TRUE SCOPE=BOTH;
--
Daniel A. Morgan
University of Washington
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc