A reader, September 30, 2020 - 7:12 pm UTC
Hi Chris!
Thank you for reply.
My scenario was:
User A owns procedure TEST_PROC
User B is granted execute priv on A.TEST_PROC
Now User A wants to revoke execute on A.TEST_PROC from user B, and this revoke was happening inside the same A.TEST_PROC
So the question was why a lock needed while revoking privs from others, and why would user A block himself into a deadlock?
PS
By the way, GRANTing privs worked that way. Why REVOKing is different?
Thank you
October 01, 2020 - 1:05 am UTC
My scenario was:
User A owns procedure TEST_PROC
User B is granted execute priv on A.TEST_PROC
not that you shared that with us in your original question.......
Grant is different because its additive, ie, *before* the grant is run we *know* that you have no privileges on the object, thus by definition, you know that the user concerned cannot be *running* it (because they dont have the privileges to do so).
Revoke doesn't get that luxury.
Why the locking at all? When if the proc contains 10 deletes in a row, and 3 of them are done when someone tries to do the revoke. What should we do? Keep 3 and stop the remaining 7 ? That's a data corruption. Cancel the running call and roll it back? Thats an application breakage. Wait until it finishes? What if it runs for an hour - you've basically now blocked a user from operating further because their security privs are in a state of flux. What if this proc is a function and revoking access to this proc means the user would no longer get access to views that call this function, or virtual columns that reference it etc...There's a LOT of complexity here which is why we lock.
Use DBMS_JOB to submit a job to run this asynchronously so that its not part of the routine itself.