{"id":433,"date":"2017-01-04T12:01:13","date_gmt":"2017-01-04T11:01:13","guid":{"rendered":"http:\/\/explainit.pl\/wordpress\/?p=433"},"modified":"2017-01-04T12:01:13","modified_gmt":"2017-01-04T11:01:13","slug":"oracle-database-fgacvpd-blaski-i-cienie-a-k-a-context_sensitive","status":"publish","type":"post","link":"https:\/\/explainit.pl\/wordpress\/2017\/01\/04\/oracle-database-fgacvpd-blaski-i-cienie-a-k-a-context_sensitive\/","title":{"rendered":"Oracle Database FGAC\/VPD – blaski i cienie (a.k.a. CONTEXT_SENSITIVE)"},"content":{"rendered":"

Drobnoziarnista kontrola dost\u0119pu (Fine-Grained Access Control – FGAC) jest popularn\u0105 technik\u0105 kontroli dost\u0119pu u\u017cytkownik\u00f3w do danych na poziomie rekord\u00f3w<\/strong> (w przeciwie\u0144stwie do kontroli dost\u0119pu na poziomie tabeli – realizowanej poleceniami GRANT), pozwalaj\u0105c\u0105 udost\u0119pnia\u0107 r\u00f3\u017cnym u\u017cytkownikom r\u00f3\u017cne zestawy rekord\u00f3w z tej samej tabeli (co daje efekt tzw. Virtual Private Database – VPD). Og\u00f3lna idea FGAC sprowadza si\u0119 do implementacji funkcji PL\/SQL, kt\u00f3re generuj\u0105 tre\u015bci predykat\u00f3w selekcji – automatycznie i nieodwo\u0142alnie do\u0142\u0105czanych do klauzuli WHERE ka\u017cdego zapytania kierowanego do tabeli. Poni\u017csza ilustracja przedstawia przebieg realizacji zapytania na tabeli obj\u0119tej drobnoziarnist\u0105 kontrol\u0105 dost\u0119pu. U\u017cytkownicy U1 i U2 pocz\u0105tkowo kieruj\u0105 identyczne zapytania do tabeli. Serwer bazy danych Oracle Database automatycznie wywo\u0142uje funkcj\u0119 generuj\u0105c\u0105 predykaty selekcji, a jej wyniki dokleja do klazul WHERE zapyta\u0144. W efekcie zapytania u\u017cytkownik\u00f3w U1 i U2 zostaj\u0105 odpowiednio zaw\u0119\u017cone np. tylko do rekord\u00f3w, w kt\u00f3rych figuruje u\u017cytkownik o nazwie zgodnej z nazw\u0105 u\u017cytkownika wykonuj\u0105cego zapytanie. St\u0105d ka\u017cdy z u\u017cytkownik\u00f3w uzyskuje inn\u0105 odpowied\u017a na pozornie identyczne zapytania.<\/p>\n

\"Oracle<\/p>\n

Programi\u015bci rozwa\u017caj\u0105cy zastosowanie mechanizm\u00f3w FGAC cz\u0119sto pytaj\u0105 o konsekwencje wydajno\u015bciowe takiego rozwi\u0105zania. Przeprowad\u017amy zatem prosty eksperyment. Dla przyk\u0142adowej tabeli PAYMENTS(PAYMENT_ID, AMOUNT, USER_ID) zdefiniujemy polityk\u0119 FGAC pozwalaj\u0105c\u0105 u\u017cytkownikom na dost\u0119p wy\u0142\u0105cznie do tych rekord\u00f3w, w kt\u00f3rych w kolumnie USER_ID znajduje si\u0119 warto\u015b\u0107 zgodna z identyfikatorem u\u017cytkownika wykonuj\u0105cego zapytanie. Funkcja generuj\u0105ca predykaty selekcji mog\u0142aby mie\u0107 nast\u0119puj\u0105c\u0105 posta\u0107:<\/p>\n

create function fgac_fun( schema_var in varchar2, table_var in varchar2) return varchar2
\nis
\nreturn_val varchar2(400);
\nbegin
\nreturn_val := 'USER_ID = '''||SYS_CONTEXT('userenv','session_user')||'''';
\nreturn return_val;
\nend;
\n\/<\/code><\/p>\n

W celu u\u017cycia powy\u017cszej funkcji do drobnoziarnistej kontroli dost\u0119pu tworzymy polityk\u0119 FGAC:<\/p>\n

begin
\ndbms_rls.add_policy (
\nobject_schema => 'system',
\nobject_name => 'payments',
\npolicy_name => 'payments_policy',
\nfunction_schema => 'system',
\npolicy_function => 'fgac_fun',
\nstatement_types => 'select, insert, update, delete'
\n);
\nend;
\n\/<\/code><\/p>\n

Od tego momentu ka\u017cde zapytanie u\u017cytkownika do tabeli PAYMENTS zostanie automatycznie „wzbogacone” o predykat selekcji zwr\u00f3cony dynamicznie przez funkcj\u0119 FGAC_FUN. (Co ciekawe, administrator bazy danych nie zobaczy tak wzbogaconej tre\u015bci w perspektywie V$SQL – nale\u017cy dodatkowo si\u0119gn\u0105\u0107 do V$VPD_POLICY). Oto co widz\u0105 u\u017cytkownicy U1 i U2 wykonuj\u0105c zapytania „SELECT * FROM PAYMENTS”:<\/p>\n

SQL> connect u1
\nSQL> select * from system.payments;
\nPAYMENT_ID AMOUNT\u00a0\u00a0\u00a0\u00a0 USER_ID
\n---------- ---------- -------
\n1\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 950 U1
\n2\u00a0 \u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1500 U1<\/code><\/p>\n

SQL> connect u2
\nSQL> select * from system.payments;
\nPAYMENT_ID AMOUNT\u00a0\u00a0\u00a0\u00a0 USER_ID
\n---------- ---------- -------
\n3 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 300 U2
\n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 875 U2<\/code><\/p>\n

A oto co widzi u\u017cytkownik SYS (spod polityk FGAC wyj\u0119ci s\u0105 SYSDBA i posiadacze roli EXEMPT_ACCESS_POLICY):<\/p>\n

SQL> connect sys as sysdba
\nSQL> select * from system.payments;
\nPAYMENT_ID AMOUNT\u00a0\u00a0\u00a0\u00a0 USER_ID
\n---------- ---------- -------
\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 950 U1
\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1500 U1
\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 300 U2
\n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 875 U2<\/code><\/p>\n

Wr\u00f3\u0107my teraz do kwestii wydajno\u015bci. Ka\u017cde zapytanie skierowane do tabeli PAYMENTS powoduje obecnie uruchomienie funkcji FGAC_FUN, kt\u00f3ra dynamicznie generuje tre\u015b\u0107 predykatu selekcji. To istotny narzut obliczeniowy – 1 000 000 zapyta\u0144 spowoduje 1 000 000 wywo\u0142a\u0144\/wykona\u0144 funkcji PL\/SQL! Oto jak wygl\u0105da\u0142yby wska\u017aniki wydajno\u015bciowe dla powt\u00f3rzonego milion razy zapytania „SELECT COUNT(*) FROM PAYMENTS” (serwer Oracle Database 12.1.0.2, RHEL 7):<\/p>\n

SQL ordered by CPU Time
\nCPU Time(s) Executions SQL Text
\n76.23\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1,000,000\u00a0 SELECT COUNT(*) FROM SYSTEM.PAYMENTS
\n33.11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1,000,000\u00a0 begin :con := \"FGAC_FUN\"(:sn, ...<\/code><\/p>\n

Zauwa\u017cmy, \u017ce w tym prostym przyk\u0142adzie czas wykonywania funkcji FGAC_FUN stanowi prawie po\u0142ow\u0119 czasu (33s) wykonania zapyta\u0144 (76s)! To bardzo du\u017ca rozrzutno\u015b\u0107…
\nCo z tym mo\u017cemy zrobi\u0107? Na przyk\u0142ad mo\u017cemy skorzysta\u0107 z technik automatycznego buforowania wynik\u00f3w funkcji generuj\u0105cej predykaty selekcji. FGAC oferuje nast\u0119puj\u0105ce poziomy buforowania [https:\/\/docs.oracle.com\/database\/121\/DBSEG\/vpd.htm<\/a>]:<\/p>\n