Security | Oracle 11i E-Business Suite from the Front Lines

< Day Day Up >

One significant thing to notice is that Oracle E-Business Suite installs with a complete set of predefined passwords. This is not necessarily a bad thing; it does install 150 plus schemas and trying to creatively come up with that many passwords during an installation would be difficult. It is not completely a good thing, however. That means that anyone who has ever been connected with an installation of the software and anyone who has had access to documentation or an article knows what these passwords are. It also means that if you have an interface to the Internet, you are potentially leaving yourself open to someone being able to simply log into your system with one of those passwords and have access to much or all of your information.

Changing Passwords

One of the most important things to know about security is that it is critical that you change the apps password, the applsys password, and the passwords for all schemas that are responsible for licensed or shared products in your implementation. There are two methods for changing passwords — the Manual Method and the Batch Method. The Manual Method requires that you follow all instructions to the letter and in the exact order specified. You are highly discouraged from following the Manual Method if you have more than just a few licensed products, as the act of changing the passwords can get long and tedious.

First (and probably by far the most important step) back up the FND_ORACLE_USERID table and the FND_USER table. This needs to be done regardless of what method you use (Manual or Batch) as a precaution to enable you to back out anything that might go awry. As the apps user:

 create table FND_ORACLE_USERID_BAK as select * from FND_ORACLE_USERID create table FND_ USER_BAK as select * from FND_ USER

This will allow you to get back your previous settings if you totally mess up your system and no one can log on. Yes, it is that important. Otherwise you would have to restore from the previous good backup. Once the table is backed up, you are safe to follow the following procedure exactly as stated.

Make sure that all users are logged out of the application.
Shutdown the Concurrent Managers and make sure that any running concurrent jobs are finished.
Log into the applications' front end as a user with sysadmin responsibility.
Navigate to Register Oracle IDs form by following Security/Oracle/Register.
Query up into the form all available Oracle IDs in your version.
Log into SQL*Plus as SYSTEM or as SYSDBA (preferred).
```
 $ sqlplus '/as sysdba' 
```
In your applications session, enter your new password for the APPLSYS user in the following manner:
1. View/Query by example/Run
2. Change password of applsys/press down arrow.
3. Verify password of applsys/press down arrow.
4. File/save. Do not under any circumstances requery or exit the form at this time.

In the SQL*Plus session, run the following command:

 SQL> alter user applsys identified by <same new password as you entered into the screen>;

Back in the open applications session, enter the new password for APPS (the same one that was for applsys):
1. View/Query by example/Run
2. Change password of apps/press down arrow.
3. Verify password of apps/press down arrow.
4. File/save. Do not under any circumstances requery or exit the form at this time.

In the SQL*Plus session, run the following command:

 SQL> alter user applsys identified by <same new password as you entered into the screen>;

Exit entirely out of the application.
Completely close all of your browser sessions and empty the cache.
Log completely out of SQL*Plus.
Open a new browser session and log into the application as a user with sysadmin responsibility again.
Restart your Concurrent Managers after making sure that you changed the batch Concurrent Manager startup script password (remember, this script uses the APPS password).

You have now changed the two most critical passwords. You have up to 180 others that you will now have to change individually in exactly the same manner to make sure that your system and data are protected not only from anyone trying to gain unlawful access to your data, but from well meaning users or developers who want to see how things are done or who want quick access to just one little thing. It is important to note that this can take several hours if you have many modules implemented.

Fortunately, there is a simpler method. Oracle provides a utility called FNDCPASS that allows you to change passwords in batch mode. Patch 1685689 provides you with this utility (check for new versions that include FNDCPASS through the patch download screens).

After you have installed the patch, you can run the following command to change the passwords:

Format:
- FNDCPASS apps/<apps password> 0 Y system/<system password> SYSTEM APPLSYS APPS1
Examples:
- FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS APPS1
- FNDCPASS apps/apps 0 Y system/manager ORACLE BIS BIS1
- FNDCPASS apps/apps 0 Y system/manager USER CUSTOM_USER USER1

The first example would be used to change the applsys password. By specifying SYSTEM parameter, you are telling FNDCPASS to expect the next argument in the string to be the applsys name and the password you want to assign to the applsys user. The steps that Oracle RDBMS and Financials together make at this point are:

Validate the applsys username is correct.
Reencrypt all of the passwords in the FND_USER table based on the new applsys password (it is used as a seed value from what I understand).
Reencrypt all passwords (again based on the value of the applsys password) in the FND_ORACLE_USERID table.
Updates the applsys password in the FND_ORACLE_USERID table and sets all IDs with the read only flag that refers to the fact that the associated user IDs have to have the same password as applsys. It sets those passwords the same as the applsys password at this point.
Runs an alter user <username> identified by <new password> command for all affected users at the database level.

Example 2 (Oracle with BIS) passes Oracle as a parameter, telling FNDCPASS that it is an Oracle user ID and password (not an apps one) so all it needs to do is update and reencrypt the password in the FND_ORACLE_USERID table and run the alter user identified by script at the database level.

Note

APPLSYS's password and APPS's password always have to be identical.

Other Security Issues

But this is not the only place where you are vulnerable. Your entire system resides on a set of servers. It sits on a network and is communicated to by means of a series of workstations (either PCs or thin clients) and is likely developed against by programmers and developers. Much of the information in the system is personal information, much more is information that only certain people within the company should really have access to read, let alone alter. There are security holes that people rarely consider and several others that sometimes are simply allowed to exist. Any of these holes could potentially mean disaster for your implementation. Many of these holes are simple to fix.

On the Development server, any of the developers and programmers who work with the system should have rights to alter the data via the apps interface. They should have the ability to compile their forms and reports and PL/SQL code and have their own play area in which they can freely work. They should probably not have the same privileges in Production. It is not so much that anything would happen to the Production server due to maliciousness, but a badly formed SQL statement could have devastating effects.

Oracle E-Business Suite is a complex and complicated set of objects with interconnections and interdependencies; that means that something that might not be devastating in another database could potentially mean a complete restore of the system in Apps (a malformed drop command could drop a custom interface table instead of dropping a column on the same table).

Maintain a secure central environment where you can maintain copies of all custom objects. If you have interface tables that allow information from other systems to be brought into the Applications environment, keep the create table script somewhere central and, if relevant, version those scripts. If there has to be a change made to the table structure, alter the create table script to reflect that. Have you created any new indexes in your tuning attempts? Keep copies of those scripts, as well. You do not usually know ahead of time all of the changes that a patch or a minipack will bring with it. You may have to recreate your index or you may have to change it because columns that used to be available (columns that you built indexes on) are no longer found on the table. Control versions and maintain a code repository for at least the Production versions of any customized forms, reports, and PL/SQL program units. This way you can determine what changed, when it changed, how it changed, and have a way to unchange it if for some reason a change causes unreliable output from a previously useful reporting program.

There are directories that you cannot completely control access to. Log file directories and out file directories have to be writable to anyone running a concurrent request. You may not want someone to have the ability to map a drive across the network to that directory, because if they have write access, they also have read access and, therefore, can see the output of any report that has been run. There is certain information that is maintained by HR that other people may not be supposed to have complete access to. The interface controls what those people can and cannot see online, but you (or your sysadmin or your network administrator) have to control the access to the text files that are created on a network accessible machine.

There are directories you may want to have complete control over. The places where the Forms and Reports source code is stored probably needs to be readable by developer and programmers, but it might not need to be writable by them in Production. It will become necessary to compare two versions of a form or report to determine what changed or if anything changed between the Production version and a customized version before anyone starts making other changes. It may not be a good idea to allow them to edit the Production version directly, however.

Does your company have a policy for leaving a workstation unlocked when a user walks away from it (e.g., to use the bathroom, to go to lunch, or to a meeting)? Is there a mandate that screensavers have to be password protected in case someone forgets to lock their workstation when they walk away? That way the window of opportunity for someone to come and access their computer is at least limited. Do all employees follow this mandate? Do you always follow this mandate? You can use Oracle's built-in features of Profile options to automatically make a user log back into the application if that user's session goes unused for a company specified period of time. This will cause the user to have to log back in, but will not cause any work that that user might have had happening in the session to be lost. Once there is a successful relogin, the user gets taken back to exactly where they were. Closing the application or choosing to not log back in will cause any changes made and not committed to be lost. If it does not, you should implement one at the profile option site level.

Do your users have to change their passwords? Again, does your company have a policy that all passwords have to expire at some point in time or after an elapsed amount of time? A common situation is one where a company will implement a password expiration policy company-wide where a password is only good on any system for a maximum of 60 or 90 days. If the user does not change their password within that time, the system makes them change at the end of the specified time. This can be controlled at the profile option site level as well.

< Day Day Up >