Oracle ebs 11i clone 過來的新系統，需要修改的幾個賬戶

今天一個朋友問起的。。

以前雖然整理過，但還不是很全面。

現在找個ORACLE 比較權威的文件，和大家一起共享一下

[@more@]

Applies to:

Oracle Applications Manager - Version: 11.5.9 to 12.0
Information in this document applies to any platform.

Abstract

When cloning a production database instance of the Oracle E-Business Suite (EBS), all production account credentials should be cleared in the cloned copy of the database. This will prevent retrieval of credentials that could be used to compromise of the integrity of the production database.

Ideally this happens as soon as possible after the database data files have been copied, at least it should be done before the database is turned over to a party less trusted than the production database DBA team.

This document describes the steps required to clear the cloned copy of the production EBS database of credentials such as database user (schema) password hashes and encrypted passwords and goes on to describe how to reestablish credentials in the cloned copy such 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.

Document History

Author :
Create Date 14-Mar-2007
Update Date 25-Jul-2007
Expire Date

Removing Credentials from a Cloned EBS Production Database

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
To clear all credentials in the cloned copy of a production database, run the following 3 SQL statements:
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:

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.
The script below has comments explaining what is done in step 2.

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.
The comments in script below explains what is done in step 3.

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.

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.

Now run the dbusers4.sql file

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.

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.

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.


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.

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.

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

Summary

In this whitepaper we have outlined a 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.

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.

Discussion

Q1: This document assumes that the FNDNAM in my database is APPS?
If you have configured your database to use a database user other than APPS for the FNDNAM, you must change the above scripts accordingly. The scripts were written using APPS as FNDNAM to make them more readable.

Q2: The database user password hashes are cleared using an update against SYS.USER$, is that supported?

It is a cruel and unusual way of doing it and certainly not the recommended way in general.
It may not work on database version 11g, however it works on 9i and 10g versions.
Remember these scripts are being run on a copy of your database, so the risk is minimal.
Feel free to change the scripts to use "alter user ..." syntax instead.

Q3: Should the clearing of application user passwords do a full overwrite of the current values?

If you are concerned that someone might be using a database block dumper, and run bruteforce to guess at the overwritten parts of the encryped passwords in FND_USER, you should overwrite the full length of the current value, commit and then set the value to "INVALID".
Simply add an update statement against FND_USER using translate() modeled off the update of USER$.

Q4: How do I integrate the clearing of passwords in the Rapid Clone process?

The step in this note will likely be incorporated in future versions of the Rapid Clone notes. To use this note with the current (as of April 1st 2007) versions of the Rapid Clone notes:

11i - Cloning Oracle Applications Release 11i with Rapid Clone
Perform steps 1-4 of this note between steps 3a and 3b of note
Perform step 5 of this note any convenient time before you need to use the cloned environment.

R12 - Cloning Oracle Applications Release 12 with Rapid Clone

Perform steps 1-4 of this note between steps 3a and 3b of note <406982:1>
Perform step 5 of this note any convenient time before you need to use the cloned environment.

Q5: My version of FNDCPASS does not support the ALLORACLE mode, what should I do?

The FNDCPASS with the ALLORACLE mode is available

• in RUP 4 (11i.ATG_PF.H RUP 4, )
• via patch for bug 4745998 "FNDCPASS new mode : ALLORACLE"

you should get the newer version for your own convenience.

However if you are eager to get it running now, with your current version of FNDCPASS, you can do so.

You will have to run FNDCPASS in ORACLE mode for each of the base product schemas, to generate the script to automatically generate these commands, you must change the script step3.sql as follows:

Change:
to

Q6: I Cannot Login as sysadmin/clone?

If this is the case, try logging in as SYSADMIN/CLONE (uppercase password).

Q7: Will this work if I use SSO?

These procedures have only been tested with local authentication of FND_USERs. The cloning notes will probably need to be updated to cover the "integrated with SSO" scenario.
However if your production system uses SSO and 2 way synchronization is active, you will most likely wish to break the connection from the cloned copy of the database to your production SSO system. At a minimum you will want to clear the SSO credential stored in the EBS clone such that it cannot be retrieved and used to compromise the production SSO system. The below SQL statements will delete SSO related passwords from the user profile table; incorporate at beginning of step 1.