Skip to Main Content
  • Questions

  • Revoking Execute Privilege On Package Within The Same

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Silvio.

Asked: November 03, 2015 - 1:09 pm UTC

Last updated: November 04, 2015 - 9:58 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello Tom and team

I just encountered a PL/SQL package which revokes execution rights to itself.

Like:
CREATE OR REPLACE PACKAGE BODY xyz
IS
  PROCEDURE abc
  IS
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON xyz FROM user1';
  END;
END;


The user executing this package is not "user1".
When testing this package, the procedure "abc" doesn't seem to come to an end.

I assume, the revoking of the privilege cannot run while the package is executed....
Or what is your opinion to this problem?

Thanks in advance
Silvio

and Chris said...

You're correct, you can't change the privileges on a package when someone is executing it.

Oracle pins the package in memory while executing it. The revoke needs to pin it as well to change the privileges.

This means you can't have a procedure revoke access from itself. The session running it holds the library cache lock. The revoke is then stuck waiting. Eventually the process will timeout.

Rating

  (1 rating)

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

Comments

Fast, friendly and detailed

Silvio Marghitola, November 10, 2015 - 6:43 am UTC

Thank you!
I didn't expect to get an answer that fast. Your statement was clear and comprehensible.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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