OraFAQ Forum: SQL & PL/SQL » To Restricte User to Login in SQL*Plus.

Home » SQL & PL/SQL » SQL & PL/SQL » To Restricte User to Login in SQL*Plus.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To Restricte User to Login in SQL*Plus. [message #21323]

Fri, 26 July 2002 04:30

SAGGI
Messages: 3
Registered: February 2002

Junior Member

how can i Restricte the user or allow some user to login in SQL*Plus ?
I have try in Login.sql but it does not allow IF condition & also try for PlSql script but it give error.

Thank in adv.
saggi.

Report message to a moderator

Re: To Restricte User to Login in SQL*Plus. [message #21325 is a reply to message #21323]

Fri, 26 July 2002 04:55

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I beleive, there is no direct method to impliment this.
there are some workaround. Wish someone can give more EFFECTIVE solutions.
1. you can remove the execution privileges on sqlplus to all but the
   Oracle user.
2. you can impliment  protocol.ora file to limit the nodes that can
   connect to the database(stop the user logging in remotely).
3. and Remove SQLPLUS from their machine (user cannot log frm his
   machine).
4. alongwith , you can also make use of product_user_profile.
   IT WONT PREVENT CONNECTING, but will help to restrict the usage of
   specific ddl, host commands.

Report message to a moderator

Re: To Restricte User to Login in SQL*Plus. [message #21340 is a reply to message #21323]

Fri, 26 July 2002 10:35

andrew again
Messages: 2577
Registered: March 2000

Senior Member

sqlplus uses "module" in v$session to register itself when you connect. Although the user can call dbms_application_info.SET_MODULE(some_var) to overwrite the "sqlplus" label, that's probably quite unlikely. Anyway - querying v$session allows you to detect sqlplus connections that way. Don't try reading "program" as someone can just rename the executable. By monitoring connections (using a dbms_job) you can kill sessions you don't want connecting directly. Because it's a periodic job, you cant expect to kill the connections the moment they occur. In 8i you can use login triggers to kill unwanted sessions, but remember that the moment the connection is made, the "module" is still blank until the client s/w (sqlplus) sets it.

I believe 9i has multi tiered security to provide a watertight solution.

Don't focus on detecting sqlplus connections if your users are then just going to start using ODBC or something similar.

Be careful to consider sqlplus users connecting to another (allowable) database and then opening a DBlink to the db you are trying to control. I think the session (due to the dblink) in the db you are trying to control will show up as "sqlplus" even though the user connects directly only to the otrher DB.

select module, program, osuser from v$session;

Report message to a moderator

Re: To Restricte User to Login in SQL*Plus. [message #21347 is a reply to message #21323]

Fri, 26 July 2002 22:50

SAGGI
Messages: 3
Registered: February 2002

Junior Member

Well i find the solution from the site
asktom.oracle.com
and i think that it is usefull .
just add following line in your login.sql

whenever sqlerror exit
variable n number
begin select 1 into :n from dual where user='SCOTT'; end;
/

it will only allow SCOTT user to login.
it will not work by connect command.

Report message to a moderator

Previous Topic:	Re: Creating a user in a stored procedure - compile error
Next Topic:	Insert Images in a BLOB Datatype?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:12:39 CDT 2024