10 Keeping Your Oracle Database Secure

10.1 About the Security Guidelines in This Chapter

Information security, and privacy and protection of corporate assets and data are critical in any business. Oracle Database comprehensively addresses the need for information security by providing cutting-edge security features such as deep data protection, auditing, scalable security, secure hosting, and data exchange.

Oracle Database leads the industry in security. To maximize the security features offered by Oracle Database in any business environment, it is imperative that the database itself be well protected.

Security guidelines provide advice about how to configure Oracle Database to be secure by adhering to and recommending industry-standard and advisable security practices for operational database deployments. Many of the guidelines described in this section address common regulatory requirements such as those described in the Sarbanes-Oxley Act. For more information about how Oracle Database addresses regulatory compliance, protection of personally identifiable information, and internal threats, visit:

http://www.oracle.com/technology/deploy/security/db_security/index.html

10.2 Downloading Security Patches and Contacting Oracle Regarding Vulnerabilities

This section includes the following topics:

• Applying Security Patches and Workaround Solutions

• Contacting Oracle Security Regarding Vulnerabilities in Oracle Database

10.3 Guidelines for Securing User Accounts and Privileges

Follow these guidelines to secure user accounts and privileges:

1. Practice the principle of least privilege.

   Oracle recommends the following guidelines:

   1. Grant necessary privileges only.

      Do not provide database users more privileges than are necessary. In other words, the principle of least privilege is that users be given only those privileges that are actually required to efficiently perform their jobs.

      To implement this principle, restrict the following as much as possible:

      • The number of SYSTEM and OBJECT privileges granted to database users.

      • The number of people who are allowed to make SYS-privileged connections to the database.

      • The number of users who are granted the ANY privileges, such as the DROP ANY TABLE privilege. For example, there is generally no need to grant CREATE ANY TABLE privileges to a non-DBA-privileged user.

      • The number of users who are allowed to perform actions that create, modify, or drop database objects, such as the TRUNCATE TABLE, DELETE TABLE, DROP TABLE statements, and so on.

   2. Do not allow non-administrative users access to objects owned by the SYS schema.

      Do not allow users to alter table rows or schema objects in the SYS schema, because doing so can compromise data integrity. Limit the use of statements such as DROP TABLE, TRUNCATE TABLE, DELETE, INSERT, or similar object-modification statements on SYS objects only to highly privileged administrative users.

      The SYS schema owns the data dictionary. You can protect the data dictionary by setting the 07_DICTIONARY_ACCESSIBILITY parameter to FALSE. See Guideline 1 under "Guidelines for Securing Data" for more information.

   3. Revoke unnecessary privileges from the PUBLIC user group.

      The PUBLIC user group represents all users in the database. Revoke all unnecessary privileges and roles from the database server user group PUBLIC. PUBLIC acts as a default role granted to every user in an Oracle database. Any database user can exercise privileges that are granted to PUBLIC. These privileges include EXECUTE on various PL/SQL packages, potentially enabling someone with minimal privileges to access and execute functions that this user would not otherwise be permitted to access directly.

   4. Restrict permissions on run-time facilities.

      Many Oracle Database products use run-time facilities, such as Oracle Java Virtual Machine (OJVM). Do not assign all permissions to a database run-time facility. Instead, grant specific permissions to the explicit document root file paths for facilities that might run files and packages outside the database.

      Here is an example of a vulnerable run-time call, which individual files are specified:

      call dbms_java.grant_permission('wsmith', 'SYS:java.io.FilePermission','<<ALL FILES>>','read');
      

      Here is an example of a better (more secure) run-time call, which specifies a directory path instead:

      call dbms_java.grant_permission('wsmith', 'SYS:java.io.FilePermission','<<actual directory path>>','read');
      

2. Lock and expire predefined user accounts.

   Oracle Database installs with a number of default (predefined) database user accounts. Upon successful installation of the database, the Database Configuration Assistant automatically locks and expires most default database user accounts.

   If a manual (without using Database Configuration Assistant) installation of Oracle Database is performed, then no default database users are locked upon successful installation of the database server. Left open in their default states, these user accounts can be exploited, to gain unauthorized access to data or disrupt database operations.

   Therefore, after performing any kind of initial installation that does not use the Database Configuration Assistant, you should lock and expire all default database user accounts. Oracle Database provides SQL statements to perform these operations. For example:

   ALTER USER ANONYMOUS PASSWORD EXPIRE ACCOUNT LOCK;
   

   See Oracle Database SQL Language Reference for more information about the ALTER USER statement.

   Installing additional products and components after the initial installation also results in creating more default database accounts. Database Configuration Assistant automatically locks and expires all additionally created database user accounts. Unlock only those accounts that need to be accessed on a regular basis and assign a strong, meaningful password to each of these unlocked accounts. Oracle provides SQL and password management to perform these operations.

   If any default database user account other than the ones left open is required for any reason, then a database administrator (DBA) needs to unlock and activate that account with a new, secure password.

   See Oracle Database 2 Day + Security Guide for a description of the predefined user accounts that are created when you install Oracle Database.

   If a default database user account, other than the ones left open, is required for any reason, then a database administrator (DBA) can unlock and activate that account with a new, secure password.

   Oracle Enterprise Manager Accounts

   The preceding list of accounts depends on whether or not you install Oracle Enterprise Manager. If you do, the SYSMAN and DBSNMP accounts are open, unless you configure Oracle Enterprise Manager for central administration. In this case, the SYSMAN account (if present) will be locked.

   If you do not install Oracle Enterprise Manager, then only the SYS and SYSTEM accounts are open. Database Configuration Assistant locks and expires all other accounts (including SYSMAN and DBSNMP).

3. Use the following views to ensure that access is granted. Only users and roles that need access should be granted access to them.

   • DBA_*

   • DBA_ROLES

   • DBA_SYS_PRIVS

   • DBA_ROLE_PRIVS

   • DBA_TAB_PRIVS

   • SYS.AUD$ (if auditing is enabled)

   • SYS.FGA_LOG$

4. Monitor the granting of the following privileges only to users and roles who need these privileges.

   By default, Oracle Database audits the following privileges:

   • ALTER SYSTEM

   • AUDIT SYSTEM

   • CREATE EXTERNAL JOB

   Oracle recommends that you also audit the following privileges:

   • ALL PRIVILEGES

   • BECOME USER

   • CREATE LIBRARY

   • CREATE PROCEDURE

   • DBMS_BACKUP_RESTORE package

   • EXECUTE to DBMS_SYS_SQL

   • SELECT ANY TABLE

   • SELECT on PERFSTAT.STATS$SQLTEXT

   • SELECT on PERFSTAT.STATS$SQL_SUMMARY

   • SELECT on SYS.USER$

   • SELECT on SYS.SOURCE$

   • Privileges that have the WITH ADMIN clause

   • Privileges that have the WITH GRANT clause

   • Privileges that have the CREATE keyword

5. Revoke access to the following:

   • The SYS.USER_HISTORY$ view from all users except SYS and DBA accounts

   • The RESOURCE role from typical application accounts

   • The CONNECT role from typical application accounts

   • The DBA role from users who do not need this role

6. Grant privileges only to roles.

7. Limit the proxy account (for proxy authorization) privileges to CREATE SESSION only.

8. Use secure application roles to protect roles that are enabled by application code.

   Secure application roles allow you to define a set of conditions, within a PL/SQL package, that determine whether or not a user can log on to an application. Users do not need to use a password with secure application roles.

   Another approach to protecting roles from being enabled or disabled in an application is the use of role passwords. This approach prevents a user from directly accessing the database in SQL (rather than the application) to enable the privileges associated with the role. However, Oracle recommends that you use secure application roles instead, to avoid having to manage another set of passwords.

9. Discourage users from using the NOLOGGING clause in SQL statements.

   In some SQL statements, the user has the option of specifying the NOLOGGING clause, which indicates that the database operation is not logged in the online redo log file. Even though the user specifies the clause, a redo record is still written to the online redo log file. However, there is no data associated with this record. Because of this, using NOLOGGING has the potential for malicious code to be entered can be accomplished without an audit trail.

10.4 Guidelines for Securing Roles

Follow these guidelines when managing roles:

1. Grant a role to users only if they need all privileges of the role.

   Roles (groups of privileges) are useful for quickly and easily granting permissions to users. Although you can use Oracle-defined roles, you have more control and continuity if you create your own roles containing only the privileges pertaining to your requirements. Oracle may change or remove the privileges in an Oracle Database-defined role, as it has with the CONNECT role, which now has only the CREATE SESSION privilege. Formerly, this role had eight other privileges. Both CONNECT and RESOURCE roles will be deprecated in future Oracle releases.

   Ensure that the roles you define contain only the privileges that reflect job responsibility. If your application users do not need all the privileges encompassed by an existing role, then apply a different set of roles that supply just the correct privileges. Alternatively, create and assign a more restricted role.

   For example, it is imperative to strictly limit the privileges of user SCOTT, because this is a well known account that may be vulnerable to intruders. Because the CREATE DBLINK privilege allows access from one database to another, drop its privilege for SCOTT. Then, drop the entire role for the user, because privileges acquired by means of a role cannot be dropped individually. Re-create your own role with only the privileges needed, and grant that new role to that user. Similarly, for better security, drop the CREATE DBLINK privilege from all users who do not require it.

2. Do not grant user roles to application developers.

   Roles are not meant to be used by application developers, because the privileges to access schema objects within stored programmatic constructs need to be granted directly. Remember that roles are not enabled within stored procedures except for invoker's right procedures. See "How Roles Work in PL/SQL Blocks" for information about this topic.

3. Create and assign roles specific to each Oracle Database installation.

   This principle enables the organization to retain detailed control of its roles and privileges. This also avoids the necessity to adjust if Oracle Database changes or removes Oracle Database-defined roles, as it has with CONNECT, which now has only the CREATE SESSION privilege. Formerly, it also had eight other privileges. Both CONNECT and RESOURCE roles will be deprecated in future Oracle Database versions.

4. For enterprise users, create global roles.

   Global roles are managed by an enterprise directory service, such as Oracle Internet Directory. See the following sections for more information about global roles:

   • "Configuring Global User Authentication and Authorization"

   • "Global Role Authorization by an Enterprise Directory Service"

   • Oracle Database Enterprise User Security Administrator's Guide

10.5 Guidelines for Securing Passwords

When you create a user account, Oracle Database assigns a default password policy for that user. The password policy defines rules for how the password should be created, such as a minimum number of characters, when it expires, and so on. You can strengthen passwords by using password policies. See also "Configuring Password Protection" for additional ways to protect passwords.

Follow these guidelines to further strengthen passwords:

1. Choose passwords carefully.

   "How Oracle Database Checks the Complexity of Passwords" describes the minimum requirements for passwords. Follow these additional guidelines when you create or change passwords:

   • Make the password between 8 and 30 characters.

   • Use the database character set for the password's characters, which can include the underscore (_), dollar ($), and number sign (#) characters.

   • In addition to including at least 1 digit and 1 alphabetic character, include at least 1 punctuation mark in the password.

   • Do not start the password with a number.

   • Do not use Oracle reserved words in the password.

     See Oracle Database SQL Language Reference for a list of Oracle Database reserved words.

   • Do not include the password in a dictionary or in a name (for example, an object name).

   Oracle Database provides a password complexity verification routine, the PL/SQL script UTLPWDMG.SQL, that you can run to check whether or not passwords are sufficiently complex. Ideally, edit the UTLPWDMG.SQL script to provide stronger password protections. See also "Enforcing Password Complexity Verification" for a sample routine that you can use to check passwords.

2. Change default user passwords.

   Oracle Database installs with a set of predefined, default user accounts. Security is most easily broken when a default database user account still has a default password even after installation. This is particularly true for the user account SCOTT, which is a well known account that may be vulnerable to intruders. In Oracle Database 11g Release 1 (11.1), default accounts are installed locked with the passwords expired, but if you have upgraded from a previous release, you may still have accounts that use default passwords.

   To find user accounts that have default passwords, query the DBA_USERS_WITH_DEFPWD data dictionary view. See "Finding User Accounts That Have Default Passwords" for more information.

3. Change default passwords of administrative users.

   You can use the same or different passwords for the SYS, SYSTEM, SYSMAN, and DBSNMP administrative accounts. Oracle recommends that you use different passwords for each. In any Oracle environment (production or test), assign strong, secure, and distinct passwords to these administrative accounts. If you use Database Configuration Assistant to create a new database, then it requires you to enter passwords for the SYS and SYSTEM accounts, disallowing the default passwords CHANGE_ON_INSTALL and MANAGER.

   Similarly, for production environments, do not use default passwords for administrative accounts, including SYSMAN and DBSNMP.

   See Oracle Database 2 Day + Security Guide for information about changing a default password.

4. Enforce password management.

   Apply basic password management rules (such as password length, history, complexity, and so forth) to all user passwords. Oracle Database has password policies enabled for the default profile. Guideline 1 in this section lists these password policies. Oracle Database 2 Day + Security Guide lists initialization parameters that you can use to further secure user passwords.

   You can find information about user accounts by querying the DBA_USERS view. This view contains a column for passwords, but for better security, Oracle Database encrypts (disguises) the data in this column. The DBA_USERS view provides useful information such as the user account status, whether the account is locked, and password versions. You can query DBA_USERS as follows:

   sqlplus system
   Enter password: password
   Connected.
   SQL> SELECT * FROM DBA_USERS;
   

   Oracle also recommends, if possible, using Oracle Advanced Security (an option to Oracle Database Enterprise Edition) with network authentication services (such as Kerberos), token cards, smart cards, or X.509 certificates. These services provide strong authentication of users, and provide protection against unauthorized access to Oracle Database.

5. Do not store user passwords in clear text in Oracle tables.

   For better security, do not store passwords in clear text (that is, human readable) in Oracle tables. You can correct this problem by encrypting the table column that contains the password. See Oracle Database 2 Day + Security Guide for information about how to use transparent data encryption to encrypt a table column.

   When you create or modify a password for a user account, Oracle Database automatically encrypts it. If you query the DBA_USERS view to find information about a user account, the data in the PASSWORD column is encrypted.

10.6 Guidelines for Securing Data

Follow these guidelines to secure data on your system:

1. Enable data dictionary protection.

   Oracle recommends that you protect the data dictionary to prevent users that have the ANY system privilege from using those privileges on the data dictionary. Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database.

   To enable data dictionary protection, set the following initialization parameter to FALSE (which is the default) in the initsid.ora control file:

   07_DICTIONARY_ACCESSIBILITY = FALSE
   

   You can set the 07_DICTIONARY_ACCESSIBILITY parameter in a server parameter file. For more information about server parameter files, see Oracle Database Administrator's Guide.

   After you set O7_DICTIONARY_ACCESSIBILTY to FALSE, only users who have the SELECT ANY DICTIONARY privilege and those authorized users making DBA-privileged (for example CONNECT / AS SYSDBA) connections can use the ANY system privilege on the data dictionary. If O7_DICTIONARY_ACCESSIBILITY parameter is not set to FALSE, then any user with the DROP ANY TABLE (for example) system privilege will be able to drop parts of the data dictionary. However, if a user needs view access to the data dictionary, then you can grant that user the SELECT ANY DICTIONARY system privilege.

   Note:

   • In a default installation, the O7_DICTIONARY_ACCESSIBILITY parameter is set to FALSE. However, in Oracle8i, this parameter is set to TRUE by default, and must be changed to FALSE to enable this security feature.

   • The SELECT ANY DICTIONARY privilege is not included in the GRANT ALL PRIVILEGES statement, but you can grant it through a role. Chapter 4, "Configuring Privilege and Role Authorization" describes roles in detail.

2. Restrict operating system access.

   Follow these guidelines:

   • Limit the number of operating system users.

   • Limit the privileges of the operating system accounts (administrative, root-privileged, or DBA) on the Oracle Database host computer to the least privileges required for a user to perform necessary tasks.

   • Restrict the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.

   • Restrict symbolic links. Ensure that when you provide a path or file to the database, neither the file nor any part of the path is modifiable by an untrusted user. The file and all components of the path should be owned by the database administrator or trusted account, such as root.

     This recommendation applies to all types of files: data files, log files, trace files, external tables, BFILE data types, and so on.

10.7 Guidelines for Securing a Database Installation and Configuration

For this release, changes were made to the default configuration of Oracle Database to make it more secure. The recommendations in this section augment the new, secure default configuration.

Follow these guidelines to secure the database installation and configuration:

1. Before you begin an Oracle Database installation on UNIX systems, ensure that the umask value is 022 for the Oracle owner account.

2. Install only what is required.

   Options and Products: The Oracle Database CD pack contains products and options in addition to the database. Install additional products and options only as necessary. Use the Custom Installation feature to avoid installing unnecessary products, or perform a typical installation, and then deinstall options and products that are not required. There is no need to maintain additional products and options if they are not being used. They can always be properly installed, as required.

   Sample Schemas: Oracle Database provides sample schemas to provide a common platform for examples. If your database will be used in a production environment, then do not install the sample schema. If you have installed the sample schema on a test database, then before going to production, remove or relock the sample schema accounts. See Oracle Database Sample Schemas for more information about the sample schemas.

3. During installation, when you are prompted for a password, create a secure password.

   Follow Guidelines 1, 2, and 3 in "Guidelines for Securing Passwords".

4. Immediately after installation, lock and expire default user accounts.

   See Guideline 2 in "Guidelines for Securing User Accounts and Privileges".

10.8 Guidelines for Securing the Network

Security for network communications is improved by using client, listener, and network guidelines to ensure thorough protection. Using SSL is an essential element in these lists, enabling top security for authentication and communications.

These guidelines are as follows:

• Securing the Client Connection

• Securing the Network Connection

• Securing a Secure Sockets Layer Connection

10.9 Guidelines for Auditing

This section describes the following guidelines for auditing:

• Enabling Default Auditing of SQL Statements and Privileges

• Keeping Audited Information Manageable

• Auditing Typical Database Activity

• Auditing Suspicious Database Activity

10.10 Addressing the CONNECT Role Change

The CONNECT role was introduced with Oracle Database version 7, which added new and robust support for database roles. The CONNECT role is used in sample code, applications, documentation, and technical papers.

This section discusses the effects of changed CONNECT privileges in the following sections:

• Why Was the CONNECT Role Changed?

• How the CONNNECT Role Change Affects Applications

• How the CONNECT Role Change Affects Users

• Approaches to Addressing the CONNECT Role Change

SQL> ALTER SESSION SET EVENTS ........

SQL> ALTER SESSION SET NLS_TERRITORY = FRANCE;

SQL> CREATE ROLE my_app_developer;
SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE SYNONYM, CREATE CLUSTER, CREATE DATABASE LINK, ALTER SESSION TO my_app_developer;
SQL>

SQL> SELECT user$.name, admin_option, default_role
     FROM user$, sysauth$, dba_role_privs
     WHERE privilege# = 
     (SELECT user# from user$ WHERE name = 'CONNECT')
     AND user$.user# = grantee#
     AND grantee = user$.name
     AND granted_role = 'CONNECT';

NAME                           ADMIN_OPTI DEF
------------------------------ ---------- ---
R1                             YES        YES
R2                             NO         YES

SQL> GRANT my_app_developer TO R1 WITH ADMIN OPTION;
SQL> GRANT my_app_developer TO R2;

SQL> AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;

SQL> SELECT userid, name FROM aud$, system_privilege_map 
WHERE - priv$used = privilege;
USERID                         NAME
------------------------------ ----------------
ACME                           CREATE TABLE
ACME                           CREATE SEQUENCE
ACME                           CREATE TABLE
ACME                           ALTER SESSION
APPS                           CREATE TABLE
APPS                           CREATE TABLE
APPS                           CREATE TABLE
APPS                           CREATE TABLE

8 rows selected.

SQL> AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;

SQL> SELECT userid, name FROM aud$, system_privilege_map WHERE - priv$used = privilege;
USERID                         NAME
------------------------------ ----------------
ACME                           CREATE TABLE
ACME                           CREATE SEQUENCE
ACME                           CREATE TABLE
ACME                           ALTER SESSION
APPS                           CREATE TABLE
APPS                           CREATE TABLE
APPS                           CREATE TABLE
APPS                           CREATE TABLE
8 rows selected.
SQL>