Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Amar
    SQL script for Roles , Privileges, Inherit roles & Data...Answered
    Topic posted July 15, 2019 by AmarBlue Ribbon: 750+ Points, tagged BI Publisher, OBI Answers, OBIEE Answers, OTBI, Reports, Sample Reports, Tip 
    169 Views, 6 Comments
    Title:
    SQL script for Roles , Privileges, Inherit roles & Data Security Policies
    Summary:
    SQL script for Roles , Privileges, Inherit roles & Data Security Policies
    Content:

    Trying to built a query to fetch Application Roles, Privileges, Data Security & Role Hierarchies, where i am able to get roles and their privileges but could not able to fetch the data security policies & roles inherited.

    I have tried using the application security tables ASE_ROLES_V, ASE_ROLE_ROLE_MBR, ASE_PRIV_ROLE_MBR, ASE_PRIVILEGE_VL, also not able to view data from data model.

     

    regards,

    Amar.

    Best Comment

    Elenor Haven

    See if the SQL attached gives you what you need.

    Comment

     

    • Elenor Haven

      See if the SQL attached gives you what you need.

    • Amar

      Thanks Elenor. I need only Roles, role privileges, inherited roles and data security policies.

       

      Regards,

      • Amarnath Govindu

        SELECT distinct FFF.FUNCTION_NAME "Data Privilege",
               FFF.USER_FUNCTION_NAME "Function Name",
              -- DECODE( FG.GRANTEE_KEY,FSR.ROLE_GUID,'Yes','No') "Role GUID in Sync",
               FG.ROLE_NAME "Role Code",
               (SELECT ROLE_NAME
                  FROM ASE_ROLE_VL
                 WHERE CODE = FG.ROLE_NAME
                   AND ROWNUM = 1 ) "Role Name",
               (SELECT b.ROLE_TYPE_NAME
                  FROM ASE_ROLE_VL A, ASE_ROLE_TYPE_VL B
                 WHERE A.ROLE_TYPE_CODE = B.ROLE_TYPE_CODE
                   AND a.CODE = FG.ROLE_NAME
                   AND ROWNUM =1) "Role Type Name",
               FG.NAME  "Task Name",
               FFF.description "Function Desc",
               FO.OBJ_NAME "Data Resource",
               FOIS.PREDICATE "Predicate"
          FROM FND_GRANTS FG,
               FND_OBJECTS_VL FO,
               FND_FORM_FUNCTIONS_VL FFF,
               FND_MENU_ENTRIES FME,
               FND_OBJECT_INSTANCE_SETS_VL FOIS,
               FND_SESSIONS FS,
               FND_SESSION_ROLES FSR
         WHERE FFF.FUNCTION_ID = FME.FUNCTION_ID
           AND FME.MENU_ID  = FG.MENU_ID
           AND FO.OBJECT_ID = FFF.OBJECT_ID
           AND FO.OBJECT_ID = FG.OBJECT_ID
           AND FOIS.INSTANCE_SET_ID (+) = FG.INSTANCE_SET_ID
           AND FG.ROLE_NAME  = FSR.ROLE_NAME
           AND FS.SESSION_ID = FSR.SESSION_ID
          -- AND FG.ROLE_NAME  = 'ORA_AP_ACCOUNTS_PAYABLE_MANAGER_JOB'
           -- AND FO.OBJ_NAME!='PER_PERSONS'
        --AND FFF.USER_FUNCTION_NAME LIKE '%Ledger%'
        AND FG.ROLE_NAME IN (:P_ROLE)
         ORDER BY FG.ROLE_NAME,  FFF.USER_FUNCTION_NAME,FO.OBJ_NAME

    • FNU Preksha

      The role details can also be fetched using the Import User and role access Audit Report with the data security policies checked while downloading the report