Reporting and Analytics for ERP

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    angela ahrens
    manager data access for users
    Topic posted September 20, 2019 by angela ahrensRed Ribbon: 250+ Points, tagged BI Publisher 
    82 Views, 6 Comments
    Title:
    manager data access for users
    Summary:
    manage data access for users - users without data access
    Content:

    On Manager data access for user page

    what sql or tables do i use to get USERS WITHOUT Data Access

    I found table for users with data access. FUN_USER_ROLE_DATA_ASGNMNTS but need to find how to get the ones with users without data access

    Document:
    without data access.docx (80KB)

    Comment

     

    • Sudhakara Rao Kovuru

      Angela, Do you need SQL query for it? I mean what is the ask here?

    • Senthilrajan Vaithianathan

      Use below query and build a report, it will give the list of users With/Without Data Access,

      S.No USER NAME ROLE NAME SECURITY CONTEXT SECURITY CONTEXT VALUE CREATION DATE
      1 User 1 Role XX Data Access Set US West 2019-03-06T21:08:14.451+00:00

       

      SELECT
      T1.USERNAME,
      T1.ROLE_NAME,
      T1.ROLE_COMMON_NAME,
      T2.SECURITYCONTEXT,
      T2.SECURITYCONTEXTVALUE,
      T2.CREATION_DATE
      FROM
      (SELECT U.USERNAME, RV.ROLE_NAME, RV.ROLE_COMMON_NAME
      FROM PER_USERS U, PER_USER_ROLES UR, PER_ROLES_DN_VL RV
      WHERE U.USER_ID(+) = UR.USER_ID AND UR.ROLE_ID (+) = RV.ROLE_ID AND UR.ACTIVE_FLAG = 'Y'
      ) T1,
      (SELECT  'DATA ACCESS SET' SECURITYCONTEXT, S.NAME SECURITYCONTEXTVALUE, R.ROLE_NAME, U.USERNAME, R.CREATION_DATE, R.CREATED_BY, R.LAST_UPDATE_DATE, R.LAST_UPDATED_BY
      FROM FUSION.FUN_USER_ROLE_DATA_ASGNMNTS R, FUSION.GL_ACCESS_SETS S, FUSION.PER_USERS U
      WHERE S.ACCESS_SET_ID = R.ACCESS_SET_ID AND U.USER_GUID = R.USER_GUID
      ) T2
      WHERE T1.USERNAME (+) = T2.USERNAME AND T1.ROLE_COMMON_NAME (+) = T2.ROLE_NAME

       

      • Amy Chan

        Hi,

        I found that sql did not return all data access set assign to user. This is one of the example: User Peter has 20 data access set have been assigned to his account but now the sql just return for two rows. Please advice how to include all. Thanks.

        USERNAME ROLE_NAME ROLE_COMMON_NAME SECURITYCONTEXT SECURITYCONTEXTVALUE CREATION_DATE
        Peter General Accountant ORA_GL_GENERAL_ACCOUNTANT_JOB DATA ACCESS SET CWW_XX_Ledger 2019-05-08T02:39:24.436+00:00
        Peter General Accounting Manager ORA_GL_GENERAL_ACCOUNTING_MANAGER_JOB DATA ACCESS SET CWW_XX_Ledger 2019-05-08T02:39:24.706+00:00
    • Amy Chan

      Hi Senthilrajan,

      It is perfect. How to include some roles have been assigned to users but no need to assign data access set?

      Thanks.

    • Sunny Chugh

      Hi

      i have a question, how we can restrict the data access in the reports?

      e.g user A has one BU access and another has different BU , how can we dynamically find out what access user has , is there any API available which can be used to initialize the session and then include the security or access which user has

       

      regards

      sunny

     

    community forum