Stored Procedures and Roles (Example 1)

Tested on Oracle 10.2.0.1.0.

I do not understand why, but Oracle does not seem to recognise GRANTs made to roles when creating stored procedures. I do not believe this is a bug either as it has been the case for as long as I can remember. Perhaps one day I will raise a Service Request with Oracle for clarification. For now, here is an example to show what I mean. First, create a role:

SQL> conn system/manager@test10
Connected.
SQL> create role test_role
  2  /

Role created.

SQL>

Next, create a user who will own a table:

SQL> create user john
  2  identified by smith
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL> grant create session,
  2        create table to john
  3  /

Grant succeeded.

SQL>

And another user who will access that table using a stored procedure:

SQL> create user fred identified by bloggs
  2  /

User created.

SQL> grant create session,
  2        create procedure,
  3        test_role to fred
  4  /

Grant succeeded.

SQL>

John logs in, creates a table and gives full access on it to the role:

SQL> conn john/smith@test10
Connected.
SQL> create table one_number
  2  as select 1 counter from dual
  3  /

Table created.

SQL> grant all on one_number to test_role
  2  /

Grant succeeded.

SQL>

Fred logs in and tests the access through SQL*Plus:

SQL> conn fred/bloggs@test10
Connected.
SQL> select * from john.one_number
  2  /

COUNTER
----------
         1

SQL> update john.one_number
  2  set counter = counter + 1
  3  /

1 row updated.

SQL> select * from john.one_number
  2  /

COUNTER
----------
         2

SQL>

Fred tries to create a stored procedure to do the same thing. This fails but I do not know why:

SQL> create procedure add_one is
  2  begin
  3  update john.one_number
  4  set counter = counter + 1;
  5  end add_one;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE ADD_ONE:

LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
3/1
PL/SQL: SQL Statement ignored

3/13
PL/SQL: ORA-00942: table or view does not exist

SQL>

To make it work, John has to GRANT the access directly to Fred:

SQL> conn john/smith@test10
Connected.
SQL> grant all on one_number to fred
  2  /

Grant succeeded.

SQL>

Then when Fred tries again, he succeeds:

SQL> conn fred/bloggs@test10
Connected.
SQL> create or replace procedure add_one is
  2  begin
  3  update john.one_number
  4  set counter = counter + 1;
  5  end add_one;
  6  /

Procedure created.

SQL> show errors
No errors.
SQL> exec add_one;

PL/SQL procedure successfully completed.

SQL> select * from john.one_number
  2  /

COUNTER
----------
         3

SQL>

Go to example 2:

http://international-dba.blogspot.com/2011/06/stored-procedures-and-roles-example-2.html