Clarity

Expand all | Collapse all

How to get the resources list under the Resource - manager auto right using query.

Jump to Best Answer
  • 1.  How to get the resources list under the Resource - manager auto right using query.

    Posted 02-20-2018 04:43 AM

    Hi,

     

    When we click on the Instance right "Resource manager - auto", then it shows list of resources who are under that RM.

     

    Which table will store this information?

     

    Thanks.



  • 2.  Re: How to get the resources list under the Resource - manager auto right using query.

    Posted 02-20-2018 06:25 AM

    Hi JayaSantoshKumar ,

     

    Tables stores the information CMN_SEC_ASSGND_OBJ_PERM and CMN_SEC_GROUPS_V.

    You can also use the below query to get the list of rights given to user:

     

    SELECT DISTINCT A.PRINCIPAL_ID user_id,
    g.group_name group_name,
    g.description description,
    g.right_type,
    'INSTANCE' granted_through_type,
    g.group_name granted_through
    from CMN_SEC_ASSGND_OBJ_PERM A, SRM_RESOURCES R, CMN_SEC_GROUPS_V G
    WHERE
    A.PRINCIPAL_TYPE = 'USER'
    AND A.PRINCIPAL_ID = R.USER_ID
    AND A.RIGHT_ID = G.ID
    AND G.LANGUAGE_CODE = 'en'
    AND A.PRINCIPAL_ID = {USER_ID}

     

    You can also use the below query to get the list of user(s) who have the "Resource manager - auto" rights:

     

    SELECT DISTINCT A.PRINCIPAL_ID user_id,
    g.group_name group_name,
    g.description description,
    g.right_type,
    G.ID,
    'INSTANCE' granted_through_type,
    g.group_name granted_through
    from CMN_SEC_ASSGND_OBJ_PERM A, SRM_RESOURCES R, CMN_SEC_GROUPS_V G
    WHERE
    A.PRINCIPAL_TYPE = 'USER'
    AND A.PRINCIPAL_ID = R.USER_ID
    AND A.RIGHT_ID = G.ID AND A.RIGHT_ID = 3710
    AND G.LANGUAGE_CODE = 'en'

     

    Regards

    /Shubham



  • 3.  Re: How to get the resources list under the Resource - manager auto right using query.

    Posted 02-20-2018 06:51 AM

    Hi Shubham,

     

    Thanks for the query   

     

    In the Administration --> Resource --> Instance rights. When we click on Instance right "Resource - manager Auto" it shows  list of resources may be 10 or 20 , which table will has this info ?

     

    Thanks.



  • 4.  Re: How to get the resources list under the Resource - manager auto right using query.
    Best Answer

    Posted 02-20-2018 08:06 AM

    Hi JayaSantoshKumar ,

     

    The tables are same but we need to manipulate the query. Hope this query works for you.

     

    SELECT DISTINCT SRM.UNIQUE_NAME "Resource ID",
    SRM.FULL_NAME "Resource Name",
    G.GROUP_NAME "Access Right Name",
    (select srmr.unique_name from srm_resources srmr where id = object_instance_id) Resources,
    object_instance_id,
    G.DESCRIPTION DESCRIPTION


    FROM CMN_SEC_ASSGND_OBJ_PERM A
    inner join CMN_SEC_GROUPS_V G ON A.RIGHT_ID = G.ID
    inner join SRM_RESOURCES SRM ON SRM.USER_ID = A.PRINCIPAL_ID
    WHERE A.PRINCIPAL_ID = {user_id}
    AND A.PRINCIPAL_TYPE = 'USER'
    AND G.GROUP_NAME = 'Resource - Manager (Auto)'
    AND G.LANGUAGE_CODE = 'en'
    AND G.IS_ACTIVE = 1

     

    Regards

    /Shubham



  • 5.  Re: How to get the resources list under the Resource - manager auto right using query.

    Posted 02-22-2018 02:10 AM

    So similarly is there a way to get list of Pages which are added to a group ?

     

    Suppose a Manager group may have been added all the Resource related OOTB portlets pages, we can check in the Instance right , can we get the list through query ?

     



  • 6.  Re: How to get the resources list under the Resource - manager auto right using query.

    Posted 02-22-2018 02:28 AM

    Yes, we need to modify the query by including the page/portlet tables. Example below for the page-view only:

     

    SELECT DISTINCT SRM.UNIQUE_NAME "Resource ID",
    (SELECT PAGE_CODE FROM CMN_PAGES WHERE ID=object_instance_id ) PAGE_CODE,
    object_instance_id

    FROM CMN_SEC_ASSGND_OBJ_PERM A
    inner join CMN_SEC_GROUPS_V G ON A.RIGHT_ID = G.ID
    inner join SRM_RESOURCES SRM ON SRM.USER_ID = A.PRINCIPAL_ID
    WHERE A.PRINCIPAL_ID = 5004001
    AND A.PRINCIPAL_TYPE = 'USER'
    AND G.GROUP_NAME = 'Page - View'
    AND G.LANGUAGE_CODE = 'en'
    AND G.IS_ACTIVE = 1

     

    Let me know if it works for you.

     

    Regards

    /Shubham