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

[Legacy User]

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.

[SHUBHAM BHATIA]

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

[Legacy User]

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.

[SHUBHAM BHATIA]

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

[Legacy User]

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 ?

 

[SHUBHAM BHATIA]

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