Ch 10 Keeping Your Oracle Database Secure

10 Keeping Your Oracle Database Secure

This chapter contains:

About the Security Guidelines in This Chapter

This chapter provides a set of guidelines to keep your Oracle database secure. 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:

Downloading Security Patches and Contacting Oracle Regarding Vulnerabilities

This section contains:

Applying Security Patches and Workaround Solutions

Always apply all relevant security patches for both the operating system on which Oracle Database resides and Oracle Database itself, and for all installed Oracle Database options and components.
Periodically check the security site on Oracle Technology Network for details about security alerts released by Oracle at
Also check the Oracle Worldwide Support Service site, My Oracle Support, for details about available and upcoming security-related patches at

Contacting Oracle Security Regarding Vulnerabilities in Oracle Database

If you are an Oracle customer or an Oracle partner, use My Oracle Support to submit a Service Request on any potential Oracle product security vulnerability. Otherwise, send an email to secalert_us@oracle.com with a complete description of the problem, including product version and platform, together with any scripts and examples. Oracle encourages those who want to contact Oracle Security to employ email encryption, using our encryption key.

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 or roles more privileges than are necessary. (If possible, grant privileges to roles, not users.) 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 theDROP ANY TABLE privilege. For example, there is generally no need to grantCREATE 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 TABLEstatements, and so on.
    2. Restrict the CREATE ANY JOB, BECOME USER, EXP_FULL_DATABASE, and IMP_FULL_DATABASE privileges.
      These are powerful security-related privileges. Only grant these privileges to users who need them.
    3. Restrict library-related privileges to trusted users only.
      The CREATE LIBRARY, CREATE ANY LIBRARY, ALTER ANY LIBRARY, andEXECUTE ANY LIBRARY privileges, and grants of EXECUTE ON library_name convey a great deal of power to users. If you plan to create PL/SQL interfaces to libraries, only grant the EXECUTE privilege to the PL/SQL interface. Do not grant EXECUTE on the underlying library. You must have the EXECUTE privilege on a library to create the PL/SQL interface to it. However, users have this privilege implicitly on libraries that they create in their own schemas. Explicit grants of EXECUTE ON library_name are rarely required. Only make an explicit grant of these privileges to trusted users, and never to the PUBLIC role.
    4. Restrict synonym-related privileges to trusted users only.
      The CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM system privileges convey a great deal of power to these users. Do not grant these privileges to users, unless they are trusted.
    5. 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 asDROP 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 O7_DICTIONARY_ACCESSIBILITY parameter to FALSE. See Guideline 1 under"Guidelines for Securing Data" for more information.
    6. Restrict permissions on run-time facilities.
      Many Oracle Database products use run-time facilities, such as Oracle Java VirtualMachine (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 default (predefined) user accounts.
    Oracle Database installs with several default database user accounts. Upon successful installation of the database, the Database Configuration Assistant automatically locks and expires most default database user accounts.
    If you perform a manual (without using Database Configuration Assistant) installation of Oracle Database, then no default database users are locked upon successful installation of the database server. Or, if you have upgraded from a previous release of Oracle Database, you may have default accounts from earlier releases. Left open in their default states, these user accounts can be exploited, to gain unauthorized access to data or disrupt database operations.
    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 theALTER 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 must 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) must 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
    If you install Oracle Enterprise Manager, the SYSMAN and DBSNMP accounts are open, unless you configure Oracle Enterprise Manager for central administration. In this case, theSYSMAN 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 (includingSYSMAN 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
    • DBA_AUDIT_TRAIL (if standard auditing is enabled)
    • DBA_FGA_AUDIT_TRAIL (if fine-grained auditing is enabled)
  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 (which includes privileges such as BECOME USER, CREATE LIBRARY, and 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.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$ table 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.
    Granting privileges to roles and not individual users makes the management and tracking of privileges much easier.
  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 NOLOGGINGhas the potential for malicious code to be entered can be accomplished without an audit trail.

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 youcreate 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 theCONNECT role, which now has only the CREATE SESSION privilege. Formerly, this role had eight other privileges.
    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 DBLINKprivilege 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 must 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 SESSIONprivilege. Formerly, it also had eight other privileges.
  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:

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.
    "Minimum Requirements for Passwords" describes the minimum requirements for passwords. Follow these additional guidelines when you create or change passwords:
    • Make the password between 12 and 30 characters and numbers.
    • Use mixed case letters and special characters in the password. (See "Ensuring Against Password Security Threats by Using the SHA-1 Hashing Algorithm" for more information.)
    • You can include multibyte characters in the password.
    • Use the database character set for the password's characters, which can include the underscore (_), dollar ($), and number sign (#) characters.
    • You must enclose the following passwords in double-quotation marks:
      • Passwords containing multibyte characters.
      • Passwords starting with numbers or special characters and containing alphabetical characters. For example:
        "123abc"
        "#abc"
        "123dc$"
      • Passwords containing any character other than alphabetical characters, numbers, and special characters. For example:
        "abc>"
        "abc@",
        " "
    • You do not need to specify the following passwords in double-quotation marks.
      • Passwords starting with an alphabet (a-z, A-Z) and containing numbers(0-9) or special characters ($,#,_). For example:
        abc123
        ab23a
        ab$#_
      • Passwords containing only numbers.
      • Passwords containing only alphabetical characters.
    • Do not use an actual word for the entire password.
  2. To create a longer, more complex password from a shorter, easier to remember password, follow these techniques:
    • Create passwords from the first letters of the words of an easy-to-remember sentence. For example, "I usually work until 6 almost every day of the week" can be Iuwu6aedotw.
    • Combine two weaker passwords, such as welcome1 and binky into WelBinkyCome1.
    • Repeat a character at the beginning or end of the password.
    • Add a string, another password, or part the same password to the beginning or end of the password that you want to create. For example, ways that you can modify the password fussy2all are as follows:
      • fussy2all34hj2
      • WelBinkyCome1fussy2all
      • fusfussy2all
    • Double some or all of the letters. For example, welcome13 can becomewwellCcooMmee13.
  3. Ensure that the password is sufficiently complex.
    Oracle Database provides a password complexity verification routine, the PL/SQL scriptUTLPWDMG.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.
  4. 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_DEFPWDdata dictionary view. See "Finding User Accounts That Have Default Passwords" for more information.
  5. Change default passwords of administrative users.
    You can use the same or different passwords for the SYS, SYSTEM, SYSMAN, and DBSNMPadministrative 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.
  6. 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. ThePASSWORD column of the DBA_USERS view indicates whether the password is global, external, or null. The DBA_USERS view provides useful information such as the user account status, whether the account is locked, and password versions.
    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.
  7. 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 indicates if the user password is global, external, or null.

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 ANYsystem 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:
    O7_DICTIONARY_ACCESSIBILITY = FALSE
    You can set the O7_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 theSELECT 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 theGRANT 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.
  3. Encrypt sensitive data and all backup media that contains database files.
    According to common regulatory compliance requirements, you must encrypt sensitive data such as credit card numbers and passwords. When you delete sensitive data from the database, encrypted data does not linger in data blocks, operating system files, or sectors on disk.
    In most cases, you may want to use transparent data encryption to encryrpt your sensitive data. See Oracle Database Advanced Security Administrator's Guide for more information. See also "Security Problems That Encryption Does Not Solve" for when you should not encrypt data.

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.
    See Oracle Database Administrator's Reference for Linux and UNIX for more information about managing Oracle Database on Linux and UNIX systems.
  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 theCustom 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. SeeOracle 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, 4, and 5 in "Guidelines for Securing Passwords".
  4. Immediately after installation, lock and expire default user accounts.

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

Because authenticating client computers is problematic, typically, user authentication is performed instead. This approach avoids client system issues that include falsified IP addresses, hacked operating systems or applications, and falsified or stolen client system identities. Nevertheless, the following guidelines improve the security of client connections:
  1. Enforce access controls effectively and authenticate clients stringently.
    By default, Oracle allows operating system-authenticated logins only over secure connections, which precludes using Oracle Net and a shared server configuration. This default restriction prevents a remote user from impersonating another operating system user over a network connection.
    Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE forces the database to accept the client operating system user name received over an unsecure connection and use it for account access. Because clients, such as PCs, are not trusted to perform operating system authentication properly, it is poor security practice to use this feature.
    The default setting, REMOTE_OS_AUTHENT = FALSE, creates a more secure configuration that enforces proper, server-based authentication of clients connecting to an Oracle database. Be aware that the REMOTE_OS_AUTHENT has been deprecated for Oracle Database Release 11g (11.1) and is retained only for backward compatibility.
    You should not alter the default setting of the REMOTE_OS_AUTHENT initialization parameter, which is FALSE.
    Setting this parameter to FALSE does not mean that users cannot connect remotely. It means that the database will not trust that the client has already authenticated, and will therefore apply its standard authentication processes.
    Be aware that the REMOTE_OS_AUTHENT parameter has been deprecated in Oracle Database 11g Release 1 (11.1), and is retained only for backward compatibility.
  2. Configure the connection to use encryption.
    Oracle network encryption makes eavesdropping difficult. To learn how to configure encryption, see Oracle Database Advanced Security Administrator's Guide.
  3. Set up strong authentication.
    See Oracle Database Advanced Security Administrator's Guide for more information about using Kerberos and public key infrastructure (PKI).

Securing the Network Connection

Protecting the network and its traffic from inappropriate access or modification is the essence of network security. You should consider all paths the data travels, and assess the threats on each path and node. Then, take steps to lessen or eliminate those threats and the consequences of a security breach. In addition, monitor and audit to detect either increased threat levels or penetration attempts.
To manage network connections, you can use Oracle Net Manager. For an introduction to using Oracle Net Manager, see Oracle Database 2 Day DBA. See also Oracle Database Net Services Administrator's Guide.
The following practices improve network security:
  1. Use Secure Sockets Layer (SSL) when administering the listener.
  2. Monitor listener activity.
    You can monitor listener activity by using Enterprise Manager Database Control. In the Database Control home page, under General, click the link for your listener. The Listener page appears. This page provides detailed information, such as the category of alert generated, alert messages, when the alert was triggered, and so on. This page provides other information as well, such as performance statistics for the listener.
  3. Prevent online administration by requiring the administrator to have the write privilege on the listener password and on the listener.ora file on the server.
    1. Add or alter this line in the listener.ora file:
      ADMIN_RESTRICTIONS_LISTENER=ON
    2. Use RELOAD to reload the configuration.
    3. Use SSL when administering the listener by making the TCPS protocol the first entry in the address list, as follows:
      LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=
        (PROTOCOL=tcps)
        (HOST = sales.us.example.com)
        (PORT = 8281)))
      To administer the listener remotely, you define the listener in the listener.ora file on the client computer. For example, to access listener USER281 remotely, use the following configuration:
      user281 =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = tcps)
      (HOST = sales.us.example.com)
      (PORT = 8281))
    )
  )
    For more information about the parameters in listener.ora, see Oracle Database Net Services Reference.
  4. Do not set the listener password.
    Ensure that the password has not been set in the listener.ora file. The local operating system authentication will secure the listener administration. The remote listener administration is disabled when the password has not been set. This prevents brute force attacks of the listener password.
  5. When a host computer has multiple IP addresses associated with multiple network interface controller (NIC) cards, configure the listener to the specific IP address.
    This allows the listener to listen on all the IP addresses. You can restrict the listener to listen on a specific IP address. Oracle recommends that you specify the specific IP addresses on these types of computers, rather than allowing the listener to listen on all IP addresses. Restricting the listener to specific IP addresses helps to prevent an intruder from stealing a TCP end point from under the listener process.
  6. Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space.
    This restriction prevents external procedure agents spawned by the listener (or procedures executed by an agent) from inheriting the ability to perform read or write operations. The owner of this separate listener process should not be the owner that installed Oracle Database or executes the Oracle Database instance (such as ORACLE, the default owner).
    For more information about configuring external procedures in the listener, see Oracle Database Net Services Administrator's Guide.
  7. Use encryption to secure the data in flight.
    See Oracle Database 2 Day + Security Guide and Oracle Database Advanced Security Administrator's Guide for more information about network data encryption.
  8. Use a firewall.
    Appropriately placed and configured firewalls can prevent outside access to your databases.
    • Keep the database server behind a firewall. Oracle Database network infrastructure,Oracle Net (formerly known as Net8 and SQL*Net), provides support for a variety of firewalls from various vendors. Supported proxy-enabled firewalls include Gauntlet from Network Associates and Raptor from Axent. Supported packet-filtering firewalls include PIX Firewall from Cisco, and supported stateful inspection firewalls (more sophisticated packet-filtered firewalls) include Firewall-1 from CheckPoint.
    • Ensure that the firewall is placed outside the network to be protected.
    • Configure the firewall to accept only those protocols, applications, or client/server sources that you know are safe.
    • Use a product such as Oracle Connection Manager to manage multiplex multiple client network sessions through a single network connection to the database. It can filter on source, destination, and host name. This product enables you to ensure that connections are accepted only from physically secure terminals or from application Web servers with known IP addresses. (Filtering on IP address alone is not enough for authentication, because it can be falsified.)
  9. Prevent unauthorized administration of the Oracle listener.
    For more information about the listener, see Oracle Database Net Services Administrator's Guide.
  10. Check network IP addresses.
    Use the Oracle Net valid node checking security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following sqlnet.ora configuration file parameters:
    tcp.validnode_checking = YES

tcp.excluded_nodes = {list of IP addresses}

tcp.invited_nodes = {list of IP addresses}
    The tcp.validnode_checking parameter enables the feature. Thetcp.excluded_nodes and tcp.invited_nodes parameters deny and enable specific client IP addresses from making connections to the Oracle listener. This helps to prevent potential denial-of-service attacks.
    You can use Oracle Net Manager to configure these parameters. See Oracle Database Net Services Administrator's Guide for more information.
  11. Encrypt network traffic.
    If possible, use Oracle Advanced Security to encrypt network traffic among clients, databases, and application servers. Oracle Database 2 Day + Security Guide provides an introduction to network encryption. For detailed information about network encryption, seeOracle Database Advanced Security Administrator's Guide.
  12. Secure the host operating system (the system on which Oracle Database is installed).
    Secure the host operating system by disabling all unnecessary operating system services. Both UNIX and Windows provide a variety of operating system services, most of which are not necessary for typical deployments. These services include FTP, TFTP, TELNET, and so forth. Be sure to close both the UDP and TCP ports for each service that is being disabled. Disabling one type of port and not the other does not make the operating system more secure.

Securing a Secure Sockets Layer Connection

Secure Sockets Layer (SSL) is the Internet standard protocol for secure communication, providing mechanisms for data integrity and data encryption. These mechanisms can protect the messages sent and received by you or by applications and servers, supporting secure authentication, authorization, and messaging through certificates and, if necessary, encryption. Good security practices maximize protection and minimize gaps or disclosures that threaten security. The following guidelines show the cautious attention to detail necessary for the successful use of SSL. For detailed information about Oracle SSL configuration, seeOracle Database Advanced Security Administrator's Guide.
  1. Ensure that configuration files (for example, for clients and listeners) use the correct port for SSL, which is the port configured upon installation.
    You can run HTTPS on any port, but the standards specify port 443, where any HTTPS-compliant browser looks by default. The port can also be specified in the URL, for example:
    https://secure.example.com:4445/
    If a firewall is in use, then it too must use the same ports for secure (SSL) communication.
  2. Ensure that TCPS is specified as the PROTOCOL in the ADDRESS parameter in the tnsnames.ora file (typically on the client or in the LDAP directory).
    An identical specification must appear in the listener.ora file (typically in the$ORACLE_HOME/network/admin directory).
  3. Ensure that the SSL mode is consistent for both ends of every communication. For example, the database (on one side) and the user or application (on the other) must have the same SSL mode.
    The mode can specify either client or server authentication (one-way), both client and server authentication (two-way), or no authentication.
  4. Ensure that the server supports the client cipher suites and the certificate key algorithm in use.
  5. Enable DN matching for both the server and client, to prevent the server from falsifying its identity to the client during connections.
    This setting ensures that the server identity is correct by matching its global database name against the DN from the server certificate.
    You can enable DN matching in the tnsnames.ora file. For example:
    set:SSL_SERVER_CERT_DN="cn=finance,cn=OracleContext,c=us,o=example"
    Otherwise, a client application would not check the server certificate, which could allow the server to falsify its identity.
  6. Do not remove the encryption from your RSA private key inside your server.key file, which requires that you enter your pass phrase to read and parse this file.
    Note:
    A server without SSL does not require a pass phrase.
    If you decide your server is secure enough, you could remove the encryption from the RSA private key while preserving the original file. This enables system boot scripts to start the database server, because no pass phrase is needed. Ideally, restrict permissions to the root user only, and have the Web server start as root, but then log on as another user. Otherwise, anyone who gets this key can impersonate you on the Internet, or decrypt the data that was sent to the server.
    See Also:

Guidelines for Auditing

This section contains:

Auditing Sensitive Information

Be aware that sensitive data, such as credit card numbers, appear in the fine-grained audit trail if you collect SQL text. For standard auditing, setting the AUDIT_TRAIL initialization parameter to DB, EXTENDED or XML, EXTENDED enables the collection of SQL text. For fine-grained auditing, you would set the audit_trail parameter of the DBMS_FGA PL/SQL package to DBMS_FGA.DB + DBMS_FGA.EXTENDED orDBMS_FGA.XML + DBMS_FGA.EXTENDED.
If you have sensitive data that is being audited, do not enable the collection of SQL text in the audit trail. Use the following settings instead:

Enabling Default Auditing of SQL Statements and Privileges

When you create a new database, you have the option to enable the auditing of a select set of SQL statements and privileges. Oracle recommends that you enable default auditing. Auditing is an effective method of enforcing strong internal controls so that your site can meet its regulatory compliance requirements, as defined in the Sarbanes-Oxley Act.
See Also:
"Using Default Auditing for Security-Relevant SQL Statements and Privileges"

Keeping Audited Information Manageable

Although auditing is relatively inexpensive, limit the number of audited events as much as possible. This minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.
Follow these guidelines when devising an auditing strategy:
  1. Evaluate your reason for auditing.
    After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.
    For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing strategy might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.
  2. Audit knowledgeably.
    Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and using valuable space in the SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.
    For example, if you are auditing to gather information about database activity, then determine exactly what types of activities you want to track, audit only the activities of interest, and audit only for the amount of time necessary to gather the information that you want. As another example, do not audit objects if you are only interested in logical I/O information for each session.
  3. Before you implement an auditing strategy, consult your legal department.
    You should have the legal department of your organization review your audit strategy. Because your auditing will monitor other users in your organization, you must ensure that you are correctly following the compliance and corporate policy of your site.

Auditing Typical Database Activity

When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:
  1. Audit only pertinent actions.
    At a minimum, audit user access, the use of system privileges, and changes to the database schema structure. To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities. Remember also that auditing too much can affect database performance.
    For example, auditing changes to all tables in a database produces far too many audit trail records and can slow down database performance. However, auditing changes to critical tables, such as salaries in a Human Resources table, is useful.
    You can audit specific actions by using fine-grained auditing, which is described in"Auditing Specific Activities with Fine-Grained Auditing".
  2. Archive audit records and purge the audit trail.
    After you collect the required information, archive the audit records of interest and then purge the audit trail of this information. See "Archiving and Purging the Database Audit Trail" for instructions.
    To purge audit records, you can delete standard audit records from the SYS.AUD$ table and fine-grained audit records from the SYS.FGA_LOG$ table. See "Purging the Database Audit Trail" for examples.
    See "Controlling the Size of the Database Audit Trail" for more information about managing the database audit trail.
  3. Remember your company's privacy considerations.
    Privacy regulations often lead to additional business privacy policies. Most privacy laws require businesses to monitor access to personally identifiable information (PII), and monitoring is implemented by auditing. A business-level privacy policy should address all relevant aspects of data access and user accountability, including technical, legal, and company policy concerns.
  4. Check the Oracle Database log files for additional audit information
    The log files generated by Oracle Database contain useful information that you can use when auditing a database. For example, an Oracle database creates an alert file to recordSTARTUP and SHUTDOWN operations, and structural changes such as adding data files to the database.
    For example, if you want to audit committed or rolled back transactions, you can use theredo log files.

Auditing Suspicious Database Activity

When you audit to monitor suspicious database activity, use the following guidelines:
  1. First audit generally, and then specifically.
    When you start to audit for suspicious database activity, often not much information is available to target specific users or schema objects. Therefore, set audit options more generally at first, that is, by using the standard audit options described in Chapter 9, "Verifying Security Access with Auditing" explains how you can use the standard audit options to audit SQL statements, schema objects, privileges, and so on.
    After you have recorded and analyzed the preliminary audit information, disable general auditing, and then audit specific actions. You can use fine-grained auditing, which is described in "Auditing Specific Activities with Fine-Grained Auditing", to audit specific actions. Continue this process until you have gathered enough evidence to draw conclusions about the origin of the suspicious database activity.
  2. Audit common suspicious activities.
    Common suspicious activities are as follows:
    • Users who access the database during unusual hours
    • Multiple failed user login attempts
    • Login attempts by non-existent users
    In addition, monitor users who share accounts or multiple users who are logging in from the same IP address. You can query the DBA_AUDIT_SESSION data dictionary view to find this kind of activity. For a very granular approach, create fine-grained audit policies.
  3. Protect the audit trail.
    When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited. You can audit the standard audit trail by using the AUDIT SQL statement.
    For example:
    AUDIT SELECT ON SYS.AUD$ BY ACCESS;
    To audit the fine-grained audit trail, as user SYS, you would enter the following statement:
    AUDIT SELECT ON SYS.FGA$ BY ACCESS;
    If you have Oracle Database Vault enabled, you can further protect the SYS.AUDIT$,SYSTEM.AUD$, SYS.FGA$, and SYS.FGA_LOG$ tables by enclosing them in a realm. (In an Oracle Database Vault environment, the AUD$ table is moved to the SYSTEM schema when Oracle Label Security is enabled. SYS.AUD$ becomes a synonym for the SYSTEM.AUD$table.) See Oracle Database Vault Administrator's Guide for more information.

Recommended Audit Settings

Database schema or structure changes. Use the following AUDIT statement settings:
  • AUDIT ALTER ANY PROCEDURE BY ACCESS;
  • AUDIT ALTER ANY TABLE BY ACCESS;
  • AUDIT ALTER DATABASE BY ACCESS;
  • AUDIT ALTER SYSTEM BY ACCESS;
  • AUDIT CREATE ANY JOB BY ACCESS;
  • AUDIT CREATE ANY LIBRARY BY ACCESS;
  • AUDIT CREATE ANY PROCEDURE BY ACCESS;
  • AUDIT CREATE ANY TABLE BY ACCESS;
  • AUDIT CREATE EXTERNAL JOB BY ACCESS;
  • AUDIT DROP ANY PROCEDURE BY ACCESS;
  • AUDIT DROP ANY TABLE BY ACCESS;
Database access and privileges. Use these AUDIT statement settings:
  • AUDIT ALTER PROFILE BY ACCESS;
  • AUDIT ALTER USER BY ACCESS;
  • AUDIT AUDIT SYSTEM BY ACCESS;
  • AUDIT CREATE PUBLIC DATABASE LINK BY ACCESS;
  • AUDIT CREATE SESSION BY ACCESS;
  • AUDIT CREATE USER BY ACCESS;
  • AUDIT DROP PROFILE BY ACCESS;
  • AUDIT DROP USER BY ACCESS;
  • AUDIT EXEMPT ACCESS POLICY BY ACCESS;
  • AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS;
  • AUDIT GRANT ANY PRIVILEGE BY ACCESS;
  • AUDIT GRANT ANY ROLE BY ACCESS;
  • AUDIT ROLE BY ACCESS;

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. In Oracle Database 10g Release 2 (10.2), theCONNECT role was changed. If you are upgrading from a release earlier than Oracle Database 10.2 to the current release, then read this section.
This section contains:

Why Was the CONNECT Role Changed?

The CONNECT role was originally established with the following privileges:
ALTER SESSIONCREATE SESSION
CREATE CLUSTERCREATE SYNONYM
CREATE DATABASE LINKCREATE TABLE
CREATE SEQUENCECREATE VIEW
Beginning in Oracle Database 10g Release 2, the CONNECT role has only theCREATE SESSION privilege, all other privileges are removed.
Although the CONNECT role was frequently used to provision new accounts in Oracle Database, connecting to the database does not require all those privileges. Making this change enables you to enforce good security practices more easily.
Each user should have only the privileges needed to perform his or her tasks, an idea called the principle of least privilege. Least privilege mitigates risk by limiting privileges, so that it remains easy to do what is needed while concurrently reducing the ability to do inappropriate things, either inadvertently or maliciously.

How the CONNNECT Role Change Affects Applications

The effects of the changes to the CONNECT role can be seen in database upgrades, account provisioning, and installation of applications using new databases.

How the CONNECT Role Change Affects Database Upgrades

Upgrading your existing Oracle database to Oracle Database 10g Release 2 (10.2) automatically changes the CONNECT role to have only the CREATE SESSION privilege. Most applications are not affected because the applications objects already exist: no new tables, views, sequences, synonyms, clusters, or database links must be created.
Applications that create tables, views, sequences, synonyms, clusters, or database links, or that use the ALTER SESSION command dynamically, may fail due to insufficient privileges.

How the CONNECT Role Change Affects Account Provisioning

If your application or DBA grants the CONNECT role as part of the account provisioning process, then only CREATE SESSION privileges are included. Any additional privileges must be granted either directly or through another role.
This issue can be addressed by creating a new customized database role.
See Also:
Approaches to Addressing the CONNECT Role Change

How the CONNECT Role Change Affects Applications Using New Databases

New databases created using the Oracle Database 10g Release 2 (10.2) Utility (DBCA), or using database creation templates generated from DBCA, define the CONNECT role with only the CREATE SESSION privilege. Installing an application to use a new database may fail if the database schema used for the application is granted privileges solely through the CONNECTrole.

How the CONNECT Role Change Affects Users

The change to the CONNECT role affects three classes of users differently: general users, application developers, and client/server applications.

How the CONNECT Role Change Affects General Users

The new CONNECT role supplies only the CREATE SESSION privilege. Users who connect to the database to use an application are not affected, because the CONNECT role still has theCREATE SESSION privilege.
However, appropriate privileges will not be present for a certain set of users if they are provisioned solely with the CONNECT role. These are users who create tables, views, sequences, synonyms, clusters, or database links, or use the ALTER SESSION command. The privileges they need are no longer provided with the CONNECT role. To authorize the additional privileges needed, the database administrator must create and apply additional roles for the appropriate privileges, or grant them directly to the users who need them.
Note that the ALTER SESSION privilege is required for setting events. Few database users should require the ALTER SESSION privilege.
ALTER SESSION SET EVENTS ........
The alter session privilege is not required for other alter session commands.
ALTER SESSION SET NLS_TERRITORY = FRANCE;

How the CONNECT Role Change Affects Application Developers

Application developers provisioned solely with the CONNECT role do not have appropriate privileges to create tables, views, sequences, synonyms, clusters, or database links, nor to use the ALTER SESSION statement. The database administrator must either create and apply additional roles for the appropriate privileges, or grant them directly to the application developers who need them.

How the CONNECT Role Change Affects Client Server Applications

Most client/server applications that use dedicated user accounts will not be affected by this change. However, applications that create private synonyms or temporary tables using dynamic SQL in the user schema during account provisioning or run-time operations will be affected. They will require additional roles or grants to acquire the system privileges appropriate to their activities.

Approaches to Addressing the CONNECT Role Change

Oracle recommends the following three approaches to address the impact of this change.

Approach 1: Create a New Database Role

The privileges removed from the CONNECT role can be managed by creating a new database role.
First, connect to the upgraded Oracle database and create a new database role. The following example uses a role called my_app_developer.
CREATE ROLE my_app_developer;
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE SYNONYM, CREATE CLUSTER, CREATE DATABASE LINK, ALTER SESSION TO my_app_developer;
Second, determine which users or database roles have the CONNECT role, and grant the new role to these users or roles.
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

GRANT my_app_developer TO R1 WITH ADMIN OPTION;
GRANT my_app_developer TO R2;
You can determine the privileges that users require by using Oracle Auditing. The audit information can then be analyzed and used to create additional database roles with finer granularity.
Privileges not used can then be revoked for specific users. Note that before auditing, the database initialization parameter AUDIT_TRAIL must be initialized and the database restarted.
AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;
Database privilege usage can now be monitored periodically.
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.

Approach 2: Restore CONNECT Privileges

Starting with Oracle Database 11g Release 1 (11.1), Oracle provides a script calledrstrconn.sql in the $ORACLE_HOME/rdbms/admin directory. After a database upgrade or new database creation, this script can be used to grant the privileges that were removed from the CONNECT role in Oracle Database 11g Release 1 (11.1).
If this approach is used, then privileges that are not used should be revoked from users who do not need them. To identify such privileges and users, the database must be restarted with the database initialization parameter AUDIT_TRAIL initialized, for example,AUDIT_TRAIL=DB. Oracle Database auditing should then be turned on to monitor what privileges are used, as follows:
AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;
Database privilege usage can also be monitored periodically.
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.
New View Showing CONNECT Grantees
A new view enables administrators who continue using the old CONNECT role to see quickly which users have that role.
Table 10-1 shows the columns in the new DBA_CONNECT_ROLE_GRANTEES view.
Table 10-1 Columns and Contents for DBA_CONNECT_ROLE_GRANTEES
Column NameContents
Grantee
User granted the CONNECT role
Path_of_connect_role_grant
Role (or nested roles) by which the user is grantedCONNECT
Admin_opt
VARCHAR2(3), YES if user has ADMIN OPTION onCONNECT; otherwise, NO

Approach 3: Conduct Least Privilege Analysis

Oracle partners and application providers should use this approach to deliver more secure products to the Oracle customer base. The principle of least privilege mitigates risk by limiting privileges to the minimum set required to perform a given function.
For each class of users that the analysis shows need the same set of privileges, create a role with only those privileges. Remove all other privileges from those users, and assign that role to those users. As needs change, you can grant additional privileges, either directly or through these new roles, or create new roles to meet new needs. This approach helps to ensure that inappropriate privileges have been limited, thereby reducing the risk of inadvertent or malicious harm.

Comments

Post a Comment

Popular posts from this blog

VMware fix for Invalid manifest and ova file import failed errors

Image
Recently we got a OVA file for a virtual machine. The vendor instructions were to import the ova file in vmware Workstation, Player for Windows/Linux, Fusion for Mac, and VirtualBox as well.  The instructions were to take the available package and launch the VM with VMware workstation. The package contained  Module.mf, Module.ovf and Module-disk.vmdk and a Module.ova file. The .mf and .ovf file were 2 KB each whereas the vmdk was several gigs. The package also contained a Module.ova file which was several gigs as well. OVF           Open Virtualization Format MF             Manifest file VMDK       Virtual Machine Disk OVA           Open Virtualization Appliance The ovf file is a xml file that contains metadata for the ovf package The mf file contains the SHA1 hash codes of all files in the package The vmdk file is the disk image of the virtual machine,  VMware Workstation or VirtualBox. (vmdk format was originally developed by VMware and is an open format now). All of
Read more

SOAPUI - import certificate

Image
Note: SoapUI has two versions, one is open source and second Professional version. The open source can be download here . (confirmed link 12/19/2018). SSL Handshake issue:   There is an Issue in SoapUI version 5.3.0 (and 5.2.0 version) with SSL handshake error. It was resolved by updating below in vmoptions file ( refer here ). However, the error that shows up while trying to load wsdl is "Error loading WSDL" as below The fix is to Enable TLS 1.2 protocol for SOAP/REST calls in SoapUI, by ammending the vmoptions file to add the directive for TLS as (-Dsoapui.https.protocols=TLSv1.2). Refer here . Update: Version 5.5.0 does not have this issue. If you are on 5.3.0 better upgrade to 5.5.0 which is available now (Feb 2019). I had above issue as well as another issue reaching to https endpoint. Upgrade to 5.5.0 resolved issue. Select "Check for updates" under the Help menu and you will get option for upgrade. Select upgrade current version and accept all defaul
Read more

Centrally Managed Users (CMU) - New Feature in Oracle Database 18c

Image
Centrally Managed Users (CMU) Centrally Managed Users or CMU is a new feature introduced since Oracle DB 18c which allows simplified database user management through integration with Microsoft Active Directory (AD). Beginning with Oracle Database release 18c, version 18.1 and later supports direct integration with Microsoft Active Directory (AD) using the new centrally managed users capability. CMU allows the Oracle database to perform user authentication and authorization directly against AD. Benefits of CMU With centrally managed users, users accessing the database can be centrally managed to improve an organization's security posture. An enterprise user (a user in Microsoft Active Directory) can be exclusively mapped to a database account, or many enterprise users (in an Microsoft Active Directory group) can be mapped to a shared account in the database. Microsoft Active Directory groups can also be mapped to a database global role, which provides users with additional privilege
Read more