The difference between a direct grant and a role in PL/SQL
Posted by Martin Bach on May 27, 2010
A little post about “beginning Oracle development”. Some people I work with sometimes struggle to grasp the permission-granted-via-role PL/SQL problem. But it’s so simple! One of the testers scratched his head many times asking himself why the procedure doesn’t compile. ORA-942 in the procedure, but in SQL Plus he can select from it. Let’s create the test case. As user martin, owner of table “charlie” we grant select on the table via role to alice.
mbach@pc:~> sqlplus martin@dev SQL*Plus: Release 184.108.40.206.0 Production on Thu May 27 15:10:59 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: ***** Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create user alice identified by xxxx; User created. SQL> grant connect to alice; Grant succeeded.
This is the table Alice requires access to.
SQL> create table charlie (id number); Table created.
Now let’s populate the table so we can test.
SQL> begin for i in 1..100 loop insert into charlie values (i); end loop; end; 2 / PL/SQL procedure successfully completed.
This is the role we initially create to grant Alice access to table martin.charlie. NB I initially complicated the scenario with “bob” but decided that was overkill-hence the table is called charlie. First we create the role, then we grant the select privilege on martin.charlie to it and finally allow Alice to make use of it
SQL> create role delta; Role created. SQL> grant select on martin.charlie to delta; Grant succeeded. SQL> grant delta to alice; Grant succeeded. SQL> grant resource to alice; Grant succeeded.
With this initial work done, let’s see what Alice can access.
mbach@pc:~> sqlplus alice/xxxx@dev SQL*Plus: Release 220.127.116.11.0 Production on Thu May 27 15:12:53 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> sho user USER is "ALICE" SQL> select * from martin.charlie 2 / select * from martin.charlie * ERROR at line 1: ORA-00942: table or view does not exist
Oops- forgot to enable the role.
SQL> set role all; Role set. With the role SQL> select * from martin.charlie 2 / ID ---------- 1 2 3 4 ... 98 99 ID ---------- 100 100 rows selected.
This is better. Now let’s think of a function f which returns the ID from table charlie for a given input parameter. Yes, the example is silly and nothing you’d ever see, but it helps to get the idea.
SQL> get f 1 create or replace function alice.f(pi_nbr number) return number 2 as 3 v_id number; 4 begin 5 select id into v_id from martin.charlie 6 where id = pi_nbr; 7 exception 8 when no_data_found then 9 raise_application_error(-20001, 'no data found for id ' || pi_nbr); 10* end; Let's try to run the script SQL> @f Warning: Function created with compilation errors.
What’s the problem?
SQL> show err Errors for FUNCTION F: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/3 PL/SQL: SQL Statement ignored 4/38 PL/SQL: ORA-00942: table or view does not exist
Remember that the select in sqlplus actually worked! This is what gets many people’s head in. But we know better-now grant select on the table directly as the owner of the table (martin)
SQL> grant select on chalie to alice;
As alice, try again:
SQL> @f Function created.
- When you grant access to a table via a role, you can select it from clients such as sqlplus
- If you want to use the object you are granted access to in PL/SQL, then the grant via the role isn’t enough
- Access needs to be granted _directly_ to the user, not through a role