|
我们可以通过【管理系统】查看单个人员对应的目录权限，并且无法导出，但是我们可以通过FineDB表结构以及表之间的关系使用SQL导出，以下是Oracle 语法FineDB查询表与角色和人员关系的SQL语句
select id,
path,
parent2,
parent3,
parent4,
displayname,
nvl(max(authority_r), 'N') authority_r,
nvl(max(authority_w), 'N') authority_w,
role_name,
role_category,
realname,
username
from (select a.id,
cast(a.path as varchar(100)) path,
c.DISPLAYNAME parent2,
d.DISPLAYNAME parent3,
e.DISPLAYNAME parent4,
a.DISPLAYNAME,
FINE_AUTHORITY.AUTHORITY,
decode(cast(FINE_AUTHORITY.AUTHORITYTYPE as number), 1, 'Y', null) AUTHORITY_R,
decode(cast(FINE_AUTHORITY.AUTHORITYTYPE as number), 3, 'Y', null) AUTHORITY_W,
role_name,
role_category,
FINE_USER.REALNAME,
FINE_USER.USERNAME
from (select id,
path,
DISPLAYNAME,
substr(FULLPATH, instr(FULLPATH, '-_-', 1, 1) + 3,
instr(FULLPATH, '-_-', 1, 2) - 3 - instr(FULLPATH, '-_-', 1, 1)) p2,
case
when instr(FULLPATH, '-_-', 1, 3) > 0 then substr(FULLPATH, instr(FULLPATH, '-_-', 1, 2) + 3,
instr(FULLPATH, '-_-', 1, 3) - 3 -
instr(FULLPATH, '-_-', 1, 2))
else substr(FULLPATH, instr(FULLPATH, '-_-', 1, 2) + 3,
length(FULLPATH) - 2 - instr(FULLPATH, '-_-', 1, 2)) end p3,
case
when instr(FULLPATH, '-_-', 1, 3) > 0 then substr(FULLPATH, instr(FULLPATH, '-_-', 1, 3) + 3,
length(FULLPATH) - 2 -
instr(FULLPATH, '-_-', 1, 3)) end p4
from FINE_AUTHORITY_OBJECT
where 1=1
-- 报表模板路径
-- and path like '01/07/%'
-- 报表目录名称
-- and DISPLAYNAME like '%周期%'
) a
left join (select id,
DISPLAYNAME
from FINE_AUTHORITY_OBJECT) c
on a.p2 = c.id
left join (select id,
DISPLAYNAME
from FINE_AUTHORITY_OBJECT) d
on a.p3 = d.id
left join (select id,
DISPLAYNAME
from FINE_AUTHORITY_OBJECT) e
on a.p4 = e.id
left join (select id,
authority,
AUTHORITYTYPE,
AUTHORITYENTITYID,
ROLEID
from FINE_AUTHORITY) FINE_AUTHORITY
on a.ID = FINE_AUTHORITY.AUTHORITYENTITYID
left join (select id,
REALNAME,
UserName,
'' role_name,
'user' role_category
from FINE_USER
union
select distinct FINE_CUSTOM_ROLE.id,
FINE_USER.REALNAME,
FINE_USER.UserName,
FINE_CUSTOM_ROLE.name,
'role' role_category
from (select id,
name
from FINE_CUSTOM_ROLE) FINE_CUSTOM_ROLE
inner join (select id, ROLEID, UserID
from FINE_USER_ROLE_MIDDLE) FINE_USER_ROLE_MIDDLE
on FINE_CUSTOM_ROLE.id = FINE_USER_ROLE_MIDDLE.ROLEID
inner join (select id,
REALNAME,
UserName
from FINE_USER) FINE_USER
on FINE_USER_ROLE_MIDDLE.userID = FINE_USER.id) FINE_USER
on FINE_AUTHORITY.ROLEID = FINE_USER.ID) dw_role
where (AUTHORITY_R is not null or AUTHORITY_W is not null)
and AUTHORITY = 2
group by id,
path,
parent2,
parent3,
parent4,
displayname,
role_name,
role_category,
realname,
username
order by parent2,
parent3,
parent4
|
