User Dashboard Permission Query

Version

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.

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.

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.