Hi all,
Please would someone clarify for me how the OF ... FOR UPDATE column works when joining tables or views.
Scenario:
In the DEV schema, I have created a table called APP_USER. The table is logically related to SYS.DBA_XS_USERS such that DEV.APP_USER.NAME = SYS.DBA_XS_USERS.NAME.
DEV user has READ privilege on SYS.DBA_XS_USERS specifically because I don't want to DEV will never neet to lock SYS.DBA_XS_USERS.NAME (principal of least privilege).
Within a stored procedure, I want to lock a row on DEV.APP_USER but retrieve the related information from SYS.DBA_XS_USERS at the same time without locking SYS.DBA_XS_USERS so I write a query like this:
SELECT A.NAME, A.ID, A.MODIFIED, B.START_DATE, B.END_DATE
FROM APP_USER A
LEFT OUTER JOIN DBA_XS_USERS B
ON A.NAME = B.NAME
FOR UPDATE OF A.MODIFIED
I thought this would work without attempting to lock SYS.DBA_XS_USERS because I have specified A.MODIFIED in the FOR UPDATE OF ... column portion of the clause but instead I get “Error(124,34): PL/SQL: ORA-01031: insufficient privileges”
If I grant SELECT on SYS.DBA_XS_USERS to DEV, the lock works.
Have I misunderstood the purpose of providing a column list in the FOR UPDATE OF … column list
Thanks in advance.
L
Platform: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0 (for Windows x64)
Tools: IDE SQL Developer Version 23.1.0.097