CODEWITHSUNDEEP
oracle-databaseplsql
Lock
Lock

Reputation: 5522

Insufficient privileges compiling a package that updates another users tables

I have an object in USER A that updates tables in USER B's schema. When the package is run, it is run in Oracle using USER B's login.

USER A does not have privileges to update the tables in USER B's schema. When I try to compile the package in USER A's schema, I get insufficient privileges error messages which stops it from compiling.

Is there a way to compile an object in USER A's schema, even though he doesn't have the privileges to update USER B's tables? If the package is run under the context of USER B, will the package correctly update the tables?

I don't want to put the package in USER B's schema.

Upvotes: 0

Views: 13748

Answers (1)

Justin Cave
Justin Cave

Reputation: 231761

You can. But it's probably not the best approach. The package would need to be declared as an invoker's rights package. And the UPDATE statement would need to use dynamic SQL.

Create the table in B

SQL> create table b.foo( col1 number );

Table created.

SQL> insert into b.foo values( 1 );

1 row created.

SQL> commit;

Commit complete.

Create the package in A. Note that the package is declared authid current_user which means that it relies on the privileges of the invoking user rather than the defining user. And since A cannot see the table, we use dynamic SQL so that the syntax checks are deferred to runtime

SQL> create package update_foo
  2    authid current_user
  3  as
  4    procedure set_val( p_new_val in number );
  5  end;
  6  /

Package created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace package body update_foo
  2  as
  3    procedure set_val( p_new_val in number )
  4    as
  5    begin
  6      execute immediate 'update b.foo set col1 = :new_val'
  7        using p_new_val;
  8    end;
  9* end;
SQL> /

Package body created.

SQL> grant execute on update_foo to b;

Grant succeeded.

Now, B can execute the package and modify the data

SQL> exec a.update_foo.set_val( 2 );

PL/SQL procedure successfully completed.

SQL> select * from foo;

      COL1
----------
         2

In general, however, this isn't a particularly sensible approach. In general, the whole point of having code in one schema and objects in another is to provide for a separation of duties and a separation of responsibilities. If you have to be logged in as a user that can issue an UPDATE against the table anyway, having a package to do it doesn't add much security. In this case, it's just adding complexity.

Upvotes: 2

Related Questions