This discussion is archived
9 Replies Latest reply: Dec 5, 2012 6:47 AM by John Spencer RSS

Granting privilege through role not working for PL/SQL

N.Page Newbie
Currently Being Moderated
Version: 11.2.0.2


In our shop, we don't grant privileges directly to a user, we grant it to a role and grant that role to the intended grantee.

Granting privileges through a role seems to be fine with SQL Engine. But it doesn't work from PL/SQL engine.

In the below example GLS_DEV user is granted SELECT access on SCOTT.pets table through a role called tstrole. GLS_DEV can select this table from SQL. But PL/SQL Engine doesn't seem to know this.

Reproducing the issue:
SQL> show user
USER is "SCOTT"

SQL> select * from pets;

NAME
-----------------------------------
PLUTO


SQL> conn / as sysdba
Connected.


SQL> create user GLS_DEV identified by test1234 default tablespace TSTDATA;

User created.

SQL> alter user GLS_DEV quota 25m on TSTDATA;

User altered.


SQL> grant create session, resource to GLS_DEV;

Grant succeeded.


--- Granting SELECT privilege on scott.pets to tstrole and then grant this role to GLS_DEV.

SQL> conn / as sysdba
Connected.
SQL>
SQL> create role tstrole;

Role created.

SQL> grant select on scott.pets to tstrole;

Grant succeeded.

SQL> grant tstrole to GLS_DEV;

Grant succeeded.

SQL> conn GLS_DEV/test1234
Connected.
SQL>
SQL> select * From scott.pets;

NAME
-----------------------------------
PLUTO

---- All fine till here. From SQL engine , GLS_DEV user can SELECT scott.pets table.

--- Now , I am going to create a PL/SQL object in GLS_DEV which tries to refer scott.pets

SQL> show user
USER is "GLS_DEV"


create or replace procedure my_proc
is
myvariable varchar2(35);

begin
     select name into myvariable from scott.pets ;

     dbms_output.put_line(myvariable);

end my_proc;
/

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/2      PL/SQL: SQL Statement ignored
6/41     PL/SQL: ORA-01031: insufficient privileges

SQL>
SQL> 6
  6*    select name into myvariable from scott.pets ;


--- PL/SQL Engine doesn't seem to know that GLS_DEV has select privilege on scott.pets indirectly granted through a role

--- Fix
--- Instead of granting privilege through a role, I am granting the SELECT privilege on scott.pets to GLS_DEV directly.
--- The error goes away, I can compile and execute the procedure !!

SQL> conn / as sysdba
Connected.
SQL>
SQL> grant select on scott.pets to GLS_DEV;

Grant succeeded.

SQL> conn GLS_DEV/test1234
Connected.
SQL>
SQL> create or replace procedure my_proc
is
myvariable varchar2(35);

begin
        select name into myvariable from scott.pets ;

        dbms_output.put_line(myvariable);

end my_proc;  2    3    4    5    6    7    8    9   10
 11  /

Procedure created.

SQL> set serveroutput on
SQL> exec my_proc;
PLUTO

PL/SQL procedure successfully completed.
Has anyone encountered the same issue ?
  • 1. Re: Granting privilege through role not working for PL/SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    It's not an issue. PL/SQL ignores roles by default, that's the way it's designed.


    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1796

    Edited by: BluShadow on 09-Oct-2012 09:49
  • 2. Re: Granting privilege through role not working for PL/SQL
    Ora Pro
    Currently Being Moderated
    More info:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319
  • 3. Re: Granting privilege through role not working for PL/SQL
    Paul Horth Expert
    Currently Being Moderated
    Yes, well-known and well-documented.

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1796

    You must grant directly and not via a role for PL/SQL packages if they execute under definer's rights.
    You can use role if you execute under invoker's rights.
  • 4. Re: Granting privilege through role not working for PL/SQL
    N.Page Newbie
    Currently Being Moderated
    Ok. Thanks everyone.
    Is there any workaround for this like using AUTHID clause or something ?
  • 5. Re: Granting privilege through role not working for PL/SQL
    Sven W. Guru
    Currently Being Moderated
    N.Page wrote:
    Ok. Thanks everyone.
    Is there any workaround for this like using AUTHID clause or something ?
    Yes. using AUTHID (invoker vs. definer rights) can make a difference.
    However it makes sense that pl/sql uses roles slightly differently. Although at first glance it is difficult to see why. The reasoning is similiar to the difference between a user and a schema.

    A user is a person that connects to a database. it has a role and this role defines what privileges it gets when working with data.
    A schema consists of several obejcts that reside in the database. A package is also such an object. For this package to be valid, it is neccessary to have all the privs directly. A role can be enabled/disabled and is a kind of temporary right. A direct grant has a more permanent status. A user will only get execution right to this package/procedure/function. The package is a kind of user interface. It encapsulates the needed privs to do something from this user.

    The logical problem only arrives when the connected user and the package are in the same schema. The better approach is to have the package in the normal schema and have a separate "access schema" with that the user connects. Then give execute privs on the package to this second schema.
  • 6. Re: Granting privilege through role not working for PL/SQL
    N.Page Newbie
    Currently Being Moderated
    There seems to be an exception to this rule for ALTER SESSION privilege. In the below example, I am not directly granting the privilege to user Slater. Instead i am granting it through a role. But , still the PL/SQL function is working without any issues.

    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    
    
    SQL> create role tstrole;
    
    Role created.
    
    SQL> grant create session, create procedure , create table to tstrole;
    
    Grant succeeded.
    
    SQL> create user slater identified by test123;
    
    User created.
    
    SQL> select privilege from dba_sys_privs where grantee = 'TEST123';
    
    no rows selected
    
    SQL> grant tstrole to slater;
    
    Grant succeeded.
    
    SQL> conn slater/test123
    Connected.
    SQL>
    SQL> select privilege from user_sys_privs;
    
    PRIVILEGE
    ----------------------------------------
    DEBUG CONNECT SESSION   -------------------------------> Never knew that any user will get these privileges by default.
    DEBUG ANY PROCEDURE
    
    SQL> show user
    USER is "SLATER"
    SQL>
    SQL>
    SQL> create or replace function random
      2  (dummy in varchar2)
      3  return number is
      4     x           number;
      5     y           number;
      6  begin
      7          execute Immediate 'alter session enable parallel ddl';
      8          select 4 into x from dual;
      9          y :=x;
     10  return (y);
     11  end random;
     12  /
    
    Function created.
    
    SQL>
    SQL>
    SQL> variable myvar number
    SQL> exec :myvar:= random('a');
    
    PL/SQL procedure successfully completed.
    
    SQL> print myvar
    
         MYVAR
    ----------
             4
  • 7. Re: Granting privilege through role not working for PL/SQL
    John Spencer Oracle ACE
    Currently Being Moderated
    You really should start your own new thread for this question instead of resurrecting an old one, but to answer your question.

    There are two things going on here. First, there are a number of aler session commands that can be used by any user regardless of what privileges they are granted. Although I do not have the entire list at hand, things like nls_date_format and current_schema are available to all users, sort of like the grants to public in the data dictionary.

    Second, when you use execute immediate, the PL/SQL engine never really sees the statement, as far as the compiler is concerned it is just a string. It is only when the string is passed to the sql engine that permissions are checked, and there roles are not enabled.
    SQL> create role t_role;
    
    Role created.
    
    SQL> grant select on ops$oracle.t to t_role;
    
    Grant succeeded.
    
    SQL> create user a identified by a default tablespace users;
    
    User created.
    
    SQL> grant create session, create procedure to a;
    
    Grant succeeded.
    
    SQL> grant t_role to a;
    
    Grant succeeded.
    
    SQL> connect a/a
    Connected.
    SQL> select * from ops$oracle.t;
    
            ID DESCR
    ---------- ----------
             1 One
             1 Un
    
    SQL> create function f (p_descr in varchar2) return number as
      2     l_num number;
      3  begin
      4     select id into l_num
      5     from ops$oracle.t
      6     where descr = p_descr;
      7     return l_num;
      8  end;
      9  /
    
    Warning: Function created with compilation errors.
    
    SQL> show error
    Errors for FUNCTION F:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/4      PL/SQL: SQL Statement ignored
    5/20     PL/SQL: ORA-00942: table or view does not exist
    
    SQL> create or replace function f (p_descr in varchar2) return number as
      2     l_num number;
      3  begin
      4     execute immediate 'select id from ops$oracle.t where descr = :b1'
      5                       into l_num using p_descr;
      6     return l_num;
      7  end;
      8  /
    
    Function created.
    
    SQL> select f('One') from dual;
    select f('One') from dual
           *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "A.F", line 4
    John
  • 8. Re: Granting privilege through role not working for PL/SQL
    N.Page Newbie
    Currently Being Moderated
    THANK YOU John.

    My question was specific to the System Privilege ALTER SESSION . Your example is using an Object privilege.
  • 9. Re: Granting privilege through role not working for PL/SQL
    John Spencer Oracle ACE
    Currently Being Moderated
    N.Page wrote:
    THANK YOU John.

    My question was specific to the System Privilege ALTER SESSION . Your example is using an Object privilege.
    The principle is the same for both. Privileges acquired through roles are not valid in definer rights (the default) stored procedures for the reasons explained earlier in this thread. In the case of the simple select in my first version of the function, the visibility of the table was checked at compile time since the compiler "knew" that the table would be accessed by the function. In the execute immediate version the compiler only sees the string being passed to execute immediate but does not attempt to interpret it. The parsing of the string only happens when execute immediate passes it on to the SQL engine at run-time. At that point, no roles are enabled so the statement fails in the parse phase in SQL.

    Also, as I said, some alter session commands work for any user whehther or not they have the alter session privilege either directly or through a role. Using the same minimally privileged user as in my prior example:
    SQL> connect a/a
    Connected.
    SQL> alter session enable parallel ddl;
    
    Session altered.
    So, parallel ddl is one of those privileges that anyone can set without alter session privileges.

    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points