Pete Finnigan's Oracle Security Weblog

I did a very successful pair of webinars for Sentrigo earlier this month on the 9th and 11th of March on the subject of an "Oracle Security Master Class". The USA webinar was recorded by Sentrigo and is available from this link if you would like to hear me speak on the subject of Oracle security.

Also Laszlo emailed me to say he has translated his Hactivity presentation on TNS attacks such as protocol downgrades on the Java thin client and also TNS session hijacks. This is great material. The presentation itself is here and is a 67 page pdf file. Laszlo also has released his pytnsproxy code here and also the module for Squirtle. There are also three demo videos, how to hijack an Oracle session, how to downgrade an 11g authentication when a Java thin client is used and finally a demo on how to use the squirtle module. Nice work from Laszlo as usual.

I did two webinars this week with Sentrigo titled "The right way to Secure Oracle", these went well. The slides for the talks have been added to my Oracle Security white papers page.

I have also written a short article about the Sentrigo HedgeHog Enterprise Database activity monitoring and Intrusion prevention product. The article is around 30 pages long and covers install, why I like the product and also how to use the built in vPatch rules and also how to set up custom rules. The article includes plenty of examples and also shows examples of testing the rules with exploits. The paper is in pdf only and is available from my Oracle Security white papers page, please have a look!

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.

It has been quite a while since my last blog post; i keep promising to post more often and even worse I have a long list of things to blog about but I don't seem to get enough time recently, so I should probably stop promising. I have spent hardly any time in my office over the last three to four weeks having been out teaching my class "How to perform a security audit of an Oracle database" on public trainings and also on quite a lot of private trainings for clients - (I am going to be in Bratislava next week teaching the next public class) and some more public dates are going to added to my calendar very soon - watch out for those.

We have also just added the dates for the class in Denver Collorado in June. I have also spent quite a lot of time on short consulting engagements around Oracle security reviews, audits and also recently quite a lot of design reviews and design work - again all around Oracle security. Things have been quite mad for me which is a pity for you guys as I have not had time to blog.....

Tomorrow 9th March at 10am UK time and also this coming Thursday, 11th March at 1pm EST (6pm UK time) I am giving a webinar hosted by Sentrigo. The talk is focused on the The right way to secure your data in an Oracle database; this is about starting at the right place and concentrating on the access models implemented in your database, relating these models to real people or job titles or processes and then to the actual data (all of it). If you would like to hear me talk about the methodologies I use to secure Oracle (and hear my voice if you are over the pond, as I don't speak in the US/Canada very often) or even ask me a question via the Q&A at the end then you can register at these two URL's:

Tuesday, March 9 10-11am GMT (England Time)
Thursday, March 11 01-02pm EST (NA Eastern Time)

On the subject of Sentrigo, I am also writing a short article about using Sentrigo's Hedgehog IDS/IPS product that will follow on from the detailed article i did in 2008 - "Sentrigo Hedgehog". This article is going to cover some example uses of the product. Watch out for it; coming soon!

I also came across Simon Fletchers blog a week or so ago whilst on the train when i was looking for something in google for a client and then today I noticed that Paul also mentioned Simons blog in his. Simon has started a blog about Oracle security called "Fifteen Twenty One". I have of course added his blog feed to my Oracle blogs aggregator. There are not many posts there yet (3 as i write this) but what is there is good so its going to be worth following.

I actually found one of his blog posts when searching but went for a look and found that his most recent post is about something I was dicussing recently with a client. This discussion was about the issue of the SQL92_SECURITY parameter and why it doesn't seem to work in some circumstances. OK, doesnt seem to work is unfair, it is not a bug but there is a couple of issues that you should be aware of if you think of using it.

I have been checking this parameter for many years and wrote a script in 2003 that tests for tables that have delete, update privileges and no select privilege so that I could advise clients to change this parameter as part of our security audits. I found that if you have sql92_security set to TRUE

SQL> sho parameter sql92 NAME TYPE VALUE ------------------------------------ ----------- -------- sql92_security boolean TRUE SQL>

Then if I have a table that has just DELETE privilege granted to it as follows:

SQL> @who_can_access who_can_access: Release 1.0.3.0.0 - Production on Mon Mar 08 13:23:02 2010 Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. NAME OF OBJECT TO CHECK [USER_OBJECTS]: CB1 OWNER OF THE OBJECT TO CHECK [USER]: ORABLOG OUTPUT METHOD Screen/File [S]: S FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: EXCLUDE CERTAIN USERS [N]: USER TO SKIP [TEST%]: Checking object => ORABLOG.CB1 ==================================================================== Object type is => VIEW (TAB) Privilege => DELETE is granted to => User => SQL92 (ADM = NO) PL/SQL procedure successfully completed. For updates please visit /tools.htm SQL>

As you can see the user SQL92 can DELETE but nothing else; then i cannot DELETE:

SQL> sho user USER is "SQL92" SQL> delete from orablog.cb1 2 where name_on_card like '%Pete%'; delete from orablog.cb1 * ERROR at line 1: ORA-01031: insufficient privileges SQL>

OK, so sql92_security does what it says on the tin but it does now stop DELETE grants from totally working, i.e. I can still delete but not specific records:

SQL> delete from orablog.cb1; 6 rows deleted. SQL> rollback; Rollback complete. SQL>

OK, if we now look at all the details for the sql92_security parameter via my check_parameter.sql is a one way to do it:

SQL> @check_parameter check_parameter: Release 1.0.2.0.0 - Production on Mon Mar 08 13:28:43 2010 Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved. PARAMETER TO CHECK [utl_file_dir]: sql92_security CORRECT VALUE [null]: TRUE OUTPUT METHOD Screen/File [S]: S FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: Investigating parameter => sql92_security ==================================================================== Name : sql92_security Value : TRUE Type : BOOLEAN Is Default : ***SPECIFIED IN INIT.ORA Is Session modifiable : FALSE Is System modifiable : FALSE Is Modified : FALSE Is Adjusted : FALSE Description : require select privilege for searched update/delete Update Comment : ------------------------------------------------------------------------- value is correct PL/SQL procedure successfully completed. For updates please visit /tools.htm SQL>

OK, so Oracle explains this in the comment, SELECT privileges are required only for searched update or delete. So being able to delete all rows is allowed. What Simon showed in his blog post to locate records via inference is not possible (which is the intended use of this parameter) but deleteing is not totally stopped. You should be aware of this.

A more subtle issue is the fact that sql92_security prohibits a DELETE or UPDATE from working if there is no read privileges .... what if there is a read privilege but via a different route....

SQL> @get_data get_data: Release 1.0.0.0.0 - Production on Mon Mar 08 13:48:30 2010 Copyright (c) 2004,2010, PeteFinnigan.com Limited. All rights reserved. OBJECT TO CHECK [XXX_XXXX]: CREDIT_CARD SCHEMA/OWNER OF THE OBJECT TO CHECK [USER]: ORABLOG OUTPUT MODE [All,Equal] [E]: E OUTPUT METHOD Screen/File [S]: S FILE NAME FOR OUTPUT [priv.lst]: OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]: Access to object, copies and children [ORABLOG.CREDIT_CARD] =================================================================== Tables to analyse [IMPORTER.C23] ==> IMPORTER.C45 --------------------------------------------- Tables to analyse [ORABLOG.CREDIT_CARD] ==> ORABLOG.CB1 ORABLOG.CC1 ORABLOG.C67 ORABLOG.CC87 ORABLOG.CCNAME --------------------------------------------- Main Table [IMPORTER.C23] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - IMPORTER BD X Child [IMPORTER.C45] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - IMPORTER BD X -------------------------------------------------------------- Main Table [ORABLOG.CREDIT_CARD] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - ORABLOG BA X ORABLOG IMPORTER X ORABLOG SYSTEM X X X [A,D][ORABLOG_CREDIT] ORABLOG HH X X X [,D][ORABLOG_CREDIT] ORABLOG PUBLIC X Child [ORABLOG.CB1] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - ORABLOG SQL92 X Child [ORABLOG.CC1] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - ORABLOG BB X Child [ORABLOG.C67] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - Child [ORABLOG.CC87] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - Child [ORABLOG.CCNAME] GRANTOR GRANTEE S I U D A F D I R Q C E ------------- -------------- - - - - - - - - - - - - ORABLOG BC X X -------------------------------------------------------------- PL/SQL procedure successfully completed. For updates please visit /tools.htm SQL>

The above output shows that the user SQL92 has the DELETE privilege against the view CB1 but also inherits the PUBLIC SELECT against the base table CREDIT_CARD.

Would you (as a designer of the application) want DELETE to then work instead of being blocked? As it stands its clearly not a bug as the parameter stops a DELETE without a READ on the SAME VIEW or TABLE it should work this way in my view. But if you have enforced this sql92_security parameter to TRUE expecting that it will stop deletes with no select - then fine - but if you expect reverse logic then beware.

For my sample view CB1 I have read privileges on the table its a CTAS on, so i can read the data but not read via the view and i have only DELETE privileges on the view. If I wanted to turn on SQL92_SECURITY and I checked the privileges I would find CB1 has only DELETE and would think about adding SELECT privileges to this view (increasing the privilege count), I would not find that I can see the data via ORABLOG.CREDIT_CARD (the base table) simply by searching for tables/views that do not have SELECT. I guess what I am getting at is maybe the solution isn't (in this example) to grant SELECT to SQL92 on ORABLOG.CB1 but maybe to remove SELECT on ORABLOG.CREDIT_CARD as well or maybe to add DELETE to ORABLOG.CREDIT_CARD for SQL92 and remove it from CB1 - bearing in mind we must know which columns are exposed and also how the tables and views are used in the application....

Finally there is the subtle problem of potential privilege. When I do an audit I also check for potential privileges and in the case of suggesting to a client to turn on sql92_security we must be also aware of the potential for issues in the future by analysing who may grant privileges that may break because of this parameter.