9 Replies Latest reply: Dec 5, 2012 8:47 AM by John Spencer RSS

    Granting privilege through role not working for PL/SQL

    N.Page
      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
          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
            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
              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
                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.
                  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
                    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
                      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
                        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
                          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