|
|
APPLIES TO:
Oracle Applications
Manager - Version 11.5.9 to 12 [Release 11.5 to 1.2]
Oracle Application Object Library - Version 12.1.3 to 12.1.3 [Release 12.1] Information in this document applies to any platform.
When cloning a
Production database in Oracle E-Business Suite (EBS) it is a best practice to
remove all Production account credentials in the cloned copy of the database.
This will help to prevent retrieval of Production credentials, which could be
used to compromise the security and integrity of the Production database.
It is ideal to complete this process as soon as possible after the database data files have been copied. At a minimum it should be completed before the database is turned over to any party less trusted than the Production database DBA team. This document describes the steps required to remove the Production EBS database credentials, such as database user (schema) password hashes and encrypted passwords. Additionally information is provided about how to reestablish credentials in the cloned copy so that the clone may be used for functional, performance or patch application testing. Steps from this paper should be incorporated into your database cloning process and procedures.
Author :
Create Date 14-Mar-2007 Update Date 11-JUL-2011 Expire Date
The steps outlined in
this White Paper will:
The steps in this
document should be integrated in your database cloning process, see the
"Reference" section below for documentation on cloning EBS systems
for Releases 11i and 12.
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. All of the new accounts on the clone target will have the password "clone".
Steps 1 through 4 are
run on the database server running as the Operating System user, "oracle", using "sqlplus"
connected as the "SYS" or "APPS" database user. Step 5 is
run as the Operating System user "applmgr" on an
application tier and uses the "FNDCPASS"
command line utility. This means that steps 1 through 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.
To clear all
credentials on a target clone of a production database you must establish a
shell environment with sufficient Oracle environment variables to
successfully start "sqlplus" via the "BEQ" (bequeth)
driver. If Rapid Clone has been completed successfully, then each Oracle Home
should have a
$ export ORACLE_SID= $ export ORACLE_HOME= $ export PATH=$ORACLE_HOME/bin $ unset TWO_TASK
oracle$ sqlplus '/
as sysdba'
To clear all credentials in the cloned copy of a Production database, create and execute the following 3 SQL scripts:
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
REM end of script
At this point, the
cloned copy of the database is free from Production credentials. The database
was shut down by the script in order for the unusual way of clearing the
database user (schema) passwords to take effect. You will need to restart the
cloned copy of the database in preparation for steps 2, 3 and 4:
oracle$ echo
startup | sqlplus '/ as sysdba'
The database at the
moment has no credentials. Now log on as "SYS" with operation system
authentication. This will allow you to establish new credentials.
oracle$ sqlplus '/ as
sysdba'
Here is the script for
step 2, including inline comments which explains what is done.
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", which indicates success.
ATTENTION :
It has been identified, that some Customers running into an error for the SQL PLus command
select
APPS.fnd_web_sec.change_password('SYSADMIN','CLONE') "RES" from
dual;
In this case, please check Note 1350776.1 for the solution, before
your are going ahead with the next steps !
Now we have completed establishing a set of bootstrap EBS credentials in the database.
In this step scripts
are prepared to assign passwords to the other database users which were
disabled in Step 1. Dynamically generated scripts are used to accomplish this
because the set of database users may differ between instances of EBS. Create
the script below and run it as the Operating System user "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
REM End of Script
NOTE: The script above
calls the UNIX command
"grep" to extract 2
sets of lines from the step3.lst spool file. If you are running Windows,
the shell redirection will fail when attempted from within sqlplus. You can perform the failed 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 Windows
command (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
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", for example purposes 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;
Note: Prior to running your script, you should
review the contents of the script for any obvious problems or syntax errors-
this is good advice for any dynamically-created SQL scripts.
Connect as
"SYSDBA":
$ sqlplus "/ as
sysdba"
Now run the "dbusers4.sql" file:
SQL> spool
step4.lst
SQL> start dbusers4.sql SQL> exit The output spool file should show many output lines stating "User altered.". No error messages (ORA-nnnnn) should appear. At this point, the database should be started and running. Stop and restart the database at this time. To ensure that the application tier code can access the database for Step 5, you must also ensure that the database TNS-listener service is running.
$ echo shutdown |
sqlplus "/ as sysdba"
$ echo startup |
sqlplus "/ as sysdba"
$ lsnrctl start
This step uses the
"FNDCPASS" command to set the passwords for all
the EBS managed schemas and all the base product schemas. The "FNDCPASS" must be run from an application tier
node.(Any node with an APPL_TOP file system.)
You will need to locate and copy the "dbusers5.sh" script from the directory where it was created in Step 3. Again, as with any dynamcially 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:
# Verify that the
Oracle client environment is set to correct database (as "applmgr"
OS user)
applmgr$ sqlplus -s
apps/clone <
|
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
The following is sample content of a "dbusers5.sh" file is listed below for illustration purposes only, run the one you generated on your system.
NOTE: This "dbusers5.sh" is for 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 an output/log file in the current
working directory, you should review these log files (example "L2763902.log") for errors.
NOTE: If your version
of the "FNDCPASS" utility 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 and re-establishment of account credentials from a cloned database. Please see the following 2 steps "Additional Steps" and "Running Autoconfig" before attempting to use the system.
What remains to be
done is to set new passwords for additional applications users or the
creation of new test users, depending on your needs. Changing passwords for
applications users can be done using the "Define User" form
(logged on as "SYSADMIN/CLONE") or by running "FNDCPASS" with the below syntax from an "applmgr" applications shell environment.
applmgr$ FNDCPASS
apps/clone 0 Y system/clone USER
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.
Before you can
actually start and access the cloned EBS system from the Application, a
number of other configuration items, such as system Profile Options, most
likely need to be changed in 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 "Reference" section below, will provide you with
information on how to run AutoConfig. Running AutoConfig is a requirement and
it must be run on all tiers of the cloned system to propagate password
changes and other changed settings into Autoconfig-managed files.
Prior to running AutoConfig ensure that the AutoConfig Context file contains the new "GUEST" password (Context variable "s_guest_pass") and the new password for "APPLSYSPUB" (Context variable "s_gwyuid_pass").
Prior to running AutoConfig ensure that the AutoConfig Context file contains the new "GUEST" password (Context variable "s_guest_pass") and the new password for "APPLSYSPUB" (Context variable "s_gwyuid_pass").
|
Password for
|
Context Variable
|
New Value
|
|
APPLSYSPUB
|
s_gwyuid_pass
|
CLONE
|
|
GUEST
|
s_guest_pass
|
CLONE
|
