Oracle Security Audit: SQL92_SECURITY

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.

Values:

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.

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.

Check parameter value:

Create users:

Connect U1 user, create table and grant permissions to the u2 user.

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.

The user was able to do the update and delete operation without any issues.

Try to change the parameter value.

Restart the database.

Check the parameter value

Now connect to the u2 user and try to perform the update and delete operations.

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.

Now connect to the u2 user and try to perform the update and delete operations.

The user was able to do the update and delete operation without any issues.

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Add Comment