Clarity

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_ACTIVE
FROM
CMN_SEC_USER_GROUPS
,CMN_SEC_USERS
,CMN_SEC_GROUPS
,CMN_CAPTIONS_NLS
WHERE
CMN_SEC_USER_GROUPS.USER_ID  = CMN_SEC_USERS.ID
AND CMN_SEC_USER_GROUPS.GROUP_ID  = CMN_SEC_GROUPS.ID
AND CMN_CAPTIONS_NLS.LANGUAGE_CODE = 'en'
and CMN_CAPTIONS_NLS.TABLE_NAME like '%group%'
AND CMN_SEC_GROUPS.ID = CMN_CAPTIONS_NLS.PK_ID
and CMN_SEC_GROUPS.GROUP_ROLE_TYPE ='GROUP'
AND CMN_SEC_USERS.USER_NAME IN ('*********')

I posted a query using
CMN_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