Oracle EBS R12: Create User & Assign Responsibility — Step-by-Step DBA Guide with SQL Verification

This guide explains how to create a user in Oracle E-Business Suite R12, assign a responsibility, and verify the setup using SQL queries.

---

1. Business Requirement

As an Oracle Apps DBA, you may receive a request to create a new EBS application user and assign required responsibilities.

Example Requirement:

Create EBS user       : MAHILANIA
Assign Responsibility : System Administrator
Application          : System Administration

---

2. Important Tables Used

Table Name	Purpose
FND_USER	Stores EBS application user details
FND_RESPONSIBILITY	Stores responsibility details
FND_RESPONSIBILITY_TL	Stores translated responsibility names
FND_APPLICATION	Stores application details
FND_USER_RESP_GROUPS_DIRECT	Stores direct user responsibility assignments

---

3. Source the EBS Environment

Login to the application tier as the application OS user and source the environment file.

cd $INST_TOP/ora/10.1.2
. <CONTEXT_NAME>.env

Or source the main EBS environment file:

. /u01/oracle/EBSapps.env run

---

4. Connect to SQL*Plus as APPS

sqlplus apps/<apps_password>

---

5. Create EBS User Using FND_USER_PKG

Use the standard Oracle seeded API FND_USER_PKG.CREATEUSER to create an application user.

BEGIN
  FND_USER_PKG.CREATEUSER(
    x_user_name              => 'MAHILANIA',
    x_owner                  => 'CUST',
    x_unencrypted_password   => 'Welcome123',
    x_start_date             => SYSDATE,
    x_end_date               => NULL,
    x_password_date          => SYSDATE,
    x_email_address          => 'mahilania@example.com'
  );

  COMMIT;
END;
/

Note: Replace the password and email address as per your organization policy.

---

6. Verify User Creation

SELECT user_id,
       user_name,
       start_date,
       end_date,
       email_address,
       creation_date
FROM   fnd_user
WHERE  user_name = 'MAHILANIA';

---

7. Find Responsibility Details

Before assigning a responsibility, find the correct responsibility name, responsibility ID, application ID, and security group ID.

SELECT fr.responsibility_id,
       fr.application_id,
       frt.responsibility_name,
       fa.application_short_name
FROM   fnd_responsibility fr,
       fnd_responsibility_tl frt,
       fnd_application fa
WHERE  fr.responsibility_id = frt.responsibility_id
AND    fr.application_id = frt.application_id
AND    fr.application_id = fa.application_id
AND    frt.language = USERENV('LANG')
AND    frt.responsibility_name LIKE 'System Administrator';

---

8. Assign Responsibility to User

Use FND_USER_PKG.ADDRESP to assign responsibility to the EBS user.

BEGIN
  FND_USER_PKG.ADDRESP(
    username       => 'MAHILANIA',
    resp_app       => 'SYSADMIN',
    resp_key       => 'SYSTEM_ADMINISTRATOR',
    security_group => 'STANDARD',
    description    => 'System Administrator responsibility assigned by Apps DBA',
    start_date     => SYSDATE,
    end_date       => NULL
  );

  COMMIT;
END;
/

---

9. Verify Responsibility Assignment

SELECT fu.user_name,
       frt.responsibility_name,
       fa.application_short_name,
       furg.start_date,
       furg.end_date
FROM   fnd_user fu,
       fnd_user_resp_groups_direct furg,
       fnd_responsibility_tl frt,
       fnd_responsibility fr,
       fnd_application fa
WHERE  fu.user_id = furg.user_id
AND    furg.responsibility_id = fr.responsibility_id
AND    furg.responsibility_application_id = fr.application_id
AND    fr.responsibility_id = frt.responsibility_id
AND    fr.application_id = frt.application_id
AND    fr.application_id = fa.application_id
AND    frt.language = USERENV('LANG')
AND    fu.user_name = 'MAHILANIA';

---

10. Check User Login Status

SELECT user_name,
       start_date,
       end_date,
       password_date,
       password_lifespan_days,
       password_accesses_left
FROM   fnd_user
WHERE  user_name = 'MAHILANIA';

---

11. End Date a Responsibility

If you need to remove access, do not delete records directly. End-date the responsibility using Oracle API.

BEGIN
  FND_USER_PKG.DELRESP(
    username       => 'MAHILANIA',
    resp_app       => 'SYSADMIN',
    resp_key       => 'SYSTEM_ADMINISTRATOR',
    security_group => 'STANDARD'
  );

  COMMIT;
END;
/

---

12. End Date an EBS User

UPDATE fnd_user
SET    end_date = SYSDATE
WHERE  user_name = 'MAHILANIA';

COMMIT;

---

13. Common Issues

Issue	Possible Cause	Action
User not visible in EBS	User not committed or wrong username	Verify in FND_USER
Responsibility not visible after login	Incorrect responsibility key or application short name	Verify responsibility details using SQL
Password issue	Password policy restriction	Reset password from System Administrator responsibility
Responsibility expired	End date is already set	Check FND_USER_RESP_GROUPS_DIRECT

---

14. Best Practices for Apps DBA

• Always use Oracle seeded APIs where possible.
• Do not directly insert records into FND tables.
• Take approval before granting powerful responsibilities.
• Use strong password policies.
• Validate user and responsibility assignment using SQL.
• End-date users when access is no longer required.
• Keep audit details for production access changes.

---

15. Quick Validation Script

SET LINES 200
COL user_name FORMAT A20
COL responsibility_name FORMAT A40
COL application_short_name FORMAT A20

SELECT fu.user_name,
       frt.responsibility_name,
       fa.application_short_name,
       furg.start_date,
       furg.end_date
FROM   fnd_user fu,
       fnd_user_resp_groups_direct furg,
       fnd_responsibility_tl frt,
       fnd_responsibility fr,
       fnd_application fa
WHERE  fu.user_id = furg.user_id
AND    furg.responsibility_id = fr.responsibility_id
AND    furg.responsibility_application_id = fr.application_id
AND    fr.responsibility_id = frt.responsibility_id
AND    fr.application_id = frt.application_id
AND    fr.application_id = fa.application_id
AND    frt.language = USERENV('LANG')
AND    fu.user_name = UPPER('&USER_NAME');

---

Conclusion

Creating an Oracle EBS R12 user and assigning responsibility is a common Apps DBA activity. The safest approach is to use Oracle seeded APIs such as FND_USER_PKG.CREATEUSER and FND_USER_PKG.ADDRESP. Always verify the user and responsibility assignment from backend tables before confirming access to the business team.

Author: Punit Kumar
Role: Oracle Apps DBA / Oracle DBA Specialist