Gunther Oracle APEX und Datenbank Wiki

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.

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

Ärgerlich

• Dynamisches SQL
• View
• Synonym

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

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

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.

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