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.
at

7 comments:

  1. Srikanth.October 2, 2014 at 4:52 AM

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

    ReplyDelete
  2. Rob ZombronOctober 2, 2014 at 8:05 AM

    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

    ReplyDelete
  3. BULLPSIFebruary 19, 2015 at 10:23 AM

    would you have a BIRT version of the report?

    Thanks in advance
    satish

    ReplyDelete
  4. Rob ZombronFebruary 19, 2015 at 11:11 AM

    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.

    ReplyDelete
  5. BULLPSIFebruary 19, 2015 at 2:06 PM

    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 

    ReplyDelete
  6. BULLPSIFebruary 19, 2015 at 2:08 PM

    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 

    ReplyDelete
  7. BULLPSIFebruary 20, 2015 at 9:58 AM

    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

    ReplyDelete

Note: Only a member of this blog may post a comment.