Skip to Main Content
  • Questions
  • Grant Compile Packaged to Other User

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Farzad.

Asked: January 16, 2017 - 8:08 am UTC

Last updated: January 16, 2017 - 11:10 am UTC

Version: 11.2.0.2

Viewed 10K+ times! This question is

You Asked

Hi,

I have a package in schema A. I want schema B to be able to compile the code for just this package and not any other packages that schema A owns.

Can this be achieved?

and Chris said...

I take it by compile you mean "deploy a new version of the code"?

To recompile procedures in another schema you need the "alter any procedure" privilege. To compile a new version you need "create any procedure". These both allow you to create and alter all non-SYS procedures!

So the simple answer is no.

The complicated answer is you can by creating a procedure in schema A. This accepts the new code and executes it using dynamic SQL. Then grant schema B execute on this.

But this opens up a security hole. If the procedure in schema A accepts any code, user B can do whatever they want in schema A! You need very careful design to ensure B can only change the package you want.

Rating

  (1 rating)

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

Comments

Thanks

Farzad Soltani, January 17, 2017 - 5:39 am UTC

Hi Chris,

Thanks for your complete answer.

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