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