When it comes to database administration, understanding the underlying structures of Oracle EBS and Oracle Fusion is crucial. Both systems approach user access differently, which is reflected in their database designs and the SQL queries used to manage responsibilities and roles.
Database Structure in Oracle EBS:
Oracle EBS is built on a traditional database-centric architecture, where responsibilities are central to user access management. The structure is designed to support a form-based interface with a focus on direct database interactions
SQL Query for Active Users and Responsibilities
To generate a list of active users and their assigned responsibilities, the following SQL query can be utilized:
SELECT usr.user_name, usr.start_date, usr.end_date,
usr.description, ftl.application_name, resp.responsibility_name,
usr_resp.start_date responsibility_start_date,usr_resp.end_date responsibility_end_date
FROM fnd_user_resp_groups_direct usr_resp, fnd_user usr, fnd_responsibility_vl resp , fnd_application_vl fvl
WHEREusr_resp.user_id = usr.user_id
ANDusr_resp.responsibility_id = resp.responsibility_id
AND fvl.application_id =usr_resp.responsibility_application_id
AND fvl.language = USERENV('LANG')
AND SYSDATE BETWEENusr_resp.start_date AND NVL(usr_resp.end_date,SYSDATE + 1)
AND SYSDATE BETWEEN usr.start_date AND NVL(usr.end_date,SYSDATE + 1)
AND SYSDATE BETWEEN resp.start_date AND NVL(resp.end_date,SYSDATE + 1)
This query is designed to exclude standard users such as SYSADMIN and GUEST, offering a focused view of active users and their responsibilities.
The below entity relationship diagram shows the relation among the tables FND_USER_RESP_GROUPS, FND_USER, FND_RESPONSIBILTY_VL, FND_APPLICATION_VL.
Database Structure in Oracle Fusion:
In contrast, Oracle Fusion adopts a service-oriented architecture (SOA) that leverages orchestration technology. This modern approach allows for a more flexible and modular design, where roles are composed of duty roles and privileges, offering layered access control.
SQL Query for Users and Roles
The following SQL query can be used to fetch the roles assigned to users in Oracle Fusion:
SELECT usr.username, usr.start_date,usr.end_date, role_tl.role_name, prd.role_common_name
FROM per_user_roles usr_rol, per_users usr, per_roles_dn_tl role_tl, per_roles_dn rol
WHERE usr.user_id = usr_rol.user_id
AND role_tl.role_id = usr_rol.role_id
AND role_tl.role_id = rol.role_id
AND role_tl.language = USERENV ('lang')
AND usr.active_flag = 'Y'
ORDER BY usr.username, role_tl.role_name
This query provides a list of usernames along with their corresponding role IDs and names, concentrating solely on active users.
The below entity diagram shows the relationship among the tables PER_USER_ROLES, PER_USERS, PER_ROLES_DN, PER_ROLES_DN_TL
Conclusion:
The transition from Oracle EBS to Oracle Fusion represents not just a shift in user access management but also a fundamental change in database architecture. The SQL queries highlight the structural differences, with EBS focusing on responsibilities within a form-based system, and Fusion emphasizing roles within a service-oriented framework. Understanding these differences is essential for database administrators to navigate and manage these systems effectively.
DataFusing CloudSQL is your streamlined gateway to the world of Oracle Fusion, offering a clear and intuitive path through its complex database structures. It’s the simplicity you need to unlock the power of advanced database management.
