dbms_rls.add_policy访问控制

1、场景说明：

      以前的一套数据分发环境，将省局的数据通过GoldenGate过滤分发到相应的地市，通过 “纳税人代码” 过滤分发后，各个地市只能得到自己地市的数据。例如：纳税人代码以13701开头，则表示A地市的数据，只会分发到A地市；纳税人代码以13704开头，则表示B地市的数据，只会分发到B地市。

      目前，由于业务重组，该GoldenGate分发环境将被停止使用，打算给各地市创建一个帐号，直接连接省局进行数据访问，同样要求各地市只能访问到本地市自己的数据。这个需求就涉及到了数据的访问控制。

 

2、在测试环境进行了简单测试，记录下来。

(1).准备业务用户及业务表，该业务表的sjgsdq字段，类似于纳税人代码，后期用来进行过滤，区分各个地市：
create user BK_HXZG identified by oracle123;
grant connect,resource to BK_HXZG;

create table BK_HXZG.DJ_NSRXX
(
nsrsbh VARCHAR2(20) not null,
sjgsdq CHAR(11) not null,
zgswj_dm CHAR(11)
);

业务表中插入数据，模拟三个地市，每个地市各一条记录：
insert into BK_HXZG.dj_nsrxx (NSRSBH, SJGSDQ, ZGSWJ_DM) values ('370125306830381', '13701251600', '13701250000');
insert into BK_HXZG.dj_nsrxx (NSRSBH, SJGSDQ, ZGSWJ_DM) values ('370302640709031', '13704021500', '13705020000');
insert into BK_HXZG.dj_nsrxx (NSRSBH, SJGSDQ, ZGSWJ_DM) values ('370305577771555', '13705052100', '13705050000');
commit;

(2).为各个地市创建连接用户：
create user U_13701 identified by oracle1234;
grant connect to U_13701;
grant select on BK_HXZG.dj_nsrxx to U_13701;

create user U_13704 identified by oracle1234;
grant connect to U_13704;
grant select on BK_HXZG.dj_nsrxx to U_13704;

create user U_13705 identified by oracle1234;
grant connect to U_13705;
grant select on BK_HXZG.dj_nsrxx to U_13705;

(3).创建SPMON.PKG_VPD_SECURITY，并赋权，主要作用是为各地市的连接用户设置CONTEXT属性
create user SPMON identified by oracle1234;
grant connect, resource to SPMON;

CREATE OR REPLACE PACKAGE SPMON.PKG_VPD_SECURITY IS
PROCEDURE SET_VPD_CONTEXT;
END PKG_VPD_SECURITY;
/

CREATE OR REPLACE PACKAGE BODY SPMON.PKG_VPD_SECURITY IS
PROCEDURE SET_VPD_CONTEXT IS
V_USER VARCHAR2(30);
BEGIN
V_USER := SYS_CONTEXT('USERENV', 'SESSION_USER');

IF V_USER = 'U_13701' THEN
DBMS_SESSION.SET_CONTEXT('CTX_VPD_SECURITY', 'VPD_VALUE', '13701');
ELSIF V_USER = 'U_13704' THEN
DBMS_SESSION.SET_CONTEXT('CTX_VPD_SECURITY', 'VPD_VALUE', '13704');
ELSIF V_USER = 'U_13705' THEN
DBMS_SESSION.SET_CONTEXT('CTX_VPD_SECURITY', 'VPD_VALUE', '13705');
ELSE
DBMS_SESSION.SET_CONTEXT('CTX_VPD_SECURITY', 'VPD_VALUE', NULL);
END IF;

END SET_VPD_CONTEXT;
END PKG_VPD_SECURITY;
/

GRANT EXECUTE ON SPMON.PKG_VPD_SECURITY TO PUBLIC;

 

(4).创建CONTEXT:
CREATE CONTEXT CTX_VPD_SECURITY USING SPMON.PKG_VPD_SECURITY;

(5).创建trig_logon触发器，主要的目的是当用户登录数据库时，为该用户设置CONTEXT属性
create or replace trigger trig_logon
after logon on database
begin
SPMON.PKG_VPD_SECURITY.SET_VPD_CONTEXT;
end;
/

(6).创建访问控制的配置表
后期创建访问控制策略的业务表，都需要加入到该配置表中，访问控制策略函数会从该配置表中获取业务表名，以及策略控制的过滤字段名。如果业务表未加入到配置表中，则业务表返回的记录数为0。
create table SPMON.VPD_TABLES
(
table_owner VARCHAR2(30) not null,
table_name VARCHAR2(30) not null,
vpd_column VARCHAR2(30) not null,
constraint PK_VPD_TABLES primary key (TABLE_OWNER, TABLE_NAME)
)
organization index;

 

(7).创建访问控制策略函数：
CREATE OR REPLACE FUNCTION SPMON.USER_DATA_SECURITY(OWNER VARCHAR2,
TABNAME VARCHAR2)
RETURN VARCHAR2 IS
V_VPD_COLUMN VARCHAR2(30);
V_VPD_VALUE VARCHAR2(100);
V_RETURN VARCHAR2(2000);
BEGIN

BEGIN
SELECT TRIM(VPD_COLUMN)
INTO V_VPD_COLUMN
FROM SPMON.VPD_TABLES T
WHERE T.TABLE_OWNER = OWNER
AND T.TABLE_NAME = TABNAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_RETURN := '1=2';
RETURN V_RETURN;
END;

V_VPD_VALUE := TRIM(SYS_CONTEXT('CTX_VPD_SECURITY', 'VPD_VALUE'));
V_RETURN := V_VPD_COLUMN || ' LIKE ''' || V_VPD_VALUE || '%''';

RETURN V_RETURN;
END USER_DATA_SECURITY;
/

(8).针对某张业务表，创建访问控制：
配置表中插入信息：
insert into SPMON.VPD_TABLES (TABLE_OWNER, TABLE_NAME, VPD_COLUMN) values ('BK_HXZG', 'DJ_NSRXX', 'SJGSDQ');
commit;

begin
dbms_rls.add_policy(object_schema => 'BK_HXZG',
object_name => 'DJ_NSRXX',
policy_name => 'POLICY_DJ_NSRXX',
function_schema => 'SPMON',
policy_function => 'USER_DATA_SECURITY',
statement_types => 'SELECT, INSERT, UPDATE, DELETE',
enable => TRUE,
policy_type => DBMS_RLS.CONTEXT_SENSITIVE);
end;
/

(9). 测试：

SQL> conn u_13701/oracle1234
Connected.
SQL> select * from BK_HXZG.DJ_NSRXX;

NSRSBH SJGSDQ ZGSWJ_DM
-------------------- ----------- -----------
370125306830381 13701251600 13701250000

SQL> conn u_13704/oracle1234
Connected.
SQL> select * from BK_HXZG.DJ_NSRXX;

NSRSBH SJGSDQ ZGSWJ_DM
-------------------- ----------- -----------
370302640709031 13704021500 13705020000

SQL> conn u_13705/oracle1234
Connected.
SQL> select * from BK_HXZG.DJ_NSRXX;

NSRSBH SJGSDQ ZGSWJ_DM
-------------------- ----------- -----------
370305577771555 13705052100 13705050000

SQL>
SQL> conn / as sysdba
Connected.
SQL> select * from BK_HXZG.DJ_NSRXX;

NSRSBH SJGSDQ ZGSWJ_DM
-------------------- ----------- -----------
370125306830381 13701251600 13701250000
370302640709031 13704021500 13705020000
370305577771555 13705052100 13705050000

SQL>

可以看出，简单的访问控制已经实现。