Roles Are Disabled at Package Compilation Time

When granting permissions through roles, it is important to know that rolesare disabled while packages are compiled. For example, consider two schemas: User_A and User_B. Let’s create Role_A:

 CREATE ROLE Role_A NOT IDENTIFIED;

Grant access on all the tables of User_A to Role_A. And then grant the role toUser_B:

 GRANT Role_A to User_B

Next, let’s create Sample_Package in User_B:

 CREATE OR REPLACE PACKAGE BODY 
SAMPLE_PKGASPROCEDURE TestProc ( iMemId IN   
MemberTable.MEMBERID%TYPE,                     OName   
OUT MemberTable.FIRSTNAME%TYPE )ASBEGIN   SELECT FirstName   INTO   oName   FROM   User_A.MemebrTable   WHERE  MemId = iMemId;EXCEPTION WHEN OTHERS THEN     ROLLBACK;	RAISE_APPLICATION_ERROR(-20001, 
'Sample_PKG.TestProc :'                                      
||sqlcode||' '||sqlerrm);END TestProc;End SAMPLE_PKG;

This would give a compilation error because Role_A is disabled at compile time. You would have to grant explicit SELECT on this table to User_B from User_A.

 GRANT SELECT On MemberTable TO User_B