How to prevent from using select for update
Luciano Morais, May 29, 2012 - 12:56 pm UTC
Hi Tom,
I have a very similar situation, I have created an special role with password, so as some developers could update data, complile or alter objects on the production database. It works this way: all of theses users have read access to the tables, but if they want to do any DML or DDL statement, they will have to issue "set role ... identified by xxx" before. It works fine, but it lets any of this users issue "select ... for update" even though the statemente "set role" has never been issued. Is there any way to prevent this?
May 30, 2012 - 12:53 am UTC
no, if they have select, they have select. for update is just a clause in a select.
What about packages?
Jon, August 24, 2016 - 2:37 pm UTC
I am supplying code to a client with Oracle EBS. My install scripts create objects in a custom schema and I do what I have done for almost 25 years as an APPS developer, I grant from APPS (in the old days from RA, SO, GL etc) to my custom schema. But in this install 2 grants are failing, specifically:
grant execute on FND_Global to {my schema} and
grant execute on FND_Profile to {my schema}
Both statements give "ORA-04020: deadlock detected while trying to lock object".
Since these are core APPS packages no one on the clients instance should be altering them.
The really strange thing is that this did not happen the first 3 or 4 times the client installed my code, on instances I have access to, but it has now occurred twice, on UAT and Production, and both times it was exactly the same two packages that raised the deadlock error.
Any idea what could be locking them?
Jon
August 24, 2016 - 4:38 pm UTC
Are the packages valid? Is it possible there's another session running DDL against the packages at the same time?
In any case you should have a tracefile when the deadlock happens. Look in the location the following query returns:
select * from v$diag_info
where name = 'Diag Trace'
This should give some clue to what's going on.
What about Packages - 2?
A reader, August 25, 2016 - 2:43 pm UTC
Sorry, can't figure out how to continue the thread.
Not really possible that the packages are invalid, these are core Oracle E-Business Suite packages on a production system, if they were invalid then all the applications would be failing.
Also I can think of no reason short of total insanity why anyone would want to touch FND_GLOBAL or FND_PROFILE they should not be altered by anything other than an Oracle E-Business Suite patch, and that should be very rare because these packages have been around since the 1990s and from the change history niether has altered since 2011 (I'm looking at EBS 12.1 change history, there may be 12.2 changes but I would expect both packages to be stable).
Bascially, changing these packages by any other method than an Oracle patch would not be supported by Oracle and would carry a high risk of damaging your E-Business Suite system. My client is one of the more conservative of Blue Chip companies with EBS there is just no way the DBAs would sanction any change to either of these packages.
So I'm at a complete loss. I have asked them to check the trace files, but I'm not sure that they will care enough to do that, as far as thier concerned the problem is now fixed. I would like to know for the next client, but I can't expect them to care about that.
August 25, 2016 - 4:54 pm UTC
OK. Unfortunately I'm not sure how to help you either! We're not EBS experts here...
If you can get the trace files that will help. Or if you can find a way to build a test case that works too :)
Otherwise you could try asking support.
Chris