Home » SQL & PL/SQL » Client Tools » Restricting User from login to DB (Oracle 9i ,OS-UNIX HP)
Restricting User from login to DB [message #597137] Tue, 01 October 2013 05:27 Go to next message
live4learn
Messages: 11
Registered: September 2013
Location: Bangalore, India
Junior 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.
Re: Restricting User from login to DB [message #597141 is a reply to message #597137] Tue, 01 October 2013 05:50 Go to previous messageGo to next message
gazzag
Messages: 316
Registered: November 2010
Location: Bristol, UK
Senior Member
Google "After logon trigger".

HTH
-g
icon4.gif  Re: Restricting User from login to DB [message #597143 is a reply to message #597137] Tue, 01 October 2013 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
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
Re: Restricting User from login to DB [message #597144 is a reply to message #597137] Tue, 01 October 2013 05:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2324
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
icon3.gif  Re: Restricting User from login to DB [message #597147 is a reply to message #597143] Tue, 01 October 2013 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
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.


Re: Restricting User from login to DB [message #597321 is a reply to message #597147] Thu, 03 October 2013 03:48 Go to previous messageGo to next message
live4learn
Messages: 11
Registered: September 2013
Location: Bangalore, India
Junior Member
Thanks Everyone .. I think "After logon trigger" may serve the purpose for me .Thanks Again.!!

Re: Restricting User from login to DB [message #597322 is a reply to message #597141] Thu, 03 October 2013 04:19 Go to previous messageGo to next message
gazzag
Messages: 316
Registered: November 2010
Location: Bristol, 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.
Re: Restricting User from login to DB [message #597813 is a reply to message #597322] Tue, 08 October 2013 09:39 Go to previous messageGo to next message
live4learn
Messages: 11
Registered: September 2013
Location: Bangalore, India
Junior 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
Re: Restricting User from login to DB [message #597815 is a reply to message #597813] Tue, 08 October 2013 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
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'

Re: Restricting User from login to DB [message #597866 is a reply to message #597815] Wed, 09 October 2013 00:15 Go to previous messageGo to next message
live4learn
Messages: 11
Registered: September 2013
Location: Bangalore, India
Junior 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 .
Re: Restricting User from login to DB [message #597867 is a reply to message #597866] Wed, 09 October 2013 00:19 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
use USER() function
Re: Restricting User from login to DB [message #597885 is a reply to message #597867] Wed, 09 October 2013 02:14 Go to previous messageGo to next message
live4learn
Messages: 11
Registered: September 2013
Location: Bangalore, India
Junior Member
•user()
•sys_context('USERENV', 'CURRENT_USER')
•sys_context('USERENV', 'SESSION_USER')

Is All three same or how it is different ?
icon1.gif  Re: Restricting User from login to DB [message #597888 is a reply to message #597885] Wed, 09 October 2013 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
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.

Re: Restricting User from login to DB [message #597896 is a reply to message #597888] Wed, 09 October 2013 03:16 Go to previous messageGo to next message
live4learn
Messages: 11
Registered: September 2013
Location: Bangalore, India
Junior Member
Thank you so much Michel . I went through the document but It was not clear to me , now it is clear. Thanks!!
Re: Restricting User from login to DB [message #597905 is a reply to message #597888] Wed, 09 October 2013 04:08 Go to previous message
pablolee
Messages: 2617
Registered: May 2007
Location: Scotland
Senior Member
Nice Demo Michel, I'm going to steal it Smile
Previous Topic: unable to connect to database
Next Topic: ORA_ROWSCN and SCN_TO_TIMESTAMP - in Toad
Goto Forum:
  


Current Time: Wed Sep 17 22:41:42 CDT 2014

Total time taken to generate the page: 0.25826 seconds