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