About

This report is intended to provide you with a list of assigned staff permissions. You will be able to see at a glance which permissions are assigned to which staff members. Further, you will be able to tell if the user was assigned the permission as an individual, group, or both.

The first column specifies the permission. The second column shows the users for that permission. When a user is listed next to the permission, the permission type will appear next to the user. Individual permissions will show INDIVIDUAL.  Group permissions will show as the group name (example: 4. Supervisor). Sometimes a user may be shown twice next to the permission because the user has been assigned the permission both as an individual and as a group. So, if a user has both individual and group permissions assigned to the same permission, then that user will have access to that permission until BOTH individual and group permissions are removed.

This grid will also assist in showing if a user is missing specific permission when a function in OP requires multiple permissions. For example, if a function requires a user to have permissions A, B, and C and the user has only permissions A and B, then the user will not have access to the specific function in the software. The grid will allow you to look at all the necessary permissions and determine which permission the user is missing so it can be assigned.

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

• When the results display in the Database Viewer, you will need to expand the USERS column to be able to see the complete list of users.
• It may be easier to export this list as a .csv file so it can be viewed in a spreadsheet.

SQL Code: Firebird

To highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

with alldata as
(select * from
(select username, permission, groupname as source from uisec_groupmembers gm
left outer join uisec_groupaccess ga on ga.groupname = gm.groupname
union
select username, permission, 'Individual' as source from uisec_useraccess)
where  username not in( '$AD','DD9') )
select permission, cast(list(who_how, ',') as char(20000)) as users from
(
select distinct username, permission, source,( username || '-' || source ) as who_how from alldata
) group by permission	

SQL Code: MySQL

To highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

select permission, group_concat(who_how separator ',') as users from
(
select distinct username, permission, source,( username || '-' || source ) as who_how from
(select * from 
(select username, permission, gm.groupname as source from uisec_groupmembers gm
left outer join uisec_groupaccess ga on ga.groupname = gm.groupname
union
select username, permission, 'Individual' as source from uisec_useraccess) t1
where  username not in( '$AD','DD9') ) 

 alldata
)  t2 group by permission