User Group & Rights - Reporting Dashboards - Dashboard User Group Privileges

[Audrey_Van_Land]

Is there a report or a query that can be used to report on user groups that have the Dashboard Access Privileges set?  I'm not seeing this listed in the User Group / Right Configuration Report created from inside of the Configuration Module.

I need to be able to identify and or report on user groups that have the ability to be able to access the Administration areas for Reporting Dashboards, Categories, Dashboards, Reports.

[Barbara_Ebel-La]

Here's some SQL if you'd like to make a dashboard dashboard 🙂

select distinct trim(ua.username) "UserName",
trim(ua.realname) as "RealName",
'Dashboards - Granted by Username' AS "AccessArea",
trim(di.dashboardname) as "Access",
ua.usernum as "UserNum"
from hsi.dashboardxuser dxu
join hsi.useraccount ua on ua.usernum = dxu.usernum
join hsi.dashboardinfo di on di.dashboardnum = dxu.dashboardnum
where trim(upper(ua.username)) = @{USERNAME}
and dxu.usertype = 0
UNION
select distinct trim(ua.username) "UserName",
trim(ua.realname) as "RealName",
'Dashboards - Granted by Usergroup - '|| trim(usergroupname) AS "AccessArea",
trim(di.dashboardname) "Access",
ua.usernum as "UserNum"
from hsi.dashboardxuser dxu
join hsi.usergroup ug on ug.usergroupnum = dxu.usernum
join HSI.USERXUSERGROUP uxg ON uxg.USERgroupnum = ug.usergroupnum
join hsi.useraccount ua on ua.usernum = uxg.usernum
join hsi.dashboardinfo di on di.dashboardnum = dxu.dashboardnum
where trim(upper(ua.username)) = @{USERNAME}
and dxu.usertype = 1

[Audrey_Van_Land]

@Barbara Ebel , this is a great query!  However it doesn't quite get me the information that I need.  I need to be able to identify and or report on user groups that have the ability to be able to access the Administration areas for Reporting Dashboards, Categories, Dashboards, Reports. 

[Larissa_Armand · Accepted solution]

Audrey,

That information is in the table DASHBOARDPRIVS. 

PRIVILEGE0
BIT	Override Privilege	Value
1	User Access	1
2	Create categories	2
3	Create dashboards	4
4	Create data providers	8
5	Configure External Data Sources	16
6	Create reports	32
7	Author SQL data providers	64
8		128
9		256
10		512
11		1024
12		2048
13	Administrator Access	4096

If you are using an Oracle database, you could query this like: 

select trim(G.USERGROUPNAME),
case when bitand(P.PRIVILEGE0, 1) >0 then 'X' end as UserAccess,
case when bitand(P.PRIVILEGE0, 4096) >0 then 'X' end as AdminAccess
from DASHBOARDPRIVS P, USERGROUP G
where P.USERGROUPNUM = G.USERGROUPNUM
and P.USERGROUPNUM = '1602'
;

[Audrey_Van_Land]

Lifesaver Larissa!