Impact on Packages - Assign permissions directly vs Assign permission via Roles

articles: 

Document depicts how Roles and Privileges behave for Packages

DB Version: 10.2.0.4

Users: TESTUSR (Table Owner)
TESTUSR_PKG (Package Onwer)
TESTUSR_APP (Application Owner)

Roles: TESTUSR_APP_ROLE
TESTUSR_PKG_ROLE

Table: TEST_TBL
Package: TEST_PKG

Table Text:
create table test_tbl(a number);

Package Text:
CREATE OR REPLACE PACKAGE test_pkg AS -- spec
PROCEDURE insert_test_tbl (
a NUMBER);
PROCEDURE delete_test_tbl (
a NUMBER);
END test_pkg;

CREATE OR REPLACE PACKAGE BODY test_pkg AS -- body
PROCEDURE insert_test_tbl (
a NUMBER) IS
BEGIN
INSERT INTO test_tbl VALUES (1);
END insert_test_tbl;

PROCEDURE delete_test_tbl (
a NUMBER) IS
BEGIN
delete from test_tbl where a=10;
END delete_test_tbl;
END test_pkg;

Case 1:
=======
Permission to Package Owner through Role (this doesn't work)

Users: TESTUSR (Table Owner)
TESTUSR_PKG (Package Onwer)

Role: TESTUSR_PKG_ROLE

SQL> show user
USER is "TESTUSR"
SQL> desc test_tbl
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A NUMBER

SQL>

Granted Permission on Table TEST_TBL to role.

SQL> select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs
2 where owner='TESTUSR';

GRANTEE OWNER TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
TESTUSR_PKG_ROLE TESTUSR TEST_TBL DELETE
TESTUSR_PKG_ROLE TESTUSR TEST_TBL INSERT
TESTUSR_PKG_ROLE TESTUSR TEST_TBL SELECT
TESTUSR_PKG_ROLE TESTUSR TEST_TBL UPDATE

SQL>

Granted Role TESTUSR_PKG_ROLE to TESTUSR_PKG

SQL> show user
USER is "TESTUSR_PKG"
SQL> desc test_pkg
PROCEDURE DELETE_TEST_TBL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN
PROCEDURE INSERT_TEST_TBL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN

SQL>

SQL> show user
USER is "TESTUSR_PKG"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
TESTUSR_PKG_ROLE

SQL>

Tried compiling the package:

SQL> alter package test_pkg compile;

Warning: Package altered with compilation errors.

SQL> alter package test_pkg compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY TEST_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/7 PL/SQL: SQL Statement ignored
5/19 PL/SQL: ORA-00942: table or view does not exist
11/7 PL/SQL: SQL Statement ignored
11/19 PL/SQL: ORA-00942: table or view does not exist
SQL>

Case 2:
=======
Permission directly to package owner (this works)

Users: TESTUSR (Table Owner)
TESTUSR_PKG (Package Onwer)

SQL> show user
USER is "TESTUSR"
SQL> desc test_tbl
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A NUMBER

SQL>

Granted Permission on Table TEST_TBL to user.

SQL> ;
1 select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs
2* where owner='TESTUSR'
SQL> /

GRANTEE OWNER TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
TESTUSR_PKG TESTUSR TEST_TBL DELETE
TESTUSR_PKG TESTUSR TEST_TBL INSERT
TESTUSR_PKG TESTUSR TEST_TBL SELECT
TESTUSR_PKG TESTUSR TEST_TBL UPDATE

SQL>

SQL> show user
USER is "TESTUSR_PKG"
SQL> desc test_pkg
PROCEDURE DELETE_TEST_TBL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN
PROCEDURE INSERT_TEST_TBL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN

SQL>

SQL> show user
USER is "TESTUSR_PKG"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

SQL>

Tried compiling the package:

SQL> desc test_pkg
PROCEDURE DELETE_TEST_TBL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN
PROCEDURE INSERT_TEST_TBL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN

SQL> alter package test_pkg compile;

Package altered.

SQL> alter package test_pkg compile body;

Package body altered.

SQL>

Case 3:
=======
Execute Permission to Application User through Role (this works)

Users:
TESTUSR (Table Owner)
TESTUSR_PKG (Package Owner)
TESTUSR_APP (Application Owner)

Table: TEST_TBL
Package: TEST_PKG

Role: TESTUSR_APP_ROLE

SQL> set linesize 150
SQL> ;
1 select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs
2* where owner='TESTUSR_PKG'
SQL> /

GRANTEE OWNER TABLE_NAME PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ----------------------------------------
TESTUSR_APP_ROLE TESTUSR_PKG TEST_PKG EXECUTE

SQL>

SQL> conn TESTUSR_APP/abc123
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
TESTUSR_APP_ROLE

SQL>

SQL> exec TESTUSR_pkg.test_pkg.insert_test_tbl(1);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

Comments

Great post, thanks!