Clarity

I've been tasked with creating a series of Portlets that will help in idendifying what rights users and groups have, at all levels, OBS, Global and Instance Rights.

Essentially we want one page with 3 portlets, one portlet where you can enter a User ID and see what groups they belong to, another portlet on the page or different tab where you can select the group and it will display what OBS, Global or Instance rights are assigned to the group, and another that you can enter the User ID and see what rights that are assigned to them by the OBS, Global and Instance level.

I've found a lot of the tables and their associations but I can't seem to find everything I'm looking at.

I see that CMN_SEC_GROUPS_V, CMN_SEC_GROUPS, CMN_SEC_ASSGNED_RIGHT, CMN_GROUP_HIERARCHIES

I am very novice in the database side and since it's security I've been stumbling around since everything uses ID's and it's kind of hard to find all the relationships.

Is there another table I'm missing because I can't seem to find the Instance Level Rights, other than that I think I've found everything else. Any help identifying would be great.

Hi - its not an uncommon question (working out all the way security works).

Have a look at this thread ; Clarity rights Query it probably answers a lot of the questions you will come up against!

e_martin,

There is a FREE ready to use portlet available on RegoXchange titled "Resources in Security Groups" that should give you a lot of what you are looking for. At the least it could be a good starting point for you.

Registration is free, then you can download the XML file and XOG it into your dev environment. Here is the link: http://regoxchange.com/show/67/

Good luck!

-Dave

The attached is is how the portlet looks like !!!

NJ

This portlet looks great, but looking at how our organization is going to use it I'd like to make the Group, Resource and OBS pick lists, which is great, until I come to Group, I'd have to create a look up, but I can't attach the lookup to just a query, it would need to go to an object, so create an object that stores all the groups?

This portlet looks great, but looking at how our organization is going to use it I'd like to make the Group, Resource and OBS pick lists, which is great, until I come to Group, I'd have to create a look up, but I can't attach the lookup to just a query, it would need to go to an object, so create an object that stores all the groups?

You can create a query type lookup that is a lookup of all the current groups. This is a query to pull the groups - you will have to set it up as a lookup. Once you have the lookup, you can associate this lookup within the query to the group_code then add that field into the filter.

/* Group Lookup List */
SELECT cap.NAME, g.group_code, cap.description
FROM cmn_sec_groups g, cmn_captions_nls cap
WHERE cap.language_code = 'en'
AND cap.table_name = 'CMN_SEC_GROUPS'
AND cap.pk_id = g.id
AND g.group_role_type = 'GROUP'
AND g.is_Active = 1

This seems to help out a lot, but is there any help in finding the table that helps relate what instance, global, and OBS level rights are in each group?

This seems to help out a lot, but is there any help in finding the table that helps relate what instance, global, and OBS level rights are in each group?

Have you looked at the thread that Dave had pointed to ?

Have you checked the below table ?

cmn_sec_assgnd_right
CMN_SEC_USER_GROUPS
CMN_SEC_GROUPS_V


NJ

You could also look at the queries behind the license portlets.
Looking at the CMN_SEC_GROUPS_V view and the tables involved should also help which NJ suggested.
There is security in the Entity relations diagrams in Tech ref, but I don't think that is any good for anybody.

Are you on premise so that you have access to the database or On Demand with no access to the db?

Martti K.

Thanks for re-confirming ... :wink:

"You could also look at the queries behind the license portlets" - should also help !!!

NJ

I did check Dave's post, but there is so much code there without formatting I've got a headache and when I pop in to do a query, I get mixed results. At this point I'm just looking to find security rights for a group, and see what instance rights that group has, I have the Query to get the Group Global, and OBS level rights, but I can't put it together to see the Instance rights still.

I did check Dave's post, but there is so much code there without formatting I've got a headache and when I pop in to do a query, I get mixed results. At this point I'm just looking to find security rights for a group, and see what instance rights that group has, I have the Query to get the Group Global, and OBS level rights, but I can't put it together to see the Instance rights still.

I did check Dave's post, but there is so much code there without formatting I've got a headache

You are getting the hang of it.

The rights setup in the database is so complex that many others have got a headache and tried and tried as you can see.

It simply ain't so that the rights were in a single table and you just write a simple query to it.

One way to tackle that is to put the SQL trace on and pick the query from the trace.

If you look at the security ERD in tech ref that is a joke. Something more useful is needed.

If you say you cannot get what you want with the suggestions give you will be getting more suggestions.

Martti K.

Since I'm just trying to get the Instance Rights, I have this one 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)


I'm not able to do a trace, I have limited DB access. But I get what I'm looking for, but I need expansion because I need to know what Page - View and Portlet - View rights are shown in my response (dummy group in out DEV environment)