Home » SQL & PL/SQL » Client Tools » Block access to production databases using TOAD,SQLNAV (Oracle 9i Rel2, On AIX 5.3, client PC's Windows XP)
Block access to production databases using TOAD,SQLNAV [message #331745] Sat, 05 July 2008 03:25 Go to next message
Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka
Junior Member


Dear Gurus,

I wanted to block the access of productin databases using TOAD,SQLNAV & other development tools. I found following script(trigger) to enable that task from web forum. I created this trigger in sys schema. But still users can loging to the databaseusing TOAD,SQLNAV...etc. Is there any other things to be done with this.?

Your attention on this regard will be highly appreciated

Thanks
Thushantha



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 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 on PROD DB!');
END IF;
END;
/
Re: Block access to production databases using TOAD,SQLNAV [message #331746 is a reply to message #331745] Sat, 05 July 2008 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can ALWAYS workaround ANY trigger.
The best way is to audit and fire all people that do not follow entreprise policy.
I bet this will have a better success rate.

Regards
Michel
Re: Block access to production databases using TOAD,SQLNAV [message #331748 is a reply to message #331746] Sat, 05 July 2008 03:45 Go to previous messageGo to next message
Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka
Junior Member

Hi Micheal,

I undestand, But still audit department is not satisfied with these access privileges, There are so many places these software has been installed. Presently there is no one to monitor the such audit log's even. What their suggetion is DBA has to control these things technically.

The trigger is created properly in SYS schema & it's ENABLED. what else do i have to check on this..

Thanks'N regards
Thushantha
Re: Block access to production databases using TOAD,SQLNAV [message #331749 is a reply to message #331745] Sat, 05 July 2008 03:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A better way of securing your data is by using application roles.
Don't grant privileges to users, grant the user an application role that is enabled only by the application itself.
It _is_ however possible to circumvent that, especially for people with access to the code, but it decreases the number of people who can access the data.

Since a lot of people are comfortable looking up data by means of sql, instead of the application, it might even be better to grant users read-only privs on the application-tables and -views by default, and let the app handle DML by means of stored procedures (or combine default read-only access with read-write application roles)

In general, if you block the way people are used to work, they will try to find ways around your block, so you will have to convince them that their way is not safe. For read-write access, this is easy to explain; for read-only it is close to paranoid.
Re: Block access to production databases using TOAD,SQLNAV [message #331751 is a reply to message #331745] Sat, 05 July 2008 04:01 Go to previous messageGo to next message
Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka
Junior Member

Hi

Thanks for your suggetions..

I completely undestand the situation. Even my suggetion is also the same. But here, there are difficulties of changing application user passwords, we are in the process of identifying each & every application users & preparaing password policy's for them. It takes some period of time hence there are lot's of systems & some dependencis from application developers side. Till we prepare smooth environment for this, as a precautionay action we are going to implement this.

Thanks'N Regards
Thushantha
Re: Block access to production databases using TOAD,SQLNAV [message #331752 is a reply to message #331749] Sat, 05 July 2008 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
for read-only it is close to paranoid.

You are not working in banking where information may mean million or even billion dollars.
You are not working in pharmacy or perfume industry where product component also worth billion dollars.
And so on.
Information is money.
Developer should never has access to production data.

Regards
Michel
Re: Block access to production databases using TOAD,SQLNAV [message #331754 is a reply to message #331748] Sat, 05 July 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What their suggetion is DBA has to control these things technically.

It is ALWAYS a bad decision to workaround organisational problem with technical solution.
It is more expensive and less efficient.

As already been said, whatever you do, I can always connect with TOAD, SQL*Plus and any other tool I want.

Regards
Michel
Re: Block access to production databases using TOAD,SQLNAV [message #331756 is a reply to message #331754] Sat, 05 July 2008 04:32 Go to previous messageGo to next message
Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka
Junior Member

Hi Micheal,

Thank you very much for all your suggetions, I'm sorry to say, I'm in working in industry which is similarly important as you given. We have policies kept in place as you mentioned. For application requierement sometime back all the PC's has been installed with sqlplus even. We have controlled/restricted posibilities of external software installation. While we are enfocing such security policies in place, we are trying to controle/minimize technical posibilities to secure the data.



Thanks'N regards
Thushantha
Re: Block access to production databases using TOAD,SQLNAV [message #331761 is a reply to message #331756] Sat, 05 July 2008 05:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As all gents have already said,
Securing the data is the best way, not the tools. Look into VPD.
With specific context policies, any user will have specific access only to specific data no matter what tool is been used.
Even if the said trigger works,
one can just rename toad.exe to something.exe and continue to work.
Re: Block access to production databases using TOAD,SQLNAV [message #331773 is a reply to message #331752] Sat, 05 July 2008 08:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Sat, 05 July 2008 11:02
Quote:
for read-only it is close to paranoid.

You are not working in banking where information may mean million or even billion dollars.
You are not working in pharmacy or perfume industry where product component also worth billion dollars.
And so on.
Information is money.
Developer should never has access to production data.

Regards
Michel


Of course I am talking about people that do have the same access to the data through the application.

And, by the way I DID work for a bank, AND I did work for a pharma-company.
And I have to say that neither relied on the "you cannot do that or we will fire you" security-mechanism

[Updated on: Sat, 05 July 2008 08:59]

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331779 is a reply to message #331745] Sat, 05 July 2008 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But here, there are difficulties of changing application user passwords

Application passwords are NOT the same as schema passwords!

Why do non-DBAs have passwords to ANY schema in the instance?

You have a procedural issue & NOT a technical one.

Simply publish a policy that any non-authorized access to the production data will be summarily terminated & dismissed!
Re: Block access to production databases using TOAD,SQLNAV [message #331783 is a reply to message #331779] Sat, 05 July 2008 08:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
anacedent wrote on Sat, 05 July 2008 15:41
>But here, there are difficulties of changing application user passwords

Application passwords are NOT the same as schema passwords!

Why do non-DBAs have passwords to ANY schema in the instance?


Not necessarily bad. Why build a fallable authentication system when you get quite a solid one for free?
That doesn't mean however that those db-accounts should be granted each and every priv.
Re: Block access to production databases using TOAD,SQLNAV [message #331786 is a reply to message #331783] Sat, 05 July 2008 09:26 Go to previous messageGo to next message
Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka
Junior Member

Hi, Sorry. I think you have mis undestand it. Application user ID's managed by the application it self. But I'm talking about the schema user of the application.,Which is having full rights to all the objects of application.

Thanks'N regards
Thushantha
Re: Block access to production databases using TOAD,SQLNAV [message #331788 is a reply to message #331745] Sat, 05 July 2008 09:37 Go to previous messageGo to next message
Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka
Junior Member

Hi All,

I agreed we can enforce a security policy, As human beings if somebody intrested of getting data from the database how do we control it. Oneway is having password with relevant complexity., & it's already in place. And you mean to say there is no way of controlling it technically..? ( The trigger i found may not work as you said..)

What i'm trying to say is, a person whose having simple knowlege even can retrieve our data if he get to know the password somehow. hence the necessary tools available in his client PC it self.

Thanks'N regards
Thushantha
Re: Block access to production databases using TOAD,SQLNAV [message #331793 is a reply to message #331786] Sat, 05 July 2008 10:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The schema owner should only be accessible by people that can be trusted.
No matter how you do this, there will always be people responsible for the maintenance of your database. If you cannot trust them, then you are out of luck.
Re: Block access to production databases using TOAD,SQLNAV [message #331796 is a reply to message #331745] Sat, 05 July 2008 10:34 Go to previous messageGo to next message
Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka
Junior Member

Hi,

Thanks for your support. I have one more question, practically it's not possible to own high level user ID(Schema user for app) a one person. So we may have to share it & it will not be regularly change hence changing password is effecting for 24*7 service. My concern is due to any reason audit pepoples won't allow you to share such high level user ID's. In top of that we are not in a situation where we can enable full audit on such user id's as they heavily used & enabling audit will result for performance degration/ storage issues.

Thanks'N regards
Thushantha
Re: Block access to production databases using TOAD,SQLNAV [message #331993 is a reply to message #331793] Mon, 07 July 2008 03:25 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can make a strong case for locking the account of the application schema owner to preventing anyone from connecting at it.

If you don't audit your DBA accounts as well then you have no reliable knowledge at all of what goes on in the DB.
Previous Topic: Unable to Connect
Next Topic: Toad Pl/sql
Goto Forum:
  


Current Time: Sat Dec 10 08:56:04 CST 2016

Total time taken to generate the page: 0.25849 seconds