Impact on Packages - Assign permissions directly vs Assign permission via Roles
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>
- girish.rohini's blog
- Log in to post comments
Comments
Great post!
Great post, thanks!