Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

[Anon Anon]

Hello,

 

I am looking for the technical reference of table cmn_sec_assgnd_obj_perm and associated tables. Unfortunately it is not included in the tech. ref. guide, even though the updates coming with rev 13 have been documented. Could anyone tell me where to find this, please?

 

Update: Since the official documentation is missing may be one could provide us with a descriptions of the fields here:

Field:

ID

PRINCIPAL_TYPE - seems to be either group or user

PRINCIPAL_ID - ? points to?

OBJECT_ID - ? points to?

PERMISSION_ID - ? points to?

OBJECT_INSTANCE_ID - ? points  to?

CREATED_DATE - seems clear

CREATED_BY - seems clear

LAST_UPDATED_DATE - seems clear

LAST_UPDATED_BY - seems clear

RIGHT_ID - ? points to ?

IS_RIGHT_DEFINITION - 0 or 1, purpose unclear!?

CLASS_ID - ? -1, purpose unclear!?

 

Best Regards

[navzjoshi00]

Agreed, don't see any definition for the table.

 

You can find the related info in the communities, though

 

https://communities.ca.com/message/14809266#14809266

 

https://communities.ca.com/message/7112887#7112887

 

https://communities.ca.com/message/101301646#101301646

 

NJ

[Anon Anon]

Thank you. However, I am looking for a technical reference and field explanation.

[Georgy]

Hi Rnitram,

 

Couldn't find any field explanation for this cmn_sec_assgnd_obj_perm table anywhere. But we do have some sample queries which might help you to extract meaningful data from this table or to understand how this table is linked to a couple of others.

 

NOTE: cmn_sec_assgnd_obj_perm.principal_type can be 'USER' or 'GROUP'

 

Assigned User Permissions on Project Instances

SELECT csu.user_name,csu.first_name||' '||csu.last_name Full_Name,csaop.object_id,cso.object_code,csaop.object_instance_id,

sp.unique_name Project_Code,sp.name Project_Name, cspe.permission_code, cspe.permission_id, cspe.permission_value

FROM cmn_sec_assgnd_obj_perm csaop,

  cmn_sec_users csu,

  cmn_sec_objects cso,

  cmn_sec_perm_elements cspe,

  srm_projects sp

WHERE csu.id            =csaop.principal_id

AND csaop.principal_type='USER'

AND cso.id              =csaop.object_id

AND csaop.permission_id =cspe.permission_id

AND csu.user_name      ='******'

AND cso.object_code    ='PRJ_PROJECT'

AND sp.id              =csaop.object_instance_id

ORDER BY csu.user_name, csaop.object_id, csaop.object_instance_id, cspe.permission_code

 

Assigned User Permissions on Object Instances

SELECT csu.user_name,csu.first_name ||' '||csu.last_name Full_Name, csaop.object_id,cso.object_code,csaop.object_instance_id,

oo.code Object_Instance_Code,cspe.permission_code,cspe.permission_id,cspe.permission_value

FROM cmn_sec_assgnd_obj_perm csaop,

  cmn_sec_users csu,

  cmn_sec_objects cso,

  cmn_sec_perm_elements cspe,

  odf_objects oo

WHERE csu.id            =csaop.principal_id

AND csaop.principal_type='USER'

AND cso.id              =csaop.object_id

AND csaop.permission_id =cspe.permission_id

AND csu.user_name       ='******'

AND cso.object_code     ='ODF_OBJECT'

AND oo.id               =csaop.object_instance_id

ORDER BY csu.user_name, csaop.object_id, csaop.object_instance_id, cspe.permission_code

 

Assigned User Permissions on Job Runs

SELECT csu.user_name,csu.first_name||' '||csu.last_name Full_Name,csaop.object_id,cso.object_code,csaop.object_instance_id,

csj.name Job_Name,cspe.permission_code,cspe.permission_id,cspe.permission_value

FROM cmn_sec_assgnd_obj_perm csaop,

  cmn_sec_users csu,

  cmn_sec_objects cso,

  cmn_sec_perm_elements cspe,

  cmn_sch_jobs csj

WHERE csu.id            =csaop.principal_id

AND csaop.principal_type='USER'

AND cso.id              =csaop.object_id

AND csaop.permission_id =cspe.permission_id

AND csu.user_name       ='******'

AND cso.object_code     ='JOB'

AND csj.id              =csaop.object_instance_id

ORDER BY csu.user_name, csaop.object_id, csaop.object_instance_id, cspe.permission_code

 

 

Assigned Group Permissions on Portlets

SELECT csg.group_code,csg.group_role_type,csaop.object_id,cso.object_code,csaop.object_instance_id,

cp.portlet_code,cspe.permission_code,cspe.permission_id,cspe.permission_value

FROM cmn_sec_assgnd_obj_perm csaop,

  cmn_sec_groups csg,

  cmn_sec_objects cso,

  cmn_sec_perm_elements cspe,

  cmn_portlets cp

WHERE csg.id            =csaop.principal_id

AND csaop.principal_type='GROUP'

AND cso.id              =csaop.object_id

AND csaop.permission_id =cspe.permission_id

AND csg.group_role_type ='GROUP'

AND csg.group_code      ='******'

AND cso.object_code     ='PORTLET'

AND cp.id               =csaop.object_instance_id

ORDER BY csg.group_code, csaop.object_id, csaop.object_instance_id, cspe.permission_code

 

 

Assigned Group Permissions on Pages

SELECT csg.group_code,csg.group_role_type,csaop.object_id,cso.object_code,

csaop.object_instance_id,cp.page_code,cp.page_type_code,cspe.permission_code,cspe.permission_id

FROM cmn_sec_assgnd_obj_perm csaop,

  cmn_sec_groups csg,

  cmn_sec_objects cso,

  cmn_sec_perm_elements cspe,

  cmn_pages cp

WHERE csg.id            =csaop.principal_id

AND csaop.principal_type='GROUP'

AND cso.id              =csaop.object_id

AND csaop.permission_id =cspe.permission_id

AND csg.group_role_type ='GROUP'

AND csg.group_code      ='******'

AND cso.object_code     ='PAGE'

AND cp.id               =csaop.object_instance_id

ORDER BY csg.group_code, csaop.object_id, csaop.object_instance_id, cspe.permission_code

 

 

As you can see, the first 3 user queries need a UserName instead of '******' and the last 2 group queries need a GroupID instead of '******' to work.

 

You can also generate more and more queries like the above ones, by selecting different values for the cso.object_code and linking the corresponding tables in the join with cmn_sec_assgnd_obj_perm table.

 

Hope these queries will help you a little

 

Regards,

Georgy

[navzjoshi00]

Thanks for sharing these Georgy

 

NJ

[Anon Anon]

Thank you Georgy. However, I do know these kind of queries, but what I am looking for is some kind of official documentation...

[navzjoshi00]

Don't think there's any official documentation as such

 

NJ