Oracle : Granting privileges via roles does not work ?

While working on an migration from oracle 8i to oracle 10g I discovered the following, granting of privileges via role does not work for creating procedures, functions or views that have dependencies on objects owned by other schema. It was still working in 8.1.6.

I did a small test by running the follow on 8i and 10g database seperately

create user a identified by a default tablespace DATA01 quota unlimited on DATA01;
create user b identified by b default tablespace DATA01 quota unlimited on DATA01;

grant create session to a;
grant create session to b;

create table a.t1 (v1 char);
create role test_role;
grant all on a.t1 to test_role;
grant test_role to b;
create public synonym t1 for a.t1;

create or replace procedure b.test
as
vn_Count NUMBER(10);
BEGIN
SELECT count(*) INTO vn_Count FROM t1;
END;/

In the case of 8i database

Procedure created.

In the case of 10g database

Warning: Procedure created with compilation errors.
SQL> show errorErrors for PROCEDURE B.TEST:
LINE/COL ERROR-------- -----------------------------------------------------------------5/3 PL/SQL: SQL Statement ignored
7/23 PL/SQL: ORA-00942: table or view does not exist

If the object privileges is granted directly to the user (explicitly granted ) rather than via the role in the 10g database,

grant all on a.t1 to b;

Then the procedure will be created successfully. Hence this concludes that granting via roles does not work for procedure, function and view creation for Oracle 9i onwards.


http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#CHDEDFHE http://www.adp-gmbh.ch/ora/err/ora_00942.html

Extracted from the documents

5.1.4.1 Privileges Required to Create Views

To create a view, you must meet the following requirements:
You must have been granted one of the following system privileges, either explicitly or through a role:
The CREATE VIEW system privilege (to create a view in your schema)
The CREATE ANY VIEW system privilege (to create a view in another user's schema)
You must have been explicitly granted one of the following privileges:
The SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view
The SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges
In addition, in order to grant other users access to your view, you must have received object privileges to the base objects with the GRANT OPTION clause or appropriate system privileges with the ADMIN OPTION clause. If you have not, then grantees cannot access your view.