How to rest password to a user in Oracle Apps
--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/
--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/
SELECT usr.user_name,
get_pwd.decrypt
((SELECT (SELECT get_pwd.decrypt
(fnd_web_sec.get_guest_username_pwd,
usertable.encrypted_foundation_password
)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name =
(SELECT SUBSTR
(fnd_web_sec.get_guest_username_pwd,
1,
INSTR
(fnd_web_sec.get_guest_username_pwd,
'/'
)
- 1
)
FROM DUAL)),
usr.encrypted_user_password
) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = 'XXXX';
Script for creating user and assign sysadmin responsibility
DECLARE
lc_user_name VARCHAR2(100) := 'BHASKAR.REDDY';
lc_user_password VARCHAR2(100) := 'Oracle123';
ld_user_start_date DATE := TO_DATE(SYSDATE);
ld_user_end_date VARCHAR2(100) := NULL;
ld_password_date VARCHAR2(100) := TO_DATE(SYSDATE);
ld_password_lifespan_days NUMBER := 90;
ln_person_id NUMBER := NULL;
lc_email_address VARCHAR2(100) := NULL;
BEGIN
fnd_user_pkg.createuser
( x_user_name => lc_user_name,
x_owner => NULL,
x_unencrypted_password => lc_user_password,
x_start_date => ld_user_start_date,
x_end_date => ld_user_end_date,
x_password_date => ld_password_date,
x_password_lifespan_days => ld_password_lifespan_days,
x_employee_id => ln_person_id,
x_email_address => lc_email_address
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
SELECT *FROM FND_USER WHERE USER_NAME='BHASKAR.REDDY';
/
BEGIN
fnd_user_pkg.addresp
(username => UPPER
('BHASKAR.REDDY'),
resp_app => 'SYSADMIN',
resp_key => 'SYSTEM_ADMINISTRATOR',
security_group => 'STANDARD',
description => 'DESCRIPTION',
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Responsibility is not added due to'|| SQLCODE|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
/
No comments:
Post a Comment