prog:plsql_und_rolen
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:plsql_und_rolen [2018/02/22 12:49] – [Execute immediate - Dynamisches SQL Verwenden] gpipperr | prog:plsql_und_rolen [2018/02/22 12:52] (aktuell) – [Über eine View kapseln] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====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 " | ||
+ | |||
+ | |||
+ | Laut Doku: | ||
+ | |||
+ | .. | ||
+ | All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer' | ||
+ | .. | ||
+ | |||
+ | siehe https:// | ||
+ | |||
+ | |||
+ | Gibt es nun aber doch eine Möglichkeit ein 12c die Rollen in PL/SQL zu verwenden mit " | ||
+ | |||
+ | |||
+ | Testcase: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | -- 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' | ||
+ | |||
+ | CREATE OR REPLACE PROCEDURE procTestRoleRighst | ||
+ | | ||
+ | IS | ||
+ | | ||
+ | BEGIN | ||
+ | BEGIN | ||
+ | SELECT COUNT(*) INTO v_count FROM scott.emp; | ||
+ | END; | ||
+ | dbms_output.put_line(' | ||
+ | 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 | ||
+ | |||
+ | --- > Das funktioniert also doch nicht so einfach ! | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | Aus der Doku https:// | ||
+ | |||
+ | - A unit whose AUTHID value is **CURRENT_USER** is called an **invoker' | ||
+ | - A unit whose AUTHID value is **DEFINER** (the default) is called a **definer' | ||
+ | |||
+ | |||
+ | |||
+ | 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: | ||
+ | <code sql> | ||
+ | |||
+ | connect gpi | ||
+ | |||
+ | CREATE OR REPLACE PROCEDURE procTestRoleRighst | ||
+ | | ||
+ | IS | ||
+ | | ||
+ | BEGIN | ||
+ | BEGIN | ||
+ | execute immediate ' | ||
+ | END; | ||
+ | dbms_output.put_line(' | ||
+ | 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: | ||
+ | |||
+ | <code sql> | ||
+ | connect gpi | ||
+ | |||
+ | create view v_local_emp as select * from scott.emp; | ||
+ | |||
+ | |||
+ | SQL> CREATE OR REPLACE PROCEDURE procTestRoleRighst | ||
+ | AUTHID CURRENT_USER | ||
+ | IS | ||
+ | | ||
+ | BEGIN | ||
+ | BEGIN | ||
+ | SELECT COUNT(*) INTO v_count FROM v_local_emp; | ||
+ | END; | ||
+ | | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | Procedure created. | ||
+ | |||
+ | SQL> set serveroutput on | ||
+ | |||
+ | |||
+ | SQL> exec procTestRoleRighst; | ||
+ | |||
+ | -- Info :: Count 14 | ||
+ | |||
+ | </ | ||
+ | |||
+ | === Über ein Synoym kapseln === | ||
+ | |||
+ | |||
+ | testcase: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | SQL> create synonym t_local_emp for scott.emp; | ||
+ | |||
+ | Synonym created. | ||
+ | |||
+ | SQL> CREATE OR REPLACE PROCEDURE procTestRoleRighst | ||
+ | AUTHID CURRENT_USER | ||
+ | IS | ||
+ | | ||
+ | BEGIN | ||
+ | BEGIN | ||
+ | SELECT COUNT(*) INTO v_count FROM t_local_emp; | ||
+ | END; | ||
+ | | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | Procedure created. | ||
+ | |||
+ | SQL> exec procTestRoleRighst; | ||
+ | -- Info :: Count 14 | ||
+ | |||
+ | PL/SQL procedure successfully completed. | ||
+ | |||
+ | </ | ||
+ | ---- | ||
+ | |||
+ | ====Quellen ==== | ||
+ | |||
+ | |||
+ | |||
+ | Web: | ||
+ | * https:// | ||
+ | |||
+ | |||
+ | Mehr zu Inherit Privilige | ||
+ | * https:// | ||
prog/plsql_und_rolen.txt · Zuletzt geändert: 2018/02/22 12:52 von gpipperr