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
Categories: 12c, 19c, Administration, DB parameters, Feature, ORA errors, Oracle 18c, security
Adityanath's Oracle Blog 
I could not refrain from commenting. Perfectly written!
Its like you read my mind! You appear to know a lot about this, like
you wrote the book in it or something. I think that you can do
with a few pics to drive the message home a little bit, but other than that,
this is magnificent blog. A great read. I’ll definitely be back.
Hi there! Someone in my Myspace group shared this website with us so I came to check it out. I’m definitely loving the information. I’m book-marking and will be tweeting this to my followers! Terrific blog and superb design.
Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your webpage? My website is in the exact same area of interest as yours and my visitors would really benefit from a lot of the information you present here. Please let me know if this ok with you. Many thanks!
Good day! I could have sworn I’ve been to this blog before but after browsing through some of the post I realized it’s new to me. Anyhow, I’m definitely delighted I found it and I’ll be book-marking and checking back frequently!
Hi there! Do you use Twitter? I’d like to follow you if that would be okay. I’m undoubtedly enjoying your blog and look forward to new posts.
There is definately a lot to learn about this issue.
I like all of the points you’ve made.
I love your blog.. very nice colors & theme. Did you create this website yourself or did you hire someone to do it for you? Plz respond as I’m looking to construct my own blog and would like to find out where u got this from. thanks
Appreciating the time and energy you put into your site and in depth information you provide. It’s good to come across a blog every once in a while that isn’t the same unwanted rehashed material. Fantastic read! I’ve bookmarked your site and I’m including your RSS feeds to my Google account.