=====PL/SQL - Rollen in PLSQL 12c verwenden - PL/SQL: ORA-00942: table or view does not exist =====

Die generelle Aussage allgemein ist, dass Rechte die über Rollen dem Schema Owner einer PL/SQL Routine vergeben werden in PL/SQL **NICHT** funktionieren.

Die einfachste Lösung bei einem "PL/SQL: ORA-00942: table or view does not exist" 'Fehler in PL/SQL ist also ein direkter Grant des Rechtes an den Schema Owner.


Laut Doku:

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

siehe https://docs.oracle.com/database/121/DBSEG/authorization.htm#GUID-5C57B842-AF82-4462-88E9-5E9E8FD59874


Gibt es nun aber doch eine Möglichkeit ein 12c die Rollen in PL/SQL zu verwenden mit "AUTHID CURRENT_USER"?


Testcase:

<code sql>

-- Create ROLE and Grant the rights

sqlplus / as sysdba


SQL> create role READ_SCOTT;

SQL> grant select on scott.emp to READ_SCOTT;
	
SQL> grant READ_SCOTT to GPI;

-------------------------------------
-- Use Role in SQL

connect gpi

-- check your roles and enable if missing

select * from session_roles;
set role all;

--- select EMP

SQL> SELECT COUNT(*) FROM scott.emp;
 
-- All is fine
 

-------------------------------------------------------------
-- define a PL/SQL with with invoker's rights => CURRENT_USER

CREATE OR REPLACE PROCEDURE procTestRoleRighst
 AUTHID CURRENT_USER
IS
 v_count pls_integer;
BEGIN
  BEGIN
    SELECT COUNT(*) INTO v_count FROM scott.emp; 
  END;
  dbms_output.put_line('-- Info :: Count '||to_char(v_count));
END;
/

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PROCTESTROLERIGHST:

LINE/COL ERROR
-------- ---------------------------------------------------
8/5      PL/SQL: SQL Statement ignored
8/45     PL/SQL: ORA-00942: table or view does not exist

--- > Das funktioniert also doch nicht so einfach !

</code>



Aus der Doku  https://docs.oracle.com/database/121/LNPLS/subprograms.htm#LNPLS599

  - A unit whose AUTHID value is **CURRENT_USER** is called an **invoker's rights** unit, or **IR unit**. 
  - A unit whose AUTHID value is **DEFINER** (the default) is called a **definer's rights** unit, or **DR unit**. 
 


Eine Rolle ist ja kein festes Objekt, die Rolle kann sich jederzeit ändern, daher wird eine Rolle in PL/SQL nicht ausgewertet wenn PL/SQL übersetzt wird.


==== Lösung ====

D.h. es führt doch keine Weg daran herum die Rechte direkt auf die notwendigen Objekte zu vergeben!

Ärgerlich


----

==== Workarounds ====


  * Dynamisches SQL
  * View
  * Synonym


===Execute immediate - Dynamisches SQL Verwenden===

da ja das Problem nur beim Übersetzen auftaucht, kann dynamisches SQL eingesetzt werden:

Testcase:
<code sql>

connect gpi

CREATE OR REPLACE PROCEDURE procTestRoleRighst
 AUTHID CURRENT_USER
IS
 v_count pls_integer;
BEGIN
  BEGIN
    execute immediate 'SELECT COUNT(*) FROM scott.emp' into v_count; 
  END;
  dbms_output.put_line('-- Info :: Count '||to_char(v_count));
END;
/


Procedure created.

SQL> set serveroutput on

SQL> exec procTestRoleRighst

-- Info :: Count 14

PL/SQL procedure successfully completed.

</code>

D.h. zur Laufzeit sind die Rechte da, nur beim Übersetzen fehlen PL/SQL diese Informationen


=== Über eine View kapseln ===


Testcase:

<code sql>
connect gpi

create view v_local_emp as select * from scott.emp;


SQL> CREATE OR REPLACE PROCEDURE procTestRoleRighst
    AUTHID CURRENT_USER
    IS
     v_count pls_integer;
    BEGIN
      BEGIN
        SELECT COUNT(*) INTO v_count FROM v_local_emp;
      END;
     dbms_output.put_line('-- Info :: Count '||to_char(v_count));
   END;
  /

Procedure created.

SQL> set serveroutput on


SQL> exec procTestRoleRighst;

-- Info :: Count 14

</code>

=== Über ein Synoym kapseln ===


testcase:

<code sql>

SQL> create synonym t_local_emp for scott.emp;

Synonym created.

SQL> CREATE OR REPLACE PROCEDURE procTestRoleRighst
    AUTHID CURRENT_USER
    IS
     v_count pls_integer;
    BEGIN
      BEGIN
        SELECT COUNT(*) INTO v_count FROM t_local_emp;
      END;
     dbms_output.put_line('-- Info :: Count '||to_char(v_count));
   END;
   /

Procedure created.

SQL> exec procTestRoleRighst;
-- Info :: Count 14

PL/SQL procedure successfully completed.

</code>
----

====Quellen ====



Web:
  * https://dba.stackexchange.com/questions/158079/problem-granting-user-privileges-via-roles-in-oracle-12c


Mehr zu Inherit Privilige
  * https://oracle-base.com/articles/12c/control-invoker-rights-privileges-for-plsql-code-12cr1