虛擬專用資料庫VPD應用

系統中有個需求，需要把一個表裡的信用卡號欄位進行許可權管理，大家討論再三，說TDE不太好，因為需求希望能夠讓有些使用者可以訪問這個表，但是卡號欄位讀不到東西。有些使用者可以直接讀取該欄位的內容。

 

我給了一個建議，使用VPD,基於列的VPD可以滿足這樣的需求。

以下是一個樣例，供參考。

 

--create user and grant privileges

create user test identified by test;

grant connect,resource to test;

grant execute dbms_rls to test;

 

create user credit_test identified by oracle;

grant connect,resource to credit_test;

grant create synonym to credit_test;

 

--create samples tables

SQL> create table credit_card_test as select object_id card_id,object_name card_name,object_id card_pwd from all_objects ;

 

Table created.

 

SQL> select count(*) from all_objects;

 

  COUNT(*)

----------

      4876

 

 

SQL> select * from credit_card_test where rownum<10;

 

   CARD_ID CARD_NAME                        CARD_PWD

---------- ------------------------------ ----------

       258 DUAL                                  258

       259 DUAL                                  259

       311 SYSTEM_PRIVILEGE_MAP                  311

       313 SYSTEM_PRIVILEGE_MAP                  313

       314 TABLE_PRIVILEGE_MAP                   314

       316 TABLE_PRIVILEGE_MAP                   316

       317 STMT_AUDIT_OPTION_MAP                 317

       319 STMT_AUDIT_OPTION_MAP                 319

       605 MAP_OBJECT                            605

 

9 rows selected.

 

SQL> desc credit_card_test

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

CARD_ID                                   NOT NULL NUMBER

CARD_NAME                                 NOT NULL VARCHAR2(30)

CARD_PWD                                  NOT NULL NUMBER

 

 

--create synonyms or roles using credit_test

create or replace synonym credit_card_test for test.credit_card_test;

 

 

--using test(table owner account)

--add_policy  create_rls.sql

exec  dbms_rls.add_policy(object_schema => 'test',object_name => 'credit_card_test',policy_name => 'card_pwd_policy',function_schema =>'test',policy_function => 'rls_encryption',statement_types =>'select',policy_type => dbms_rls.CONTEXT_SENSITIVE,sec_relevant_cols=>'card_pwd',sec_relevant_cols_opt=>dbms_rls.all_rows);

 

--add function  create_rls_f.sql

create or replace function rls_encryption (p_owner in varchar2,p_obj in varchar2)

return varchar2

is  v_flag varchar2(1000);

begin

if(p_owner=USER ) then

v_flag:=null;

else

v_flag:='1=2';

end if;

return v_flag;

end;

 

--drop policy  drop_rls.sql

exec  dbms_rls.drop_policy(object_schema => 'test',object_name => 'credit_card_test',policy_name => 'card_pwd_policy');

 

 

SQL> @drop_rls.sql

 

PL/SQL procedure successfully completed.

 

SQL> @create_rls.sql

 

PL/SQL procedure successfully completed.

 

SQL> @create_rls_f.sql

 

Function created.

 

SQL> conn test/test

seConnected.

SQL> lect * from credit_card_test where rownum<10;

 

   CARD_ID CARD_NAME                        CARD_PWD

---------- ------------------------------ ----------

       258 DUAL                                  258

       259 DUAL                                  259

       311 SYSTEM_PRIVILEGE_MAP                  311

       313 SYSTEM_PRIVILEGE_MAP                  313

       314 TABLE_PRIVILEGE_MAP                   314

       316 TABLE_PRIVILEGE_MAP                   316

       317 STMT_AUDIT_OPTION_MAP                 317

       319 STMT_AUDIT_OPTION_MAP                 319

       605 MAP_OBJECT                            605

 

9 rows selected.

 

SQL> conn credit_test/oracle

Connected.

SQL> select * from credit_card_test where rownum<10;

 

   CARD_ID CARD_NAME                        CARD_PWD

---------- ------------------------------ ----------

       258 DUAL

       259 DUAL

       311 SYSTEM_PRIVILEGE_MAP

       313 SYSTEM_PRIVILEGE_MAP

       314 TABLE_PRIVILEGE_MAP

       316 TABLE_PRIVILEGE_MAP

       317 STMT_AUDIT_OPTION_MAP

       319 STMT_AUDIT_OPTION_MAP

       605 MAP_OBJECT

 

9 rows selected.