Security Audit Reports

[Camsaway]

Hi,

I'm looking to put together some audit reports on security permissions that
have been assigned to users. From my understanding of the security model
there are 4 reports I would need.
1) Which Groups a User is a member of
2) Which Catgeories has the user been explicitly given (as opposed to
inherited from a group)
3) What overrides to the catgeory defaults have been given to the user
4) What overrides on global permissions has the user been given.

The first one is quite simple using the group members table in the Published
Database. I can't however work out how to derive the next 3. Can anyone
advise tables that contain the relevant data?

Note. I'm NOT tyring to derive the effective permission for a given user.
I'm trying to get reports that show who has been given what permissions. (I
want to ensure that there are very few, APPROVED, exceptions to the rule that
all permissions are granted via membership of the relevant group).

Cheers,
Cameron.

 

[Camsaway]

In case anyone is interested (Or if you want to double-check my SQL for me!)
we now seem to have achieved this.
NOTE: Data only available in the Published Database (Not reporting) :-<

Group Membership SQL
===============
SELECT MSP_WEB_RESOURCES.RES_NAME, MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME
FROM MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
MSP_WEB_SECURITY_GROUPS ON
MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID =
MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
MSP_WEB_RESOURCES ON
MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = MSP_WEB_RESOURCES.WRES_GUID

CATEGORY MEMBERSHIP SQL
===================
SELECT MSP_RESOURCES.RES_NAME, MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_NAME
FROM MSP_WEB_SECURITY_SP_CAT_RELATIONS INNER JOIN
MSP_WEB_SECURITY_CATEGORIES ON
MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_CAT_UID =
MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_UID INNER JOIN
MSP_RESOURCES ON
MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_SP_GUID =
MSP_RESOURCES.RES_SECURITY_GUID

USER LEVEL PERMISSION OVERRIDES SQL
===========================
SELECT MSP_WEB_RESOURCES.RES_NAME, MSP_WEB_CONVERSIONS_1.CONV_STRING AS
ParentFEAACT,
MSP_WEB_CONVERSIONS.CONV_STRING AS FEAACT,
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_ALLOW,
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_DENY,
MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_NAME
FROM MSP_WEB_SECURITY_FEATURES_ACTIONS INNER JOIN
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS ON
MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID =
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_FEA_ACT_UID INNER JOIN
MSP_WEB_SECURITY_FEATURES_ACTIONS AS PARENT_FEA_ACT ON
MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_PARENT
= PARENT_FEA_ACT.WSEC_FEA_ACT_UID INNER JOIN
MSP_WEB_SECURITY_SP_CAT_RELATIONS ON
MSP_WEB_SECURITY_SP_CAT_PERMISSIONS.WSEC_REL_UID =
MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_REL_UID INNER JOIN
MSP_WEB_RESOURCES ON
MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_SP_GUID = MSP_WEB_RESOURCES.WRES_GUID
INNER JOIN
MSP_WEB_CONVERSIONS ON
MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_NAME_ID
= MSP_WEB_CONVERSIONS.CONV_VALUE INNER JOIN
MSP_WEB_CONVERSIONS AS MSP_WEB_CONVERSIONS_1 ON
PARENT_FEA_ACT.WSEC_FEA_ACT_NAME_ID =
MSP_WEB_CONVERSIONS_1.CONV_VALUE LEFT OUTER JOIN
MSP_WEB_SECURITY_CATEGORIES ON
MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_CAT_UID =
MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_UID

Cheers,
Cameron.