Pete Finnigan's Oracle Security Weblog

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.