Skip Headers

Oracle9i Security Overview
Release 2 (9.2)

Part Number A96582-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Protecting Data Within the Database

Data is vulnerable at many points in any computer system, and many security techniques and types of functionality can be employed to protect it. This chapter provides a systematic introduction to security features that can protect the memory, files, and processes residing on the server. It contains the following sections:

Introduction to Database Security Concepts

Confidentiality, integrity, and availability are the hallmarks of database security. Who should have the right to access data? What portion of all the data should a particular user be able to access? What operations should an authorized user be able to perform on the data? Can authorized users access valid data when necessary?

Authorization is permission given to a user, program, or process to access an object or set of objects.The type of data access granted to a user can be read-only, or read/write. Privileges specify the type of Data Manipulation Language (DML) operations which the user can perform upon data.

This chapter introduces these and other fundamental concepts of database security.

System and Object 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 (administrators). Privileges can be granted to enable a particular user to connect to the database (create a session); create a table in his own schema; select rows from someone else's table; or execute someone else's stored procedure.

The following sections describe the two distinct categories of privileges within a database:

System Privileges

System privileges allow users to perform a particular systemwide action or a particular action on a particular type of schema object. For example, the privileges to create a tablespace or to delete the rows of any table in the database are system privileges. Many system privileges are available only to administrators and application developers because the privileges are very powerful.

Schema Object Privileges

Access to data is most commonly controlled on the level of access to the database itself, or to specific tables. Schema object privileges allow users to perform a particular action on a specific schema object. For example, the privilege to delete rows of a specific table is an object privilege.

Schema object privileges for tables allow table security at the level of data manipulation language (DML) and data dictionary language (DDL) operations. For example, an administrator can grant individual users the privileges to use the DML operations DELETE, INSERT, SELECT, and UPDATE on a table or view, or to use the ALTER, INDEX, and REFERENCES privileges to perform DDL operations on a table.

Privileges can be specified at the column level. It is possible to restrict a user's INSERT and UPDATE privileges for a table to individual columns of the table. Likewise, privileges can be specified at the row level. It is possible to restrict a user's SELECT, INSERT, UPDATE, and DELETE privileges for a table to specific rows of the table.

As a general rule, object privileges can only be granted by the object owner. However, an owner can also specify that a particular user has the right to grant a privilege to others. The full range of privileges for any action on any object in the schema is typically granted by default to the administrator. Even this complete set can be delegated by the administrator to other users, that is, selectively granted to or revoked from any user or group. In particular, an administrator can grant an application developer or DBA the privilege to GRANT ANY OBJECT PRIVILEGE. Having this privilege can make it easier for the developer to do the security configuration tasks he faces, and aid the DBA in resolving access control problems as they arise.

Managing System and Object Privileges

The user's ability to supply a valid username and password can be used as a first level of authorization for a user to access a database or specific database tables. The following sections discuss several additional techniques that can be used to further manage system and object privileges:

Using Roles to Manage Privileges

A role mechanism can be used to provide authorization. A single person or a group of people can be granted a role or a group of roles. One role can be granted in turn to other roles. By defining different types of roles, administrators can manage access privileges much more easily.

This section contains these topics:

Database Roles

Privileges enable users to access and modify data in the database. Database roles are named groups of privileges relating to a specific job function that are granted to users or other roles. Because roles allow for easier and better management of privileges, privileges are normally granted to roles and not to specific users. 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. For example, you can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password; that way, users cannot enable the role if they do not know the password.

The following properties of roles allow for easier privilege management:

By using various levels of roles and privileges, you can achieve increased granularity of access controls and adhere to the principle of least privilege, as illustrated in Figure 2-1. Here, each individual has only the privileges necessary to perform his or her job.

Figure 2-1 Common Uses for Roles

Text description of scn81082.gif follows
Text description of the illustration scn81082.gif

Global Roles

Global roles are one component of enterprise user security. A global role only applies to one database, but it can be granted to an enterprise role defined in the enterprise directory. Although a global role is managed in a directory, its privileges are contained within a single database--the database in which it is defined.

You define the global role locally in the database by granting privileges and roles to it, but you cannot actually grant the global role to any user or to any other role in the database. When an enterprise user attempts to connect to the database, the directory is queried to obtain any global roles associated with the user.

Enterprise Roles

An enterprise role is a directory structure which can contain global roles on multiple databases, and which can be granted to enterprise users. By storing and managing enterprise roles in an LDAP-based directory service, you can centralize management of user-related information, including authorizations.

For example, the enterprise role clerk could contain the global role hrclerk with its unique privileges on the Human Resources database, and the analyst role with its unique privileges on the Payroll database.

An enterprise role can be granted to or revoked from one or more enterprise users. For example, you could grant the enterprise role clerk to a number of enterprise users who hold the same job. This information is protected in the directory, and only you, as the administrator, can manage users and grant and revoke their roles.

A user can be granted local roles and privileges in a database, in addition to enterprise roles.

See Also:

Chapter 6, "Administering Enterprise User Security"

Secure Application Roles

A long-standing security problem has been that of limiting how users access data, to prevent users from bypassing application logic to access data directly. For example, in web-based applications, even if users are known to the database, it may not be desirable to allow them to have direct access to data. To date, this has been a very difficult security problem to solve, because there has been no secure way to validate which application is used to access data. For example, a malicious user could write a program that appears to be a valid human resources application.

One way to address this challenge is through a secure application role: a role implemented by a package. The package can perform any desired validation to ensure that the appropriate conditions are met before the user can exercise privileges granted to the role in the database. The database ensures that it is only the trusted package implementing the role that determines the correct access conditions.

A secure application role is used by an application, can only be enabled by the application, and does not need a password.

See Also:

"Secure Application Role"

Using Stored Procedures to Manage Privileges

Through stored procedures you can restrict the database operations that users can perform. You can allow them to access data only through procedures and functions that execute with the definer's privileges. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself. When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users who have only the privilege to execute the procedure (but not the privileges to query, update, or delete from the underlying tables) can invoke the procedure, but they cannot manipulate table data in any other way.

See Also:

"Views, Stored Program Units, Triggers"

Using Network Facilities to Manage Privileges

Database roles can potentially be mapped to external services (such as DCE groups and RADIUS authorizations) so that you can centrally manage and administer privileges for all network resources--of which databases are only one piece.

See Also:

Chapter 3, "Protecting Data in a Network Environment"

"Oracle Net Services"

Using Views to Manage Privileges

Rather than granting users privileges on a particular table, you can give them access to a view of the table. Views add two more levels of security:

To use a view requires appropriate privileges only for the view itself. The user need not be given privileges on base objects underlying the view.

Figure 2-2 shows an example of a view called staff derived from the base table emp. Notice that the view shows only five of the columns in the base table.

Figure 2-2 An Example of a View

Text description of scn81045.gif follows
Text description of the illustration scn81045.gif

See Also:

"Views, Stored Program Units, Triggers"

Row Level Security

A much more granular form of data access is row level access. For any table with data, access to particular rows can be based on such considerations as the department to which employees belong, their job responsibility or title, or other significant factors. In the past, complex and dynamic views have been used to implement row level security. There are, however, two more effective approaches to this problem: Virtual Private Database (VPD), in which you create your own implementation of row level security; and label-based access control, in which you customize a ready-made VPD policy to accomplish this. This section describes these alternative approaches.

Complex and Dynamic Views

Complex views and dynamic views are among the historical approaches to row level security. Complex view definitions result when application designers build their own user security tables and join the application tables with the new security table based on the name of the application user. This approach usually requires many complex view definitions which must be maintained as security requirements change. Another approach is dynamic view creation. This approach uses dynamic DDL execution utilities to define new view definitions based on the identity of the application user. Using dynamic views, however, is costly and time consuming.

Application Query Rewrite: Virtual Private Database

Virtual Private Database is the ability to perform query modification based on a security policy you have defined in a package, and associated with a table, view, or synonym. Virtual private database provides fine-grained access control which is data-driven, context-dependent, and row-based. It is a key enabling technology in building three-tier systems which expose mission-critical resources to customers and partners.

See Also:

"Virtual Private Database in Oracle9i"

Label-Based Access Control

Label-based access control allows organizations to assign sensitivity labels to data rows, control access to data based on those labels, and ensure that data is marked with the appropriate sensitivity label. The most familiar example of this is perhaps the security classification system used by the United States and other governments. In this model, hierarchical classification labels such as CONFIDENTIAL, SECRET, or TOP SECRET are assigned to data based on the sensitivity level of the information. In addition, formal security compartments are defined, such as NATO or CRYPTO, and assigned to the data. Access to data labeled at a certain level (such as SECRET) is restricted to those users who have been granted that level of access or higher. Access to data in a specific compartment (such as CONFIDENTIAL NATO) is restricted to those who have access to the appropriate level, as well as explicit permission to access the compartment in question.

While e-businesses do not typically have label data classification systems, they almost always have data labeling requirements. For example, an e-business may differentiate between Company Confidential information and Public information. Further, there may be some Company Confidential information that can be shared with partners, under a Confidential Disclosure Agreement or other legal document, while other information is only accessible by certain groups within the company (such as Finance or Sales divisions). The ability to natively manage labeled data is a tremendous advantage for e-businesses in providing the right information to the right people at the right level of secure data access.

See Also:

"Oracle Label Security"

Encrypting Data on the Server

Encryption is a technique of encoding data, so that only authorized users can understand it. Encryption alone, however, is not sufficient to secure your data. Protecting data in the database includes access control, data integrity, encryption, and auditing. This section includes:

Selective Encryption of Stored Data

For certain applications, you may decide to encrypt data as an additional measure of security. Most issues of data security can be handled by appropriate authentication and access control, ensuring that only properly identified and authorized users can access data. Data in the database, however, cannot normally be secured against the database administrator's access, since a DBA has all privileges. Likewise, organizations may have concerns about securing sensitive data stored offline, such as backup files stored with a third party. They may want to guard against intruders accessing the data where it is physically stored on the database.

Although encryption is not a substitute for effective access control, you can obtain an additional measure of security by selectively encrypting sensitive data before it is stored in the database. Information which may be especially sensitive and warrant encryption could include credit card numbers, national identity numbers in countries with strict privacy laws, or trade secrets, such as industrial formulas. Applications for which a user is authenticated to the application, rather than to the database, may also use encryption to protect the application user password or cookie.

Industry Standard Encryption Algorithms

A number of industry-standard encryption algorithms are useful for the encryption and decryption of data on the server. Two of the most popular are:

Data Encryption Standard (DES)

Provides standards-based encryption for data privacy

Triple DES (3DES)

Encrypts message data with three passes of the DES algorithm

Note that the RC4 encryption algorithm is a stream cipher, and therefore not suitable for encryption in the database. It is useful for network encryption.

See Also:

"Encryption Algorithms"

"Java Security Implementation in the Database"

"Oracle Net Services"

Database Integrity Mechanisms

Database integrity ensures that data in the database is correct and consistent. Database integrity mechanisms can be divided into those mechanisms that support system integrity, and those that enforce relational database integrity properties (like entity integrity, referential integrity, transaction integrity, and business rules).

Traditional system integrity involves ensuring that the data inserted into the system is the same as the contents of that data when it is subsequently retrieved. Further, data must not be altered or deleted by a user who is not authorized to do so.

A database must ensure that data adheres to certain business rules, as determined by the database administrator or application developer. For example, assume that a business rule says that no employee in the emp table can receive a raise greater than 20% of the value in the salary column. If an insert or update statement attempts to violate this integrity rule, then the statement must fail. Integrity constraints and database triggers can be used to manage a database's data integrity rules.

Referential integrity provides that a rule defined on a column or set of columns in one table, match the values in a related table (the referenced value). Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. Referential integrity rules can be used to provide for these relationships.

See Also:


System Availability Factors

Data security also involves the accessibility of the data to authorized users, as needed. Availability is often thought of as continuity of service, ensuring that a database is available 24 hours a day, 7 days a week. However, there are security aspects to availability. For example, if a user is able to manipulate system resources in order to deny their availability to other users, he is breaching security. This is referred to as denial of service.

System availability can be protected by factors such as these:

Table 2-1 System Availability Factors
Factor Description

Storage quotas

The administrator should be able to direct and limit the use of disk space allocated to the database for each user, including default and temporary tablespaces and tablespace quotas.

Resource limits

Each user should be assigned a profile that limits the system resources available to the user, including the number of concurrent sessions the user can establish, the CPU processing time available to the user's session, the amount of logical I/O available to the user, and so on.

Hot backups

Data should be copied as a safeguard against unexpected data loss and application errors. If you lose the original data, then you can reconstruct it by using a backup.

Resistance to attack

Software should be written according to secure coding standards.

Secure configuration

The system should be set up in such a way as to avoid exposing any vulnerabilities which could be exploited by a malicious intruder.

Parallel systems

Clusters can be used to ensure highly available access to queue data. Queues can be implemented by using database tables. In case of an instance failure, messages managed by the failed instance can be processed immediately by one of the surviving instances.

See Also:

"High Availability"

Secure Configuration Practices

Finally, any techniques you employ to assure data security can be rendered useless unless the database administrator follows good security practices. For example, the DBA should always:

Go to previous page Go to next page
Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index