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 thoughts on “SQL92_SECURITY Parameter

  1. 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

    Like

  2. Hi SalmaThe 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 statementshttps://docs.oracle.com/cd/B13789_01/server.101/b10755/initparams203.htmSo it's depend on what you want

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.