This appendix is intended to assist Oracle DBA's (Database Administrators) in establishing the necessary security and audit control settings for Oracle databases and users. The examples list specific settings that should be used when configuring Oracle databases. The listed examples present one approach to ensuring adequate security control is maintained, however, changes may be necessary based on the nature of each instance.

Operating system security controls must be in place to prevent unauthorized access to the Oracle databases. Specific files should only be accessed by DBAs, or those individuals specifically assigned the responsibility of supporting Oracle. These files include:

• Data files and online redo log files that are used by the background processes within Oracle
• Export dump files
• Backup files
• Archive files
• Control files
• INIT.ORA

All other files within Oracle should be set to the default security value (this takes place during Oracle installation).

An Oracle Owner ID must be created in the operating system. Adequate password controls must be maintained over this ID.

Lock and expire all except these if doing a manual install. Change the passwords for SYS, SYSTEM, SCOTT, OUTLN, DBSNMP, and the three JSERV accounts immediately after installation.

Delete the SCOTT schema from all production databases, if installed.

Individual user accounts must be assigned to all users who require access to data that is stored in Oracle databases.

The naming of these IDs should be consistent with network naming conventions in order to avoid confusion. Full names of users are not included in the Oracle account name, so naming the user account the same as the network account provides a method for identifying individual users.

When users no longer require access to the database due to termination or transfer, the ID must be locked and then removed promptly with the DROP USER command.

User accounts that need to be explicitly locked should be locked with the

ALTER USER user_id ACCOUNT LOCK;

command. This can be done by issuing the command in SQLPlus, or by clicking the "locked" button in OEM or DBA studio. An account cannot be unlocked automatically if the account was locked explicitly using this command.

System-level roles can be used to manage user privileges, by simplifying the administration process. Instead of granting individual system-level privileges to USER ACCOUNTS, system-level roles allow groups of privileges to be assigned by simply specifying a particular role.

Some Oracle-defined roles should not be used since they grant excessive access to database objects. Oracle recommends not using these roles: CONNECT, RESOURCE and DBA. These built-in roles should be restricted to the following groups of users:

• Rather than granting the CONNECT role, users should be granted the CREATE SESSION privilege.
• Rather than granting the RESOURCE; role, database developers should be granted a custom role including any required database privileges.
• The DBA role should only be granted to Oracle DBA's. This role includes all system level privileges.
• IMP_FULL_DATABASE and EXP_FULL_DATABASE are typically restricted to DBA's, however, they may be granted to individuals who will be performing limited database management functions.
• DELETE_CATALOG_ROLE should not be granted, unless the individual who uses the role is authorized to delete audit records. This special role grants the user the ability to delete records from the SYS.AUD$ table (all audit records are written to this system table).

Oracle allows for the creation of custom system-level roles. If user account administration is performed by non-DBA's, a new role should be created that only grants the specific system level privileges that are required.

• Example: Create a system level role that grants the following privileges: CREATE SESSION, CREATE USER, ALTER USER and DROP USER (if USER ACCOUNT deletion is a required function). This custom system-level role can now be assigned to individual users, who will handle user administration,

For a second level of security, assign a password to the new role. This will prevent individuals from being able to perform administration level functions if they inadvertently have the new role assigned to their User Account.

• Example: CREATE ROLE User_Admm_Role IDENTIFIED BY "Unique password'';

Profiles are used to place limits on the system and database resources that are available to a user. Password restrictions are also set using profiles. If a profile is not created in an instance (database), then the default profile that specifies unlimited resources for all users will be used.

The following resources must be set in each default profile or a new profile with these settings should be created and assigned to each user account:

• SESSIONS_PER_USER: Limits the number of simultaneous sessions that a user can log into. Since some applications may create multiple sessions for a user, this must be investigated for each application.
• FAILED_LOGIN_ATTEMPTS: 3
• PASSWORD_LIFE_TIME: 60 (days)
• PASSWORD_REUSE_TIME: 365 (days)
• PASSWORD_REUSE_MAX: UNLIMITED (NOTE: This resource must be defined to UNLIMITED when the PASSWORD_REUSE_TIME resource has been assigned a specific value.)
• PASSWORD_LOCK_TIME: 500 (days) (maximum amount allowed)
• PASSWORD_GRACE_TIME: 3 (days)
• PASSWORD_VERIFY_FUNCTION: Verify_Function (name of the function that the utipwdmg.sql script creates)

Recommend: Alpha - Numeric passwords

For Oracle applications, password changes must be instituted. Provide the capability for users to change their own passwords voluntarily (not upon expiration) and upon expiration.

The PASSWORD_VERIFY_FUNCTION calls a script that checks for various password restrictions (based on specified criteria). By default, the script is not created. To create the script, run the following:

• UTLPWDMG.SQL located in the /rdbms/admin subdirectory under the Oracle software home directory.

Exceptions – approved by Director of IT Security

During the login sequence, passwords are initially encrypted. If the connection fails, Oracle re-attempts the connection by transmitting the password in clear text. To prevent malicious users from forcing Oracle to re-attempt a connection with an unencrypted version of the password, the following changes must be made on both the client workstation and database server. These will ensure that passwords used to verify connections are always encrypted.

• On the client workstation, set an environment variable called ORA_ENCRYPT_LOGIN to TRUE
• On the database server, set the DBLINK_ENCRYPT_LOGIN parameter in the init.ora file to TRUE
• Note: The database must be shut down and restarted before the server change is activated.

Object-level privileges are used to give users access to data that they do not own. Grant necessary privileges only. In order to simplify the security control surrounding this function, roles should be created that grant logical access to data, based on employee business requirements. If roles are not used, each user will have to be explicitly granted access to all required data. This method of granting access is not a recommended approach since it is easy to make mistakes when working with potentially several hundred privileges per instance. This could result in several security control issues if sensitive data is inadvertently granted to inappropriate users.

Appropriate data owners can only determine the specific object-level privileges that are included in each role.

For added security, consider enabling roles only when a user starts an application, and then disabling the role upon leaving the application.

Example: When the application is started: SET ROLE '''Specific Role";

When the application is ended: SET ROLE NONE. This will prevent a user from accessing data without using the application.

If all users require access to data, the role can be granted to PUBLIC. This would grant data access to all user accounts in the database. Caution should be used when using this feature to avoid granting access to unauthorized data users. Therefore, this should be avoided unless absolutely necessary.

Revoke unnecessary privileges from PUBLIC. Unless absolutely necessary, revoke UTL_SMTP, UTL_TCP, UTL_HTTP, UTL_FILE, and DBMS_RANDOM from PUBLIC.

Audit the following events and retain for 6 months:

• Userid password violations
• Successful and unsuccessful access attempts to sensitive data
• Changes to highly sensitive data

Oracle has the ability to record the following: Login attempts (both successful and unsuccessful), use of roles, system-level and data manipulation actions on objects, and whether all users or only specific users will be audited.

Review of these logs will occur on a timely basis.

Audit trails provide an opportunity to investigate what actions have taken place within a particular database. An individual or group of users should be assigned the task of routinely reviewing the audit trail logs for anomalies. This provides an additional level of security control (appropriate user account and data use in the database).

To enable auditing, set the AUDIT_TRAIL parameter in the init.ora file to the following:

• NONE - If auditing is disabled
• DB - If audit trails are written to SYS.AUD$ table (internal)
• OS - If audit trails are written to the operating system audit logs (external).

If internal audit trail records are maintained (DB), the audit trail itself must be protected. To enable auditing of the audit trail log (SYS.AUD$), issue the following command:

• AUDIT INSERT, UPDATE, DELETE ON SYS.AUD$ BY ACCESS;

This provides a second level of security by recording every action against the audit trail log.

To prevent users having the ANY system privilege from using it on the data dictionary, set the following initialization parameter:

07_DICTIONARY_ACCESSIBILITY = FALSE

This must be set on version 8i. It is the default on version 9i.

Maintain a copy of the following items at the offsite storage location as defined in the Disaster Recovery Standards:

• Original database software diskettes, CDs or tapes
• At least two of the most recent full database backups (no more than 1 week old)
• Incremental backups performed since the full system backup