Gunther Oracle APEX und Datenbank Wiki

Aufgabe:

Über eine View einen dritten Daten zur Verfügung stellen, ohne Leserechte auf die eigentliche Basis Tabelle.

Wir wollen erreichen, das DB Schema C nur die View in B lesen kann aber dafür KEINE Rechte auf die Basis Tabelle A benötigt!

Granted der User A nur das Select Recht ganz normal an B und granted der User B das select Recht an der View an C funktioniert das nicht, da B das Leserecht auf A nicht weitergeben kann (ORA-01720: grant option does not exist for) !

Dazu muss A dem User B das select Recht mit „with grant option“ granten, ansonsten kann der User B das Lese Rechte auf C nicht weitergeben!

Vorteil:

Nun kann der User C die Daten NUR über die View B lesen, in dieser View können wir z.b. die Daten dann noch weiter einschränken um zu verhindern, das C alles in A lesen kann. Stichwort Mandanden Fähigkeit !

Die User anlegen:

--A
CREATE USER A IDENTIFIED BY A;
GRANT CONNECT TO A;
GRANT CREATE TABLE TO A;
GRANT unlimited tablespace TO A;
 
--B
CREATE USER B IDENTIFIED BY B;
GRANT CONNECT TO B;
GRANT CREATE VIEW TO B;
 
--C
CREATE USER C IDENTIFIED BY C;
GRANT CONNECT TO C;

Testcase 1

User A:

CONNECT a/A
 
CREATE TABLE T AS SELECT * FROM all_objects;
GRANT SELECT ON T TO B;
 
SELECT COUNT(*) FROM b.v_t;
 
 COUNT(*)
---------
    57357
 

User B:

CONNECT b/B
CREATE OR REPLACE VIEW v_t AS SELECT * FROM A.t WHERE owner='SYS';
GRANT SELECT ON v_t TO c;
 
ERROR at line 1:
ORA-01720: GRANT OPTION does NOT exist FOR 'A.T'
 
CONNECT a/A
GRANT SELECT ON T TO B WITH GRANT OPTION;
 
CONNECT b/B
GRANT SELECT ON v_t TO c;

User C:

CONNECT c/C
SELECT COUNT(*) FROM b.v_t;
 
 COUNT(*)
---------
    39401
 
SELECT COUNT(*) FROM a.t;
 
ERROR at line 1:
ORA-00942: TABLE OR VIEW does NOT exist

Damit haben wir den User C über die View fest eingeschränkt, er kann nur Daten lesen die ihm das Schema B zur Verfügung stellt.

Geht da auch über nur zwei Ebenen?

Das ganze funktioniert auch über zwei Ebenen, A hat eine Tabelle TT und legt eine View v_TT mit einem Filter Kriterium an. Die Rechte werden an den User B nur auf die View V_TT vergeben, der User B hat keine Rechte auf die Basis Tabelle TT!. Der User B kann nun NUR die Daten über die View lesen!

Übersicht:

Test Case:

CONNECT /  AS sysdba
 
GRANT CREATE VIEW TO A
 
CONNECT a/A
 
CREATE TABLE tt AS SELECT * FROM all_objects;
CREATE OR REPLACE VIEW v_tt AS SELECT * FROM TT WHERE owner='SYS';
 
GRANT SELECT ON v_tt TO B;
 
CONNECT b/B
 
SELECT COUNT(*) FROM a.v_tt;
 
  COUNT(*)
----------
     39401
 
SQL> SELECT COUNT(*) FROM a.TT;
SELECT COUNT(*) FROM a.TT
                       *
ERROR at line 1:
ORA-00942: TABLE OR VIEW does NOT exist

In plsql verwenden funktioniert dann auch

CONNECT b/B
SQL> DECLARE
  2  v_count pls_integer;
  3  BEGIN
  4  SELECT COUNT(*) INTO v_count FROM a.v_tt;
  5  END;
  6  /
 
PL/SQL PROCEDURE successfully completed.

create view ⇒ https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30