Clarity

Expand all | Collapse all

Resource-Security Group Mapping Tables in CA PPM?

  • 1.  Resource-Security Group Mapping Tables in CA PPM?

    Posted 02-06-2018 03:30 AM

    Can anyone name the DB tables which are updated when a security group is added/removed for a resource?



  • 2.  Re: Resource-Security Group Mapping Tables in CA PPM?

    Posted 02-06-2018 03:39 AM

    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 ('*********')



  • 3.  Re: Resource-Security Group Mapping Tables in CA PPM?

    Posted 02-06-2018 04:09 AM

    I posted a query using
    CMN_SEC_USER_GROUPS
    ,CMN_SEC_USERS
    ,CMN_SEC_GROUPS
    ,CMN_CAPTIONS_NLS

     

    but it is being moderated



  • 4.  Re: Resource-Security Group Mapping Tables in CA PPM?

    Posted 02-06-2018 04:10 AM

    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