SQL92_SECURITY
Oracle Security Audit:
Q: How to improve security level for DML transactions?.
Ans: By setting the SQL92_SECURITY parameter we can improve the security level for DML transactions.
The configuration option SQL92_SECURITY specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values.
| Parameter | SQL92_SECURITY |
| Parameter type | BOOLEAN |
| Range of values | TRUE / FALSE |
Check video link for demo :
The SQL92 standards specify that security administrators, users must have SELECT privilege on a table when performing DML operations UPDATE or DELETE. It can control users transactions that reference table column values in a WHERE clause specified on the right side of the statement.
|
1 |
SQL> UPDATE <USERNAME.TABLE_NAME> SET <COLUMN>=<VALUE> WHERE <COLUMN> =<VALUE>; |
Values:
|
1 2 3 4 |
SQL> show parameter sql92_security NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ Sql92_security boolean FALSE |
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.
|
1 2 3 4 |
SQL> show parameter sql92_security NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ Sql92_security boolean TRUE |
TRUE
The 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.
ORACLE RECOMMENDATION:
The configuration option SQL92_SECURITY must be set to true.
|
1 |
SQL92_SECURITY = TRUE |
Check parameter value:
|
1 2 3 4 |
SQL> show parameter sql92 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean FALSE |
Create users:
|
1 2 3 4 5 |
SQL> grant connect, resource, unlimited tablespace to u1 identified by u1; Grant succeeded. SQL> grant connect, resource, unlimited tablespace to u2 identified by u2; Grant succeeded. |
Connect U1 user, create table and grant permissions to the u2 user.
|
1 2 3 4 5 |
SQL> conn u1/u1 Connected. SQL> create table t1 (sno number, name varchar2(10)); Table created. |
|
1 2 3 4 5 6 |
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------- ---------------- ---------- T1 TABLE 1 row selected. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> insert into t1 values (1,'a'); 1 row created. SQL> insert into t1 values (2,'b'); 1 row created. SQL> insert into t1 values (3,'c'); 1 row created. SQL> insert into t1 values (4,'d'); 1 row created. SQL> commit; Commit complete. |
|
1 2 3 4 5 6 7 8 9 |
SQL> select * from t1; SNO NAME ---------- ---------- 1 a 2 b 3 c 4 d 4 rows selected. |
|
1 2 |
SQL> grant update, delete on t1 to u2; Grant succeeded. |
Update and delete permissions are granted to the u2 user on the t1 table.
Now connect to the u2 user and try to perform the update and delete operations.
|
1 2 |
SQL> conn u2/u2 Connected. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> update u1.t1 set sno=2; 4 rows updated. SQL> commit; Commit complete. SQL> update u1.t1 set sno=3 where name='d'; 1 row updated. SQL> delete from u1.t1 where sno=3; 1 row deleted. SQL> commit; Commit complete. |
The user was able to do the update and delete operation without any issues.
Try to change the parameter value.
|
1 2 |
SQL> conn / as sysdba Connected. |
|
1 2 3 4 |
SQL> show parameter sql92 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean FALSE |
|
1 2 |
SQL> alter system set sql92_security=true scope=spfile; System altered. |
Restart the database.
|
1 2 3 4 |
SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. |
|
1 2 3 4 5 6 7 8 9 |
SQL> startup; ORACLE instance started. Total System Global Area 3707904000 bytes Fixed Size 2294936 bytes Variable Size 922749800 bytes Database Buffers 2768240640 bytes Redo Buffers 14618624 bytes Database mounted. Database opened. |
Check the parameter value
|
1 2 3 4 |
SQL> show parameter sql92 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean TRUE |
Now connect to the u2 user and try to perform the update and delete operations.
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> conn u2/u2 Connected. SQL> update u1.t1 set sno=4; 3 rows updated. SQL> update u1.t1 set sno=3 where name='d'; update u1.t1 set sno=3 where name='d' * ERROR at line 1: ORA-01031: insufficient privileges |
Because of insufficient privileges User unable to do update or delete operations after changed the parameter value.
Connect U1 user grant SELECT permissions to u2 user.
|
1 2 3 4 5 |
SQL> conn u1/u1 Connected. SQL> grant select on t1 to u2; Grant succeeded. |
Now connect to the u2 user and try to perform the update and delete operations.
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> conn u2/u2 Connected. SQL> update u1.t1 set sno=5; 3 rows updated. SQL> update u1.t1 set sno=3 where name='c'; 1 row updated. SQL> commit; Commit complete. |
The user was able to do the update and delete operation without any issues.
Loading...
