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:
