0

I'm trying to understand package permissions in Oracle.

If user A is granted EXECUTE on a package owned/created by user B, and that package makes various inserts and updates to a table owned by user B, does user A also require explicit permissions on the underlying objects?

Or does the granted EXECUTE privilege on the package owned by user B mean that user A can do everything without requiring permissions on the table owned by user B?

3

Yes and no

The EXECUTE permission only grants User A the ability to call (execute) the package. It has nothing to do with the permissions of the underlying objects.

What permissions User A needs depends on the value for the AUTHID line of the compiled package (and if CBAC is in effect).

Definer Rights

These Packages are those whose authorization line is AUTHID DEFINER (default)

The code only has the privileges of the Definer (user B)

User A does not need the underlying privileges.

Invoker Rights

These are packages/procedures/functions whose authorization line is AUTHID CURRENT_USER

The code only has the privileges of the Invoker (user A).

User A will need underlying permissions.

CBAC

CBAC === Code Based Access Control

CBAC is usually used in conjunction with Invoker's Rights code to enhance security.

Oracle 12c introduced the ability to grant a role to a package.

Under CBAC, the user has (temporarily) the privileges that were granted to the package through a role.

The code only has the privileges of the granted Role.

User A does not need the underlying privileges if and only if the appropriate privileges have been granted to the package being called.

This allows the developer to (temporarily) grant only the neccesary permissions needed to run the code to the user calling the code.

You can read more about CBAC here

note

This applies to Packages and standalone Functions and standalone Procedures

1
  • Thank you, so in my scenario, the package spec has been created with the following clause: authid CURRENT_USER - so my understanding is that the invoker will not have permissions on the underlying table without requiring explicit grants. If the package is recompiled with authid DEFINER, will the invoker then be able to execute the package and update/insert/select into the owner tables without triggering ORA-00942 errors?
    – Huskie69
    Nov 11 '21 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.