Clarity PPM

Expand all | Collapse all

Clarity rights Query

Jump to Best Answer
  • 1.  Clarity rights Query

    Posted 09-07-2009 05:56 AM
    I am looking to write a query that specifies which users have a specific right. Such as who has the resource edit right (weather it is global, inherited via Group, OBS or instance).I have tried several ways but still unsucessfull.Any ideas or help would be apreciated.ThanksFederico


  • 2.  Re: Clarity rights Query
    Best Answer

    Posted 09-07-2009 06:27 AM
    Its tricky.  Very tricky.  Heres how I did it (can't send you the code unfortunately as it has a lot of effort involved in it).  ---  Turn SQL Trace on for a user.  Go to a project, access the "Access to this Project -> Full View" screen. And kit the "Key" icon next to a user.  Now look at the SQL that that has generated - its a massive union of 9 different SQL statements.   Work out how that big statement works and that is the answer!  (That last bit is the tricky bit - as I recall the comments in the code don't help much either) - the 9 unioned statements are all solving different parts of the "rights problem"; inherited through a group, inherited through an OBS etc etc etc.    Dave.  (Good luck)


  • 3.  Re: Clarity rights Query

    Posted 09-07-2009 07:49 AM
    Other tools you might want to look at are the (old) licence protlet queries in   7.5.3 (I think) and the new ones in r8.1.fp03 and v12sp3.You could create a test user with a test right and optionally trace it through the lincence type - user - rights by user.  Martti K.


  • 4.  Re: Clarity rights Query

    Posted 09-07-2009 08:12 AM
    Hi,  When     an     user   login     itself     for     every   15     secs     this     function     will   be     called     to     check   the     user     rights.CMN_SEC_CHECK_RIGHT_FCT.
    In   the Function it is calling cmn_security_pkg and get the object_id and then passing the object_id and user_id in the CMN_SEC_ASSGND_OBJ_PERM_R_V0 view.
    Inside the function there you can see an view CMN_SEC_ASSGND_OBJ_PERM_R_V0 will be Queried to check the results.You can Query the view for the permissionsselect * from CMN_SEC_ASSGND_OBJ_PERM_R_V0 where user_id=Pass the useridNote:- But this result will show only the Generic Page level access for each object that user has got.For your Access Rights on OBS,Project,Resource then-->If your organisation uses Groups to control access rights.then Retrieve the Groups in which the  user has Member.   SELECT   S.UNIQUE_NAME "Resource ID",S.FULL_NAME "FULL NAME", G.GROUP_NAME   GROUP_NAME, G.DESCRIPTION   DESCRIPTION,DECODE(S.IS_ACTIVE,1,'ACTIVE',0,'IN ACTIVE') "USER STATUS"FROM       CMN_SEC_GROUPS_V G,   CMN_SEC_USER_GROUPS UG,SRM_RESOURCES SWHERE     G.LANGUAGE_CODE='en'   AND         G.GROUP_ROLE_TYPE='GROUP'   AND         UG.GROUP_ID = G.IDAND UG.USER_ID=S.USER_ID   and s.IS_ACTIVE=1AND       S.UNIQUE_NAME=''AND         G.PRINCIPAL_TYPE IS NULL   Then pass the Group id here to get the rights. SELECT g.group_name "Role Name",'No' "OBS level Access",'' "OBS Name",g1.group_name "Group Name",g1.group_code "Group ID",g1.description   description,DECODE(g1.IS_ACTIVE,1,'ACTIVE',0,'IN ACTIVE') STATUSFROM cmn_sec_groups_v g, cmn_sec_groups_v g1, cmn_sec_group_hierarchies hWHERE h.parent_group_id = g1.id and g.language_code='en' AND h.group_id = g.idAND (g.is_automatic is null or g.is_automatic != 1) AND g.right_type IS NOT NULLAND   g1.language_code='en'union allSELECT   g.group_name "Role Name",'Yes' "OBS level Access", t.name || ':' || u.name   "OBS Name",g1.group_name "Group Name",g1.group_code "Group ID",   g1.description   description,DECODE(g1.IS_ACTIVE,1,'ACTIVE',0,'IN ACTIVE') STATUSFROM cmn_sec_groups_v g, prj_obs_units u, prj_obs_types t ,cmn_sec_assgnd_right a, cmn_sec_groups_v g1WHERE g.language_code='en'     AND t.id = u.type_id     AND g.id = a.right_id     AND u.id = a.instance_idand g1.id =a.principal_id     AND a.principal_type = 'GROUP' AND a.right_id = g.idAND g.language_code = 'en' and g1.language_code='en'  Iam trying to build an Query for All access rights  the users have.  Will try to send you soon.

    Regards,sundar


  • 5.  Re: Clarity rights Query

    Posted 09-07-2009 09:49 AM
    Thanks Sundar,  If it is any help, you can find all the queries in the install directory of clarity   META-INF\nmc\pmd folders.    Once I get it i will send you the query as well.best regards,


  • 6.  Re: Clarity rights Query

    Posted 09-07-2009 12:11 PM
    Hi,  After struggling for few hours iam able to  form the Query.Sql trace helped me but missed the Instance level rights  so digged a little bit and added mine and changed a Little bit andthen finally the result came.   This Query (Oracle)  will provide all the Rights that an user have at Instance (Both provided by system and Group),Global (Both provided by system and Group) and OBS (Both at user level and OBS units Level).   select group_name,description,granted_through,granted_through_type
    from
    (
    --Instance Access Rights (Includes Instance Rights for the user and also the instance rights provided via the Groups)
       
          SELECT DISTINCT     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 = 5875780  
              union  -- Global Access Rights Provided Via System and access provided at OBS
    select  
                                  g.group_name   group_name,
                                  g.description   description,
                                  g.right_type,
                                  case when instance_type like '%OBS%' then 'OBS' else 'USER' end granted_through_type,
                                  '' granted_through
                  from       cmn_sec_groups_v g,
                                  cmn_sec_assgnd_right ar
                  where     AR.PRINCIPAL_ID = 5875780
                  and         g.language_code= 'en'
                  and         ar.right_id = g.id
                  and         (g.is_automatic != 1 OR g.is_automatic is null)
                  and         g.right_type is not null
                  and         g.is_active = 1
                 
                  union
                 
        --Global   Rights   for the User provided via Group Level
         
                  select  
                                  ri.group_name   group_name,
                                  ri.description   description,
                                  ri.right_type,
                                  'GROUP' granted_through_type,
                                  g.group_name   granted_through
                  from       cmn_sec_groups_v ri,
                                  cmn_sec_groups_v g,
                                  cmn_sec_group_flat_hiers h,
                                  cmn_sec_user_groups ug
                  where     ug.group_id = h.parent_group_id
                  and         ug.group_id = g.id
                  and         g.group_role_type = 'GROUP'
                  and         g.is_active = 1
                  and         ug.user_id = 5875780
                  and         g.language_code=   'en'
                  and         ri.language_code=   'en'
                  and         ri.id = h.group_id
                  and         (ri.is_automatic != 1 OR ri.is_automatic is null)
                  and         ri.right_type is not null  
                 
                  union
                 
                  --Rights got via Access Rights of the OBS Unit Level But Not at the user level OBS.
                  --(In the OBS Units you can see the Access Rights at the Individual Unit level)
                 
                  select
                                  g.group_name   group_name,
                                  g.description   description,
                                  g.right_type,
                                  'OBS' granted_through_type,  
                                  u2.name || ':' || l.name   granted_through  
                  from       cmn_sec_groups_v g,
                                  cmn_sec_assgnd_right ar,
                                  obs_unit_users_v u,
                                  prj_obs_units u2,
                                  cmn_lookups_v l
                  where     ar.principal_id = u.unit_id
                  and         ar.principal_type = u.unit_mode
                  and         ar.principal_id = u2.id
                  and         u.user_id = 5875780
                  and         g.language_code= 'en'
                  and         ar.right_id = g.id
                  and         (g.is_automatic != 1 OR g.is_automatic is null)
                  and         g.right_type is not null
                  and         g.is_active = 1
                  and         l.lookup_type = 'RIGHT_PRINCIPAL_ASSOC_TYPE'
                  and         l.language_code = 'en'
                  and         l.lookup_code = u.unit_mode    
                       
        )
     order by granted_through_type,group_name  Regards,sundar


  • 7.  Re: Clarity rights Query

    Posted 09-07-2009 12:14 PM
      Forgot to mention that you need to pass userid in the Query.(cmn_sec_users -- id).You can remove the user id that i have used in the Query and add yours.


  • 8.  Re: Clarity rights Query

    Posted 09-07-2009 12:24 PM
    Hi SUndar,Thanks for your help. I really appreciate it. The query you wrote requires i provide the User id as the parameter, what I'm looking for is providing the Right as the parameter.  please see example below.  this gets the rights for   Portfolio create it is not quite complete as Istill have to get the instance rights, but this gets the OBS, Group and User rights.  select distinct--csgv.group_name,csgv.group_code, csgv.description,
    csu.user_name, r.role_name Rights
    from
                (SELECT g.group_name "Role_Name",
                'No' "OBS level Access",
                '' "OBS Name",
                g1.group_name "Group Name",
                g1.group_code "Group ID",
                g1.description   description,
                g1.IS_ACTIVE
                FROM cmn_sec_groups_v g,
                cmn_sec_groups_v g1,
                cmn_sec_group_hierarchies h
                WHERE h.parent_group_id = g1.id and g.language_code='en'
                AND h.group_id = g.id
                AND (g.is_automatic is null or g.is_automatic != 1)
                AND g.right_type IS NOT NULL
                AND   g1.language_code='en'
                and g.id = 51041
                ) r,
    cmn_sec_user_groups ugr
    left outer join cmn_sec_users csu ON ugr.user_id=csu.id
    left outer join cmn_sec_groups gr ON gr.id=ugr.group_id,
    cmn_sec_groups_v csgv
    where csgv.group_role_type = 'GROUP'
    and csgv.language_code='en'
    and ugr.group_id=csgv.id
    and (csgv.group_code IN
                (SELECT
                g1.group_code
                FROM cmn_sec_groups_v g,
                cmn_sec_groups_v g1,
                cmn_sec_group_hierarchies h
                WHERE h.parent_group_id = g1.id and g.language_code='en'
                AND h.group_id = g.id
                AND (g.is_automatic is null or g.is_automatic != 1)
                AND g.right_type IS NOT NULL
                AND   g1.language_code='en'
                and g.id=51041-- This should be a parameter
                )
    OR csgv.group_code IN
                (SELECT
                g1.group_code
                FROM cmn_sec_groups_v g, prj_obs_units u,
                prj_obs_types t ,cmn_sec_assgnd_right a,
                cmn_sec_groups_v g1
                WHERE g.language_code='en'    
                AND t.id = u.type_id    
                AND g.id = a.right_id    
                AND u.id = a.instance_id
                and g1.id =a.principal_id    
                AND a.principal_type = 'GROUP'
                AND a.right_id = g.id
                AND g.language_code = 'en'
                and g1.language_code='en'
                and g.id=51041-- This should be a parameter
                )
    OR csu.id IN
                (Select principal_id
                from cmn_sec_assgnd_right
                Where right_id=51041 -- This should be a parameter
                and principal_type='USER')
    )
    --order by csgv.group_name


  • 9.  Re: Clarity rights Query

    Posted 09-07-2009 01:00 PM
    Bravo! Sundar/ Federico   Now just to remind you that once you get your queries solved, you have the option of 'table functions' - where you can pass parameters  [see ootb use of table functions: cmn_sec_chk_inst_fct, cmn_sec_chk_user_fct, cmn_sec_user_inst_privs_fct ]  


  • 10.  Re: Clarity rights Query

    Posted 09-07-2009 09:10 PM
    Hi,    The below query used to fetch the access rights which users have a specific access right (Instance, OBS Unit, Global (Including Group Assigned))  -- Instance Level Access RightsSELECT DISTINCT G.GROUP_NAME "Access Right Name",G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE, O.NAME GRANTED_THROUGH , '' GROUP_CODE, 'Instance' "Access Right Type"
    FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G, ODF_OBJECTS_V O WHERE A.PRINCIPAL_ID = :CMN_ID AND A.PRINCIPAL_TYPE = 'USER' AND A.RIGHT_ID = G.ID
    AND G.RIGHT_TYPE = O.RIGHT_CODE AND G.LANGUAGE_CODE = 'en' AND O.LANGUAGE_CODE = 'en' AND G.IS_ACTIVE=1UNION-- OBS Unit LevelSELECT DISTINCT G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, T.NAME || ':' || U.NAME GRANTED_THROUGH_TYPE, A.INSTANCE_TYPE GRANTED_THROUGH , ' ' GROUP_CODE, 'OBS Unit' "Access Right Type" FROM CMN_SEC_GROUPS_V G, PRJ_OBS_UNITS U, PRJ_OBS_TYPES T, CMN_SEC_ASSGND_RIGHT A
    WHERE G.LANGUAGE_CODE='en' AND T.ID = U.TYPE_ID AND G.ID = A.RIGHT_ID AND U.ID = A.INSTANCE_ID
    AND A.PRINCIPAL_TYPE = 'USER' AND A.PRINCIPAL_ID = :CMN_ID AND A.RIGHT_ID = G.ID AND G.LANGUAGE_CODE = 'en'UNION  --Global Level Access RightSELECT G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE,
    'USER' GRANTED_THROUGH_TYPE, '' GRANTED_THROUGH, 'Global' "Access Right Type" FROM CMN_SEC_GROUPS_V G, CMN_SEC_ASSGND_RIGHT AR WHERE AR.PRINCIPAL_ID = :CMN_ID AND AR.PRINCIPAL_TYPE = 'USER'
    AND AR.INSTANCE_TYPE = 'SYSTEM' AND G.LANGUAGE_CODE= 'en' AND AR.RIGHT_ID = G.ID AND (G.IS_AUTOMATIC != 1 OR G.IS_AUTOMATIC IS NULL) AND G.RIGHT_TYPE IS NOT NULL AND G.IS_ACTIVE = 1
    UNION
    SELECT RI.GROUP_NAME "Access Right Name", RI.DESCRIPTION DESCRIPTION, RI.RIGHT_TYPE, 'GROUP' GRANTED_THROUGH_TYPE, G.GROUP_NAME GRANTED_THROUGH, 'Global' "Access Right Type" FROM CMN_SEC_GROUPS_V RI,
    CMN_SEC_GROUPS_V G, CMN_SEC_GROUP_FLAT_HIERS H, CMN_SEC_USER_GROUPS UG WHERE UG.GROUP_ID = H.PARENT_GROUP_ID AND UG.GROUP_ID = G.ID AND G.GROUP_ROLE_TYPE = 'GROUP'
    AND G.IS_ACTIVE = 1 AND UG.USER_ID = :CMN_ID AND G.LANGUAGE_CODE= 'en' AND RI.LANGUAGE_CODE= 'en' AND RI.ID = H.GROUP_ID AND (RI.IS_AUTOMATIC != 1 OR RI.IS_AUTOMATIC IS NULL) AND RI.RIGHT_TYPE IS NOT NULL
    UNION
    SELECT G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE, 'OBS' GRANTED_THROUGH_TYPE, U2.NAME || ':' || L.NAME GRANTED_THROUGH, 'Global' "Access Right Type" FROM CMN_SEC_GROUPS_V G,
    CMN_SEC_ASSGND_RIGHT AR, OBS_UNIT_USERS_V U, PRJ_OBS_UNITS U2, CMN_LOOKUPS_V L WHERE AR.PRINCIPAL_ID = U.UNIT_ID AND AR.PRINCIPAL_TYPE = U.UNIT_MODE AND AR.PRINCIPAL_ID = U2.ID
    AND U.USER_ID = :CMN_ID AND AR.INSTANCE_TYPE = 'SYSTEM' AND G.LANGUAGE_CODE= 'en'
    AND AR.RIGHT_ID = G.ID AND (G.IS_AUTOMATIC != 1 OR G.IS_AUTOMATIC IS NULL) AND G.RIGHT_TYPE IS NOT NULL AND G.IS_ACTIVE = 1 AND L.LOOKUP_TYPE = 'RIGHT_PRINCIPAL_ASSOC_TYPE' AND L.LANGUAGE_CODE = 'en' AND L.LOOKUP_CODE = U.UNIT_MODE ORDER BY "Access Right Type", "Access Right Name"CMN_ID = CMN_SEC_USERS.ID  Thanks
    Senthi.  


  • 11.  Re: Clarity rights Query

    Posted 09-07-2009 11:37 PM
    Fedrico,  Here  comes the Final Query.I belive by passing the Rights Id you want to retrive the users who are having that specific Rights.You can build this as an view and then call in a Function(As mentioned by Paul).i don't know whether this is the exact requirement but you can modify accordingly.  select group_name,description,granted_through,granted_through_type
    ,rights.user_id,s.unique_name "Resource Id",s.first_name || '' || s.last_name "Resource Name"
    from
    (
    --Instance Access Rights (Includes Instance Rights for the user and also the instance rights provided via the Groups)
       
          SELECT DISTINCT     G.ID,g.group_name   group_name,
                                  g.description   description,
                                  g.right_type,
                                  'INSTANCE' granted_through_type,
                                  g.group_name   granted_through,
                                  A.PRINCIPAL_ID user_id
                                  FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G
                                  WHERE
                                  A.PRINCIPAL_TYPE = 'USER'
                                  AND A.RIGHT_ID = G.ID (+)
                                  AND G.LANGUAGE_CODE = 'en'
                                  AND G.ID=  pass the Rights id      
              union-- Global Access Rights Provided Via System and access provided at OBS
    select  
                                  g.id,g.group_name   group_name,
                                  g.description   description,
                                  g.right_type,
                                  case when instance_type like '%OBS%' then 'OBS' else 'USER' end granted_through_type,
                                  '' granted_through,
                                  AR.PRINCIPAL_ID user_id
                  from       cmn_sec_groups_v g,
                                  cmn_sec_assgnd_right ar
                  where     ar.right_id = g.id
                  and         g.language_code= 'en'
                  and         (g.is_automatic != 1 OR g.is_automatic is null)
                  and         g.right_type is not null
                  and         g.is_active = 1
                  and         g.id=pass the Rights id      
                   
                  union
                 
        --Global   Rights   for the User provided via Group
         
                  select   ri.id,
                                  ri.group_name   group_name,
                                  ri.description   description,
                                  ri.right_type,
                                  'GROUP' granted_through_type,
                                  g.group_name   granted_through,
                                  ug.user_id
                  from       cmn_sec_groups_v ri,
                                  cmn_sec_groups_v g,
                                  cmn_sec_group_flat_hiers h,
                                  cmn_sec_user_groups ug
                  where     ug.group_id = h.parent_group_id
                  and         ug.group_id = g.id
                  and         g.group_role_type = 'GROUP'
                  and         g.is_active = 1        
                  and         g.language_code=   'en'
                  and         ri.language_code=   'en'
                  and         ri.id = h.group_id
                  and         (ri.is_automatic != 1 OR ri.is_automatic is null)
                  and         ri.right_type is not null  
                  and         ri.id=pass the Rights id      
                   
                  union
                 
                  --Rights got via Access Rights of the OBS .Not at the user level
                  --(In the OBS Units you can see the Access Rights at the Individual Unit level)
                 
                  select distinct
                                  g.id,
                                  g.group_name   group_name,
                                  g.description   description,
                                  g.right_type,
                                  'OBS' granted_through_type,  
                                  u2.name || ':' || l.name   granted_through,
                                  u.user_id  
                  from       cmn_sec_groups_v g,
                                  cmn_sec_assgnd_right ar,
                                  obs_unit_users_v u,
                                  prj_obs_units u2,
                                  cmn_lookups_v l
                  where     ar.principal_id = u.unit_id
                  and         ar.principal_type = u.unit_mode
                  and         ar.principal_id = u2.id
                  and         g.language_code= 'en'
                  and         ar.right_id = g.id
                  and         (g.is_automatic != 1 OR g.is_automatic is null)
                  and         g.right_type is not null
                  and         g.is_active = 1
                  and         l.lookup_type = 'RIGHT_PRINCIPAL_ASSOC_TYPE'
                  and         l.language_code = 'en'
                  and         l.lookup_code = u.unit_mode  
                  and         g.id = pass the Rights id                  
                         
        )rights,srm_resources s
        where rights.user_id = s.user_id
     order by granted_through_type,group_name,user_id  Regards,sundar


  • 12.  Re: Clarity rights Query

    Posted 09-08-2009 05:50 AM
    Hi GUys,I wanted to say thanks for your help I really appreciate it.you guys rock!!!    


  • 13.  Re: Clarity rights Query

    Posted 09-08-2009 09:12 AM
    OK... I recognise that I haven't been particularily helpful in this thread since I didn't just post my solution.... (can you give out anti-kudos I wonder?).....  BUT the query behind the "Full View" logic checks these NINE cases, I'm not convinced that the examples above cover all 9 scenarios (although the code above does look very much like the right sort of thing);   Global Rights that this Resource has through Group  Global Rights a Resource has through User  Global rights a OBS Unit has that this Resource is part of  Instance Rights a Resource has through user  Instance rights a Group has that this Resource is part of  Instance rights a OBS Unit has that this Resource is part of  Instance rights a Resource has to an OBS Unit that has items  Instance rights a Group has that this resource is part of has to an OBS Unit that has items  Instance rights a OBS Unit has that this resource is part of has to an OBS Unit that has items      As an aside - I extended my logic to tell me WHO had granted the rights as I found that information very interesting when it comes down to "disciplining" the users   ;-)   i.e. for the Global/Instance rights you can tell who granted the right (and when) by using the created_by and created_date on the cmn_sec_assgnd_right record and for the "group inherited" rights I pick up the person who put them in the group (and when) from the cmn_sec_user_group record.    Dave.    


  • 14.  Re: Clarity rights Query

    Posted 09-08-2009 09:29 AM
    Hi,  Atlast dave Jumped in,that's what iam waiting for so that we can  learn more.i haven't checked the Role Based rights in the Query.Iam taking about the  user who is an colloboration manager or PM will have some default rights.How to capture this rights so that in that query i  can  include.Because for the Colloboration Manager there is no default rights defined.    Regards,sundar


  • 15.  Re: Clarity rights Query

    Posted 09-08-2009 11:31 AM
    I tried to group Dave's nine cases to three of the five ways the rights are obtained but could not because my English is not that good.However, I think these are the same  Through Group
    Resource is member in a Group that has a Global right
    Resource is member in a Group that has an instance rightResource is member in a Group that has an OBS right

    Through OBS
    Resource is in a OBS unit that has a Global right
    Resource is in a OBS unit that has an instance right
    Resource is in a OBS unit that has an OBS right

    Through user
    Resourse is given a Global right
    Resource is given an instance right
    Resource is given an OBS right  but then the two more that are automatic remain  Through a "role" eg. project manager, resource manager  Through being a specific user eg. collaboration manager or participant in a project  which both as far as I understand it area special case instance rights assigned directly to a user, but not within the rights management system and therefore not visible when looking at the rights of a user or the rights of a group or and OBS unit..  So you can do the goruping based on the type of right  Global rights
    Resource is member in a Group that has a Global right
    Resource is in an OBS unit that has a Global right
    Resourse is given a Global right



    OBS rights
    Resource is member in a Group that has an OBS right
    Resource is given an OBS right
    Resource is in an OBS unit that has OBS right

    Instance rights
    Resource is member in a Group that has an instance right
    Resource is given an instance right
    Resource is in an OBS unit that has an instance right
    Resource is assigned a role that has an instance right
    Resource is assigned automatically an instance right based on being a specific named resource  and again when you query at the end it goes down to grouping by   resource or right   covering both type and how.That is at user level.  At group level sometimes it is needed to know what ae the groups and which rights do they have.That is a group level  But I don't recall any instance of having been asked which are the OBS units that give users   rights based on belonging to those units.  Just wondering again...A group can only have only users as members and you can only assign rights to users, but you can link other objects to OBS units as well.Is there a possiblity of controlling access through other objects?  Martti K.


  • 16.  Re: Clarity rights Query

    Posted 09-09-2009 12:48 AM
    Hi,  I agreed dave and martti statement. This is a final query, to fecth the user access rights for all scenarios as mentioned dave and also included the instance rights which directly assigned to system (e.g. resource manager). Please look at the below final query.-- Records fetch which instance access assigned thro resource Access rightsSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name",G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE, O.NAME GRANTED_THROUGH , '' GROUP_NAME,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: Instance' "Access Right Type"FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G, ODF_OBJECTS_V O, SRM_RESOURCES SRM WHERE SRM.USER_ID = A.PRINCIPAL_ID AND A.PRINCIPAL_ID = :CMN_ID AND A.PRINCIPAL_TYPE = 'USER' AND A.RIGHT_ID = G.IDAND G.RIGHT_TYPE = O.RIGHT_CODE AND G.LANGUAGE_CODE = 'en' AND O.LANGUAGE_CODE = 'en' AND G.IS_ACTIVE=1UNION-- Records fetch which OBS Unit access assigned thro resource Access rightsSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name", G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, T.NAME || ':' || U.NAME GRANTED_THROUGH_TYPE, A.INSTANCE_TYPE GRANTED_THROUGH , ' ' GROUP_NAME,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: OBS Unit' "Access Right Type" FROM CMN_SEC_GROUPS_V G, PRJ_OBS_UNITS U, PRJ_OBS_TYPES T, CMN_SEC_ASSGND_RIGHT A, SRM_RESOURCES SRM WHERE SRM.USER_ID = A.PRINCIPAL_ID AND G.LANGUAGE_CODE='en' AND T.ID = U.TYPE_ID AND G.ID = A.RIGHT_ID AND U.ID = A.INSTANCE_IDAND A.PRINCIPAL_TYPE = 'USER' AND A.PRINCIPAL_ID = :CMN_ID AND A.RIGHT_ID = G.ID AND G.LANGUAGE_CODE = 'en'UNION---- Records fetch which Global access assigned thro resource Access rights.SELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE,'USER' GRANTED_THROUGH, '' GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =AR.CREATED_BY) "Granted By",TO_CHAR(AR.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: Global' "Access Right Type"FROM CMN_SEC_GROUPS_V G, CMN_SEC_ASSGND_RIGHT AR , SRM_RESOURCES SRM WHERE SRM.USER_ID = AR.PRINCIPAL_ID AND AR.PRINCIPAL_ID = :CMN_ID AND AR.PRINCIPAL_TYPE = 'USER'AND AR.INSTANCE_TYPE = 'SYSTEM' AND G.LANGUAGE_CODE= 'en' AND AR.RIGHT_ID = G.ID AND (G.IS_AUTOMATIC != 1 OR G.IS_AUTOMATIC IS NULL) AND G.RIGHT_TYPE IS NOT NULL AND G.IS_ACTIVE = 1UNIONSELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",RI.GROUP_NAME "Access Right Name",RI.DESCRIPTION DESCRIPTION,RI.RIGHT_TYPE GRANTED_THROUGH_TYPE,'GROUP' GRANTED_THROUGH,G.GROUP_NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =UG.CREATED_BY) "Granted By",TO_CHAR(UG.CREATED_DATE,'DD-Mon-YYYY') "Granted Date",'Resource Access Rights: Global' "Access Right Type" FROM CMN_SEC_GROUPS_V RI,CMN_SEC_GROUPS_V G, CMN_SEC_GROUP_FLAT_HIERS H, CMN_SEC_USER_GROUPS UG , SRM_RESOURCES SRM WHERE SRM.USER_ID = UG.USER_ID AND UG.GROUP_ID = H.PARENT_GROUP_ID AND UG.GROUP_ID = G.ID AND G.GROUP_ROLE_TYPE = 'GROUP'AND G.IS_ACTIVE = 1 AND UG.USER_ID = :CMN_ID AND G.LANGUAGE_CODE= 'en' AND RI.LANGUAGE_CODE= 'en' AND RI.ID = H.GROUP_ID AND (RI.IS_AUTOMATIC != 1 OR RI.IS_AUTOMATIC IS NULL) AND RI.RIGHT_TYPE IS NOT NULLUNIONSELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE, 'OBS' GRANTED_THROUGH, U2.NAME || ':' || L.NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =AR.CREATED_BY) "Granted By",TO_CHAR(AR.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: Global' "Access Right Type" FROM CMN_SEC_GROUPS_V G,CMN_SEC_ASSGND_RIGHT AR, OBS_UNIT_USERS_V U, PRJ_OBS_UNITS U2, CMN_LOOKUPS_V L , SRM_RESOURCES SRM WHERE SRM.USER_ID = AR.PRINCIPAL_ID AND AR.PRINCIPAL_ID = U.UNIT_ID AND AR.PRINCIPAL_TYPE = U.UNIT_MODE AND AR.PRINCIPAL_ID = U2.IDAND U.USER_ID = :CMN_ID AND AR.INSTANCE_TYPE = 'SYSTEM' AND G.LANGUAGE_CODE= 'en'AND AR.RIGHT_ID = G.ID AND (G.IS_AUTOMATIC != 1 OR G.IS_AUTOMATIC IS NULL) AND G.RIGHT_TYPE IS NOT NULL AND G.IS_ACTIVE = 1 AND L.LOOKUP_TYPE = 'RIGHT_PRINCIPAL_ASSOC_TYPE' AND L.LANGUAGE_CODE = 'en' AND L.LOOKUP_CODE = U.UNIT_MODEUNION-- Records fetch for Access to this Resource : ResourceSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name",G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE,'Resource' GRANTED_THROUGH,'' GROUP_CODE,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Access to this Resource: Resource' "Access Right Type"FROM CMN_SEC_ASSGND_OBJ_PERM A,SRM_RESOURCES R,SRM_RESOURCES R1,CMN_SEC_GROUPS_V G , SRM_RESOURCES SRM WHERE SRM.USER_ID =R1.USER_ID AND A.OBJECT_INSTANCE_ID = R1.ID ANDR1.USER_ID = :CMN_IDAND A.PRINCIPAL_TYPE = 'USER'AND A.PRINCIPAL_ID = R.USER_IDAND A.RIGHT_ID = G.IDAND G.RIGHT_TYPE = 'SRM_RESOURCES' AND A.RIGHT_ID =3710AND G.LANGUAGE_CODE = 'en'UNION-- Records fetch for Access to this Resource : GroupSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION,G.RIGHT_TYPE GRANTED_THROUGH_TYPE,'Resource' GRANTED_THROUGH,'' GROUP_CODE,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Access to this Resource: Group' "Access Right Type" FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V R, CMN_SEC_GROUPS_V G, SRM_RESOURCES SRM WHERE SRM.USER_ID = A.OBJECT_INSTANCE_ID AND A.OBJECT_INSTANCE_ID = :CMN_ID AND A.PRINCIPAL_TYPE = 'GROUP'AND A.PRINCIPAL_ID = G.ID AND A.RIGHT_ID = R.ID AND R.RIGHT_TYPE = 'SRM_RESOURCES' AND R.LANGUAGE_CODE = 'en' AND G.PRINCIPAL_TYPE IS NULLAND G.LANGUAGE_CODE = 'en'UNION-- Records fetch for Access to this Resource : OBS UnitSELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION,T.NAME || ':' || U.NAME GRANTED_THROUGH_TYPE,A.INSTANCE_TYPE GRANTED_THROUGH,' ' GROUP_CODE,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Access to this Resource: OBS Unit' "Access Right Type" FROM CMN_SEC_GROUPS_V G, CMN_SEC_ASSGND_RIGHT A, PRJ_OBS_UNITS U, PRJ_OBS_TYPES T, CMN_LOOKUPS L , SRM_RESOURCES SRM WHERE SRM.USER_ID = A.INSTANCE_ID AND G.LANGUAGE_CODE='en' AND G.ID = A.RIGHT_ID AND L.LOOKUP_TYPE = 'OBS_UNIT_ASSOCIATION' AND A.PRINCIPAL_TYPE = L.LOOKUP_CODEAND A.PRINCIPAL_ID = U.ID AND U.TYPE_ID = T.ID AND A.INSTANCE_ID = :CMN_ID AND A.INSTANCE_TYPE = 'INSTANCE'  UNION  -- INSTNCE ACCESS RIGHTS - GROUP  SELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE,O.NAME GRANTED_THROUGH,RI.GROUP_NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =UG.CREATED_BY) "Granted By",TO_CHAR(UG.CREATED_DATE,'DD-Mon-YYYY') "Granted Date",'Group Access Rights: Instance' "Access Right Type"FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G, ODF_OBJECTS_V O, CMN_SEC_USER_GROUPS UG, CMN_SEC_GROUPS_V RI, SRM_RESOURCES SRM WHERE SRM.USER_ID = UG.USER_ID AND A.PRINCIPAL_ID =UG.GROUP_IDAND A.PRINCIPAL_TYPE = 'GROUP'AND A.RIGHT_ID = G.IDAND G.RIGHT_TYPE = O.RIGHT_CODE AND G.ID 3810 AND RI.ID = UG.GROUP_IDAND G.LANGUAGE_CODE = 'en'AND O.LANGUAGE_CODE = 'en' AND UG.USER_ID = :CMN_IDAND G.IS_ACTIVE=1  UNION-- OBS UNIT ACCESS RIGHTS - GROUPSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",g.group_name "Access Right Name", G.DESCRIPTION,t.name || ':' || u.name GRANTED_THROUGH_TYPE,a.instance_type GRANTED_THROUGH,RI.GROUP_NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date",'Group Access Rights: OBS Unit' "Access Right Type"FROM cmn_sec_groups_v g,prj_obs_units u,prj_obs_types t,cmn_sec_assgnd_right a, CMN_SEC_USER_GROUPS UG, CMN_SEC_GROUPS_V RI, SRM_RESOURCES SRM WHERE SRM.USER_ID = UG.USER_ID AND g.language_code='en'AND t.id = u.type_idAND g.id = a.right_idAND u.id = a.instance_id AND RI.ID = UG.GROUP_IDAND a.principal_type = 'GROUP'AND a.principal_id = ug.GROUP_ID and UG.USER_ID = :CMN_IdAND a.right_id = g.idAND g.language_code = 'en' ORDER BY "Access Right Name","Access Right Type"    Note : Please pass internal id for cmn_sec_users table (cmn.id = cmn_sec_users.id)  Thanks
    Senthil Message Edited by senthil on 09-09-2009 04:49 AM [left]


  • 17.  Re: Clarity rights Query

    Posted 09-09-2009 06:03 AM
    Hi,  This is  posted by  Klaus Brandlhuber  on Linked Clarity user group.So i hope finally we got  almost every  thing reg access rights.   Taken from a Report-Security-Join this one might also help:  SELECT
    user_id, null as object_instance_id, object_type, object_code, component_code, permission_code
    FROM
    cmn_sec_assgnd_obj_perm_r_v
    WHERE user_id = 1 /*replace me*/
    UNION
    SELECT
    user_id, object_instance_id, object_type, object_code, component_code, permission_code
    FROM
    cmn_sec_assgnd_obj_perm_v
    WHERE user_id = 1 /*replace me*/  This is giving you the low level access rights independent from OBS, Groups, etc.  Hope this help
    Klaus    regards,  sundar


  • 18.  Re: Clarity rights Query

    Posted 09-09-2009 07:40 AM
    Senthil, Kudo's given, thankyou for all your code. I transformed it to MSQL just in case anyone needed it. :-)   /*
    Date: 09/09/2009
    Resource Rights... this was written in Oracle, updated to MSQL
    Global Rights that this Resource has through Group
    Global Rights a Resource has through User
    Global rights a OBS Unit has that this Resource is part of
    Instance Rights a Resource has through user
    Instance rights a Group has that this Resource is part of
    Instance rights a OBS Unit has that this Resource is part of
    Instance rights a Resource has to an OBS Unit that has items
    Instance rights a Group has that this resource is part of has to an OBS Unit that has items
    Instance rights a OBS Unit has that this resource is part of has to an OBS Unit that has items

    Location: Taken from Clarity User Forum:
    http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&thread.id=3397&view=by_date_ascending&page=2
    shortened link: http://bit.ly/1MfSqy

    Authors: Senthil, Sundar

    ***** Note : Please pass internal id for cmn_sec_users table (cmn.id = cmn_sec_users.id)

    TO GET THE NUMERIC USER ID FROM RESOURCE_ID
    */
    -- START OF STATEMENT
    SELECT ID, USER_NAME, First_Name + ' ' + Last_Name AS resource
    FROM CMN_SEC_USERS
    WHERE USER_NAME = 'taylorkr' -- 3810
    AND RI.ID = UG.GROUP_ID
    AND G.LANGUAGE_CODE = 'en'
    AND O.LANGUAGE_CODE = 'en'
    AND UG.USER_ID = @CMN_ID
    AND G.IS_ACTIVE=1

    UNION

    -- OBS UNIT ACCESS RIGHTS - GROUP

    SELECT DISTINCT SRM.UNIQUE_NAME resource_id
    , SRM.FULL_NAME resource_name
    ,g.group_name access_right_name
    , G.description
    ,t.name + ':' + u.name granted_through_type
    ,a.instance_type granted_through
    ,RI.GROUP_NAME group_name
    , (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) granted_by
    ,CONVERT(DATETIME,A.CREATED_DATE,101) granted_date
    ,'Group Access Rights: OBS Unit' access_right_type

    FROM cmn_sec_groups_v g,
    prj_obs_units u,
    prj_obs_types t,
    cmn_sec_assgnd_right a,
    CMN_SEC_USER_GROUPS UG,
    CMN_SEC_GROUPS_V RI,
    SRM_RESOURCES SRM

    WHERE SRM.USER_ID = UG.USER_ID
    AND g.language_code='en'
    AND t.id = u.type_id
    AND g.id = a.right_id
    AND u.id = a.instance_id
    AND RI.ID = UG.GROUP_ID
    AND a.principal_type = 'GROUP'
    AND a.principal_id = ug.GROUP_ID
    and UG.USER_ID = @CMN_Id
    AND a.right_id = g.id
    AND g.language_code = 'en'

    ORDER BY access_right_name, access_right_type

    -----------------------------------------------------------------------------------------------
    --Taken from a Report-Security-Join this one might also help:

    SELECT user_id
    , null as object_instance_id
    , object_type
    , object_code
    , component_code
    , permission_code
    FROM
    cmn_sec_assgnd_obj_perm_r_v
    WHERE user_id = @CMN_ID
    UNION
    SELECT
    user_id, object_instance_id, object_type, object_code, component_code, permission_code
    FROM
    cmn_sec_assgnd_obj_perm_v
    WHERE user_id = @CMN_ID

    ---This is giving you the low level access rights independent from OBS, Groups, etc.    


  • 19.  Re: Clarity rights Query

    Posted 09-17-2009 09:59 AM
    Groups with Resource  Memberships    Here is a simple NSQL query I wrote for Groups/Resources/Status.   It doesn't solve your specified need in detail but may help someone.  Customize your filter and it will show you which resources have what specific group rights.  SELECT@SELECT:DIM:USER_DEF:IMPLIED:RG:G.ID:GID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:V.GROUP_NAME:GROUPNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:U.LAST_NAME:LASTNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:U.FIRST_NAME:FIRSTNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:U.USER_NAME:USERNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:CASE WHEN USER_STATUS_ID = 200 THEN 'Active' WHEN USER_STATUS_ID = 201 THEN 'In-Active' ELSE 'Locked' END:STATUSID@FROM CMN_SEC_USERS U, CMN_SEC_USER_GROUPS G, CMN_SEC_GROUPS_V V
    WHERE U.ID = G.USER_ID
    AND G.GROUP_ID = V.ID
    AND V.LANGUAGE_CODE = 'EN'
    AND @FILTER@  


  • 20.  Re: Clarity rights Query

    Posted 03-30-2010 12:45 PM
    Hello,  I am trying to create a lookup using your NSQL query. However, I get the following error:    ODF-0001: Unable to register dynamic query lookup: Could not retrieve or register the nsql::NSQL Syntax exception: NSQL_SYNTAX_ERROR_UNKNOWN_CONSTRUCT, Info: @SELECT:DIM: USER_DEF:IMPLIED:GID@.      I am new to NSQL and trying to figure out how it works. Any help/advice would be greatly appreciated.        SELECT

    @SELECT:DIM:USER_DEF:IMPLIED:RG:G.ID:GID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:V.GROUP_NAME:GROUPNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:U.LAST_NAME:LASTNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:U.FIRST_NAME:FIRSTNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:U.USER_NAME:USERNAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:RG:CASE WHEN USER_STATUS_ID = 200 THEN 'Active' WHEN USER_STATUS_ID = 201 THEN 'In-Active' ELSE 'Locked' END:STATUSID@

    FROM CMN_SEC_USERS U, CMN_SEC_USER_GROUPS G, CMN_SEC_GROUPS_V V
    WHERE U.ID = G.USER_ID
    AND G.GROUP_ID = V.ID
    AND V.LANGUAGE_CODE = 'EN'
    AND @FILTER@    Thanks,  Kart.


  • 21.  RE: Re: Clarity rights Query

    Posted 11-09-2010 12:45 PM
    Hey Tom,

    This was great, I just tried this in my Test Environment.

    Thanks
    Tammi


  • 22.  RE: Re: Clarity rights Query

    Posted 08-23-2012 08:49 AM
    Hi everyone, I'm quite new to clarity

    and i got a question from my users group if it was possible to make a list per 'Project' which users has 'Project edit management' rights
    i found this thread where you can list users with a selected rights id, but how do i join this so i can see which users have these rights on what projects?

    i find it hard to find documentation on this


  • 23.  RE: Re: Clarity rights Query

    Posted 08-23-2012 09:32 AM
    Below is the query which will help you to understand the logic to find the any instance rights for particular project.

    Remember this query only gives you information for instance rights granted through the resource.

    User can have edit management rights through Groups, OBS etc. different logic for these access rights.

    Select Distinct ri.id,inv.id project_id, Inv.Code Project_code, Inv.Name Project_Name, R.Full_Name Full_Name, Ri.Group_Name Group_Name
    FROM srm_resources r,
    Cmn_Sec_Groups_V Ri,
    Cmn_Sec_Assgnd_Obj_Perm A,
    Inv_Investments inv
    WHERE a.right_id = ri.ID
    AND a.principal_type = 'USER'
    And A.Principal_Id = R.User_Id
    And A.Object_Instance_Id = Inv.Id
    And Ri.Language_Code = 'en'
    And Ri.Id = 3599
    Group by inv.id,Inv.Code,Inv.Name,R.Full_Name, Ri.Group_Name,ri.id ;


    I hope this will help you.

    -Gurjeet


  • 24.  RE: Re: Clarity rights Query

    Posted 08-23-2012 09:38 AM
    Hi,

    Find the below query.


    SELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name",
    (SELECT CODE FROM INV_INVESTMENTS I WHERE I.ID=OBJECT_INSTANCE_ID)"Project Code",
    (SELECT Name FROM INV_INVESTMENTS I WHERE I.ID=OBJECT_INSTANCE_ID) "Project Name",
    (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",
    TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date"
    FROM
    CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G, ODF_OBJECTS_V O, SRM_RESOURCES SRM WHERE SRM.USER_ID = A.PRINCIPAL_ID
    AND A.PRINCIPAL_TYPE = 'USER' AND A.RIGHT_ID = G.ID
    AND G.RIGHT_TYPE = O.RIGHT_CODE AND G.LANGUAGE_CODE = 'en' AND O.LANGUAGE_CODE = 'en'
    AND G.IS_ACTIVE=1 AND G.RIGHT_TYPE='SRM_PROJECTS'
    AND G.GROUP_CODE='ProjectEditManagement'
    AND OBJECT_INSTANCE_ID=5702084 -->Pass your Project internal id

    oracle-->10G

    cheers,
    sundar


  • 25.  RE: Re: Clarity rights Query

    Posted 08-28-2012 06:29 AM
    Hi Sundar and Gurjeet

    thx for your replys. this gives me the info that i need, and gives me more insight into the relations in the clarity database.