Removing Credentials from a Cloned EBS Production Database

You can retrieve apps password from
$APPL_TOP/admin/$TWO_TASK/
adalldefaults.txt.


The following sequence of steps will remove production account credentials from the cloned copy of the production database and reestablish new credentials in the cloned copy. The new accounts in the cloned copy will all have the password "clone".

1. Step 1 - Clear all credentials
2. Step 2 - Re-establish basic accounts (for runtime: SYS,SYSTEM,APPLSYSPUB,APPLSYS,APPS + GUEST,SYSADMIN)
3. Step 3 - Prepare scripts for setting additional passwords
4. Step 4 - Assign new passwords to all database users not managed with EBS
5. Step 5 - Assign new passwords to all database users managed with EBS
6. Optional addition steps

Steps 1 - 4 are run on the database server running as oracle, using sqlplus connected as SYS or APPS; step 5 is run as applmgr on an application tier and uses the FNDCPASS command line utility.

This means that steps 1-4 can be performed the first time the cloned database is started, i.e. before it is made accessible to the network via the database TNS listener.

Step 5 is not time critical and can be performed when access to the cloned system for patch purposes is required.

All application tier processes must be stopped during this procedure.

Step 1 - Clear All Credentials

To clear all credentials in a cloned copy of a production database you must establish an shell environment with sufficient Oracle settings to run sqlplus via the BEQ driver.
The full environment could be established by your cloning process, the minimal environment is shown below

oracle$ export ORACLE_SID=<sid>
oracle$ export ORACLE_HOME=<db-oraclehome>
oracle$ export PATH=$ORACLE_HOME/bin
oracle$ unset TWO_TASK

oracle$ sqlplus '/ as sysdba' 

To clear all credentials in the cloned copy of a production database, run the following 3 SQL statements:

REM --- step1.sql
spool   step1.lst

REM  Start the database clone for the first time
startup restrict

REM Clear all production credentials from the cloned database

update SYS.user$ set 
      password = translate(password,'0123456789ABCDEF','0000000000000000') 
 where type#=1 and length(password) = 16 
/
update APPLSYS.FND_ORACLE_USERID set
       ENCRYPTED_ORACLE_PASSWORD='INVALID'
/

update APPLSYS.FND_USER set 
       ENCRYPTED_FOUNDATION_PASSWORD='INVALID',
       ENCRYPTED_USER_PASSWORD='INVALID'
/
commit;

REM  Shutdown the database
shutdown
exit

Now the cloned copy of the database is free from production credentials. The database was shut down in order for the unusual way of clearing the database user (schema) passwords to take effect.

Start the cloned copy of the database in preparation for steps 2, 3 and 4:

oracle$ echo startup | sqlplus '/ as sysdba' 

Step 2 - Re-establish Bootstrap Credentials

The database at the moment has no credentials, so you log on as SYS with operation system authentication.
This will allow you to establish new credentials.

oracle$ sqlplus '/ as sysdba'

The script below has comments explaining what is done in step 2.

REM --- step2.sql
spool   step2.lst
 
REM  Set a new password for a few initial database users
 
alter user SYS        identified by CLONE;
alter user SYSTEM     identified by CLONE;
alter user APPLSYSPUB identified by CLONE;
alter user APPLSYS    identified by CLONE;
alter user APPS       identified by CLONE;
 
REM  Provide boot-strap info for FNDCPASS...
update APPLSYS.FND_ORACLE_USERID set
       ENCRYPTED_ORACLE_PASSWORD='CLONE'
 where ORACLE_USERNAME = 'APPLSYSPUB'
/
 
update APPLSYS.FND_ORACLE_USERID set
       ENCRYPTED_ORACLE_PASSWORD='ZG' ||
        'B27F16B88242CE980EF07605EF528F9391899B09552FD89FD' ||
        'FF43E4DDFCE3972322A41FBB4DDC26DDA46A446582307D412'
 where ORACLE_USERNAME = 'APPLSYS'
/

update APPLSYS.FND_ORACLE_USERID set
       ENCRYPTED_ORACLE_PASSWORD='ZG' ||
        '6CC0BB082FF7E0078859960E852F8D123C487C024C825C0F9' ||
        'B1D0863422026EA41A6B2B5702E2299B4AC19E6C1C23333F0'
 where ORACLE_USERNAME = 'APPS'
/
commit;

REM  We run as SYS, now connect as APPS to run some plsql
connect APPS/CLONE

REM  Every EBS database needs a GUEST user
select APPS.fnd_web_sec.change_guest_password( 'CLONE', 'CLONE' ) "RES"
  from dual;
commit;

REM Set GUEST credential in site level profile option
set serveroutput on
declare
  dummy boolean;
begin
  dummy := APPS.FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/CLONE', 'SITE');
  if not dummy then
    dbms_output.put_line( 'Error setting GUEST_USER_PWD profile' );
  end if;
end;
/
commit;

REM  One more time for luck (avoid session caching of profiles)
connect APPS/CLONE

REM  Set SYSADMIN password
select APPS.fnd_web_sec.change_password('SYSADMIN','CLONE') "RES"
  from dual;
commit;
exit

The expected output from step 2 is as follows:

User altered.
User altered.
User altered.
User altered.
User altered.
1 row updated.
1 row updated.
1 row updated.
Commit complete.
Connected.
RES
------
Y
Commit complete.
PL/SQL procedure successfully completed.
Commit complete.
Connected.
RES
------
Y
Commit complete.

It is important to verify that no errors are reported and that the 2 returned RES values are both Y (for success).

Now the database should be able to run EBS as we have a set of bootstrap runtime users. (You will need to run AutoConfig to let the application know of the new passwords and other changes required for the cloned environment to actually run the application tier services. Don't run AutoConfig yet, we'll do that in Step 5.)

Step 3 - Prepare Scripts for Setting Additional Passwords

I this step we prepare to assign passwords to the other database users that were disabled in step 1. We use dynamically generated scripts to do this as the set of database users may be different between instances of EBS.

oracle$ sqlplus '/ as sysdba' 

The comments in script below explains what is done in step 3.

REM --- step3.sql

REM  Prepare SQL and SHELL scripts to set more passwords later
spool step3.lst

REM  Generate a sql script to set password for db users not managed with EBS

select 'alter user "'|| USERNAME ||'" identified by CLONE; '
  from SYS.DBA_USERS 
 where USERNAME not in (select ORACLE_USERNAME from APPLSYS.FND_ORACLE_USERID)
   and USERNAME not in ('SYS','SYSTEM');
   
REM  Generate a shell script to set password for all base product schemas

select 'FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone' from dual;

REM  Generate a shell script to set password for non-EBS db users managed with EBS

select 'FNDCPASS apps/clone 0 Y system/clone ORACLE "' || 
                     replace(ORACLE_USERNAME,'$','\$') || '" clone'
  from APPLSYS.FND_ORACLE_USERID
 where READ_ONLY_FLAG = 'X'
   and ORACLE_USERNAME in (select USERNAME from SYS.DBA_USERS);

REM  Generate a shell script to set password for APPS/APPLSYS/APPM_mrc db users

select 'FNDCPASS apps/clone 0 Y system/clone SYSTEM APPLSYS clone' from dual;

REM  Generate scripts for steps 4 & 5
spool off

HOST grep '^alter user ' step3.lst > dbusers4.sql
HOST grep '^FNDCPASS '   step3.lst > dbusers5.sh

exit

The script above calls grep to extract 2 sets of lines from the step3.lst spool file.
If you are running sqlplus on Windows, the shell redirection will fail when attempted from within sqlplus. You can perform this separation step by going to a command prompt (using the HOST command from sqlplus). If you have your MKS environment set, then you can use the grep syntax or alternatively you can use the below syntax from a cmd.exe prompt.

# alternative commands for extracting sql and shell commands from step3.lst
C:\ORACLE\Clone> findstr "^alter user " step3.lst > dbusers4.sql
C:\ORACLE\Clone> findstr "^FNDCPASS "   step3.lst > dbusers5.cmd

Step 4 - Assign New Passwords to All Schemas Not Managed with EBS

This step runs the SQL script - dbusers4.sql - generated in step 3.

Sample content of dbusers4.sql listed below for illustration purposes only, you must run the one you generated on your system.

NOTE:  dbusers4.sql example only!

alter user "OLAPSYS" identified by CLONE;
     ...
alter user "MDSYS" identified by CLONE;
alter user "ORDPLUGINS" identified by CLONE;
alter user "ORDSYS" identified by CLONE;
alter user "DBSNMP" identified by CLONE;
alter user "OUTLN" identified by CLONE;
alter user "AD_MONITOR" identified by CLONE;
alter user "EM_MONITOR" identified by CLONE;

Prior to running your script, you should review the contents for "obvious errors", this is good advice for any auto-generated scripts.

oracle$ sqlplus "/ as sysdba"

Now run the dbusers4.sql file

SQL> spool step4.lst
SQL> start dbusers4.sql
SQL> exit


The output should be a list of "User altered." lines - no error messages (ORA-nnnnn) should appear.

The database is currently running, shut it down and restart it.
To ensure that the application tier code can access the database for the 5th step you must also ensure that the database TNS-listener service is running.

oracle$ echo shutdown | sqlplus "/ as sysdba" 
oracle$ echo startup  | sqlplus "/ as sysdba" 
oracle$ lsnrctl start <listener_name>

Step 5 - Assign New Passwords to All Schemas Managed with EBS

This step uses the FNDCPASS command to set the passwords for all the managed schemas and all the base product schemas. FNDCPASS must be run from an application tier node.

You need to copy the dbusers5.sh script from the working directory used by oracle in step 3.
As with any generated scripts that you run on your system, you should review the contents of the file before running it. Note for Windows users: In the unlikely event that any of the usernames contain the dollar sign "$" it has been escaped by prefixing it by a backslash "\"; on windows the backslash should be removed.

To run FNDCPASS you also need a number of environment variables set, at a minimum ensure that

• FNDCPASS is on the shell's search PATH.
• ORACLE_HOME points to the tools ORACLE_HOME
• the TWO_TASK variable is set to a value that can be resolved via the $TNS_ADMIN/tnsnames.ora file to access the cloned copy of the database.

# Verify that the Oracle client environment is set to correct database
applmgr$ sqlplus -s  apps/clone <<EOF
 select SYSDATE,NAME from v\$DATABASE;
EOF

SYSDATE   NAME
--------- ---------
25-JUL-07 PRD12

applmgr$ mkdir ~/s5 ; cd ~/s5  # create new directory to hold output files
applmgr$ sh dbusers5.sh        # Run the FNDCPASS shell script

Sample content of a dbusers5.sh file is listed below for illustration purposes only, run the one you generated on your system.

NOTE: dbusers5.sh  example only!

FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone
   FNDCPASS apps/clone 0 Y system/clone  ORACLE "OWAPUB" clone
   FNDCPASS apps/clone 0 Y system/clone  ORACLE "ODM"    clone
   FNDCPASS apps/clone 0 Y system/clone  ORACLE "CTXSYS" clone
   FNDCPASS apps/clone 0 Y system/clone  SYSTEM APPLSYS clone 

Each run of FNDCPASS will generate output on the terminal and create a logfile in the current working directory, you should review these log files (example L2763902.log) for errors.

NOTE: If your version of FNDCPASS does not support the ALLORACLE mode, see Q5 in the Discussion section below.

To verify that you have assigned passwords to all the database users, run the following query and ensure that it does not return any rows.

SQL> select USERNAME,PASSWORD from DBA_USERS where PASSWORD='0000000000000000';

This concludes the clearing of account credentials from a cloned database - and the re-establishment of credentials such that the cloned database can be used for runtime and patch testing.

Additional Steps

What remains to be done is to set new passwords for additional applications users (fnd_users) or the creation of new test users, depending on what you need the cloned system for.
Changing passwords for applications users can be done using the Define User form (logged on as SYSADMIN/CLONE) or by calling FNDCPASS with the below syntax from an applmgr applications shell environment.

applmgr$ FNDCPASS apps/clone 0 Y system/clone USER <username> <password>

You may also wish to change the passwords to something other than "clone". You can use modified versions of the scripts in this note and you should reference the security best practices document for advice on changing passwords for an E-Business Suite system, see the References section below.

Running AutoConfig

Before you can actually run the cloned EBS system, a number of other configuration items such as system profile options must most likely be changed for the cloned environment. Items to change typically include

• IP addresses, hostnames and port numbers
• Profiles containing hostnames and port numbers
• Web interface URLs
• Hostnames of external services (mail, print, SSO)

The cloning notes - listed in the References section below - will provide you with information on how to run AutoConfig.

AutoConfig must be run on all tiers of the cloned system to propagate password changes and other changed settings into config files.

Prior to running AutoConfig ensure that the AutoConfig CONTEXT file contains the new GUEST password and the new password for APPLSYSPUB.

Password for	A/C Variable	New Value
APPLSYSPUB	s_gwyuid_pass	CLONE
GUEST	s_guest_pass	CLONE

list of steps that can

• Ensure that production credentials are not retrievable from a cloned copy of an EBS production database.
• Boot strap the cloned copy with enough "clone credentials" that it may be used for testing.

Regards,

Sukhwinder Singh