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: 39
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.
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: 824
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: 64098
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: 3122
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: 64098
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: 39
Registered: September 2013
Location: Bangalore, India
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: 824
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: 39
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
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: 25032
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'

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: 39
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 .
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: 25032
Registered: January 2009
Location: SoCal
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: 39
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 ?
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: 64098
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: 39
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!!
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: 2834
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: Fri Dec 02 12:33:24 CST 2016

Total time taken to generate the page: 0.12244 seconds