Skip to content

Archive for

Feb 28

2 Comments

Oracle sql92_security parameter

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.

Example :

 

SQL> create user employees identified by employees;

User created.

SQL> grant connect,resource to employees;

Grant succeeded.

SQL> conn employees/employees;
Connected.
SQL> create table empl_object as select * from all_objects;

Table created.

SQL> select count(*) from empl_object;

COUNT(*)
----------
55877

SQL> conn / as sysdba
Connected.
SQL> create user usera identified by usera;

User created.

SQL> grant connect to usera;

Grant succeeded.

SQL> create user userb identified by userb;

User created.

SQL> grant connect to userb;

Grant succeeded.

SQL> conn employees/employees;
Connected.
SQL> grant update,select on empl_object to usera;

Grant succeeded.

SQL> conn usera/usera
Connected.

SQL> update employees.empl_object set owner='test' where owner='SYS';

23687 rows updated.

SQL> COMMIT;

Commit complete.

SQL> conn userb/userb
Connected.
SQL> update employees.empl_object set owner='SYS' where owner='test';

23687 rows updated.

SQL> commit;

Commit complete.

Enable sql92_security parameter.

SQL> show parameter sql92_security;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql92_security boolean FALSE

SQL> alter system set sql92_security=TRUE scope=SPFILE;

System altered.

SQL> show parameter sql92_security;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql92_security boolean FALSE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2226912 bytes
Variable Size 687867168 bytes
Database Buffers 905969664 bytes
Redo Buffers 7348224 bytes
Database mounted.
Database opened.
SQL> show parameter sql92_security;

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

SQL> conn usera/usera
Connected.
SQL> update employees.empl_object set owner='test' where owner='SYS';

23687 rows updated.

SQL> commit;

Commit complete.

Now you can not update the table.
SQL> conn userb/userb
Connected.
SQL> update employees.empl_object set owner='SYS' where owner='test';
update employees.empl_object set owner='test' where owner='SYS'
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

Just read more detail from  This blog