SQL92_SECURITY Parameter

SQL> show parameter sql92_

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sql92_security                       boolean     FALSE

SQL92_Security seems it’s parameter related to Security But what is it ? what is the benefits of using this parameter ?

I will describe this example since it will be understandable 🙂

SQL> create user test1 identified by test1 ;

User created.

SQL> grant create session , Create table to test1 ;

Grant succeeded.

SQL>
SQL>
SQL> create user test2 identified by test2 ;

User created.

 SQL> create user test3 identified by test3 ;

User created.

SQL> grant create session to test3 ;

Grant succeeded.

SQL> conn test1/test1 ;
Connected.
SQL> create table new ( id number(20)) ;

Table created.

SQL> BEGIN
    FOR v_LoopCounter IN 1..50 LOOP
    INSERT INTO new (id)
    VALUES (v_LoopCounter);
     END LOOP;
         END;
   / 

PL/SQL procedure successfully completed.

SQL> conn test1/test1 ;
Connected.
SQL>
SQL>
SQL>
SQL> grant update on test1.new to test2 ;

Grant succeeded.

SQL> grant update on test1.new to test3;

Grant succeeded.

SQL> grant select on test1.new to test3;

 SQL> conn test2/test2 ;
Connected.
SQL> update test1.new set id=1 where id=10 ;

1 row updated.

SQL> conn test3/test3 ;
Connected.
SQL> update test1.new set id=10 where id=1 ;

2 rows updated.

Now let’s set SQL92_Security to TRUE and Restart Database and re fire the same query by each user.

Conn test2/test2

SQL>update test1.new set id=1 where id=10 ;

ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn test3/test3;
Connected.
SQL> update test1.new set id=10 where id=1 ;

2 rows updated.

What Happening Here , Each of users have Privileges on test1.new but why after set SQL92_Security to true test2 not working ?

Simply  SQL92_SECURITY need Update/delete and Select Privileges for the same user if you grant the user one of these privileges then it will be useless.

Amazing 🙂

Thank you
Osama Mustafa