"Security - Roles and Privileges Real Time" doesn't return inherited privileges

Question

We need a OTBI report which does the same as Security Console Export Privileges and Roles.

We tried out subject area Security - Roles and Privileges Real Time  for Roles it gets similar results but for privileges it doesn't return inherited privileges.

How we can get the data the same as under Security console, it is time consuming to get data manuallt from secuiryt console.

ravikiran goda-Oracle · Answer

Hi Ilmars K,

This Subject area Provides real-time information about the security setup in the application. This subject area is independent of the pillar and can be used to report on roles and details across pillars like HCM, ERP, SCM and so on.

Use this subject area to report on role categories, roles, functional security policies, data security policies, and the users assigned to the roles.

You can report on the correlation between the objects like the roles that are directly assigned to another role, the roles that are inherited by the virtue of the directly assigned roles, or the functional and data security policies that are directly assigned to the roles.

For a given user, all the roles that have been assigned can also be reported.But not the inherited privilege

Nathan CCC · Answer

Hi

To get the data viewed as IT Security Manager in Tools work area Security Console…

If you run the scheduled process

"Import user and role application security data" aseimportusersandrolesjob

you can query the ASE tables and views

ase_user_b

ase_role_b/ase_role_tl

ase_privilege_b/ase_privilege_tl

ase_user_role_mbr

ase_role_role_mbr

ase_priv_role_mbr

The inherited role hierarchy is "pigs ear"

connect by nocycle prior t.child_role_id = t.parent_role_id

select all t.membership_id , t.effective_start_date , t.effective_end_date , t.parent_role_id, t.child_role_idfromase_role_role_mbr twhere 1 = 1and (t.effective_end_date is null)and ((trunc(sysdate,upper('dd'))) between t.effective_start_date and nvl(t.effective_end_date,(trunc(sysdate,upper('dd')))))