Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====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