SQL92_SECURITY Parameter behavior on UPDATE or DELETE privileges

SQL92_SECURITY Parameter behavior on UPDATE or DELETE privileges

SQL92_SECURITY is used to specify that user must need to have SELECT Privileges for executing the UPDATE or DELETE statements on
WHERE clause column on reference objects/tables.
You can set this value in spfile and pfile. Changes effects after reboot the Oracle Database.
Default value is TRUE.

TRUE: means user must have SELECT privilege on a column to reference it in the WHERE clause of a DELETE or UPDATE statement,
on the right hand side of an assignment in the SET clause of an UPDATE statement.

FALSE: A user with DELETE privilege on the target table of a DELETE statement may reference any column of that target table in
the WHERE clause. A user with UPDATE privilege on the target table of an UPDATE statement may reference any column of that target in the
WHERE clause or on the right hand side of any assignment in the SET clause of the UPDATE statement.

Note: It need select privileges on column name which is used in where clause at SQL92_SECURITY=TRUE. Not complete table. Last scenario example will clear it.

Example
You can create two user and grant them privileges according to test the SQL92_SECURITY parameter in Oracle

1. Create two user with SYSDBA privileges and grant them permission.
— Sysdba user

-- Create users
SQL> create user test identified by test;
User created.
SQL> create user test1 identified by test1;
User created.

--Grant privileges
SQL> grant unlimited table-space to test;
Grant succeeded.
SQL> grant create session to test;
Grant succeeded.
SQL> grant create table to test;
Grant succeeded.
SQL> grant create session to test1;
Grant succeeded.

--Check parameter value
SQL> show parameter sql92_security

NAME             TYPE      VALUE
---------------- --------- -------
sql92_security   boolean   TRUE

2. Now login with TEST user and create a table, Insert data and grant update/delete to another created user.

-- TEST user
SQL> conn test/test@XEPDB1
Connected.
SQL> create table testing ( id number, name varchar2(100));
Table created.
SQL> insert into testing values (1,'RAM');
1 row created.
SQL> insert into testing values (2,'SHAM');
1 row created.
SQL> commit;
Commit complete.
SQL> grant update on test.testing to test1;
Grant succeeded.

3. Now login with another user “TEST1” and try Update command with where clause.

SQL> update test.testing set id=3 where id =2;
update test.testing set id=3 where id =2
*
ERROR at line 1:
ORA-01031: insufficient privileges

4. Now change parameter value and restart the database with sysdba user.

SQL> alter system set sql92_Security=false scope=spfile;
System altered.

-- Startup the DB
Shutdown immediate
Startup

5. Now check again with TEST1 user:

--- Update is worked.
SQL> update test.testing set id=5 where id=2;
1 row updated.
SQL> commit;
Commit complete.

Note:
Now last scenario if you does not use the where clause want to update complete table data then its work.
It not need select privileges, it worked on select privileges on where clause column. If you have one column having select privileges then it work in where clause.

--Check parameter value
SQL> show parameter sql92_security

NAME             TYPE      VALUE
---------------- --------- -------
sql92_security   boolean   TRUE

--Login with TEST1 user
SQL> update test.testing set id=2 where id=5;
update test.testing set id=2 where id=5
            *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> update test.testing set id=2;
2 rows updated.

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.