Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_und_rolen

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:plsql_und_rolen [2018/02/22 12:51]
gpipperr [Workarounds]
prog:plsql_und_rolen [2018/02/22 12:52] (aktuell)
gpipperr [Über eine View kapseln]
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 "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:
 +
 +<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'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 !
 +
 +</code>
 +
 +
 +
 +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:
 +<code sql>
 +
 +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.
 +
 +</code>
 +
 +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
 +     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
 +
 +</code>
 +
 +=== Ü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
 +     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.
 +
 +</code>
 +----
 +
 +====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
  
"Autor: Gunther Pipperr"
prog/plsql_und_rolen.txt · Zuletzt geändert: 2018/02/22 12:52 von gpipperr