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