Home » RDBMS Server » Server Administration » Restricting the user from login (merged)
Restricting the user from login (merged) [message #327819] Wed, 18 June 2008 02:05 Go to next message
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.
Re: Retricting user login by MODULE and USERNAME [message #327826 is a reply to message #327819] Wed, 18 June 2008 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL>  select SYS_CONTEXT('USERENV','MODULE') from dual ;
SYS_CONTEXT('USERENV','MODULE')
----------------------------------------------------------
SQL*Plus

1 row selected.

Without version (with 4 decimals) what you post is useless.

Regards
Michel
Re: Retricting user login by MODULE and USERNAME [message #327831 is a reply to message #327826] Wed, 18 June 2008 02:42 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

Its "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production"

Ronald
Re: Retricting user login by MODULE and USERNAME [message #327838 is a reply to message #327831] Wed, 18 June 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 9.2 you have to use DBMS_APPLICATION_INFO.READ_MODULE procedure.

Regards
Michel
Re: Retricting user login by MODULE and USERNAME [message #327840 is a reply to message #327838] Wed, 18 June 2008 03:00 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Thanks Michel

Ronald
Restricting the user from login [message #327845 is a reply to message #327819] Wed, 18 June 2008 03:33 Go to previous messageGo to next message
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 #327846 is a reply to message #327845] Wed, 18 June 2008 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please don't start a new thread for the same topic.

Your code does not prevent from a hacker and it does not prevent from any other tool.
Actually, if your purpose is to prevent from user making SQL statements, this is not the good way to do it if the user really want to do it.

Regards
Michel
Re: Restricting the user from login [message #327848 is a reply to message #327846] Wed, 18 June 2008 03:46 Go to previous messageGo to next message
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 #327874 is a reply to message #327848] Wed, 18 June 2008 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, there are other ways: "Secure Application Role", "Proxy Authentication and Authorization" for instance (those are keywords you can search for in Oracle documentation).
Logon trigger is not the correct way as I said it can always been workaround.

Regards
Michel
Re: Restricting the user from login [message #327879 is a reply to message #327874] Wed, 18 June 2008 04:33 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Thanks Michel,

as a curiosity, what could be wrong in the above code?

Regards,
Ronald
Re: Restricting the user from login [message #327884 is a reply to message #327879] Wed, 18 June 2008 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Module can be spoofed.
And as I said, many other tools can do the same things than SQL*Plus or TOAD.

Regards
Michel
Re: Restricting the user from login [message #327891 is a reply to message #327884] Wed, 18 June 2008 05:04 Go to previous messageGo to next message
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
Re: Restricting the user from login [message #327898 is a reply to message #327891] Wed, 18 June 2008 05:53 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet TOAD set the module name AFTER connection and not during it, so you can't test this part in a logon trigger.

Regards
Michel
Previous Topic: how olda datafile copy to new 10g installed folder
Next Topic: 10g error ora-12514 tns:listener does not currently know of service requested in connect descriptor
Goto Forum:
  


Current Time: Sat Dec 10 03:33:34 CST 2016

Total time taken to generate the page: 0.11760 seconds