Restricting the user from login (merged) [message #327819] |
Wed, 18 June 2008 02:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
I want to restrict the users from login depending on two parameters using Database logon trigger,
i.e
1. MODULE (like SQL*Plus...)
2. USERNAME
I can get USERNAME from
select SYS_CONTEXT('USERENV','CURRENT_USER') from dual
But, select SYS_CONTEXT('USERENV','MODULE') from dual says invalid parameter.
SQL> select SYS_CONTEXT('USERENV','CURRENT_USER') from dual
2 ;
SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------
OWB_TARGET
SQL> select SYS_CONTEXT('USERENV','MODULE') from dual ;
select SYS_CONTEXT('USERENV','MODULE') from dual
*
ERROR at line 1:
ORA-02003: invalid USERENV parameter
SQL>
Can anybody help me?
Ronald.
|
|
|
|
|
|
|
Restricting the user from login [message #327845 is a reply to message #327819] |
Wed, 18 June 2008 03:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
I have the following code snippet, I want to restrict the user from login with certain conditions,
SQL> l
1 CREATE OR REPLACE TRIGGER BOI_login_restrict AFTER LOGON ON DATABASE
2 DECLARE
3 mod_out VARCHAR2(48);
4 act_out VARCHAR2(32);
5 BEGIN
6 dbms_application_info.read_module(mod_out, act_out);
7 IF (SYS_CONTEXT('USERENV','CURRENT_SCHEMA') in ('OWB_TARGET','BOIDW')
8 and mod_out in ('SQL*Plus','T.O.A.D.')
9 and SYS_CONTEXT('USERENV','OS_USER') = 'nazaretr') THEN
10 RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database with this Schema!!!');
11 END IF;
12* END;
SQL>
The comparison values I've checked in v$session, and its correct but, still the user can login.
Any problems in the above code? I have created this trigger as user SYS.
Ronald.
|
|
|
|
Re: Restricting the user from login [message #327848 is a reply to message #327846] |
Wed, 18 June 2008 03:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi Michel,
In the above code
SYS_CONTEXT('USERENV','OS_USER') = 'nazaretr') is only for testing..
I want to restrict users connecting to schema OWB_TARGET and BOIDW from SQL*Plus and TOAD but they can connect through other Applications.
If there are other ways, your suggestions are most welcome.
Ronald.
|
|
|
|
|
|
Re: Restricting the user from login [message #327891 is a reply to message #327884] |
Wed, 18 June 2008 05:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Michel,
I understand using other tools like SQL Developer user can login..thats correct.
I mean in the above post, even after creating above trigger
os_user "nazaretr" can connect to schema "OWB_TARGET" throguh "T.O.A.D.". What was wrong ?
Regards,
Ronald
|
|
|
|