Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, July 19, 2007

SQL to create user with System Administrator privileges

Source: http://oracle.anilpassi.com/create-oracle-fnd-user-with-system-administrator.html

Create Oracle FND_USER with System Administrator

Written by Anil Passi
Tuesday, 29 August 2006
If you have the Apps Password, its quite easy to create a FND_USER for yourself by using the API.
I find this script very useful when development environment gets cloned from Production(that is when i do not have FND_USER in Production.
Please note that:-
1. You will be allocated System Administrator by this script. Hence you can assign whatever responsibilities that you desire latter, after logging in.
2. The password will be set to oracle
3. You need apps password to run this script. Alternately you need execute permission on fnd_user_pkg from the user where this script will be run. If using some other user, please use apps.fnd_user_pkg.createuser
4. You need to do a COMMIT after this script has run. I have not included the commit within this script.
5. When running this script, you will be prompted to enter a user name.

--------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 => 'oracle'
,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 email address is being protected from spam bots, 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);
END;
/

9 comments:

Deepak said...

Thanks,
This help me out great.

- Deepak

Chris Bittakis said...

Question. When using an account other then APPS I get the following error:

ORA-20001: Custom user name validation in subscription to event oracle.fnd.user.name.validate failed.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_USER_PKG", line 3367
ORA-06512: at "APPS.FND_USER_PKG", line 696
ORA-06512: at "APPS.FND_USER_PKG", line 891
ORA-06512: at "APPS.FND_USER_PKG", line 1010
ORA-06512: at line 1


Any idea how to get around this?

Thanks,
Chris

Vikram Das said...

Hi Chris,

Do you mean that you are connecting to the database as a non APPS user and running this script ? If yes, it won't succeed, you need to be connected to the database as the APPS user.

- Vikram

Michael said...

Hi

I use following command and try to add FND_USER

BEGIN
APPS.FND_USER_PKG.CreateUser
(
'TEST123',
'CUST',
'TEST123',
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE+1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
END;

but, I got
Error: ORA-20001: Unabled to create user TEST123 for this following reason:
This user name is already in use. Please enter a unique user name..
ORA-06512: at "APPS.FND_USER_PKG", line 367
ORA-06512: at "APPS.FND_USER_PKG", line 410
ORA-06512: at "APPS.FND_USER_PKG", line 529
ORA-06512: at line 2

Any comments?

Thanks

Michale

Ram said...

Hi! Vikram,
As Chris told, we are getting the same error "ORA-20001: Custom user name validation in subscription to event oracle.fnd.user.name.validate failed". This error is not getting on one landscape where we connected to Database other than APPS users and gave neccessary grants and created synonyms. But in another landscape we are getting this error (same grants and synonyms are created for User).

Please let know what might be the problem.

Thanks,
Ramu

Vikram Das said...

Hi Ramu,

You need to run the sql exactly as it is given in the post. x_employee_id needs to be given. It doesn't work if x_employee_id is null.

- Vikram

Ram said...

Hi! Vikram,
Thanks for your reponse.
We are passing employee_id, but still we are getting this error. Please let me know

Thanks,
Ramu

Vikram Das said...

Hi Ram,

As per the error TEST123 already exists. I tried your command in my test instance and it executed successfully:

SQL> BEGIN
APPS.FND_USER_PKG.CreateUser
(
'TEST123',
'CUST',
'TEST123',
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE+1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
END; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
23 /

PL/SQL procedure successfully completed.

- Vikram

Anonymous said...

Enter value for enter_user_name: TEST111
old 5: v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
new 5: v_user_name VARCHAR2(30) := upper('TEST111');
DECLARE
*
ERROR at line 1:
ORA-20001: APP-FND-02912: User TEST111: The Person is linked to an invalid
Employee (Employee ID = 30).
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_USER_PKG", line 2960
ORA-06512: at "APPS.FND_USER_PKG", line 313
ORA-06512: at "APPS.FND_USER_PKG", line 3835
ORA-06512: at "APPS.FND_USER_PKG", line 1101
ORA-06512: at "APPS.FND_USER_PKG", line 1196
ORA-06512: at "APPS.FND_USER_PKG", line 1315
ORA-06512: at line 9