OraFAQ Forum: Client Tools » Restricting User from login to DB

Home » Other » Client Tools » Restricting User from login to DB (Oracle 9i ,OS-UNIX HP)

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

Restricting User from login to DB [message #597137]

Tue, 01 October 2013 05:27

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Hi ,

I wanted to restrict the Users to login to DB using Application like TOAD or PL/SQl Developer .

But the users can login to DB using sql*plus.

We can get this info once user looged in to DB.

SQL> select SID,USER#,USERNAME,SERVER,MODULE from V$SESSION where USERNAME='LIVE4LEARN';
SID USER# USERNAME SERVER MODULE
1 24 18265 LIVE4LEARN DEDICATED SQL*Plus
2 30 18265 LIVE4LEARN DEDICATED PL/SQL Developer
4 37 18265 LIVE4LEARN DEDICATED PL/SQL Developer
5 40 18265 LIVE4LEARN DEDICATED PL/SQL Developer

Can we make this check done before login process and allow users to login when module not in ('TOAD','PL/SQL Developer
').

Do we have any method , like in user profile, where this could be added.

Thanks in advance.

Report message to a moderator

Re: Restricting User from login to DB [message #597141 is a reply to message #597137]

Tue, 01 October 2013 05:50

gazzag
Messages: 1114
Registered: November 2010
Location: Bedwas, UK

Senior Member

Google "After logon trigger".

HTH
-g

Report message to a moderator

Re: Restricting User from login to DB [message #597143 is a reply to message #597137]

Tue, 01 October 2013 05:55

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can use a database trigger on logon that checks the program name but anyway I can rename my sqlplus.exe as TOAD.exe or use a JDBC thin driver which allow me to send the properties I want when connecting.
In short, there is no reliable way to do it.

Regards
Michel

Report message to a moderator

Re: Restricting User from login to DB [message #597144 is a reply to message #597137]

Tue, 01 October 2013 05:56

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Have a look at this OTN thread on the same subject -
https://forums.oracle.com/thread/1025583?start=0&tstart=0

Regards,
Lalit

Report message to a moderator

Re: Restricting User from login to DB [message #597147 is a reply to message #597143]

Tue, 01 October 2013 06:02

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can have a look at SQL*Plus® User's Guide and Reference, chapter 9 SQL*Plus Security it may help you for some products.

Report message to a moderator

Re: Restricting User from login to DB [message #597321 is a reply to message #597147]

Thu, 03 October 2013 03:48

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Thanks Everyone .. I think "After logon trigger" may serve the purpose for me .Thanks Again.!!

Report message to a moderator

Re: Restricting User from login to DB [message #597322 is a reply to message #597141]

Thu, 03 October 2013 04:19

gazzag
Messages: 1114
Registered: November 2010
Location: Bedwas, UK

Senior Member

Of course. But assuming that this is merely a check to remind users not to log in with anything other than SQL*Plus, it's good enough. Renaming a restricted application at the O/S level to circumvent this check is at best in breach of your company's ICT Codes of Standards and at worse malicious hacking.

Report message to a moderator

Re: Restricting User from login to DB [message #597813 is a reply to message #597322]

Tue, 08 October 2013 09:39

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

One more help plz:)

I have many users , so I have created the trigger at Database level . For each database hit , this trigger will be fired .

For performance issue , I wanted to check for certain user , lets say 'coderunner' user , I don't want to perform all this checking.

I have various scripts where I am connecting to DB using 'coderunner' multiple times , what I want is instead of checking username not in ('coderunner') , check if current user is 'coderunner' exit immediately .

trigger body :

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN

SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND username not in ('coderunner','live4learn')
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;

--** Desired is ** --

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN

If current_user='coderunner' then
exit;
else

rest of the check

end if.

Thanks

Report message to a moderator

Re: Restricting User from login to DB [message #597815 is a reply to message #597813]

Tue, 08 October 2013 09:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You do not require permission from anyone here.
Please proceed to do as you desire without additional delay.

By the way, Oracle usernames default to be UPPERCASE; not like 'coderunner'

Report message to a moderator

Re: Restricting User from login to DB [message #597866 is a reply to message #597815]

Wed, 09 October 2013 00:15

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

I wanted to ask if any way we can check for current user who has just logged in without checking all the users who have logged in .

Report message to a moderator

Re: Restricting User from login to DB [message #597867 is a reply to message #597866]

Wed, 09 October 2013 00:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use USER() function

Report message to a moderator

Re: Restricting User from login to DB [message #597885 is a reply to message #597867]

Wed, 09 October 2013 02:14

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

•user()
•sys_context('USERENV', 'CURRENT_USER')
•sys_context('USERENV', 'SESSION_USER')

Is All three same or how it is different ?

Report message to a moderator

Re: Restricting User from login to DB [message #597888 is a reply to message #597885]

Wed, 09 October 2013 02:32

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just read the documentation:
USER returns the name of the session user (the user who logged on);
SESSION_USER: The name of the database user at logon.
CURRENT_USER: The name of the database user whose privileges are currently active.

SQL> create or replace procedure p is
  2  begin
  3    dbms_output.put_line('USER: '||user);
  4  dbms_output.put_line('SESSION_USER: '||sys_context('USERENV','SESSION_USER'));
  5    dbms_output.put_line('CURRENT_USER: '||sys_context('USERENV','CURRENT_USER'));
  6  end;
  7  /

Procedure created.

SQL> exec p
USER: MICHEL
SESSION_USER: MICHEL
CURRENT_USER: MICHEL

PL/SQL procedure successfully completed.

SQL> grant execute on p to scott;

Grant succeeded.

SQL> @sc
Connected.
SCOTT> exec michel.p
USER: SCOTT
SESSION_USER: SCOTT
CURRENT_USER: MICHEL

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Restricting User from login to DB [message #597896 is a reply to message #597888]

Wed, 09 October 2013 03:16

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Thank you so much Michel . I went through the document but It was not clear to me , now it is clear. Thanks!!

Report message to a moderator

Re: Restricting User from login to DB [message #597905 is a reply to message #597888]

Wed, 09 October 2013 04:08

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Nice Demo Michel, I'm going to steal it Smile

Smile

Report message to a moderator

Previous Topic:	unable to connect to database
Next Topic:	ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 26 07:02:43 CDT 2023