Gotcha: Privileges, Roles and Procedures

I came across a phenomenon the other to do with granting privileges via a role:

I had two users, A and B and a role which allowed SELECT on A’s tables. I granted this role to B. As you’d expect, when logged in as user B, I could SELECT FROM A’s tables. Great. I then created a procedure in B’s schema which referenced a table in A’s schema… and it wouldn’t compile. It was complaining that the “table or view does not exist”. But that made no sense since this was the very same table that I was SELECTing FROM successfully when logged in as B. So why was this happening?

The following SQL Plus session demonstrates this:


SQL> connect system@orcl
Enter password:
Connected.
SQL> create user a identified by a quota 1m on users;

User created.

SQL> grant create table, create session to a;

Grant succeeded.

SQL> create user b identified by b quota 1m on users;

User created.

SQL> grant create session, create table, create procedure to b;

Grant succeeded.

SQL> create table a.a_table (col1 varchar2(10));

Table created.

SQL> insert into a.a_table values(‘testing123′);

1 row created.

SQL> commit;

Commit complete.

SQL> create role selecter;

Role created.

SQL> grant select on a.a_table to selecter;

Grant succeeded.

SQL> grant selecter to b;

Grant succeeded.

SQL> create or replace procedure b.print_a_table
2 is
3 begin
4 for x in (select * from a.a_table)
5 loop
6 dbms_output.put_line(x.col1);
7 end loop;
8 end;
9 /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE B.PRINT_A_TABLE:

LINE/COL ERROR
——– —————————————————————–
4/10 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
6/1 PL/SQL: Statement ignored
6/22 PLS-00364: loop index variable ‘X’ use is invalid

SQL> connect b/b@orcl;
Connected.

SQL> select * from a.a_table;

COL1
———-
testing123

SQL> connect system@orcl
Enter password:
Connected.
SQL> grant select on a.a_table to b;

Grant succeeded.

SQL> create or replace procedure b.print_a_table
2 is
3 begin
4 for x in (select * from a.a_table)
5 loop
6 dbms_output.put_line(x.col1);
7 end loop;
8 end;
9 /

Procedure created.

As you can see above, granting SELECT on A’s table directly to B (rather than via a role) solved the problem.

But why?! Why doesn’t the privilege granted via the role work? Well, after a little digging and forum-posting I found the answer here:

http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#sthref568

“5.2.5 PL/SQL Blocks and Roles

The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer’s rights or invoker’s rights.
5.2.5.1 Named Blocks with Definer’s Rights

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer’s rights. Roles are not used for privilege checking and you cannot set roles within a definer’s rights procedure.”

So there you have it. You just can’t use roles in this way. Everyday’s a schoolday…