首页 » ORACLE 9i-23ai » ORACLE VPD ROW-LEVEL MARKING..
ORACLE VPD ROW-LEVEL MARKING..
Oracle Virtual Private Database (VPD) 是一种安全特性，允许在查询执行时动态地过滤结果，从而限制用户只能访问其有权访问的数据。VPD 主要通过在表上定义一个安全策略，并结合 PL/SQL 函数来实现行级安全控制。
行级脱敏的需求
行级脱敏通常是指在查询结果中隐藏敏感信息，只显示脱敏后的数据。这种需求在许多行业中都非常常见，尤其是在需要保护个人隐私信息（如姓名、身份证号码、银行卡号等）的情况下。
Oracle VPD 行级脱敏实现
要实现 Oracle VPD 的行级脱敏功能，可以通过以下几个步骤来进行：
创建一个视图或表：首先创建一个包含敏感数据的视图或表。
定义安全策略：使用 DBMS_RLS 包来定义一个安全策略，指定哪些列需要脱敏，并提供一个 PL/SQL 函数来实现脱敏逻辑。
编写脱敏函数：编写一个 PL/SQL 函数来实现具体的脱敏逻辑。该函数可以根据用户的权限或角色返回不同的数据。
应用策略：将定义好的安全策略应用到视图或表上。
VPD Virtual Private Database
ORACLE安全方面的控制，例如VIEW ,但是VPD比view控制更灵活。
CREATE VIEW VXX AS SELECT * FROM TXX WHERE XX
比如有个人员工资表，只想让HR的经理看到所有记录，其它部门经理只能看到自己部门的，先不讨论业务逻辑的合理的，目的就是同一张表让不同人看到不同的结果。
下面看我的实验
环境：表都是在HR SCHEMA,HRMAN HR经理，SYSDEPTMAN系统部经理
system@NCME>create user hr identified by hr;
User created.
system@NCME>grant connect,resource to hr;
Grant succeeded.
system@NCME>create user hrman identified by hrman;
User created.
system@NCME>grant connect,resource to hrman;
Grant succeeded.
system@NCME>create user sysdeptman identified by sysdeptman;
User created.
system@NCME>grant connect,resource to sysdeptman;
Grant succeeded.
sys@NCME>select * from hr.dept;
ID NAME DEPTNAME
---------- -------------------- --------------------
1 bkdcici HR
2 rgeankq HR
3 zkbstla HR
4 oxtzdbq HR
5 yctslvq HR
6 htbhqff SYS
7 beofrsy SYS
8 ixbmdyd SYS
9 ixtzghd SYS
10 sysdeptman SYS
11 hrman HR
11 rows selected.
sys@NCME>select * from hr.emp;
ID NAME SAL TEL
---------- -------------------- ---------- -----------
1 bkdcici 4656.82 138
2 rgeankq 4394.34 138
3 zkbstla 3139.03 138
4 oxtzdbq 3590.45 138
5 yctslvq 3909.43 138
6 htbhqff 4234.96 131
7 beofrsy 4237.33 131
8 ixbmdyd 4265.09 131
9 ixtzghd 3533.51 131
10 sysdeptman 10000 131
11 hrman 10000 138
11 rows selected.
system@NCME>conn hr/hr
Connected.
hr@NCME>grant select on emp to hrman;
Grant succeeded.
hr@NCME>grant select on emp to sysdeptman;
Grant succeeded.
hr@NCME>grant select on hr.dept to public;
Grant succeeded.
创建APPLICATION CONTEXT
sysdeptman@NCME>conn system/oracle
Connected.
system@NCME>grant create any context,create public synonym to hr;
Grant succeeded.
system@NCME>conn hr/hr
Connected.
hr@NCME>create or replace package context_pkg
2 is
3 procedure set_context;
4 end;
5 /
Package created.
hr@NCME>create or replace package body context_pkg
2 is
3 procedure set_context
4 is
5 v_user varchar2(30);
6 v_dept varchar2(30);
7 begin
8 v_user:=sys_context('USERENV','SESSION_USER');
9 dbms_session.set_context('VPD_SEC','CUR_USER',v_user);
10 begin
select deptname into v_dept from dept where upper(name)=v_user;
12 dbms_session.set_context('VPD_SEC','CUR_DEPT',v_dept);
13 exception
14 when NO_DATA_FOUND then
15 dbms_session.set_context('VPD_SEC','CUR_DEPT','ERR');
16 end;
17 end;
18 end;
19 /
Package body created.
hr@NCME>exec context_pkg.set_context;
BEGIN context_pkg.set_context; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "HR.CONTEXT_PKG", line 8
ORA-06512: at line 1
hr@NCME>create context VPD_SEC using context_pkg;
Context created.
hr@NCME>exec context_pkg.set_context;
PL/SQL procedure successfully completed.
hr@NCME>COL VA FOR A20
hr@NCME>select sys_context('VPD_SEC','CUR_USER') VA FROM DUAL;
VA
--------------------
HR
hr@NCME>create public synonym CONTEXT_PKG for CONTEXT_PKG;
Synonym created.
hr@NCME>grant execute on context_pkg to public;
Grant succeeded.
hr@NCME>conn hrman/hrman
Connected.
hrman@NCME>exec context_pkg.set_context;
PL/SQL procedure successfully completed.
hrman@NCME>select sys_context('VPD_SEC','CUR_DEPT') VA FROM DUAL;
VA
--------------------
HR
10G语法
DBMS_SESSION.SET_CONTEXT ( namespace VARCHAR2, attribute VARCHAR2, value VARCHAR2, username VARCHAR2, client_id VARCHAR2 );
Parameters:
namespace The namespace of the application context to be set, limited to 30 bytes.
attribute The attribute of the application context to be set, limited to 30 bytes.
value The value of the application context to be set, limited to 4 kilobytes.
username The database username attribute of the application context.Default: NULL
client_id The application-specific client_id attribute of the application context (64-byte maximum).default: NULL
NOTE:
can only be called within the package to which it belongs, If you try to execute DBMS_SESSION.SET_CONTEXT ,
you will get an error, as shown here: ORA-01031: insufficient privileges
SET_CONTEXT的NAMSPACE命名空间的值必须和context NAME一致否则也是报错ORA-01031: insufficient privileges
context的创建是USING 指定一个PACKAGE OR PROCEDURE;
--CHECK Application context select * from dba_context;
编写脱敏函数
hr@NCME>create or replace package sec_hr is
2 function emp_select(owner varchar2,objname varchar2)
3 return varchar2;
4 end;
5 /
hr@NCME>create or replace package body sec_hr
is
function emp_select(owner varchar2,objname varchar2)
return varchar2
is
wherecase varchar2(2000);
begin
wherecase :='0=1';
if(sys_context('VPD_SEC','CUR_USER')='HRMAN') then
wherecase:=null;
elsif (sys_context('VPD_SEC','CUR_DEPT')!='ERR') then
wherecase:='id in(select id from dept where deptname=sys_context(''VPD_SEC'',''CUR_DEPT''))';
end if;
return wherecase;
end;
end;
/
Package body created.
hr@NCME>grant execute on sec_hr to public;
Grant succeeded.
hr@NCME>create public synonym sec_hr for sec_hr;
Synonym created.
定义安全策略
system@NCME>begin
dbms_rls.add_policy('HR','EMP','MY_EMP_SELECT_POLICY',
'HR','SEC_HR.EMP_SELECT',
'SELECT',TRUE,TRUE);
END;
PL/SQL procedure successfully completed.
system@NCME>conn hr/hr
Connected.
hr@NCME>select * from emp;
no rows selected
hr@NCME>conn hrman/hrman
Connected.
hrman@NCME>exec context_pkg.set_context
PL/SQL procedure successfully completed.
hrman@NCME>select * from hr.emp;
ID NAME SAL TEL
---------- -------------------- ---------- -----------
1 bkdcici 4656.82 138
2 rgeankq 4394.34 138
3 zkbstla 3139.03 138
4 oxtzdbq 3590.45 138
5 yctslvq 3909.43 138
6 htbhqff 4234.96 131
7 beofrsy 4237.33 131
8 ixbmdyd 4265.09 131
9 ixtzghd 3533.51 131
10 sysdeptman 10000 131
11 hrman 10000 138
11 rows selected.
hrman@NCME>conn sysdeptman/
Enter password:
Connected.
sysdeptman@NCME>select * from hr.emp;
no rows selected
sysdeptman@NCME>exec context_pkg.set_context
PL/SQL procedure successfully completed.
sysdeptman@NCME>select * from hr.emp;
ID NAME SAL TEL
---------- -------------------- ---------- -----------
6 htbhqff 4234.96 131
7 beofrsy 4237.33 131
8 ixbmdyd 4265.09 131
9 ixtzghd 3533.51 131
10 sysdeptman 10000 131
DBMS_RLS.ADD_POLICY
This procedure adds a fine-grained access control policy to a table, view, or synonym. DBMS_RLS.ADD_POLICY ( object_schema IN VARCHAR2 NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2, function_schema IN VARCHAR2 NULL, policy_function IN VARCHAR2, statement_types IN VARCHAR2 NULL, update_check IN BOOLEAN FALSE, enable IN BOOLEAN TRUE, static_policy IN BOOLEAN FALSE, policy_type IN BINARY_INTEGER NULL, long_predicate IN BOOLEAN FALSE, sec_relevant_cols IN VARCHAR2, sec_relevant_cols_opt IN BINARY_INTEGER NULL);
如果不想每次设置exec context_pkg.set_context，建立一个数据库级trigger,as sysdba user
sysdeptman@NCME>conn / as sysdba
Connected.
sys@NCME>create or replace trigger tri_hr_sec
2 after logon on database
3 begin
4 hr.context_pkg.set_context;
5 end;
6 /
Trigger created.
sys@NCME>conn sysdeptman/sysdeptman
Connected.
sysdeptman@NCME>select * from hr.emp;
ID NAME SAL TEL
---------- -------------------- ---------- -----------
6 htbhqff 4234.96 131
7 beofrsy 4237.33 131
8 ixbmdyd 4265.09 131
9 ixtzghd 3533.51 131
10 sysdeptman 10000 131
查询代理策略
dba|all|user_policies;
如果因策略使用的函数或包补删除，再查询就会出现
ORA-28110: policy function or package HR.SEC_HR has error
删除代理策略
hr@NCME>select * from emp;
no rows selected
hr@NCME>drop package SEC_HR;
Package dropped.
hr@NCME>select * from emp;
select * from emp
*
ERROR at line 1:
ORA-28110: policy function or package HR.SEC_HR has error
hr@NCME>exec dbms_rls.drop_policy('HR','EMP','MY_EMP_SELECT_POLICY');
PL/SQL procedure successfully completed.
hr@NCME>select * from emp;
ID NAME SAL TEL
---------- -------------------- ---------- -----------
1 bkdcici 4656.82 138
2 rgeankq 4394.34 138
3 zkbstla 3139.03 138
4 oxtzdbq 3590.45 138
5 yctslvq 3909.43 138
6 htbhqff 4234.96 131
7 beofrsy 4237.33 131
8 ixbmdyd 4265.09 131
9 ixtzghd 3533.51 131
10 sysdeptman 10000 131
11 hrman 10000 138
11 rows selected.
note: sys用户不受限制。或授予 EXEMPT ACCESS POLICY 系统权限的用户可以豁免VPD控制。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。