Overview
Version
FineBI Version |
6.0 |
Functions
Query how many permissions a user has and who assigns the permissions.
Notes
1. This document takes the built-in FineDB database as an example. For SQL statements of external databases, you can modify as needed.
2. You need to first create a data connection for FineDB database in the FineBI platform. For details, see Introduction to FineDB.
Method One: Directly Query Through SQL Statements
Creating a Server Dataset
Choose Public Data > Add Dataset > SQL Dataset.
Operate as steps in the above figure.
The query statement is as follows.
SELECT*FROM (select
u.ID UserID,
u.USERNAME username,
u.REALNAME name,
rc.id roleID,
rc.NAME role ,
rd.id department ID,
d.NAME department
from FINE_USER u
left join FINE_USER_ROLE_MIDDLE rm on u.id=rm.userId
left join FINE_CUSTOM_ROLE rc on rm.ROLETYPE=2 and rm.ROLEID=rc.id
left join FINE_DEP_ROLE rd on rm.ROLETYPE=1 and rm.ROLEID=rd.id
left join FINE_DEPARTMENT d on d.id=rd.DEPARTMENTID
left join FINE_POST p on p.id=rd.POSTID
where 1=1
${if(len(user) == 0,"","and u.ID ='"+user+"' ") }) A
JOIN (
select
F.roleid,
case when f.AUTHORITYTYPE='1' then 'view permission'
when f.AUTHORITYTYPE='2' then 'authorize permission'
when f.AUTHORITYTYPE='3' then 'edit permission'
when f.AUTHORITYTYPE='4' then 'data connection management permission'
when f.AUTHORITYTYPE='101' then 'FineReport template authentication permission'
when f.AUTHORITYTYPE='102' then 'FineReport template view permission'
when f.AUTHORITYTYPE='103' then 'FineReport template report permission'
when f.AUTHORITYTYPE='201' then 'business package use permission'
when f.AUTHORITYTYPE='202' then 'business package management permission'
when f.AUTHORITYTYPE='203' then 'BI report export permission'
when f.AUTHORITYTYPE='204' then 'BI dashboard share permission'
when f.AUTHORITYTYPE='205' then 'BI template authentication permission'
when f.AUTHORITYTYPE='206' then 'BI template view permission'
when f.AUTHORITYTYPE='207' then 'BI template export permission'
when f.AUTHORITYTYPE='208' then 'BI dashboard share role control permission'
when f.AUTHORITYTYPE='209' then 'BI dashboard share function permission'
when f.AUTHORITYTYPE='210' then 'BI dashboard public link function permission' end permission type,
case when f.ROLETYPE='1' then 'department'
when f.ROLETYPE='2' then 'role'
when f.ROLETYPE='3' then 'user'
when f.ROLETYPE='4' then 'position' end type,
a.ID,
a.DISPLAYNAME name,
a.SORTINDEX,
a.PARENTID PARENTID0,
b.PARENTID PARENTID1,
c.PARENTID PARENTID2,
d.PARENTID PARENTID3,
e.PARENTID PARENTID4,
case when a.EXPANDTYPE='1' then 'platform management system node'
when a.EXPANDTYPE='2' then 'platform'
when a.EXPANDTYPE='3' then 'directory'
when a.EXPANDTYPE='5' then 'link'
when a.EXPANDTYPE='6' then 'file'
when a.EXPANDTYPE='101' then 'report flow'
when a.EXPANDTYPE='102' then ' FineReport'
when a.EXPANDTYPE='201' then ' BI report'
end 权限目录类型,
case when a.DEVICETYPE='0' then 'untick '
when a.DEVICETYPE='1' then 'PC '
when a.DEVICETYPE='2' then 'tablet '
when a.DEVICETYPE='3' then 'PC、tablet '
when a.DEVICETYPE='4' then 'mobile phone '
when a.DEVICETYPE='5' then 'PC、mobile phone '
when a.DEVICETYPE='6' then 'tablet、mobile phone '
when a.DEVICETYPE='7' then 'PC 、tablet、mobile phone ' end applicable platform,
a.SORTINDEX sorting order
from FINE_AUTHORITY_OBJECT a
left join FINE_AUTHORITY_OBJECT b on a.PARENTID=b.id
left join FINE_AUTHORITY_OBJECT c on b.PARENTID=c.id
left join FINE_AUTHORITY_OBJECT d on c.PARENTID=d.id
left join FINE_AUTHORITY_OBJECT e on d.PARENTID=e.id
join FINE_AUTHORITY f on f.AUTHORITY=2 and (a.id=f.AUTHORITYENTITYID or a.PARENTID=f.AUTHORITYENTITYID or b.PARENTID=f.AUTHORITYENTITYID or c.PARENTID=f.AUTHORITYENTITYID
or d.PARENTID=f.AUTHORITYENTITYID or e.PARENTID=f.AUTHORITYENTITYID)
where not exists (select 1 from FINE_AUTHORITY f1 where f1.AUTHORITY=1 and (a.id=f1.AUTHORITYENTITYID or a.PARENTID=f1.AUTHORITYENTITYID or b.PARENTID=f1.AUTHORITYENTITYID or c.PARENTID=f1.AUTHORITYENTITYID or d.PARENTID=f1.AUTHORITYENTITYID or e.PARENTID=f1.AUTHORITYENTITYID) and f.roleid=f1.roleid and f.AUTHORITYTYPE=f1.AUTHORITYTYPE)
order by a.PARENTID,a.SORTINDEX) B ON A.用户ID=B.roleid OR A.role ID=B.roleid OR A.department ID=B.roleid
Creating a Dashboard
Choose My Analysis > Add Analysis Subject and add data.
Add a component
Change the Name field to Permission Name and the Type field to Assigned Permission Type Name.
Create a dashboard and preview.
Method Two: Dataset Processing
Process datasets like classfiying and merging according to data. The dashboard is the same as method one, except for the selection of dataset.
User Information Data Query
Establish user information based on user-related data. You can view the role information like Dept., Role, or Position.
User Information Dataset Processing
Due to the department and role being different types in the same table, there are multiple rows after query. You can create a dataset to group and merge them.
Permission Directory Data Query
Create a SQL dataset based on permission information and its directory. You can view permission IDs and directories.
The query statement is as follows.
select
F.roleid,
case when f.AUTHORITYTYPE='1' then 'view permission'
when f.AUTHORITYTYPE='2' then 'authorize permission'
when f.AUTHORITYTYPE='3' then 'edit permission'
when f.AUTHORITYTYPE='4' then 'data connection management permission'
when f.AUTHORITYTYPE='101' then 'FineReport template authentication permission'
when f.AUTHORITYTYPE='102' then 'FineReport template view permission'
when f.AUTHORITYTYPE='103' then 'FineReport template report permission'
when f.AUTHORITYTYPE='201' then 'business package use permission'
when f.AUTHORITYTYPE='202' then 'business package management permission'
when f.AUTHORITYTYPE='203' then 'BI report export permission'
when f.AUTHORITYTYPE='204' then 'BI dahboard share permission'
when f.AUTHORITYTYPE='205' then 'BI template authentication permission'
when f.AUTHORITYTYPE='206' then 'BI template view permission'
when f.AUTHORITYTYPE='207' then 'BI template export permission'
when f.AUTHORITYTYPE='208' then 'BI dashboard share role control permission'
when f.AUTHORITYTYPE='209' then 'BI dashboard share function permission'
when f.AUTHORITYTYPE='210' then 'BI dashboard public link function permission' end permission type,
case when f.ROLETYPE='1' then 'department'
when f.ROLETYPE='2' then 'custom role'
when f.ROLETYPE='3' then 'user'
when f.ROLETYPE='4' then 'position' end type,
a.ID,
a.DISPLAYNAME name,
a.SORTINDEX,
a.PARENTID PARENTID0,
b.PARENTID PARENTID1,
c.PARENTID PARENTID2,
d.PARENTID PARENTID3,
e.PARENTID PARENTID4,
case when a.EXPANDTYPE='1' then 'platform management system node'
when a.EXPANDTYPE='2' then 'homepage'
when a.EXPANDTYPE='3' then 'directory'
when a.EXPANDTYPE='5' then 'link'
when a.EXPANDTYPE='6' then 'file'
when a.EXPANDTYPE='101' then 'report flow'
when a.EXPANDTYPE='102' then ' FineReport'
when a.EXPANDTYPE='201' then ' BI report'
end permission directory type,
case when a.DEVICETYPE='0' then 'untick '
when a.DEVICETYPE='1' then 'PC '
when a.DEVICETYPE='2' then 'tablet '
when a.DEVICETYPE='3' then 'PC、tablet '
when a.DEVICETYPE='4' then 'mobile phone '
when a.DEVICETYPE='5' then 'PC、mobile phone '
when a.DEVICETYPE='6' then 'tablet、mobile '
when a.DEVICETYPE='7' then 'PC 、tablet、mobile phone ' end applicable platform,
a.SORTINDEX sorting order from PUBLIC.FINE_AUTHORITY_OBJECT a
left join FINE_AUTHORITY_OBJECT b on a.PARENTID=b.id
left join FINE_AUTHORITY_OBJECT c on b.PARENTID=c.id
left join FINE_AUTHORITY_OBJECT d on c.PARENTID=d.id
left join FINE_AUTHORITY_OBJECT e on d.PARENTID=e.id
join FINE_AUTHORITY f on f.AUTHORITY=2 and (a.id=f.AUTHORITYENTITYID or a.PARENTID=f.AUTHORITYENTITYID or b.PARENTID=f.AUTHORITYENTITYID or c.PARENTID=f.AUTHORITYENTITYID
or d.PARENTID=f.AUTHORITYENTITYID or e.PARENTID=f.AUTHORITYENTITYID)
where not exists (select 1 from FINE_AUTHORITY f1 where f1.AUTHORITY=1 and (a.id=f1.AUTHORITYENTITYID or a.PARENTID=f1.AUTHORITYENTITYID or b.PARENTID=f1.AUTHORITYENTITYID or c.PARENTID=f1.AUTHORITYENTITYID or d.PARENTID=f1.AUTHORITYENTITYID or e.PARENTID=f1.AUTHORITYENTITYID) and f.roleid=f1.roleid and f.AUTHORITYTYPE=f1.AUTHORITYTYPE)
order by a.PARENTID,a.SORTINDEX
User Permission Directory Dataset
Create a dataset of data permission congifured by user ID according to the user information dataset and permission directory query.
Role Permission Directory Dataset
Create a dataset of data permission configured by role ID according to user information dataset and permission directory query.
Department and Position Permission Directory Dataset
Create a dataset of data permission configured by department ID according to the user information dataset and permission directory query.
User Querying All Directory Datasets with Permission
User's all permissions are the combination of permissions assigned by User/Role/Dept./Position.
Creating a Dashboard
The method to create a dashboard is the same as method one, except for the selection of dataset. Here, User Querying All Permission Dataset is selected.
