20 Database Security

Database Users and Schemas

Each Oracle database has a list of user names. To access a database, a user must use a database application and attempt a connection with a valid user name of the database. Each user name has an associated password to prevent unauthorized use.

Privileges

A privilege is a right to run a particular type of SQL statement. Some examples of privileges include the right to:

• Connect to the database (create a session)

• Create a table in your schema

• Select rows from someone else's table

• Run someone else's stored procedure

Roles

Oracle Database provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles.

Storage Settings and Quotas

You can direct and limit the use of disk space allocated to the database for each user, including default and temporary tablespaces and tablespace quotas.

This section includes the following topics:

• Default Tablespace

• Temporary Tablespace

• Tablespace Quotas

• Profiles and Resource Limits

Tablespace Encryption

Tablespace encryption is a new feature introduced in this release. Tablespace encryption enables you to encrypt an entire tablespace. This secures all data stored in the tablespace. When an authorized user accesses data in the tablespace, the data is transparently decrypted for him.

Tablespace encryption eliminates the need for granular analysis of applications to determine which columns to encrypt. You can use tablespace encryption to encrypt entire tables that might contain sensitive data.

Transparent encryption/decryption takes place during disk input/output (I/O) and not for every logical access to the data. This leads to improved performance.

Authentication by the Operating System

Some operating systems let Oracle Database use information they maintain to authenticate users, with the following benefits:

• Once authenticated by the operating system, users can connect to Oracle Database more conveniently, without specifying a user name or password. For example, an operating-system-authenticated user can invoke SQL*Plus and skip the user name and password prompts by entering the following:

  SQLPLUS / 
  
  

• With control over user authentication centralized in the operating system, Oracle Database need not store or manage user passwords, though it still maintains user names in the database.

• Audit trails in the database and operating system use the same user names.

When an operating system is used to authenticate database users, managing distributed database environments and database links requires special care.

Authentication by the Network

Oracle Database supports the following methods of authentication by the network:

• Third Party-Based Authentication Technologies

• Public-Key-Infrastructure-Based Authentication

• Remote Authentication

Authentication by Oracle Database

Oracle Database can authenticate users attempting to connect to a database by using information stored in that database.

To set up Oracle Database to use database authentication, create each user with an associated password that must be supplied when the user attempts to establish a connection. This prevents unauthorized use of the database, since the connection will be denied if the user provides an incorrect password. Oracle Database stores a user's password in the data dictionary in an encrypted format to prevent unauthorized alteration, but a user can change the password at any time.

Database authentication includes the following facilities:

• Password Encryption

• Account Locking

• Password Lifetime and Expiration

• Password Complexity Verification

Multitier Authentication and Authorization

In a multitier environment, Oracle Database controls the security of middle-tier applications by limiting their privileges, preserving client identities through all tiers, and auditing actions taken on behalf of clients. In applications that use a heavy middle tier, such as a transaction processing monitor, the identity of the client connecting to the middle tier must be preserved. Yet one advantage of a middle tier is connection pooling, which allows multiple users to access a data server without each of them needing a separate connection. In such environments, you must be able to set up and break down connections very quickly.

For these environments, Oracle database administrators can use the Oracle Call Interface (OCI) to create lightweight sessions, allowing database password authentication for each user. This preserves the identity of the real user through the middle tier without the overhead of a separate database connection for each user.

You can create lightweight sessions with or without passwords. However, if a middle tier is outside or on a firewall, then security is better when each lightweight session has its own password. For an internal application server, lightweight sessions without passwords might be appropriate.

Oracle Database 11g enables you to implement server-side connection pooling. This allows different applications and application processes to share database connections. Server-side connection pooling supports only password based authentication. Advanced Security Option (ASO) and enterprise users are currently not supported.

Authentication by the Secure Socket Layer Protocol

The Secure Socket Layer (SSL) protocol is an application layer protocol. Users identified either externally or globally (external or global users) can authenticate to a database through SSL.

Authentication of Database Administrators

Database administrators perform special operations (such as shutting down or starting up a database) that should not be performed by normal database users. Oracle Database provides a more secure authentication scheme for database administrator user names.

You can choose between operating system authentication or password files to authenticate database administrators. Figure 20-1 illustrates the choices you have for database administrator authentication schemes. Different choices apply to administering your database locally (on the computer where the database resides) and to administering many different database computers from a single remote client.

Figure 20-1 Database Administrator Authentication Methods

Description of "Figure 20-1 Database Administrator Authentication Methods"

Operating system authentication for a database administrator typically involves placing his operating system user name in a special group or giving it a special process right. (On UNIX systems, the group is the dba group.)

The database uses password files to keep track of database user names that have been granted the SYSDBA and SYSOPER privileges, enabling the following operations:

• SYSOPER lets database administrators perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER, and includes the RESTRICTED SESSION privilege.

• SYSDBA contains all system privileges with ADMIN OPTION, and the SYSOPER system privilege. Permits CREATE DATABASE and time-based recovery.

User Resource Limits and Profiles

You can set limits on the amount of various system resources available to each user as part of a user's security domain. By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.

This is very useful in large, multiuser systems, where system resources are expensive. Excessive consumption of resources by one or more users can detrimentally affect the other users of the database.

Manage a user's resource limits and password management preferences with his or her profile—a named set of resource limits that you can assign to that user. Each database can have an unlimited number of profiles. The security administrator can enable or disable the enforcement of profile resource limits universally.

If you set resource limits, then a slight degradation in performance occurs when users create sessions. This is because Oracle Database loads all resource limit data for the user when a user connects to a database.

Resource limits and profiles are discussed in the following sections:

• Types of System Resources and Limits

• Profiles

Introduction to Privileges

A privilege is a right to run a particular type of SQL statement or to access another user's object.

Grant privileges to users so that they can accomplish tasks required for their job. Grant privileges only to users who absolutely require them. Excessive granting of unnecessary privileges can compromise security. A user can receive a privilege in two different ways:

• You can grant privileges to users explicitly. For example, you can explicitly grant the privilege to insert records into the employees table to the user SCOTT.

• You can grant privileges to a role (a named group of privileges), and then grant the role to one or more users. For example, you can grant the privileges to select, insert, update, and delete records from the employees table to the role named clerk, which in turn you can grant to the users scott and brian.

Because roles allow for easier and better management of privileges, you should generally grant privileges to roles and not to specific users.

There are two distinct categories of privileges:

• System Privileges

• Schema Object Privileges

Introduction to Roles

Managing and controlling privileges is made easier by using roles, which are named groups of related privileges that you grant, as a group, to users or other roles. Within a database, each role name must be unique, different from all user names and all other role names. Unlike schema objects, roles are not contained in any schema. Therefore, a user who creates a role can be dropped with no effect on the role.

Roles ease the administration of end-user system and schema object privileges. However, 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.

Table 20-1 lists properties of roles that enable easier privilege management within a database.

Database administrators often create roles for a database application. The DBA grants a secure application role all privileges necessary to run the application. The DBA then grants the secure application role to other roles or users. An application can have several different roles, each granted a different set of privileges that allow for more or less data access while using the application.

The DBA can create a role with a password to prevent unauthorized use of the privileges granted to the role. Typically, an application is designed so that when it starts, it enables the proper role. As a result, an application user does not need to know the password for an application's role.

This section includes the following topics:

• Common Uses for Roles

• Role Mechanisms

• The Operating System and Roles

Common Uses for Roles

In general, you create a role to serve one of two purposes:

• To manage the privileges for a database application

• To manage the privileges for a user group

Figure 20-2 and the sections that follow describe the two uses of roles.

Figure 20-2 Common Uses for Roles

Description of "Figure 20-2 Common Uses for Roles"

This section includes the following topics:

• Application Roles

• User Roles

Application Roles

You grant an application role all privileges necessary to run a given database application. Then, you grant the secure application role to other roles or to specific users. An application can have several different roles, with each role assigned a different set of privileges that allow for more or less data access while using the application.

User Roles

You create a user role for a group of database users with common privilege requirements. You manage user privileges by granting secure application roles and privileges to the user role and then granting the user role to appropriate users.

Secure Application Roles

Oracle Database provides secure application roles, which are roles that can only be enabled by authorized PL/SQL packages. This mechanism restricts the enabling of such roles to the invoking application.

Security is strengthened when passwords are not embedded in application source code or stored in a table. Instead, a secure application role can be created, specifying which PL/SQL package is authorized to enable the role. Package identity is used to determine whether privileges are sufficient to enable the roles. Before enabling the role, the application can perform authentication and customized authorization, such as checking whether the user has connected through a proxy.

Because of the restriction that users cannot change security domain inside definer's right procedures, secure application roles can only be enabled inside invoker's right procedures.

Fine-Grained Access Control

Fine-grained access control lets you use functions to implement security policies and to associate those security policies with tables, views, or synonyms. The database server automatically enforces your security policies, no matter how the data is accessed (for example, by ad hoc queries).

You can:

• Use different policies for SELECT, INSERT, UPDATE, and DELETE (and INDEX, for row level security policies).

• Use security policies only where you need them (for example, on salary information).

• Use more than one policy for each table, including building on top of base policies in packaged applications.

• Distinguish policies between different applications, by using policy groups. Each policy group is a set of policies that belong to an application. The database administrator designates an application context, called a driving context, to indicate the policy group in effect. When tables, views, or synonyms are accessed, the fine-grained access control engine looks up the driving context to determine the policy group in effect and enforces all the associated policies that belong to that policy group.

The PL/SQL package DBMS_RLS let you administer your security policies. Using this package, you can add, drop, enable, disable, and refresh the policies (or policy groups) you create.

Application Context

Application context helps you apply fine-grained access control because you can associate your function-based security policies with applications.

Each application has its own application-specific context, which users cannot arbitrarily change (for example, through SQL*Plus). Context attributes are accessible to the functions implementing your security policies. For example, context attributes for a human resources application could include "position," "organizational unit," and "country," whereas attributes for an order-entry control might be "customer number" and "sales region".

Application contexts thus permit flexible, parameter-based access control using attributes of interest to an application.

You can:

• Base predicates on context values

• Use context values within predicates, as bind variables

• Set user attributes

• Access user attributes

Fine-Grained Auditing

Fine-grained auditing allows the monitoring of data access based on content. It provides granular auditing of queries, as well as INSERT, UPDATE, and DELETE operations. For example, a central tax authority needs to track access to tax returns to guard against employee snooping, with enough detail to determine what data was accessed. It is not enough to know that SELECT privilege was used by a specific user on a particular table. Fine-grained auditing provides this deeper functionality.

In general, fine-grained auditing policy is based on simple user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a returning row, the query is audited. Later, Oracle Database runs user-defined audit event handlers using autonomous transactions to process the event.

Fine-grained auditing can be implemented in user applications using the DBMS_FGA package or by using database triggers.

System Security Policy

Each database has one or more administrators responsible for maintaining all aspects of the security policy: the security administrators. If the database system is small, then the database administrator might have the responsibilities of the security administrator. However, if the database system is large, then a special person or group of people might have responsibilities limited to those of a security administrator.

A security policy must be developed for every database. A security policy should include several sub-policies, as explained in the following sections.

This section includes the following topics:

• Database User Management

• User Authentication

• Operating System Security

Data Security Policy

Data security includes mechanisms that control access to and use of the database at the object level. Your data security policy determines which users have access to a specific schema object, and the specific types of actions allowed for each user on the object. For example, user scott can issue SELECT and INSERT statements but not DELETE statements using the employees table. Your data security policy should also define the actions, if any, that are audited for each schema object.

Your data security policy is determined primarily by the level of security you want for the data in your database. For example, it might be acceptable to have little data security in a database when you want to allow any user to create any schema object, or grant access privileges for their objects to any other user of the system. Alternatively, it might be necessary for data security to be very controlled when you want to make a database or security administrator the only person with the privileges to create objects and grant access privileges for objects to roles and users.

Overall data security should be based on the sensitivity of data. If information is not sensitive, then the data security policy can be more lax. However, if data is sensitive, then a security policy should be developed to maintain tight control over access to objects.

Some means of implementing data security include system and object privileges, and through roles. A role is a set of privileges grouped together that can be granted to users. Views can also implement data security because their definition can restrict access to table data. They can exclude columns containing sensitive data.

Another means of implementing data security is through fine-grained access control and use of an associated application context. Fine-grained access control lets you implement security policies with functions and associate those security policies with tables or views. In effect, the security policy function generates a WHERE condition that is appended to a SQL statement, thereby restricting the users access to rows of data in the table or view. An application context is a secure data cache for storing information used to make access control decisions.

User Security Policy

This section describes aspects of user security policy, and contains the following topics:

• General User Security

• End-User Security

• Administrator Security

• Application Developer Security

• Application Administrator Security

CONNECT username/password AS SYSDBA/SYSOPER

Password Management Policy

Database security systems dependent on passwords require that passwords be kept secret at all times. But, passwords are vulnerable to theft, forgery, and misuse. To allow for greater control over database security, the Oracle Database password management policy is controlled by DBAs and security officers through user profiles.

Auditing Policy

Security administrators should define a policy for the auditing procedures of each database. You may decide to have database auditing disabled unless questionable activities are suspected. When auditing is required, decide what level of detail to audit the database; usually, general system auditing is followed by more specific types of auditing after the origins of suspicious activity are determined. Auditing is discussed in the following section.

Types and Records of Auditing

Oracle Database allows audit options to be focused or broad. You can audit:

• Successful statement executions, unsuccessful statement executions, or both

• Statement executions once in each user session or once every time the statement is run

• Activities of all users or of a specific user

Oracle Database auditing enables the use of several different mechanisms, with the features listed in Table 20-2.