| Oracle® Database Concepts 10g Release 2 (10.2) Part Number B14220-02 |
|
|
View PDF |
| Oracle® Database Concepts 10g Release 2 (10.2) Part Number B14220-02 |
|
|
View PDF |
This chapter provides an overview of Oracle database security.
This chapter contains the following topics:
See Also:
Oracle Database Security Guide for more detailed information on everything in this chapterDatabase security entails allowing or disallowing user actions on the database and the objects within it. Oracle uses schemas and security domains to control access to data and to restrict the use of various database resources.
Oracle provides comprehensive discretionary access control. Discretionary access control regulates all user access to named objects through privileges. A privilege is permission to access a named object in a prescribed manner; for example, permission to query a table. Privileges are granted to users at the discretion of other users.
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.
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.
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
See Also:
"Introduction to Privileges"Oracle provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles.
See Also:
"Introduction to Roles" information about role propertiesYou can direct and limit the use of disk space allocated to the database for each user, including default and temporary tablespaces and tablespace quotas.
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.
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.
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.
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
See Also:
"Profiles"The Oracle database provides security in the form of authentication, authorization, and auditing. Authentication ensures that only legitimate users gain access to the system. Authorization ensures that those users only have access to resources they are permitted to access. Auditing ensures accountability when users access protected resources. Although these security mechanisms effectively protect data in the database, they do not prevent access to the operating system files where the data is stored. Transparent data encryption enables encryption of sensitive data in database columns as it is stored in the operating system files. In addition, it provides for secure storage and management of encryption keys in a security module external to the database.
Using an external security module separates ordinary program functions from those that pertain to security, such as encryption. Consequently, it is possible to divide administration duties between DBAs and security administrators, a strategy that enhances security because no administrator is granted comprehensive access to data. External security modules generate encryption keys, perform encryption and decryption, and securely store keys outside of the database.
Transparent data encryption is a key-based access control system that enforces authorization by encrypting data with a key that is kept secret. There can be only one key for each database table that contains encrypted columns regardless of the number of encrypted columns in a given table. Each table's column encryption key is, in turn, encrypted with the database server's master key. No keys are stored in the database. Instead, they are stored in an Oracle wallet, which is part of the external security module.
Before you can encrypt any database columns, you must generate or set a master key. This master key is used to encrypt the column encryption key which is generated automatically when you issue a SQL command with the ENCRYPT clause on a database column.
See Also:
Oracle Database Advanced Security Administrator's Guide for details about using transparent data encryptionAuthentication means verifying the identity of someone (a user, device, or other entity) who wants to use data, resources, or applications. Validating that identity establishes a trust relationship for further interactions. Authentication also enables accountability by making it possible to link access and actions to specific identities. After authentication, authorization processes can allow or limit the levels of access and action permitted to that entity.
For simplicity, the same authentication method is generally used for all database users, but Oracle allows a single database instance to use any or all methods. Oracle requires special authentication procedures for database administrators, because they perform special database operations. Oracle also encrypts passwords during transmission to ensure the security of network authentication.
To validate the identity of database users and prevent unauthorized use of a database user name, you can authenticate using any combination of the methods described in the following sections:
Some operating systems let Oracle use information they maintain to authenticate users, with the following benefits:
Once authenticated by the operating system, users can connect to Oracle 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 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.
Oracle supports the following methods of authentication by the network:
Note:
These methods require Oracle Database Enterprise Edition with the Oracle Advanced Security option.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.
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.
Oracle can authenticate users attempting to connect to a database by using information stored in that database.
To set up Oracle 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 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:
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.
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.
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.
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
In a multitier environment, Oracle 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.
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.
Database administrators perform special operations (such as shutting down or starting up a database) that should not be performed by normal database users. Oracle 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
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.
See Also:
Oracle Database Administrator's Guide for information on authentication and distributed database concepts
Oracle Database Advanced Security Administrator's Guide for information about the Oracle Advanced Security option
Your Oracle operating system-specific documentation for information about authenticating
Authorization primarily includes two processes:
Permitting only certain users to access, process, or alter data
Applying varying limitations on users' access or actions. The limitations placed on (or removed from) users can apply to objects, such as schemas, tables, or rows; or to resources, such as time (CPU, connect, or idle times).
This section introduces the basic concepts and mechanisms for placing or removing such limitations on users, individually or in groups.
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 loads all resource limit data for the user when a user connects to a database.
See Also:
Oracle Database Administrator's Guide for information about security administratorsResource limits and profiles are discussed in the following sections:
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.
Each time a user connects to a database, a session is created. Each session consumes CPU time and memory on the computer that runs Oracle. You can set several resource limits at the session level.
If a user exceeds a session-level resource limit, then Oracle terminates (rolls back) the current statement and returns a message indicating that the session limit has been reached. At this point, all previous statements in the current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disconnect (in this case, the current transaction is committed). All other operations produce an error. Even after the transaction is committed or rolled back, the user can accomplish no more work during the current session.
Each time a SQL statement is run, several steps are taken to process the statement. During this processing, several calls are made to the database as part of the different execution phases. To prevent any one call from using the system excessively, Oracle lets you set several resource limits at the call level.
If a user exceeds a call-level resource limit, then Oracle halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user's session remains connected.
When SQL statements and other types of calls are made to Oracle, an amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially consume a large amount of CPU time, reducing CPU time available for other processing.
To prevent uncontrolled use of CPU time, limit the CPU time for each call and the total amount of CPU time used for Oracle calls during a session. Limits are set and measured in CPU one-hundredth seconds (0.01 seconds) used by a call or a session.
Input/output (I/O) is one of the most expensive operations in a database system. SQL statements that are I/O intensive can monopolize memory and disk use and cause other database operations to compete for these resources.
To prevent single sources of excessive I/O, Oracle lets you limit the logical data block reads for each call and for each session. Logical data block reads include data block reads from both memory and disk. The limits are set and measured in number of block reads performed by a call or during a session.
Oracle also provides for the limitation of several other resources at the session level:
You can limit the number of concurrent sessions for each user. Each user can create only up to a predefined number of concurrent sessions.
You can limit the idle time for a session. If the time between Oracle calls for a session reaches the idle time limit, then the current transaction is rolled back, the session is aborted, and the resources of the session are returned to the system. The next call receives an error that indicates the user is no longer connected to the instance. This limit is set as a number of elapsed minutes.
Shortly after a session is aborted because it has exceeded an idle time limit, the process monitor (PMON) background process cleans up after the aborted session. Until PMON completes this process, the aborted session is still counted in any session/user resource limit.
You can limit the elapsed connect time for each session. If a session's duration exceeds the elapsed time limit, then the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. This limit is set as a number of elapsed minutes.
Oracle does not constantly monitor the elapsed idle time or elapsed connection time. Doing so would reduce system performance. Instead, it checks every few minutes. Therefore, a session can exceed this limit slightly (for example, by five minutes) before Oracle enforces the limit and aborts the session.
You can limit the amount of private SGA space (used for private SQL areas) for a session. This limit is only important in systems that use the shared server configuration. Otherwise, private SQL areas are located in the PGA. This limit is set as a number of bytes of memory in an instance's SGA. Use the characters K or M to specify kilobytes or megabytes.
See Also:
Oracle Database Administrator's Guide for instructions about enabling and disabling resource limitsIn 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.
You need to create and manage user profiles only if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users. Determine how many profiles are needed to encompass all types of users in a database and then determine appropriate resource limits for each profile.
Before creating profiles and setting the resource limits associated with them, determine appropriate values for each resource limit. You can base these values on the type of operations a typical user performs. Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage.
You can gather statistics for other limits using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.
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:
See Also:
Oracle Database Administrator's Guide for a list of all system and schema object privileges, as well as instructions for privilege managementA 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.
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.
See Also:
Oracle Database Security Guide for more information about schema object privilegesManaging 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.
These following properties of roles enable easier privilege management within a database:
| Property | Description |
|---|---|
| Reduced privilege administration | Rather than granting the same set of privileges explicitly to several users, you can grant the privileges for a group of related users to a role, and then only the role needs to be granted to each member of the group. |
| Dynamic privilege management | If the privileges of a group must change, then only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role. |
| Selective availability of privileges | You can selectively enable or disable the roles granted to a user. This allows specific control of a user's privileges in any given situation. |
| Application awareness | The data dictionary records which roles exist, so you can design applications to query the dictionary and automatically enable (or disable) selective roles when a user attempts to run the application by way of a given user name. |
| Application-specific security | You can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password. |
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.
See Also:
Oracle Database Application Developer's Guide - Fundamentals for instructions for enabling roles from an applicationIn 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.
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.
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.
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.
Oracle 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.
This section describes restrictions associated not with users, but with objects. The restrictions provide protection regardless of the entity who seeks to access or alter them.
You provide this protection by designing and using policies to restrict access to specific tables, views, synonyms, or rows. These policies invoke functions that you design to specify dynamic predicates establishing the restrictions. You can also group established policies, applying a policy group to a particular application.
Having established such protections, you need to be notified when they are threatened or breached. Given notification, you can strengthen your defenses or deal with the consequences of inappropriate actions and the entities who caused them.
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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about package implementation
Oracle Database Application Developer's Guide - Fundamentals for information and examples on establishing security policies
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.
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
See Also:
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.
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 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.
This section contains the following topics:
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.
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.
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).
See Also:
"Overview of Authentication Methods"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.
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.
See Also:
Oracle Database Administrator's Guide for more about views
Oracle Database Application Developer's Guide - Fundamentals for more about fine-grained access control and application context
This section describes aspects of user security policy, and contains the following topics:
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.
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.
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.
After database creation, and if you used the default passwords for SYS and SYSTEM, immediately change the passwords for the SYS and SYSTEM administrative user names. Connecting as SYS or SYSTEM gives a user powerful privileges to modify a database.
If you have installed options that have caused other administrative user names to be created, then such user name accounts are initially created locked.
Only database administrators should have the capability to connect to a database with administrative privileges. For example:
CONNECT username/password AS SYSDBA/SYSOPER
Connecting as SYSOPER gives a user the ability to perform basic operational tasks (such as STARTUP, SHUTDOWN, and recovery operations). Connecting as SYSDBA gives the user these abilities plus unrestricted privileges to do anything to a database or the objects within a database (including, CREATE, DROP, and DELETE). SYSDBA puts a user in the SYS schema, where they can alter data dictionary tables.
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.
Database application developers are unique database users who require special groups of privileges to accomplish their jobs. Unlike end users, developers need system privileges, such as CREATE TABLE, CREATE PROCEDURE, and so on. However, only specific system privileges should be granted to developers to restrict their overall capabilities in the database.
In many cases, application development is restricted to test databases and is not allowed on production databases. This restriction ensures that application developers do not compete with end users for database resources, and that they cannot detrimentally affect a production database. After an application has been thoroughly developed and tested, it is permitted access to the production database and made available to the appropriate end users of the production database.
Security administrators can create roles to manage the privileges required by the typical application developer.
While application developers are typically given the privileges to create objects as part of the development process, security administrators must maintain limits on what and how much database space can be used by each application developer. For example, the security administrator should specifically set or restrict the following limits for each application developer:
The tablespaces in which the developer can create tables or indexes
The quota for each tablespace accessible to the developer
Both limitations can be set by altering a developer's security domain.
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.
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, Oracle's password management policy is controlled by DBAs and security officers through user profiles.
See Also:
"Authentication by the Oracle Database"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.
Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement run, or on combinations of factors that can include name, application, time, and so on. Security policies can cause auditing when specified elements in an Oracle database are accessed or altered, including content.
Auditing is generally used to:
Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content
Investigate suspicious activity. For example, if an unauthorized user is deleting data from tables, then the security administrator could audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
You can use Enterprise Manager to view and configure audit-related initialization parameters and administer audited objects for statement auditing and schema object auditing. For example, Enterprise Manager shows the properties for current audited statements, privileges, and objects. You can view the properties of each object, and you can search audited objects by their properties. You can also turn on and turn off auditing on objects, statements, and privileges.
Oracle 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 auditing enables the use of several different mechanisms, with the following features:
Table 20-1 Types of Auditing
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.
The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle database's data dictionary. Several predefined views are provided to help you use the information in this table.
Audit trail records can contain different types of information, depending on the events audited and the auditing options set. The following information is always included in each audit trail record, if the information is meaningful to the particular audit action:
User name
Instance number
Process identifier
Session identifier
Terminal identifier
Name of the schema object accessed
Operation performed or attempted
Completion code of the operation
Date and time stamp
System privileges used
Auditing is site autonomous. An instance audits only the statements issued by directly connected users. A local Oracle node cannot audit actions that take place in a remote database. Because remote connections are established through the user account of a database link, statements issued through the database link's connection are audited by the remote Oracle node.
Oracle allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle. If not, then audit records are written to a file outside the database, with a format similar to other Oracle trace files.
Oracle allows certain actions that are always audited to continue, even when the operating system audit trail (or the operating system file containing audit records) is unable to record the audit record. The usual cause of this is that the operating system audit trail or the file system is full and unable to accept new records.
System administrators configuring operating system auditing should ensure that the audit trail or the file system does not fill completely. Most operating systems provide administrators with sufficient information and warning to ensure this does not occur. Note, however, that configuring auditing to use the database audit trail removes this vulnerability, because the Oracle database server prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.
The operating system audit trail is encoded, but it is decoded in data dictionary files and error messages.
Action code describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table describes these codes.
Privileges used describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table describes all of these codes.
Completion code describes the result of the attempted operation. Successful operations return a value of zero, and unsuccessful operations return the Oracle error code describing why the operation was unsuccessful.
See Also:
Oracle Database Administrator's Guide for instructions for creating and using predefined views
Oracle Database Error Messages for a list of completion codes
Some database-related actions are always recorded into the operating system audit trail regardless of whether database auditing is enabled:
At instance startup, an audit record is generated that details the operating system user starting the instance, the user's terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This information is recorded into the operating system audit trail, because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup also acts as an auditing flag, inhibiting an administrator from performing unaudited actions by restarting a database with database auditing disabled.
At instance shutdown, an audit record is generated that details the operating system user shutting down the instance, the user's terminal identifier, the date and time stamp.
During connections with administrator privileges, an audit record is generated that details the operating system user connecting to Oracle with administrator privileges. This record provides accountability regarding users connected with administrator privileges.
On operating systems that do not make an audit trail accessible to Oracle, these audit trail records are placed in an Oracle audit trail file in the same directory as background process trace files.
Any authorized database user can set his own audit options at any time, but the recording of audit information is enabled or disabled by the security administrator.
When auditing is enabled in the database, an audit record is generated during the execute phase of statement execution.
SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is run.
The generation and insertion of an audit trail record is independent of a user's transaction being committed. That is, even if a user's transaction is rolled back, the audit trail record remains committed.
Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing statement or privilege audit options in a session does not cause effects in that session. The modified statement or privilege audit options take effect only when the current session is ended and a new session is created. In contrast, changes to schema object audit options become effective for current sessions immediately.
Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Successful SQL statements from SYS are audited indiscriminately. The audit records for sessions established by the user SYS or connections with administrative privileges are sent to an operating system location. Sending them to a location separate from the usual database audit trail in the SYS schema provides for greater auditing security.
See Also:
Oracle Database Administrator's Guide for instructions on enabling and disabling auditing
Chapter 24, "SQL, PL/SQL, and Java" for information about the different phases of SQL statement processing and shared SQL
