Can anyone name the DB tables which are updated when a security group is added/removed for a resource?
This query might help you
SELECT CMN_SEC_USERS.ID,CMN_SEC_USERS.USER_NAME,CMN_SEC_USERS.LAST_NAME,CMN_SEC_USERS.FIRST_NAME,CMN_CAPTIONS_NLS.NAME,CMN_CAPTIONS_NLS.DESCRIPTION,CMN_SEC_GROUPS.GROUP_CODE,CMN_SEC_GROUPS.IS_ACTIVEFROMCMN_SEC_USER_GROUPS,CMN_SEC_USERS,CMN_SEC_GROUPS,CMN_CAPTIONS_NLSWHERE CMN_SEC_USER_GROUPS.USER_ID = CMN_SEC_USERS.IDAND CMN_SEC_USER_GROUPS.GROUP_ID = CMN_SEC_GROUPS.IDAND CMN_CAPTIONS_NLS.LANGUAGE_CODE = 'en'and CMN_CAPTIONS_NLS.TABLE_NAME like '%group%'AND CMN_SEC_GROUPS.ID = CMN_CAPTIONS_NLS.PK_IDand CMN_SEC_GROUPS.GROUP_ROLE_TYPE ='GROUP'AND CMN_SEC_USERS.USER_NAME IN ('*********')
I posted a query usingCMN_SEC_USER_GROUPS,CMN_SEC_USERS,CMN_SEC_GROUPS,CMN_CAPTIONS_NLS
but it is being moderated
CMN_SEC_USERS - user details (one to one relationship to SRM_RESOURCES)
CMN_SEC_GROUPS - security group details
CMN_SEC_USER_GROUPS - intersection of the above two, so its an entry in this table that gets created when we add a user to a group (i.e. the created_by / last_updated_by and _date fields on this table will tell you who did this/when this happened*)
* - only for "create", we can't tell when someone was removed from a group as that involves removing that record from CMN_SEC_USER_GROUPS
Retrieving data ...