Search code examples
oracleoracle11goracle10goracle12c

Oracle - deploying objects into a different schema

I'm surprised I haven't been able to find this question on the site already. Apologies if it turns out to be a duplicate!

In Oracle (10 upwards) is it possible for USER_A to deploy objects in USER_B's schema?

For example, assuming I am logged in as USER_B:

CREATE OR REPLACE PACKAGE user_a.my_example_pkg IS

   PROCEDURE Make_Log;
   PROCEDURE Init;

END user_a.my_example_pkg;

I get an ORA-1031: insufficient privileges response when I deploy.

I know that it is possible to log in as USER_A to deploy the package, and yes I can do that. But the point is that on my database, someone appears to have modified a package "across the schema" in this way. And I need to figure out how they did it!!

I'm fairly sure that the privilege exists, but I can't find what it is. Moreover, if there are many privileges which allow this to be done, it would be a bonus to get an exhaustive list of what those privileges are.

Solution

  • You'd want to look for the ANY privileges

    • CREATE PROCEDURE lets you create procedures in your schema. CREATE ANY PROCEDURE lets you create procedures in any schema.
    • CREATE TABLE lets you create tables in your schema. CREATE ANY TABLE lets you create tables in any schema.
    • CREATE VIEW lets you create views in your schema. CREATE ANY VIEW lets you create views in any schema.

    For any of the CREATE privileges, there is a corresponding CREATE ANY privilege. Those ANY privileges are extremely powerful and really shouldn't be given to anyone other than a DBA since it would allow you to do things like create procedures owned by highly privileged users that can do anything a DBA could do.