Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_und_rolen

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

  • Dynamisches SQL
  • View
  • Synonym

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

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/plsql_und_rolen.txt · Zuletzt geändert: 2018/02/22 12:52 von Gunther Pippèrr