How to track sysadmin password in EBS R12 or track password changes

Recently, in one of our single node EBS R12 environment we were facing a issue where sysadmin password was getting changed intermittently. Every time when  DBA team tried to login with the old password they had for SYSADMIN user, it seems to be not working.

We knew that there is something fishy, as few days back we gave sysAdmin responsibilities to some of the users knowing the fact that this is high level of privilege for any user who is not involved in any system administration work.
We had to do this as per the client's business requirement.

You can run below select on FND_USERS table for the user to get the information about when records related to that user were updated..

select USER_NAME,USER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,LAST_LOGON_DATE from fnd_user where USER_NAME='SYSADMIN';

If you're looking for more information, you may have to enable Audit on the table in question. The audit trail functionality in Oracle apps allows Applications administrators to monitor data changes made on Oracle tables. Audit trail is not enabled by default as this means additional load on the server, so be very careful before making this change in production servers.
Let's say we have to enable audit for table FND_USERS, oracle will internally create an audit table FND_USER_A with selected columns for FND_USER_A. Now, all the inserts,updates done on FND_USER will be recorded in audit table.

• Enable audit for the instance

Set the value of profile Option, AuditTrail:Activate, on the Site level to Yes.
Navigation: Profile > System

• Run below select to the Application short name

select f1.TABLE_NAME, f1.USER_TABLE_NAME, f2.APPLICATION_SHORT_NAME, f2.APPLICATION_NAME from fnd_application_vl f2, FND_TABLES f1 where f1.TABLE_NAME='FND_USER' and f1.APPLICATION_ID=f2.APPLICATION_ID;

• Enable audit for the application

Enable audit for the application to which the base tables belong.
Navigation: Security > Audit > Install
Check the box named, Audit Enabled. Save and close the form.

• Add the table for auditing

The table has to be added in Oracle for auditing
Navigation: Security > AuditTrail > Tables
Query for the tables, FND_USERS and select the columns on which auditing will be done.

• Add the columns whatever you want to audit.
• Create AuditTrail Group

Go to Audit Trail -> Groups -> Query for Audit Setup Group

• Enable the 'Audit Setup Group'.
• Run audittrail update table.
• Add the Define an application user table under Audit Setup Group.
• Run the audit train update table again.
• Go to Audit Query Navigator ->Functional Groups-> Now you can see the Define an Application user table added under the audit setup
  group
• If the table is available as per the last step, now run the Audit trail report,you will be able to get the audit information.

Now, let see how can we SYSADMIN password in EBS R12.

There are three very simple steps of doing that.

1.  Connect to database as APPS user.
   
   SQL> conn apps
   Password:****
   Connected.
2. Create a Function to decrypt the encrypted password.
   
   CREATE OR REPLACE PACKAGE XXX_GET_PWD
   AS
      FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
         RETURN VARCHAR2;
   END XXX_GET_PWD;
   /
   
   CREATE OR REPLACE PACKAGE BODY XXX_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 XXX_GET_PWD;
   /
3. Query to find out the username for the password.
   
   SELECT Usr.User_Name,
          Usr.Description,
          XXX_GET_PWD.Decrypt (
             (SELECT (SELECT XXX_GET_PWD.Decrypt (
                                Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                Usertable.Encrypted_Foundation_Password)
                        FROM DUAL)
                        AS Apps_Password
                FROM applsys.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 applsys.Fnd_User Usr
    WHERE Usr.User_Name = '&User_Name'; --SYSADMIN

 

HTH

Thanks for reading..!!