Skip to content
Advertisements

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

Advertisements
2 Comments Post a comment
  1. Steve #

    Am I missing something?

    How would userb be able to ever UPDATE the EMPL_OBJECT table?

    I don’t see any table level grants to userb?

    February 29, 2012
  2. Kailash #

    Not sure how it worked for you,. You should get error here –

    SQL> conn userb/userb
    Connected.
    SQL> update employees.empl_object set owner=’SYS’ where owner=’test’;
    update employees.empl_object set owner=’SYS’ where owner=’test’
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    November 2, 2016

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: