Does a delete on a table with where condition requires select privileges along with delete on a table???

Dear Readers,

Trust you are doing well. Today I am writing about an interesting issue which we faced today.

The application team did complain about one of the job failure with error “ORA-01031: insufficient privileges” while running delete statement on one of the table:

Ofcourse first check I did was to make sure if the user has delete privileges on table to run the query successfully. I was surprised to see, user had both insert & delete permissions on table which was the requirement of the job.

We did thorough investigation, if the job is making connection to right database. Everything was right.

During investigation, I came across interesting parameter: SQL92_SECURITY

https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams244.htm

As per description, The SQL standard specifies 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.

In pre 12.2 database this parameter is set to FALSE by default. But from 12.2 its set to TRUE.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/initialization-parameter-changes-oracle-database-12c-r2.html#GUID-BBBA10C4-F00A-4A9A-95A8-CCD925E4041C

So it was expected behavior, it is required 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.

Issue was resolved after grating select privilege on affected table.

Hope u will find this post very useful.

Cheers

Regards,

Adityanath

1 reply

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