20 Database Security

Security Domain

Each user has a security domain—a set of properties that determine such things as:

• The actions (privileges and roles) available to the user

• The tablespace quotas (available disk space) for the user

• The system resource limits (for example, CPU processing time) for the user

Each property that contributes to a user's security domain is discussed in the following sections.

Default Tablespace

Each user is associated with a default tablespace. When a user creates a table, index, or cluster and no tablespace is specified to physically contain the schema object, the user's default tablespace is used if the user has the privilege to create the schema object and a quota in the specified default tablespace. The default tablespace provides Oracle with information to direct space use in situations where schema object's location is not specified.

Temporary Tablespace

Each user has a temporary tablespace. When a user runs a SQL statement that requires the creation of temporary segments (such as the creation of an index), the user's temporary tablespace is used. By directing all users' temporary segments to a separate tablespace, the temporary tablespace can reduce I/O contention among temporary segments and other types of segments.

Tablespace Quotas

Oracle can limit the collective amount of disk space available to the objects in a schema. Quotas (space limits) can be set for each tablespace available to a user. This permits selective control over the amount of disk space that can be consumed by the objects of specific schemas.

Profiles and Resource Limits

Each user is assigned a profile that specifies limitations on several system resources available to the user, including the following:

• Number of concurrent sessions the user can establish

• CPU processing time available for the user's session and a single call to Oracle made by a SQL statement

• Amount of logical I/O available for the user's session and a single call to Oracle made by a SQL statement

• Amount of idle time available for the user's session

• Amount of connect time available for the user's session

• Password restrictions:

  • Account locking after multiple unsuccessful login attempts

  • Password expiration and grace period

  • Password reuse and complexity restrictions

Third Party-Based Authentication Technologies

If network authentication services are available to you (such as DCE, Kerberos, or SESAME), then Oracle can accept authentication from the network service. If you use a network authentication service, then some special considerations arise for network roles and database links.

Public-Key-Infrastructure-Based Authentication

Authentication systems based on public key cryptography issue digital certificates to user clients, which use them to authenticate directly to servers in the enterprise without directly involving an authentication server. Oracle provides a public key infrastructure (PKI) for using public keys and certificates, consisting of the following components:

• Authentication and secure session key management using Secure Sockets Layer (SSL).

• Oracle Call Interface (OCI) and PL/SQL functions to sign user-specified data using a private key and certificate, and verify the signature on data using a trusted certificate.

• Trusted certificates, identifying third-party entities that are trusted as signers of user certificates when an identity is being validated as the entity it claims to be.

• Oracle wallets, which are data structures that contain a user private key, a user certificate, and the user's set of trust points (trusted certificate authorities).

• Oracle Wallet Manager, a standalone Java application used to manage and edit the security credentials in Oracle wallets.

• X.509v3 certificates obtained from (and signed by) a trusted entity, a certificate authority outside of Oracle.

• Oracle Internet Directory to manage security attributes and privileges for users, including users authenticated by X.509 certificates. It enforces attribute-level access control and enables read, write, or update privileges on specific attributes to be restricted to specific named users, such as administrators.

• Oracle Enterprise Security Manager, provides centralized privilege management to make administration easier and increase your level of security. This lets you store and retrieve roles from Oracle Internet Directory.

• Oracle Enterprise Login Assistant, a Java-based tool to open and close a user wallet to enable or disable secure SSL-based communications for an application.

Remote Authentication

Oracle supports remote authentication of users through Remote Dial-In User Service (RADIUS), a standard lightweight protocol used for user authentication, authorization, and accounting.

Password Encryption

To protect password confidentiality, Oracle always encrypts passwords before sending them over the network. Oracle encrypts the passwords using a modified AES (Advanced Encryption Standard) algorithm.

Account Locking

Oracle can lock a user's account after a specified number of consecutive failed log-in attempts. You can configure the account to unlock automatically after a specified time interval or to require database administrator intervention to be unlocked. The database administrator can also lock accounts manually, so that they must be unlocked explicitly by the database administrator.

Password Lifetime and Expiration

The database administrator can specify a lifetime for passwords, after which they expire and must be changed before account login is again permitted. A grace period can be established, during which each attempt to login to the database account receives a warning message to change the password. If it is not changed by the end of that period, then the account is locked. No further logins to that account are allowed without assistance by the database administrator.

The database administrator can also set the password state to expired, causing the user's account status to change to expired. The user or the database administrator must then change the password before the user can log in to the database.

The password history option checks each newly specified password to ensure that a password is not reused for a specified amount of time or for a specified number of password changes.

Password Complexity Verification

Complexity verification checks that each password is complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords.

The Oracle default password complexity verification routine checks that each password meet the following requirements:

• Be a minimum of four characters in length

• Not equal the userid

• Include at least one alphabet character, one numeric character, and one punctuation mark

• Not match any word on an internal list of simple words like welcome, account, database, user, and so on

• Differ from the previous password by at least three characters

Types of System Resources and Limits

Oracle can limit the use of several types of system resources, including CPU time and logical reads. In general, you can control each of these resources at the session level, the call level, or both.

Profiles

In the context of system resources, a profile is a named set of specified resource limits that can be assigned to a valid user name in an Oracle database. Profiles provide for easy management of resource limits. Profiles are also the way in which you administer password policy.

Different profiles can be created and assigned individually to each user of the database. A default profile is present for all users not explicitly assigned a profile. The resource limit feature prevents excessive consumption of global database system resources.

System Privileges

A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges. There are over 100 distinct system privileges.

Schema Object Privileges

A schema object privilege is a privilege or right to perform a particular action on a specific schema object:

Different object privileges are available for different types of schema objects. For example, the privilege to delete rows from the departments table is an object privilege.

Some schema objects, such as clusters, indexes, triggers, and database links, do not have associated object privileges. Their use is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER system privilege.

A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym.

Granting object privileges on a table, view, sequence, procedure, function, or package to a synonym for the object has the same effect as if no synonym were used. When a synonym is dropped, all grants for the underlying schema object remain in effect, even if the privileges were granted by specifying the dropped synonym.

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"

Role Mechanisms

Database roles have the following functionality:

• A role can be granted system or schema object privileges.

• A role can be granted to other roles. However, a role cannot be granted to itself and cannot be granted circularly. For example, role A cannot be granted to role B if role B has previously been granted to role A.

• Any role can be granted to any database user.

• Each role granted to a user is, at a given time, either enabled or disabled. A user's security domain includes the privileges of all roles currently enabled for the user and excludes the privileges of any roles currently disabled for the user. Oracle allows database applications and users to enable and disable roles to provide selective availability of privileges.

• An indirectly granted role is a role granted to a role. It can be explicitly enabled or disabled for a user. However, by enabling a role that contains other roles, you implicitly enable all indirectly granted roles of the directly granted role.

The Operating System and Roles

In some environments, you can administer database security using the operating system. The operating system can be used to manage the granting (and revoking) of database roles and to manage their password authentication. This capability is not available on all operating systems.

Dynamic Predicates

Dynamic predicates are acquired at statement parse time, when the base table or view is referenced in a DML statement, rather than having the security rules embedded in views.

The function or package that implements the security policy you create returns a predicate (a WHERE condition). This predicate controls access according to the policy specifications. Rewritten queries are fully optimized and shareable.

A dynamic predicate for a table, view, or synonym is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface.

Dynamic Contexts

Your policies can identify run-time efficiencies by specifying whether a policy is static, shared, context-sensitive, or dynamic.

If it is static, producing the same predicate string for anyone accessing the object, then it is run once and cached in SGA. Policies for statements accessing the same object do not re-run the policy function, but use the cached predicate instead.

This is also true for shared-static policies, for which the server first looks for a cached predicate generated by the same policy function of the same policy type. Shared-static policies are ideal for data partitions on hosting because almost all objects share the same function and the policy is static.

If you label your policy context-sensitive, then the server always runs the policy function on statement parsing; it does not cache the value returned. The policy function is not re-evaluated at statement execution time unless the server detects context changes since the last use of the cursor. (For session pooling where multiple clients share a database session, the middle tier must reset context during client switches.)

When a context-sensitive policy is shared, the server first looks for a cached predicate generated by the same policy function of the same policy type within the same database session. If the predicate is found in the session memory, then the policy function is not re-run and the cached value is valid until session private application context changes occur.

For dynamic policies, the server assumes the predicate may be affected by any system or session environment at any time, and so always re-runs the policy function on each statement parsing or execution.

Database User Management

Depending on the size of a database system and the amount of work required to manage database users, the security administrator might be the only user with the privileges required to create, alter, or drop database users. Or, there may be several administrators with privileges to manage database users. Regardless, only trusted individuals should have the powerful privileges to administer database users.

User Authentication

Database users can be authenticated (verified as the correct person) by Oracle using database passwords, the host operating system, network services, or by Secure Sockets Layer (SSL).

Operating System Security

If applicable, the following security issues must also be considered for the operating system environment executing Oracle and any database applications:

• Database administrators must have the operating system privileges to create and delete files.

• Typical database users should not have the operating system privileges to create or delete files related to the database.

• If the operating system identifies database roles for users, then the security administrators must have the operating system privileges to modify the security domain of operating system accounts.

General User Security

For all types of database users, consider password security and privilege management.

If user authentication is managed by the database, then security administrators should develop a password security policy to maintain database access security. For example, database users must change their passwords at regular intervals. By forcing a user to modify passwords, unauthorized database access can be reduced. To better protect the confidentiality of your password, Oracle can be configured to use encrypted passwords for client/server and server/server connections.

Also consider issues related to privilege management for all types of users. For example, a database with many users, applications, or objects, would benefit from using roles to manage the privileges available to users. Alternatively, in a database with a handful of user names, it might be easier to grant privileges explicitly to users and avoid the use of roles.

End-User Security

Security administrators must define a policy for end-user security. If a database has many users, then the security administrator can decide which groups of users can be categorized into user groups, and then create user roles for these groups. The security administrator can grant the necessary privileges or application roles to each user role, and assign the user roles to the users. To account for exceptions, the security administrator must also decide what privileges must be explicitly granted to individual users.

Roles are the easiest way to grant and manage the common privileges needed by different groups of database users. You can also manage users and their authorizations centrally, in a directory service, through the enterprise user and enterprise role features of Oracle Advanced Security.

Administrator Security

Security administrators should have a policy addressing database administrator security. For example, when the database is large and there are several types of database administrators, the security administrator might decide to group related administrative privileges into several administrative roles. The administrative roles can then be granted to appropriate administrator users. Alternatively, when the database is small and has only a few administrators, it might be more convenient to create one administrative role and grant it to all administrators.

CONNECT username/password AS SYSDBA/SYSOPER

Application Developer Security

Security administrators must define a special security policy for the application developers using a database. A security administrator could grant the privileges to create necessary objects to application developers. Or, alternatively, the privileges to create objects could be granted only to a database administrator, who then receives requests for object creation from developers.

Application Administrator Security

In large database systems with many database applications, consider assigning application administrators responsible for the following types of tasks:

• Creating roles for an application and managing the privileges of each application role

• Creating and managing the objects used by a database application

• Maintaining and updating the application code and Oracle procedures and packages, as necessary

Often, an application administrator is also the application developer who designed an application. However, an application administrator could be any individual familiar with the database application.

Audit Records and the Audit Trails

Audit records include information such as the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.