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'; commit; 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);
