包dbms_rls只适用oracle enterprise edition,他实现精细的访问控制;
并且精细的访问控制是通过sql语句中动态增加谓词(where 子句)实现的;
可以使不同的数据库用户执行相同的sql语句,操作同一张表上的不同数据;
– 1,add_policy
/*
该过程用于给表,视图,或同义词增加一个安全策略;
当执行该操作结束后,会自动提交事务;
*/
dbms_rls.add_policy(object_schema => ,object_name => ,policy_name => ,
function_schema => ,policy_function => ,statement_types =>,
update_check => ,enable => ,static_policy =>
);
– 2,drop_policy
/*
该过程用于给表,视图,或同义词删除安全策略;
当执行该操作结束后,会自动提交事务;
*/
dbms_rls.drop_policy(object_schema => ,object_name => ,policy_name => );
– 3,refrsh_policy
/*
该过程用于刷新与安全策略修改相关的所有sql语句,并是oracle重新解析相关sql语句
当执行该操作结束后,会自动提交事务;
*/
dbms_rls.refresh_policy(object_schema => ,object_name => ,policy_name => );
– 4,enable_policy
/*
该过程用于激活或禁止特定的安全策略
当执行该操作结束后,会自动提交事务;
*/
dbms_rls.enable_policy(object_schema => ,object_name => ,policy_name => ,enable => );
– 5,create_policy_group
/*
该过程用于建立安全策略组
*/
dbms_rls.create_policy_group(
object_schema =>,object_name => ,policy_group =>
);
– 6,add_grouped_policy
/*
该过程用于增加与特定策略组相关的安全策略;
*/
dbms_rls.add_grouped_policy(
object_schema => ,object_name => ,
policy_group => ,policy_name =>
);
– 7,add_policy_context
/*
该过程用于为应用安全策略增加上下文;
*/
dbms_rls.add_policy_context(
object_schema =>,
object_name => ,
namespace => ,
attribute =>
);
– 8,delete_policy_group
/*
该过程用于删除安全策略分组
*/
dbms_rls.delete_policy_group(
object_schema => ,
object_name => ,
policy_group =>
);
– 9,drop_grouped_policy
/*
该过程删除特定策略组的安全策略
*/
dbms_rls.drop_grouped_policy(
object_schema => ,
object_name => ,
policy_group => ,
policy_name =>
);
– 10,drop_policy_context
/*
该过程删除上下文
*/
dbms_rls.drop_policy_context(
object_schema => ,
object_name => ,
namespace => ,
attribute =>
);
– 11,enable_grouped_policy
/*
该过程激活或禁止特定策略组的安全策略;
*/
dbms_rls.enable_grouped_policy(
object_schema => ,
object_name => ,
group_name => ,
policy_name => ,
enable =>
);
– 12,refrsh_grouped_policy
/*
该过程刷新特定策略组的安全策略的相关sql语句,并重新解析sql语句;
*/
dbms_rls.refresh_grouped_policy(
object_schema => ,
object_name => ,
group_name => ,
policy_name =>
);
/*
使用dbms_rls实现精细访问控制;
不同用户只能访问不同部门的员工;
*/
–1,建立应用上下文
create or replace context cz_emp using scott.ctx;
–2,建立包过程设置的应用上下文
create or replace package scott.ctx
as
procedure set_depno;
end;
create or replace package body scott.ctx as
procedure set_depno
is
no number(6);
begin
if sys_context('cz_emp','session_user')='test' then
dbms_session.set_context('cz_emp','deptno',10);
else if sys_context('cz_emp','session_user')='system' then
dbms_session.set_context('cz_emp','deptno',20);
else
dbms_session.set_context('cz_emp','deptno',30);
end if;
end;
end scott.ctx;
--3,建立登陆触发器
create or replace trigger tri_login
after logon on database
call scott.ctx.set_depno
–4,建立策略函数
create or replace package scott.emp_security as
function emp_sec(p1 varchar2,p2 varchar2 ) return varchar2;
end;
create or replace package body scott.emp_security as
function emp_sec(p1 varchar2,p2 varchar2 ) return varchar2
is
v_date varchar2(2000);
begin
if user not in (‘SYS’,’SCOTT’) then
v_date := ’ depno = sys_context(”cz_emp”,”deptno”)’;
return v_date;
end if;
return ‘1=1’;
end;
end ;
–5,增加策略
begin
dbms_rls.add_policy(‘SCOTT’,’EMP’,’emp_policy’,’SCOTT’,’scott.emp_security.emp_sec’,’select’);
end;
select deptno,ename from scott.emp [where ….];