Pete Finnigan's Oracle Security Weblog

I saw Charles Hoopers post titled "Why Doesn’t This Trigger Work â€" No Developer Tools Allowed in the Database" via my Oracle blogs aggregator and read it with interest as its related to issues i come across with clients often. That is they ask me how to stop various groups of people in their organisations connecting to the database using various tools; sometimes this is developers, sometimes power users with Excel (as Charles showed) and many more examples.

I started to write this in a comment to Charles post but it was getting bigger and I also felt its a good subject to talk about so i decided to move it here to a blog post.

First, I realise the post is about posting code that doesnt work and people posting other peoples code but i cannot resist just talking about the actual problem highlighted; that is to stop people connecting to a database with certain tools, no matter what those tools are. There are two major issues:

1) You dont know what tools people may try and use to connect to your database
2) You dont know from where they will connect and with what user account

The problem of stopping them is clearly not easily solved. If you try and block Excel, SQL*Plus, TOAD or anything else its futile. The application name may change because the application vendor changes it, the name can be spoofed easily via Oracle provided API's or via a proxy to another name, the name can be changed by renaming the binary.

I discussed this issue a very long time ago (7 years ago to be exact) for SQL*Plus showing some protections and including use of a trigger which is not Franks code of course.:-) The article is here but the conclusion is of course that tools such as PUP tables, triggers et al are only any use against the casual hacker but not someone who has any modicum of determinism.

The issue of spoofing has been discussed here a few times in the past - Can application names be changed to spoof logon triggers? and Spoofing users and programs and presenting at OWASP has a good discussion and a set of links to other posts that cover spoofing at various levels. Basically spoofing is quite easy if you have some gumption to try it.

This all comes back to point 1 and point 2. If you doint know what applications people will use or even from where they will connect then a better approach (if it will work) is to use a white list approach. White list the people whoi are allowed to connect and from where; this should include applications, people, IP addresses etc. This is sometimes hard to do as most sites dont know currently who is connecting so need to investigate. Also DHCP throws a spanner into the works. Use a real firewall if possible, if not use a simple solution such as valid node checking.

Then limit the users who can connect by protecting password leakage. Limit the privileges users can have when they connect. Disable all default roles and have then turned on via secure application roles when connecting (bear in mind what I have said about spoofing!)

The problem is complex to solve and a trigger is not suitable as a complete solution; also enable audit or use an external solution such as Sentrigo Hedgehog. A good trick also is to have your applications generate a unique ID when connecting, this can make spoofing harder to acheive.