首页 » ORACLE 9i-23c » ORACLE VPD ROW-LEVEL MARKING..
ORACLE VPD ROW-LEVEL MARKING..
VPD Virtual Private Database
ORACLE安全方面的控制，例如VIEW ,但是比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;
查询Application context
select * from dba_context;
Create Security Policies
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
ADD_POLICY Procedure
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用户不受限制。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。