How to retrieve the Security Profile Rights by Query

[Former Member]

Hello Experts,

I have an requirement to make a report which will extract the security role along with right permissions .

For an example - 

masterdata.IntegratedSystemConfig

ZS:ESO:BC_BASIS_ADMIN

ODP_READ;ODP_EDIT;ODP_CREATE;ODP_SETUP;ODP_IMPORT

showing-  profile permissions of BC_BASIS_ADMIN role for integration system configuration .

As per Reference guide , security role remains within FCI_UPP_ROLE table  and security role right is a collection name ( T_RIGHTS ) which is stored in TMP_UPP_RIGHTS .

If I try to retrieve columns for the table  TMP_UPP_RIGHTS by SQL query , is shows that 'Table does not exist'

my query is -  

SELECT <%RESULTS%>

FROM <%SCHEMA%>.TMP_UPP_RIGHTS T1

WHERE T1.CONTEXTID = <%CONTEXT(upp.security.role_right)%>

Can anyone help me how to retrieve rows for this table ?

Regards

Sudipta

[Former Member]

Hi Sudipta,

You could try using table FCI_UPP_SECURITY_RIGHTS and linking it to the FCI_UPP_ROLE table you are already using with the PARENT_OBJECT_ID.

I hope this helps.

Dan

[Former Member]

Thanks Dan for the reply .

If I run your query with PARENT_OBJECT _ID , no value comes for FCI_UPP_SECURITY_RIGHTS  table .

However , I found that FCI_UPP_SECURITY_ALL_RIGHTS table is linked to FCI_UPP_SECURITY_ACL by  PARENT_OBJECT _ID . It gives the outcome in the form of  -

                     Access control list name , Security permission value .

                         contractgen.doc                         create

So I am trying to join  FCI_UPP_SECURITY_ACL  table with FCI_UPP_ROLE table to retrieve security profile names so that I get the output in the form of -

UProfile Name                          Access control list name         Security permission value .

ZS:ESO:BC_BASIS_ADMIN             contractgen.doc                             create

. Can you help me to find the key for joining please ?

Thanks in advance

Sudipta

[Former Member]

Hi Sudipta,

Unfortunately I have not had the chance to test this but I would try to use some code along the lines of:

SELECT <%RESULTS%>

FROM <%SCHEMA%>.FCI_UPP_ROLE T1,

FCI_UPP_SECURITY_RIGHTS T2

WHERE T1.CONTEXTID = <%CONTEXT(upp.role)%> AND

T2.PARENT_OBJECT_ID = T1.OBJECTID

Regards

Dan

[Former Member]

Hi Dan

When I run your query , T1.DISPLAY_NAME is coming good but T2.DISPLAY_NAME column is coming blank .

Please let me know if I am doing any mistake

Thanks

Sudipta