Clarity

I have the following Query:

SELECT 
*
FROM CMN_SEC_ASSGND_OBJ_PERM A, 
CMN_SEC_GROUPS_V G, 
ODF_OBJECTS_V O 
WHERE A.PRINCIPAL_TYPE = 'GROUP' 
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 A.PRINCIPAL_ID = '5104724' (Code for my Dummy Group with only 3 instance rights, 2 portlet view, 1 page view)

However the results return 7 results, am I missing a table?

The goal of this query is to be used in a portlet that will allow certain users to look up the Instance Rights that a given group has. The current query returns results, but I'm not sure what I need to join to be able to see if this query is even close to what I'm needing.

check the following
2290321

Check this -

99639524

/* all access rights, including automatic rights */

SELECT r.right_id
, g.group_name right_name
, g.group_code right_code
, r.permission_code
, g.description right_description
, g.lic_right_type
, g.is_automatic
, g.language_code
FROM cmn_sec_groups_v g
, cmn_sec_right r
WHERE
g.ID = r.right_id
AND g.right_type IS NOT NULL
AND g.is_active = 1
AND g.language_code = 'en'
ORDER BY permission_code, right_name
;


/* without automatic rights */

SELECT r.right_id
, g.group_name right_name
, g.group_code right_code
, r.permission_code
, g.description right_description
, g.lic_right_type
, g.is_automatic
, g.language_code
FROM cmn_sec_groups_v g
, cmn_sec_right r
WHERE
g.ID = r.right_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 g.language_code = 'en'
ORDER BY permission_code, right_name



And this -

2290329


NJ

I guess I'm not certain how to incorporate this into my Query. The links that are provided are helpfull, I've used the first one to craft the initial query that I put up, but I still feel like I'm missing a piece to the puzzle. The second post with the two queries look helpful as well.

The issue I'm running into is complicated, the query I started this post with does 1/2 of what I need it to. Since I'm looking at groups and not individuals I need to be able to see the 2 parts of the instance right. The query returns data that tells me what instance right is assigned, but not the instance if that make sense. It returns the following information:

ID       PRINCIPAL_TYPE  PRINCIPAL_ID  OBJECT_ID  PERMISSION_ID  OBJECT_INSTANCE_ID  RIGHT_ID  IS_RIGHT_DEFINITION  CLASS_ID  CLASS_CODE  ID     LANGUAGE_CODE  GROUP_NAME      DESCRIPTION                                                                                                                GROUP_CODE                GROUP_ROLE_TYPE  RIGHT_TYPE    GLOBAL_RIGHT_TYPE  GROUP_TYPE  PRINCIPAL_TYPE  PRINCIPAL_ID  IS_ACTIVE  IS_SYSTEM  IS_AUTOMATIC  LIC_RIGHT_TYPE  CLASS_CODE  
-------  --------------  ------------  ---------  -------------  ------------------  --------  -------------------  --------  ----------  -----  -------------  --------------  -------------------------------------------------------------------------------------------------------------------------  ------------------------  ---------------  ------------  -----------------  ----------  --------------  ------------  ---------  ---------  ------------  --------------  ----------  
9860641  GROUP           5104724       50040      50060          5049000             50060     0                    -1        NONE        50060  en             Portlet - View  Allows user to view a portlet in the application.                                                                          PortletViewInstanceRight  ROLE             CMN_PORTLETS  (null)             INTERNAL    (null)          (null)        1          0          (null)        viewer          NONE        
9860704  GROUP           5104724       50040      50060          5005079             50060     0                    -1        NONE        50060  en             Portlet - View  Allows user to view a portlet in the application.                                                                          PortletViewInstanceRight  ROLE             CMN_PORTLETS  (null)             INTERNAL    (null)          (null)        1          0          (null)        viewer          NONE        
9860706  GROUP           5104724       50040      50060          5005078             50060     0                    -1        NONE        50060  en             Portlet - View  Allows user to view a portlet in the application.                                                                          PortletViewInstanceRight  ROLE             CMN_PORTLETS  (null)             INTERNAL    (null)          (null)        1          0          (null)        viewer          NONE        
9860640  GROUP           5104724       50040      1              5049000             50060     0                    -1        NONE        50060  en             Portlet - View  Allows user to view a portlet in the application.                                                                          PortletViewInstanceRight  ROLE             CMN_PORTLETS  (null)             INTERNAL    (null)          (null)        1          0          (null)        viewer          NONE        
9860703  GROUP           5104724       50040      1              5005079             50060     0                    -1        NONE        50060  en             Portlet - View  Allows user to view a portlet in the application.                                                                          PortletViewInstanceRight  ROLE             CMN_PORTLETS  (null)             INTERNAL    (null)          (null)        1          0          (null)        viewer          NONE        
9860705  GROUP           5104724       50040      1              5005078             50060     0                    -1        NONE        50060  en             Portlet - View  Allows user to view a portlet in the application.                                                                          PortletViewInstanceRight  ROLE             CMN_PORTLETS  (null)             INTERNAL    (null)          (null)        1          0          (null)        viewer          NONE        
9860638  GROUP           5104724       50042      50060          5070000             50183     0                    -1        NONE        50183  en             Page - View     Allows viewing of a general page in the application, for instance pages (such as Portfolio pages) this right is not used.  PageViewInstanceRight     ROLE             CMN_PAGES     (null)             INTERNAL    (null)          (null)        1          0          (null)        viewer          NONE      

So it shows me that I have some Portlet - View rights, and Page - View rights assigned to this test group, but it doesn't tell me what instance of a Portlet or Page it has access to, I think that the key is OBJECT_INSTANCE_ID but I can't find how that links to see if that's even correct.

e_martin:

it doesn't tell me what instance of a Portlet or Page it has access to

try this query, it will give you what you want. It takes Group code as the input, test it once please