Wednesday, May 22, 2013

Security Group Permissions Report

If you need to get a handle on your security groups I have a Crystal Report that displays your security details in a viewer-friendly table format.  The data is set via SQL command, so the where clause at the end of the SQL can be modified to limit the date returned as needed.  You can download the report here.

7 comments:

Srikanth. said...

Can you please provide the used SQL Query for this report.

Rob Zombron said...

Here is the SQL:

SELECT DISTINCT
CASE
WHEN module.module_id IS NULL THEN appobj.app_object_name
ELSE module.module_name
END AS module,
CASE
WHEN module.module_id IS NULL THEN 'System'
WHEN gui.gui_id IS NULL THEN 'Module-Level'
ELSE gui.gui_name
END AS object,
grp.triNameTX AS sys_group,
CASE
WHEN module.module_id IS NULL THEN 'Full Access'
WHEN appobj.service_id <= 40 THEN 'Data Access'
ELSE appservice.service_label
END AS permission,
appservice.service_id AS service_id
FROM app_object_permission aop
JOIN T_Group grp ON
grp.spec_id = aop.group_id
JOIN application_object appobj ON
appobj.application_id = aop.application_id AND
appobj.template_id = aop.template_id AND
appobj.tab_id = aop.tab_id AND
appobj.section_id = aop.section_id AND
appobj.field_id = aop.field_id AND
appobj.service_id = aop.service_id
JOIN application_service appservice ON
appservice.service_id = appobj.service_id
LEFT OUTER JOIN ibs_module module ON
module.module_id = appobj.application_id
LEFT OUTER JOIN gui_header gui ON
gui.gui_id = appobj.template_id
WHERE
appobj.tab_id = -1 AND
appobj.section_id = -1 AND
appobj.field_id = -1

BULLPSI said...

would you have a BIRT version of the report?

Thanks in advance
satish

Rob Zombron said...

TRIRIGA 10 ships with a few similar BIRT Reports. Search External reports by title for "Group Security Permissions Report" and you should see several results.

BULLPSI said...

The OOB (shipped) ones don't seem to work though. They still have references to Crystal.

triSystem - BIRT - Group Security Permissions Report 
Data Utilities - BIRT - Group Security Permissions Report Filter By Group 
Data Utilities - BIRT - Group Security Permissions Report Filter By Groups (Has Access) 
Data Utilities - BIRT - Group Security Permissions Report Filter by Groups, Module and BOs 
Data Utilities - BIRT - Group Security Permissions Report Filter by Module, Business Object, GUI, Tab and Section 

BULLPSI said...

They don't seem to work, probably that they have references to Crystal.

triSystem - BIRT - Group Security Permissions Report 
Data Utilities - BIRT - Group Security Permissions Report Filter By Group 
Data Utilities - BIRT - Group Security Permissions Report Filter By Groups (Has Access) 
Data Utilities - BIRT - Group Security Permissions Report Filter by Groups, Module and BOs 
Data Utilities - BIRT - Group Security Permissions Report Filter by Module, Business Object, GUI, Tab and Section 

BULLPSI said...

Thanks again!
I pulled the reports from a new install of 10.4.
The reports work fine, with the ones having filters as well.

regards
satish