Inhaltsverzeichnis

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: 

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

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

  1. A unit whose AUTHID value is CURRENT_USER is called an invoker's rights unit, or IR unit.
  2. 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

Execute immediate - Dynamisches SQL Verwenden

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

Testcase: 

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.

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

Über eine View kapseln

Testcase: 

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

Über ein Synoym kapseln

testcase: 

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.

Quellen

Web:

Mehr zu Inherit Privilige