Eldapo

Thursday, April 23, 2009

create an EBS user with sysadmin privs

Rob and I are researching how to do bulk user creates and responsibility assignments in Oracle E-Business Suite and he stumbled on this PL/SQL script from Anil Passi. Full article is here.

A related article by the same author is FND_USER Script for Techies. Exposes the same methods with a little more explaination.

Here is code:

--------Beging of script--------------
DECLARE
--By: Anil Passi
--When Jun-2001
  v_session_id INTEGER := userenv('sessionid');
  v_user_name  VARCHAR2(30) := upper('&Enter_User_Name');
BEGIN
  --Note, can be executed only when you have apps password.
  -- Call the procedure to Creaet FND User
  fnd_user_pkg.createuser(x_user_name                  => v_user_name
    ,x_owner                      => ''
    ,x_unencrypted_password       => 'welcome123'
    ,x_session_number             => v_session_id
    ,x_start_date                 => SYSDATE - 10
    ,x_end_date                   => SYSDATE + 100
    ,x_last_logon_date            => SYSDATE - 10
    ,x_description                => 'appstechnical.blogspot.com'
    ,x_password_date              => SYSDATE - 10
    ,x_password_accesses_left     => 10000
    ,x_password_lifespan_accesses => 10000
    ,x_password_lifespan_days     => 10000
    ,x_employee_id                => 30 /*Change this id by running below SQL*/
     /*
     SELECT person_id
           ,full_name
     FROM   per_all_people_f
     WHERE  upper(full_name) LIKE '%' || upper('full_name’) || ‘%’
     GROUP  BY person_id
              ,full_name
     */
    ,x_email_address => ‘ appstechnical.blogspot@gmail.comThis e-mail address is being protected from spambots. You need JavaScript enabled to view it ‘
    ,x_fax           => ‘’
    ,x_customer_id   => ‘’
    ,x_supplier_id   => ‘’);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => ‘SYSADMIN’
                      ,resp_key       => ‘SYSTEM_ADMINISTRATOR’
                      ,security_group => ‘STANDARD’
                      ,description    => ‘Auto Assignment’
                      ,start_date     => SYSDATE - 10
                      ,end_date       => SYSDATE + 1000);
COMMIT;
END;

This needs to be executed as APPS user (if not APPS either must have: execute permission on fnd_user_pkg; or use apps.fnd_user_pkg.createuser — I have no idea what that means). Script prompts for user name (it is interactive as set out here). I have changed default password from original (was ‘oracle’) and added commit at end.

Syntax to run is:

SQL> @/path-to-script/script-name

For example:

SQL> @/tmp/make_ebs_user.sql

• Posted by Eldapo at 13:05:25 in Uncategorized