OraFAQ Forum: Client Tools » Block access to production databases using TOAD,SQLNAV

Home » Other » Client Tools » Block access to production databases using TOAD,SQLNAV (Oracle 9i Rel2, On AIX 5.3, client PC's Windows XP)

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

Block access to production databases using TOAD,SQLNAV [message #331745]

Sat, 05 July 2008 03:25

Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka

Junior Member

ratiperera@gmail.com

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;
/

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331746 is a reply to message #331745]

Sat, 05 July 2008 03:32

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331748 is a reply to message #331746]

Sat, 05 July 2008 03:45

Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka

Junior Member

ratiperera@gmail.com

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331749 is a reply to message #331745]

Sat, 05 July 2008 03:46

Frank
Messages: 7901
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.

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331751 is a reply to message #331745]

Sat, 05 July 2008 04:01

Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka

Junior Member

ratiperera@gmail.com

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331752 is a reply to message #331749]

Sat, 05 July 2008 04:02

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331754 is a reply to message #331748]

Sat, 05 July 2008 04:05

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331756 is a reply to message #331754]

Sat, 05 July 2008 04:32

Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka

Junior Member

ratiperera@gmail.com

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331761 is a reply to message #331756]

Sat, 05 July 2008 05:19

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331773 is a reply to message #331752]

Sat, 05 July 2008 08:30

Frank
Messages: 7901
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

BlackSwan
Messages: 26766
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!

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331783 is a reply to message #331779]

Sat, 05 July 2008 08:48

Frank
Messages: 7901
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.

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331786 is a reply to message #331783]

Sat, 05 July 2008 09:26

Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka

Junior Member

ratiperera@gmail.com

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331788 is a reply to message #331745]

Sat, 05 July 2008 09:37

Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka

Junior Member

ratiperera@gmail.com

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331793 is a reply to message #331786]

Sat, 05 July 2008 10:26

Frank
Messages: 7901
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.

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331796 is a reply to message #331745]

Sat, 05 July 2008 10:34

Thushantha
Messages: 7
Registered: July 2008
Location: Colombo, SriLanka

Junior Member

ratiperera@gmail.com

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

Report message to a moderator

Re: Block access to production databases using TOAD,SQLNAV [message #331993 is a reply to message #331793]

Mon, 07 July 2008 03:25

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.

Report message to a moderator

Previous Topic:	Unable to Connect
Next Topic:	Toad Pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 15:16:55 CST 2024