CODEWITHSUNDEEP
oracle-databaseplsql
SenPrimes
SenPrimes

Reputation: 11

ORA-01031: insufficient privileges -- package procedure

In oracle, when I execute my package, I get error :

ORA-01031: insufficient privileges

My users have dba admin role.

myPackage in the mySchema. But table different in schema.

I tried below script but not solve my problem.

grant execute on mySchema.myPackage to otherSchema;

Thanks for interest.

execute
mySchema.myPackage.disabledTableConstraint(otherSchema.table_Cons);

Upvotes: 0

Views: 7143

Answers (1)

eaolson
eaolson

Reputation: 15094

Privileges granted via roles don't really matter in PL/SQL. By default, packages execute with definer's rights, which means they execute with the privileges of the owning schema.

It looks like mySchema is trying to disable a constraint in otherSchema, so this is basically as if you were logged in as mySchema and trying to do:

alter table otherSchema.someTable disable constraint myconstraint;

It sounds like mySchema doesn't have privileges to do that to otherSchema. You can:

  1. Grant the ALTER TABLE privilege from otherSchema to mySchema. You'd have to do this on a table-by-table basis.
  2. Grant the ALTER ANY TABLE system privilege to mySchema. This may be dangerous.
  3. Make the procedure invoker's rights with the AUTHID CURRENT_USER clause. This will make the procedure run with the privileges of the schema calling it, in this case, otherSchema.

See: Managing Security for Definer's Rights and Invoker's Rights

Upvotes: 1

Related Questions