Skip to Main Content
  • Questions
  • ORA-04020: deadlock detected while revoking a priv from a procedure

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 29, 2020 - 3:56 pm UTC

Last updated: October 01, 2020 - 1:05 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi Tom,

While trying to revoke execute privilege on a procedure from inside of the same procedure the deadlock occurs.
Could you please advise on this behavior:

create procedure test_proc 
as
begin
    execute immediate 'revoke execute on test_proc from some_user';
end;

exec test_proc;

Error starting at line : 7 in command -
BEGIN test_proc; END;
Error report -
ORA-04020: deadlock detected while trying to lock object TEST_PROC
ORA-06512: at "TEST_PROC", line 4
ORA-06512: at line 1
04020. 00000 -  "deadlock detected while trying to lock object %s%s%s%s%s"
*Cause:    While trying to lock a library object, a deadlock is detected.
*Action:   Retry the operation later.


Thank you!

and Chris said...

Think about it this way:

Imagine you're driving a car. While doing so, you decide to revoke driving privileges on this car for some drivers.

Bear in mind these drivers could be a group of people who have access (a role). So potentially you're removing your own driving permissions from the car you're driving!

Sounds risky, right?

It's similar with revoking access from the procedure. To avoid problems, you're limited in how you can change a procedure while within a process running that procedure.

If you look in v$session, while calling this procedure, you'll see the session blocking itself:

select sid, blocking_session
from   v$session 
where  username is not null
and    blocking_session is not null;

       SID BLOCKING_SESSION
---------- ----------------
       194              194

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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
Connor McDonald
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library