Oracle Cloud Security Basics and Tables

Author: Krian 


One of the major implementation shifts from Oracle EBS to Oracle Cloud ERP is the approach on security and controls. While Oracle EBS had a mix of responsibilities and roles, in Oracle Cloud ERP  it has been completely moved to Roles.


Understanding the basics:


Privilege:

A privilege is a right given to a user to perform a specific task.


Role:

A group of related privileges which allow the user to perform related tasks.


While people might be familiar with the UI Navigation for the security console and add/modify/remove privileges to the user, most of the time, it is the approach that is implemented in the standard reports , that is of more importance, to mimic the same logic in the custom reports.


Understanding the technical implementation details:


We've tried a list of tables, which might be considered during the implementation of custom reports:


ASE_PRIVILEGE_B


Any standard privilege that is seeded or custom role created by the developer/user will have an entry in this table.


ASE_ROLE_B


Any Role either standard or custom has an entry in this table


ASE_PRIV_ROLE_MBNR

This table holds the details of the privileges which are included in a role.



Role Based assignment to Users:


Unlike in EBS where Application User and Employee could be different set of users, in Oracle Cloud ERP all the user controls are through HR tables. In EBS, we'd the concept the FND_USER and PER_ALL_PEOPLE_F where mapping the user to an employee was optional, but in cloud there is no concept of FND_USER , all the users are present in PER_USER


Some important tables with respect to users and their assigned roles:


PER_USERS


This table has an entry for each user, who is logging into the application.


PER_ROLES


This table has all the entries for all the roles that are available in the application both custom and standard.



PER_USER_ROLES:


This table holds the details of the roles which are assigned to a user.


Along with the roles assigned to a user, understanding the assignment of the BU/FA/GL Access is also very important.


Org Access to the users:

Some important tables for Org Access are:


FUN_USER_ROLE_DATA_ASGNMNTS 


This tables provided the details of the org access assigned to the specific role for a user.


Below are some queries which give details of how the access of a user can be checked


OU Based Restriction:


select * from hr_operating_units haou where EXISTS  (SELECT 1  FROM FUN_USER_ROLE_DATA_ASGNMNTS URDA ,    HR_OPERATING_UNITS FABU,    PER_USERS PU,    PER_USER_ROLES PUR,    PER_ROLES_DN PRD,    PER_ROLES_DN_TL PRDT  WHERE 1 =1  AND URDA.ACTIVE_FLAG !='N'  AND FABU.ORGANIZATION_ID = URDA.ORG_ID  AND FABU.NAME = HAOU.NAME  AND URDA.USER_GUID = PU.USER_GUID  AND PU.USER_ID = PUR.USER_ID  AND PUR.ROLE_ID = PRD.ROLE_ID  AND PRD.ROLE_ID = PRDT.ROLE_ID  AND PRDT.LANGUAGE = USERENV('LANG')  AND PUR.ROLE_GUID = PRD.ROLE_GUID  AND PRD.ROLE_COMMON_NAME = URDA.ROLE_NAME  AND PU.USERNAME = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )  UNION  SELECT 1  FROM FUN_USER_ROLE_DATA_ASGNMNTS URDA ,    HR_OPERATING_UNITS FABU,    PER_USERS PU,    PER_USER_ROLES PUR,    PER_ROLES_DN PRD,    PER_ROLES_DN_TL PRDT,    FND_SETID_SETS_VL FSS,    FND_SETID_ASSIGNMENTS FSA  WHERE 1 =1  AND URDA.ACTIVE_FLAG !='N'  AND FABU.ORGANIZATION_ID = FSA.DETERMINANT_VALUE  AND FSA.DETERMINANT_TYPE = 'BU'  AND FSA.SET_ID = FSS.SET_ID  AND URDA.SET_ID = FSA.SET_ID  AND FABU.NAME = HAOU.NAME  AND URDA.USER_GUID = PU.USER_GUID  AND PU.USER_ID = PUR.USER_ID  AND PUR.ROLE_ID = PRD.ROLE_ID  AND PRD.ROLE_ID = PRDT.ROLE_ID  AND PRDT.LANGUAGE = USERENV('LANG')  AND PUR.ROLE_GUID = PRD.ROLE_GUID  AND PRD.ROLE_COMMON_NAME = URDA.ROLE_NAME  AND PU.USERNAME = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )  );

 

 

 

  Ledger Based Access:

  

  select * from gl_sets_of_books gsbwhere EXISTS  (SELECT 1  FROM FUN_USER_ROLE_DATA_ASGNMNTS URDA ,    GL_SETS_OF_BOOKS FABU,    PER_USERS PU,    PER_USER_ROLES PUR,    PER_ROLES_DN PRD,    PER_ROLES_DN_TL PRDT  WHERE 1 =1  AND URDA.ACTIVE_FLAG !='N'  AND FABU.SET_OF_BOOKS_ID = URDA.LEDGER_ID  AND FABU.NAME = GSB.NAME  AND PU.USER_ID = PUR.USER_ID  AND PUR.ROLE_ID = PRD.ROLE_ID  AND PRD.ROLE_ID = PRDT.ROLE_ID  AND PRDT.LANGUAGE = USERENV('LANG')  AND PUR.ROLE_GUID = PRD.ROLE_GUID  AND PRD.ROLE_COMMON_NAME = URDA.ROLE_NAME  AND PU.USERNAME = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )  AND URDA.USER_GUID = PU.USER_GUID  UNION  SELECT 1  FROM FUN_USER_ROLE_DATA_ASGNMNTS URDA ,    GL_SETS_OF_BOOKS FABU,    PER_USERS PU,    PER_USER_ROLES PUR,    PER_ROLES_DN PRD,    PER_ROLES_DN_TL PRDT,    GL_ACCESS_SETS gas,    GL_ACCESS_SET_ASSIGNMENTS GASA  WHERE 1 =1  AND URDA.ACTIVE_FLAG !='N'  AND FABU.NAME = GSB.NAME  AND FABU.SET_OF_BOOKS_ID = GASA.LEDGER_ID  AND GAS.ACCESS_SET_ID = GASA.ACCESS_SET_ID  AND GAS.ACCESS_SET_ID = URDA.ACCESS_SET_ID  AND PU.USER_ID = PUR.USER_ID  AND PUR.ROLE_ID = PRD.ROLE_ID  AND PRD.ROLE_ID = PRDT.ROLE_ID  AND PRDT.LANGUAGE = USERENV('LANG')  AND PUR.ROLE_GUID = PRD.ROLE_GUID  AND PRD.ROLE_COMMON_NAME = URDA.ROLE_NAME  AND PU.USERNAME = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )  AND URDA.USER_GUID = PU.USER_GUID  );

 

 

  Book Based Access:

  select * from fa_book_controls fbcwhereEXISTS  (SELECT 1  FROM FUN_USER_ROLE_DATA_ASGNMNTS URDA ,    FA_BOOK_CONTROLS FABU,    PER_USERS PU,    PER_USER_ROLES PUR,    PER_ROLES_DN PRD,    PER_ROLES_DN_TL PRDT  WHERE 1 =1  AND URDA.ACTIVE_FLAG !='N'  AND FABU.BOOK_CONTROL_ID = URDA.BOOK_ID  AND FABU.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE  AND URDA.USER_GUID = PU.USER_GUID  AND PU.USER_ID = PUR.USER_ID  AND PUR.ROLE_ID = PRD.ROLE_ID  AND PRD.ROLE_ID = PRDT.ROLE_ID  AND PRDT.LANGUAGE = USERENV('LANG')  AND PUR.ROLE_GUID = PRD.ROLE_GUID  AND PRD.ROLE_COMMON_NAME = URDA.ROLE_NAME  AND PU.USERNAME = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )  UNION  SELECT 1  FROM FUN_USER_ROLE_DATA_ASGNMNTS URDA ,    FA_BOOK_CONTROLS FABU,    PER_USERS PU,    PER_USER_ROLES PUR,    PER_ROLES_DN PRD,    PER_ROLES_DN_TL PRDT,    FND_SETID_SETS_VL FSS,    FND_SETID_ASSIGNMENTS FSA  WHERE 1 =1  AND URDA.ACTIVE_FLAG !='N'  AND FABU.BOOK_CONTROL_ID = FSA.DETERMINANT_VALUE  AND FSA.DETERMINANT_TYPE = 'AB'  AND FSA.SET_ID = FSS.SET_ID  AND URDA.SET_ID = FSA.SET_ID  AND FABU.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE  AND URDA.USER_GUID = PU.USER_GUID  AND PU.USER_ID = PUR.USER_ID  AND PUR.ROLE_ID = PRD.ROLE_ID  AND PRD.ROLE_ID = PRDT.ROLE_ID  AND PRDT.LANGUAGE = USERENV('LANG')  AND PUR.ROLE_GUID = PRD.ROLE_GUID  AND PRD.ROLE_COMMON_NAME = URDA.ROLE_NAME  AND PU.USERNAME = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )  );

Detailed info in Oracle security available in Oracle Docs.