Tuesday, January 12, 2010

Extracting Security Data

If you need the details on how your security groups are set up, below is some SQL to extract that data. The query below only pulls the security at the GUI level, but that is the level that contains the most information. If you only want a summary of read/write/delete access add ass.service_id > 50 to the where clause. That will prevent GUI and section action access from displaying. Here is a link to a Crystal report using this query to put the data in a more user friendly format.

spec.spec_name group_name,
sa.module_name system_name,
'GUI' object_type,
gui.gui_name object,
ass.service_label permission,
FROM app_object_permission aop
JOIN ibs_module sa ON
sa.module_id = aop.application_id
JOIN application_service ass ON
ass.service_id = aop.service_id
JOIN ibs_spec spec ON
spec.spec_id = aop.group_id
JOIN gui_header gui ON
gui.gui_id = aop.template_id
spec.spec_name != 'Admin Group' AND
aop.tab_id = -1 AND
aop.section_id = -1 AND
aop.field_id = -1
ORDER BY 1, 2, 3, 4, 6

No comments: