Thursday, April 25, 2013

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

4 comments:

  1. Thanks for the explanation :) Got it ..!!

    ReplyDelete
  2. So, does this mean that the SQL_92_Security parameter should be configured to True? Or is it more secure when set to False?

    BR - Salma

    ReplyDelete
    Replies
    1. Hi Salma

      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

      https://docs.oracle.com/cd/B13789_01/server.101/b10755/initparams203.htm

      So it's depend on what you want

      Delete