Oracle DBA – A lifelong learning experience

What does the SQL92_SECURITY parameter actually do?

Posted by John Hallas on November 23, 2009

Whilst looking at our Grid Control I noticed that we had a few policy violations in the security configuration stating that SQL92_SECURITY=FALSE. These were against some of our older databases as we set it to true on all new builds. I thought I knew what this parameter did but had a look around and determined the following :-

It stops anyone being able to update or delete rows from a table owned by another user if you were using a where clause and did not have select on that table. Fair enough I thought, it sounds like it is some form of ANSI 92 standard and it is all a bit meaningless. I could not see what the issue was but I was intrigued as to why it was an Oracle security recommendation.

I set up the following test case on both a 10G and 11G database where SQL92_SECURITY was set to TRUE. Very simple but I had no failures other than the select statement which had insufficient privileges.

create user usera identified by usera;
create user userb identified by userb;

grant create session,create table to usera;
grant create session to userb;
alter user usera quota unlimited on users;
alter user userb quota unlimited on users;
show parameter sql92_security
connect usera/usera
create table tab1 as select * from all_objects where rownum <101;
select count(*) from tab1;
grant update,delete on tab1 to userb;

connect userb/userb
update usera.tab1 set object_type = 'WAS PACK' where object_type='PACKAGE';
select distinct status from usera.tab1;
prompt fails as no select privilege on usera.tab1
prompt SQL92 set to true and yet update is allowed ??
prompt now let's try a delete
delete from usera.tab1 where rownum <21;

prompt delete with a where clause works as well

select count(*) from
prompt end of case study

So what is all that about, where was I going wrong and why were my updates and deletes allowed when they shouldn’t have been.

This parameter is meant to prevent you from deleting a table when you specify a condition based on table’s columns. Conditions that are not checking the values stored in the table are allowed(rownum < X, etc.)

In my testcase I deleted the table rows without a condition which read one of the the tables columns. If instead of executing delete from usera.tab1 where rownum =21 I had executed delete from usera.tab1 where object_name = ‘TEST’; I would get an error.

The purpose of this parameter is to allow a user one to delete table data without giving them the possibility of guessing what values are stored in that table. Imagine that there is a table with contacts and I only have delete rights on that table. If I am able to delete the table with a condition based on table columns I can find out via multiple attempts the contents of the table. I can for example find out whether ‘John Smith’ is a contact or not :

SQL> delete from contacts where contact_first_name='John' and contact_family_name='Smith'; 

1 row deleted
SQL> rollback;
Rollback complete.

Since the row was deleted I know now that John Smith is a contact. Then I do a rollback of the transaction and the table is as it was before the delete. If the parameter is set to TRUE I cannot make use of this trick and I can only delete the rows blindly. I will be able to delete without knowing what I am going to delete.

Once I understood what could be done I understood the reason for the init.ora parameter to be enabled. However there is very little information out on the web which explains the reasons and that is why I think the parameter can be misunderstood.

Prior to enabling it on a database that is in use I would check that nobody had update or delete privileges ona table that they did not own where they did not have select privilege.

select *
from dba_tab_privs a
where privilege in ('UPDATE','DELETE')
and not exists (select null 
                from   dba_tab_privs b
                where  privilege = 'SELECT'
                and    a.grantor = b.grantor
                and    a.grantee = b.grantee
                and    a.table_name = b.table_name);

5 Responses to “What does the SQL92_SECURITY parameter actually do?”

  1. “If I am able to delete the table with a condition based on table columns I can find out via multiple attempts the contents of the table.”
    This is simply explaining all logic here. Thanks for the post John, helped me a lot.

  2. […] 6-How does SQL92_Security parameter works? John Hallas-What does the SQL92_SECURITY parameter actually do? […]

  3. […] read more detail from  This blog Share this:FacebookTwitterEmailPrintLike this:LikeBe the first to like this […]

  4. Alan Covell said

    Best explanation I’ve seen, with a clean, clear example and straightforward text that does not require an extensive knowledge of Oracle to understand. Please write a book on Oracle procedures “For the rest of us.”


    Alan C., DBA in training

  5. Peter said

    This is the best explanation of this setting on the internet by a long way, thanks

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: