Clarity rights Query

[Anon Anon]

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

[Anon Anon]

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)

[Deactivated User]

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.

[Anon Anon]

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

[Anon Anon]

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,

[Anon Anon]

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

[Anon Anon]

  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.

[Anon Anon]

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

[Anon Anon]

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 ]  

[Anon Anon]

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.  

[Anon Anon]

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

[Anon Anon]

Hi GUys,I wanted to say thanks for your help I really appreciate it.you guys rock!!!    

[Anon Anon]

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.    

[Anon Anon]

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

[Deactivated User]

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.

[Anon Anon]

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]

[Anon Anon]

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

[furrelkt7]

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.    

[Anon Anon]

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@  

[Anon Anon]

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.

[Tammi Reel-Davis]

Hey Tom,

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

Thanks
Tammi

[Anon Anon]

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

[Legacy User]

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

[Anon Anon]

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

[Anon Anon]

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.