Posts mit dem Label virtual private database werden angezeigt. Alle Posts anzeigen
Posts mit dem Label virtual private database werden angezeigt. Alle Posts anzeigen

Montag, 17. Mai 2010

"Sicheres" Suchen: Oracle TEXT und die "Virtual Private Database"

Eine Volltextsuche geht bekanntlich immer über die ganze Tabelle. Manchmal bestehen allerdings Anforderungen an "sicheres Suchen", es soll also nicht jeder alles sehen können. Hierzu bietet sich im "normalen" Datenbankumfeld die Virtual Private Database an. Diese bewirkt, kurzgefasst, dass unterschiedliche Nutzer ein SELECT * FROM TABELLE absetzen, jeder sieht jedoch was anderes (das Feature gehört zur Enterprise Edition der Datenbank). Das wollen wir nun im Zusammenspiel mit Oracle TEXT einsetzen. Zuerst erzeugen wir die Tabelle und fügen ein paar Spalten ein: 
create table dokumente(
  id                 number(10),
  sicherheitsmerkmal varchar2(50),
  dokument           clob
)
/

insert into dokumente values (1, 'LOW', 'Oracle TEXT dient zur Volltextrecherche');
insert into dokumente values (2, 'LOW', 'Oracle TEXT beherrscht linguistische Suche');
insert into dokumente values (3, 'MEDIUM', 'Der Gewinn wird im nächsten Jahr steigen');
insert into dokumente values (4, 'HIGH', 'Die Spionageabteilung sitzt in Berlin');
Das Besondere ist die Spalte SICHERHEITSMERKMAL - sie kann die Werte MEDIUM, HIGH oder LOW annehmen. Es fehlt nun nur noch eine Tabelle, mit welcher wir die Datenbanknutzer auf die Sicherheitsklassen abbilden ... 
create table dokumente_nutzer(
  userid             varchar2(30),
  sicherheitsmerkmal varchar2(100)
)
/

insert into dokumente_nutzer values ('SCOTT', 'HIGH');
insert into dokumente_nutzer values ('SCOTT', 'MEDIUM');
insert into dokumente_nutzer values ('SCOTT', 'LOW');
insert into dokumente_nutzer values ('REVISION', 'MEDIUM');
insert into dokumente_nutzer values ('REVISION', 'LOW');
insert into dokumente_nutzer values ('ALLE', 'LOW');
Dann werden (als SYS) die Nutzer angelegt und, da die Privilegiensteuerung komplett über die Virtual Private Database gesteuert wird, SELECT-Privilegien an der Tabelle an PUBLIC vergeben. 
create user revision identified by revision
/

create user alle identified by alle
/

grant create session to revision
/

grant create session to alle
/

grant select on scott.dokumente to public
/
Nun geht es ans Einrichten der Virtual Private Database. Zunächst wird die sog. Policy Function benötigt. Die Policy Function generiert eine WHERE-Bedingung, die dann von der Datenbank automatisch in das SELECT-Kommando integriert wird. Durch diese Vorgehensweise (die Sicherheitsregel "hängt" direkt an der Tabelle) kann die Sicherheitsregel nicht umgangen werden! Unsere PL/SQL-Funktion ist recht einfach ... 
create or replace function my_policy(
 p_schema varchar2, p_object varchar2
) return varchar2 is
  v_where varchar2(32767) := 'SICHERHEITSMERKMAL IN (';
  v_users boolean := false;
begin
  for i in (
    select sicherheitsmerkmal from dokumente_nutzer
    where userid = user
  ) loop
    v_users := true;
    v_where := v_where || '''' || i.sicherheitsmerkmal || ''', ';
  end loop;
  if v_users then 
    v_where := substr(v_where, 1, length(v_where) - 2) || ')'; 
  else
    v_where := '1=0';
  end if;
  return v_where;
end;
Als letztes machen wir die Virtual Private Database "scharf" ... Um dieses Kommando laufen zu lassen, wird das EXECUTE-Privileg auf DBMS_RLS benötigt - oder der DBA macht es gleich selbst ... 
begin
 DBMS_RLS.ADD_POLICY(
    object_schema => 'SCOTT',
    object_name => 'DOKUMENTE',
    policy_name => 'SCOTT_DOKUMENT_POLICY',
    function_schema => 'SCOTT',
    policy_function => 'MY_POLICY'
  );
end;
/
Und nun kommt der erste Test (noch ohne Volltextindex). Probiert nun mal, die Tabelle mit verschiedenen Nutzerkonten zu selektieren ... Zuerst als SCOTT: 
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERKMAL                                 DOKUMENT
---------- -------------------------------------------------- --------------------------------------------------
         1 LOW                                                Oracle TEXT dient zur Volltextrecherche
         2 LOW                                                Oracle TEXT beherrscht linguistische Suche
         3 MEDIUM                                             Der Gewinn wird im nächsten Jahr steigen
         4 HIGH                                               Die Spionageabteilung sitzt in Berlin

4 Zeilen ausgewählt.
... dann als User ALLE: 
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.
... schließlich als irgendein User, der in der Tabelle DOKUMENTE_NUTZER nicht vorkommt ... 
SQL> select * from scott.dokumente;

Es wurden keine Zeilen ausgewählt
Man sieht, dass zwar alle die Tabelle selektieren können, jeder sieht allerdings was anderes. Und das kann man natürlich auch mit dem Volltextindex kombinieren. Den müssen wir natürlich zuerst mal anlegen. Und hier wollen wir auch gleich das in 11g neue Feature des Composite Domain Index nutzen (siehe das eigene Blog Posting dazu. Nötig ist das, da in diesem Fall jede Abfrage eine Mixed Query sein wird - die Virtual Private Database hängt ja eine relationale Abfrage an jede Textabfrage an ... 
create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Nun können wir testen: Wir arbeiten als User ALLE und probieren mal einige Vollextabfragen ... 
SQL> select * from scott.dokumente where contains (dokument,'Oracle') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

Es wurden keine Zeilen ausgewählt
Die Abfrage mit dem Gewinn probieren wir danach mal als User REVISION - der darf mehr sehen, aber nicht alles ... 
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         3 MEDIUM          Der Gewinn wird im nächsten Jahr steigen

1 Zeile ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

Es wurden keine Zeilen ausgewählt.
Die Spionageabteilung darf aber nur der SCOTT sehen ... 
SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         4 HIGH            Die Spionageabteilung sitzt in Berlin

1 Zeile ausgewählt.
Und das Schöne ist, dass diese Sicherheitsregeln unabhängig vom Textindex arbeiten; sie ziehen sowohl bei "normalen" als auch bei Volltextrecherchen. Zum Abschluß werfen wir einen Blick auf die Performance - normalerweise sind solche Mixed Queries ja problematisch ... insbesondere, wenn das Volltextkriterium nur gemeinsam mit dem relationalen Kriterium selektiv ist. Auch dazu bauen wir einen kleinen Testcase und fügen einige Sätze in die Tabelle DOKUMENTE ein. 
drop index vt_dokumente
/

begin
  for i in 1..100000 loop
    insert into dokumente values (i+10, 'MEDIUM', 'Der Gewinn ist dieses Jahr stabil');
    insert into dokumente values (i+200010, 'LOW', 'Oracle TEXT ist Teil der Datenbank');
   end loop;
end;
/

commit
/

insert into dokumente values (500000, 'LOW', 'Gewinn ist: Mehr Einnahmen als Ausgaben');
    

create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Diese Situation bringt das Mixed Query-Problem auf den Punkt. Die Volltextrecherche nach Gewinn allein ist nun extrem unselektiv (100.000 mal in MEDIUM, 1 mal in LOW). Die Sicherheitsklasse LOW ist aber auch unselektiv (kommt auch 100.000 mal vor). Die Kombination als Volltextsuche und relationalen Kriterium allein ist sehr selektiv - denn Gewinn kommt in der Sicherheitsklasse LOW nur einmal vor. Schauen wir uns die Abfrage nun an - wir arbeiten mit dem User ALLE (dieser hat nur Zugriff auf LOW), es darf also nur die Zeile der Klasse LOW zurückkommen ... 
SQL>select * from scott.dokumente where contains (dokument,'Gewinn') > 0

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
    300000 LOW             Gewinn ist: Mehr Einnahmen als Ausgaben

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.17
Sieht auf den ersten Blick ganz gut aus - und der Ausführungsplan ...? 
SQL> set autotrace traceonly
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3982102634

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 42738 |  6135K|  9161   (4)| 00:01:50 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DOKUMENTE    | 42738 |  6135K|  9161   (4)| 00:01:50 |
|*  2 |   DOMAIN INDEX              | VT_DOKUMENTE |       |       |  7710   (1)| 00:01:33 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("DOKUMENT",'Gewinn')>0)
       filter("SICHERHEITSMERKMAL"='LOW')


Statistiken
----------------------------------------------------------
         35  recursive calls
          0  db block gets
        551  consistent gets
          0  physical reads
          0  redo size
        928  bytes sent via SQL*Net to client
        648  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
Im Ausführungsplan erkennt man, dass die komplette Abfrage aus dem Volltextindex heraus bedient wurde. Genau für solche Fälle wurde dieses Feature in 11g auch geschaffen ... Zum Abschluß noch ein paar Links zu weiterführender Dokumentation:
Eingestellt von um
Labels: , , , ,
Abonnieren Posts (Atom)

Beliebte Postings