Clarity

Expand all | Collapse all

Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

  • 1.  Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

    Posted 10-30-2014 06:22 PM

    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



  • 2.  Re: Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

    Posted 10-31-2014 12:59 AM

    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



  • 3.  Re: Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

    Posted 11-04-2014 04:58 AM

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



  • 4.  Re: Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

    Posted 11-04-2014 07:05 AM

    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



  • 5.  Re: Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

    Posted 11-04-2014 08:11 AM

    Thanks for sharing these Georgy

     

    NJ



  • 6.  Re: Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

    Posted 11-04-2014 04:47 PM

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



  • 7.  Re: Attribute description / technical reference for table cmn_sec_assgnd_obj_perm

    Posted 11-04-2014 11:54 PM

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

     

    NJ