|Oracle® Database Concepts
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This chapter provides an overview of Oracle Database database security.
This chapter contains the following topics:
See Also:Oracle Database Security Guide for more detailed information on everything in this chapter
Database security entails allowing or disallowing user actions on the database and the objects within it. Oracle Database uses schemas and security domains to control access to data and to restrict the use of various database resources.
Oracle Database 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.
This section includes the following topics:
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
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.
See Also:"Introduction to Roles" information about role properties
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:
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 Database 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 Database 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 Database made by a SQL statement
Amount of logical I/O available for the user's session and a single call to Oracle Database 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
Account locking after multiple unsuccessful login attempts
Password expiration and grace period
Password reuse and complexity restrictions
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 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 I/O and not for every logical access to the data. This leads to improved performance.
See Also:Oracle Database Advanced Security Administrator's Guide for details about using tablespace encryption
Authentication 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 Database allows a single database instance to use any or all methods. Oracle Database requires special authentication procedures for database administrators, because they perform special database operations. Oracle Database also encrypts passwords during transmission to ensure the security of network authentication.
See Also:Oracle Database Security Guide for more information about authentication methods
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:
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.
When an operating system is used to authenticate database users, managing distributed database environments and database links requires special care.
Oracle Database 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), Oracle Database 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 Database 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 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.
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:
To protect password confidentiality, Oracle Database never sends cleartext passwords over the network. If transmission of passwords over the network is required, then Oracle Database encrypts the password using the AES (Advanced Encryption Standard) algorithm approved by the NIST (National Institute of Standards and Technology).
Oracle Database 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 Oracle Database default password complexity verification routine checks that each password meet the following requirements:
Be at least eight characters and no more than 30 characters in length
Not equal to the user name, the user name spelled backward, nor the user name appended with numbers
Is not the same as the server name, nor the server name with the numbers 1-100 appended
The password is not to simple, such as
user1234, alphabetically sequential letters with numbers, or
Include at least one alphabet character and one numeric character
Differ from the previous password by at least three characters
See Also:Oracle Database Security Guide for more information about how Oracle Database verifies password complexity
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.
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 Database provides a more secure authentication scheme for database administrator user names.
You can choose between strong authentication, operating system authentication, or password files to authenticate database administrators. 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.
Strong authentication lets you centrally control
SYSOPER access to multiple databases. Consider this type of authentication for database administration when password file security is a concern, if the site has very strict security requirements, or you want to separate the identity management from your database.
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.)
SYSOPER lets database administrators perform
ALTER DATABASE OPEN/MOUNT,
ALTER DATABASE BACKUP,
ARCHIVE LOG, and
RECOVER, and includes the
RESTRICTED SESSION privilege.
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
Oracle Database Security Guide for more information about authenticating database administrators
Your Oracle Database 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.
This section includes the following topics:
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:
Oracle Database 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.
This section includes the following topics:
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 Database. You can set several resource limits at the session level.
If a user exceeds a session-level resource limit, Oracle Database 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
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 Database lets you set several resource limits at the call level.
If a user exceeds a call-level resource limit, Oracle Database 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 Database, 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 Database 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 Database 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 Database also provides for the limitation of several other resources at the session level:
You can limit the idle time for a session. If the time between Oracle Database 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 Database 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 Database 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 limits
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 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.
This section includes the following topics:
You must 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.
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
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
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 management
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.
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 privileges
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.
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 must 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.
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.
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 Advanced Application Developer's Guide for instructions for enabling roles from an application
This section includes the following topics:
To manage the privileges for a database application
To manage the privileges for a user group
Figure 20-1 and the sections that follow describe the two uses of roles.
This section includes the following topics:
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
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 Database 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 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.
Oracle Database Security Guide for more information about default roles
Oracle Database 2 Day + Security Guide for more information about secure application roles
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 must 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.
This section includes the following topics:
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).
Use different policies for
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.
Oracle Database PL/SQL Packages and Types Reference for information about package implementation
Oracle Database Security Guide for more information about fine-grained access control
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 sharable.
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.
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.
Base predicates on context values
Use context values within predicates, as bind variables
Set user attributes
Access user attributes
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
DELETE operations. For example, a central tax authority must 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.
See Also :Oracle Database Security Guide for more information on fine-grained auditing
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.
This section includes the following topics:
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.
See Also:"Overview of Authentication Methods"
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
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.
Oracle Database Administrator's Guide for more about views
Oracle Database Advanced Application Developer's Guide for more about fine-grained access control and application context
This section describes aspects of user security policy, and contains the following topics:
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.
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
SYSTEM, immediately change the passwords for the
SYSTEM administrative user names. Connecting as
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.
CONNECT SYS/AS SYSOPER|SYSDBA Enter password: enter the password
SYSOPER gives a user the ability to perform basic operational tasks (such as
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,
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.
See Also:Oracle Database Security Guide for more information for application 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 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.
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.
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 Database 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, the Oracle Database password management policy is controlled by DBAs and security officers through user profiles.
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 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.
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.
|Type of Auditing||Meaning/Description|
Audits SQL statements by type of statement, not by the specific schema objects on which they operate. Typically broad, statement auditing audits the use of several types of related actions for each option. For example,
Audits the use of powerful system privileges enabling corresponding actions, such as
Schema object auditing
Audits specific statements on a particular schema object, such as
Audits data access and actions based on content. Using
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.
This section includes the following topics:
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:
Auditing is site autonomous. An instance audits only the statements issued by directly connected users. A local Oracle Database 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 Database node.
Oracle Database 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 Database. If not, then audit records are written to a file outside the database, with a format similar to other Oracle Database trace files.
Oracle Database 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 Oracle Database 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 Database error code describing why the operation was unsuccessful.
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 Database 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 Database, these audit trail records are placed in an Oracle Database audit trail file in the same directory as background process trace files.
When auditing is enabled in the database, an audit record is generated during the execute phase of statement execution.
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
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.