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.
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
Adityanath's Database & Cloud Blog 
Leave a reply to Aron Shurak Cancel reply