公众号：杨建荣的学习笔记 · 作者：笔记48天 · 发布：2014-04-20 12:47:45 · 原文链接

系统中有个需求，需要把一个表里的信用卡号字段进行权限管理，大家讨论再三，说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.