Home » Tip Bank » Database Development » SQL » PL/SQL » SQL

Language: Enterprise
Expertise: All

Aug 11, 2000

When granting permissions through roles, it is important to know that roles are 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 to User_B:

 GRANT Role_A to User_B

Next, let's create Sample_Package in User_B:

 CREATE OR REPLACE PACKAGE BODY 
SAMPLE_PKG
AS


PROCEDURE TestProc ( iMemId IN   
MemberTable.MEMBERID%TYPE,
                     OName   
OUT MemberTable.FIRSTNAME%TYPE )
AS
BEGIN
   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