ここで紹介する例は大幅に簡略化されていますが、ソリューションで使用される必要なツールは包括的に提供されています。 Oracle 11g R2でテスト済み。
SCOTTユーザーが一部のテーブルのデータへのアクセス制限を整理する必要があるが、アクセスを許可する基準が制限されているため、制限されたテーブルのデータを分析する必要があるとします。 それは簡単かもしれないと思われる-行こう。
SCOTTに代わってデータベースへの接続を確立し、テーブルを作成します。テーブルの1つにはアクセス権を持つユーザーのリストが含まれ、2番目にはデータ自体が含まれます。
CREATE TABLE scott.user_allowed ( ua_id NUMBER, oracle_user VARCHAR2(50) ); Table created. insert into scott.user_allowed (ua_id, oracle_user) values (1, 'SCOTT'); insert into scott.user_allowed (ua_id, oracle_user) values (2, 'OTHERUSER'); 1 row created. 1 row created. commit; CREATE TABLE scott.user_data ( ud_id NUMBER, ud_user_id NUMBER, ud_data VARCHAR2(2000) ); Table created. insert into scott.user_data (ud_id, ud_user_id, ud_data) values (1, 1, 'SCOTT DATA'); insert into scott.user_data (ud_id, ud_user_id, ud_data) values (2, 2, 'OTHERUSER DATA'); 1 row created. 1 row created. commit; select ud_id, ud_user_id, ud_data from user_data; 2 rows selected.
すべてがうまくいった。 次に、 SYSに代わって接続を確立し、述語関数とセキュリティポリシーを作成します。
CREATE OR REPLACE FUNCTION sys.scott_predicate_allow ( obj_schema IN VARCHAR2, obj_name IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN return 'ud_id in (SELECT ud_id FROM scott.user_data ud, scott.user_allowed ua WHERE ud.ud_user_id = ua.ua_id AND upper(ua.oracle_user) = upper(USER))'; END scott_predicate_allow; / Function created.
BEGIN SYS.DBMS_RLS.ADD_POLICY ( Object_schema => 'SCOTT' ,Object_name => 'USER_DATA' ,policy_name => 'SCOTT_ALLOW_USER_DATA' ,function_schema => 'SYS' ,policy_function => 'SCOTT_PREDICATE_ALLOW' ,statement_types => 'SELECT,INSERT,UPDATE,DELETE,INDEX ' ,policy_type => dbms_rls.dynamic ,long_predicate => FALSE ,sec_relevant_cols => 'UD_ID,UD_USER_ID,UD_DATA' ,sec_relevant_cols_opt => NULL ,update_check => FALSE ,enable => TRUE ); END; / PL/SQL procedure successfully completed.
以上で、 SCOTTユーザーは、 USER_DATAテーブルにアクセスするときに、 USER_ALLOWEDテーブルの設定に従って、 ud_user_id列に自分に属するデータのみを表示します。 これを確認するために残り、再びSCOTTとしてログインして、
select ud_id, ud_user_id, ud_data from user_data; ORA-28113: policy predicate has error
エラーが何であるかを判断するには、詳細な説明を含むトレースファイルを表示する必要があります。
ORA-28108: circular security policies detected
実際、私はそれをトリガーの突然変異のアナログと呼びます。 この問題を解決するには、...を使用できます。練習で示したように、それほど多くはありません。 必要に応じて、自分で試してみてください。ただし、実際のタスクは上記の例とは大きく異なることを考慮してください。 SYSに代わって接続に戻り、実行してみましょう。
create or replace type sys.number_table is table of number; / Type created. CREATE OR REPLACE PACKAGE SYS.SCOTT_ALLOWED AS allowed_id number_table:=number_table(); function get_allowed_id return number_table; END SCOTT_ALLOWED; / Package created. CREATE OR REPLACE PACKAGE BODY SYS.SCOTT_ALLOWED AS function get_allowed_id return number_table is begin return allowed_id; end; END SCOTT_ALLOWED; / Package body created. CREATE OR REPLACE FUNCTION sys.scott_predicate_allow ( obj_schema IN VARCHAR2, obj_name IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN scott_allowed.allowed_id:=number_table(); select ud_id bulk collect into scott_allowed.allowed_id from scott.user_data ud, scott.user_allowed ua where ud.ud_user_id = ua.ua_id and upper(ua.oracle_user) = upper(USER); return 'EXISTS (SELECT 1 FROM TABLE(scott_allowed.get_allowed_id) a WHERE a.column_value = ud_id)'; END scott_predicate_allow; / Function created.
最初に行われたのは、 NUMBER型の単一列テーブルを反映する新しいデータ型を宣言することでした。 このタイプは、識別子のリストが選択されるパッケージ変数に使用され、それによってアクセスが許可されます。 パッケージ内の関数は、データをコールバックできるラッパーです。 セッションごとに個別のメモリ領域が割り当てられるため、セッション間でパッケージ変数の内容に問題はありません。 述語関数のカーソルの目的がテーブルの主キーの識別子を決定することであると仮定すると、生成された制約をEXISTSコンストラクトに変更することをお勧めします。
次に、 SCOTTに代わって接続することによって行われた作業の結果を確認します。
select ud_id, ud_user_id, ud_data from user_data; 1 row selected.