Oracle EBS users can be created from the front end through Oracle forms or using backend PLSQL Blocks as well. We will see how to create user in oracle apps from the backend in this post
Application User can be created from sqlplus using PLSQL Block
Set serveroutput on BEGIN fnd_user_pkg.CreateUser(x_user_name => 'UserName' ,x_owner => 'CUST' ,x_unencrypted_password => 'userpass' ,x_description => 'Your complete name' ); commit; END; /
Let’s see an example.
Set serveroutput on BEGIN fnd_user_pkg.CreateUser(x_user_name => TEST' ,x_owner => 'CUST' ,x_unencrypted_password => 'TESTWELCOME1' ,x_description => 'TEST' ); commit; END; /
This will create an application user TEST with the password TESTWELCOME1 in the FND_USER table.
Upon the creation of a new EBS user, the user is required to change the password associated with the account on the first login. We can avoid this by executing the below query
set serveroutput on declare l_user_name varchar2(2000); begin l_user_name := 'TEST'; fnd_user_pkg.updateuser(x_user_name => l_user_name, X_OWNER => 'CUST', x_password_date =>sysdate); end; /
You can add more details using the below block
Set serveroutput on BEGIN fnd_user_pkg.createuser ( x_user_name => 'UserName', x_owner => 'CUST', x_unencrypted_password => 'test pass', x_start_date => '20-JUN-2020', x_end_date => '20-JUN-2030', x_password_date => '20-JUN-2020', x_password_lifespan_days => 130 x_employee_id => 476476, x_email_address => '[email protected]' ); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(SQLERRM); END; /
There will be no responsibilities assigned to the user once it is added. Using the API below, we can assign responsibilities to the user.
Set serveroutput on BEGIN fnd_user_pkg.AddResp(username => 'UserName' ,resp_app => 'FND' ,resp_key => 'APPLICATION_DEVELOPER' ,security_group => 'STANDARD' ,description => 'Application Developer' ,start_date => SYSDATE ,end_date => NULL ); fnd_user_pkg.AddResp(username => 'UserName' ,resp_app => 'SYSADMIN' ,resp_key => 'SYSTEM_ADMINISTRATOR' ,security_group => 'STANDARD' ,description => 'System admin' ,start_date => SYSDATE ,end_date => NULL ); COMMIT; END; /
This will add application developer and system administrator responsibilities to the user. Hope you like this article on how to create user in oracle apps from backend
FNDCPASS : FNDCPASS & AFPASSWD is the utility used to change the apps schema ,Oracle EBS schema and user password in Oracle EBS all versions
Guest User password in 11i/R12 : Check out how to troubleshoot Guest User password in 11i/R12, how to change the guest user password and how to check it
oracle apps dba interview questions : You should not miss these 60 awesome oracle apps dba interview questions.Must read to succeed in interviews and jobs.Download also available
APPLSYSPUB schema : applsyspub schema is the public schema in Oracle EBS which is used first while connecting to Oracle Forms and Oracle OAF pages.
oracle apps queries : This page contains the very useful and practical Top 30 Useful oracle apps queries for APPS DBA to help in day to day administration activities
How To Set The Password Expiration Field To xx Days As Default? (Doc ID 758036.1)