SELECT * FROM sys.user_policies t; --安全性规则表
--以V_REGION为例
--控制规则函数filter_policy_sql.v_region_s
--首先调用 FILTER_POLICY_SQL.SET_USER_FILTER判断其ORG权限及买卖权限
--首先判断用户是否在SEC_USER_GROUP中,如果不在其为超级用户
SELECT * FROM sec_user_group sug WHERE sug.user_id = 'CMX';
SELECT * FROM sec_user_group sug WHERE sug.user_id = 'LN10370032';
--then the user is associated to a group that is not associated to a hierarchy
--and is therefore a superuser.
/*
FILTER_GROUP_ORG ORG权限表,如果没有则为超级用户.对比CMX与用户LN10370032
--This table contains the Organization Hierarchy LOV filtering access
--information for a User Security Group
O--组织单位 T转移实体 CODE TYPE FLOW
*/
SELECT * FROM filter_group_org fgo WHERE fgo.sec_group_id = '1001';
SELECT * FROM filter_group_org fgo WHERE fgo.sec_group_id = '1100';
/*
FILTER_GROUP_MERCH 买卖权限
--This table contains the Merchandise Hierarchy LOV filtering access
--information for a User Security.
*/
SELECT * FROM filter_group_merch fgm WHERE fgm.sec_group_id = '1001';
SELECT * FROM filter_group_merch fgm WHERE fgm.sec_group_id = '1100';
--回到V_REGION安全性规则
--V_REGION由V_DISTRICT决定
--filter_org_level决定了权限控制的级别 filter_org_id 为此级别的ID
--此项目并未对C A R D进行控制,所以都可以访问
select 1 from v_district
where
exists
(select 'X'
from filter_group_org fgo, sec_user_group sug
where /*sug.user_id = 'LN10370032'
and */sug.group_id = fgo.sec_group_id
and fgo.filter_org_level in ('C', 'A', 'R', 'D')
and v_district.chain = DECODE(fgo.filter_org_level, 'C', fgo.filter_org_id, v_district.chain)
and v_district.area = DECODE(fgo.filter_org_level, 'A', fgo.filter_org_id, v_district.area)
and v_district.region = DECODE(fgo.filter_org_level, 'R', fgo.filter_org_id, v_district.region)
and v_district.district = DECODE(fgo.filter_org_level, 'D', fgo.filter_org_id, v_district.district)
--以V_REGION为例
--控制规则函数filter_policy_sql.v_region_s
--首先调用 FILTER_POLICY_SQL.SET_USER_FILTER判断其ORG权限及买卖权限
--首先判断用户是否在SEC_USER_GROUP中,如果不在其为超级用户
SELECT * FROM sec_user_group sug WHERE sug.user_id = 'CMX';
SELECT * FROM sec_user_group sug WHERE sug.user_id = 'LN10370032';
--then the user is associated to a group that is not associated to a hierarchy
--and is therefore a superuser.
/*
FILTER_GROUP_ORG ORG权限表,如果没有则为超级用户.对比CMX与用户LN10370032
--This table contains the Organization Hierarchy LOV filtering access
--information for a User Security Group
O--组织单位 T转移实体 CODE TYPE FLOW
*/
SELECT * FROM filter_group_org fgo WHERE fgo.sec_group_id = '1001';
SELECT * FROM filter_group_org fgo WHERE fgo.sec_group_id = '1100';
/*
FILTER_GROUP_MERCH 买卖权限
--This table contains the Merchandise Hierarchy LOV filtering access
--information for a User Security.
*/
SELECT * FROM filter_group_merch fgm WHERE fgm.sec_group_id = '1001';
SELECT * FROM filter_group_merch fgm WHERE fgm.sec_group_id = '1100';
--回到V_REGION安全性规则
--V_REGION由V_DISTRICT决定
--filter_org_level决定了权限控制的级别 filter_org_id 为此级别的ID
--此项目并未对C A R D进行控制,所以都可以访问
select 1 from v_district
where
exists
(select 'X'
from filter_group_org fgo, sec_user_group sug
where /*sug.user_id = 'LN10370032'
and */sug.group_id = fgo.sec_group_id
and fgo.filter_org_level in ('C', 'A', 'R', 'D')
and v_district.chain = DECODE(fgo.filter_org_level, 'C', fgo.filter_org_id, v_district.chain)
and v_district.area = DECODE(fgo.filter_org_level, 'A', fgo.filter_org_id, v_district.area)
and v_district.region = DECODE(fgo.filter_org_level, 'R', fgo.filter_org_id, v_district.region)
and v_district.district = DECODE(fgo.filter_org_level, 'D', fgo.filter_org_id, v_district.district)
and rownum = 1)
依次类推可以知道控制数据权限的规则,甚至可以自己制定权限