1 Introducing Oracle Database Real Application Security

Disadvantages of Traditional Security for Managing Application Users

Using the traditional security model, it was often difficult to manage three-tier applications, especially when performing these security tasks:

• Extending security policies independent of application code

• Enforcing security policies at the database level, where the application user is unknown

• Enforcing least privilege principle as full access is granted to highly privileged two-tier components

Advantages of Real Application Security

Real Application Security enables these security tasks, which improve database security and performance:

• Three-tier and two-tier applications can declaratively define, provide, and enforce access control requirements at the database layer.

• The database can provide a uniform security model across all tiers and support multiple application user stores, including the associated roles, authentication credentials, database attributes, and application-defined attributes. This model enables application users to have a single unique global identity across an Oracle enterprise.

• An Oracle database can natively support the application security context. The database supports integrated policy specification and enforcement for both the application and the database, so the application does not need to do this through application code. Because the database stores the application security context information, this also reduces network traffic.

• Developers can use Real Application Security to control application user access to data in an Oracle database throughout all components of an Oracle enterprise in a common manner.

  See Chapter 5, "Configuring Data Security" for more information about defining data security policies and access control requirements.

Architecture of Real Application Security

Real Application Security is managed through a collection of PL/SQL and Java APIs. This architecture that enables you to configure its components—application users, application roles, sessions, and other security-related components. With Real Application Security, you configure application counterparts to the traditional user, role, and session, through the use of entities, which are stored in tables.

Figure 1-1 shows the various components used in Oracle Database Real Application Security. This includes application users, application roles, access control lists, security classes, and application sessions. These components are discussed in the following sections. Figure 1-1 also shows Web applications establishing application sessions to the database.

Figure 1-1 Oracle Database Real Application Security Components

Description of "Figure 1-1 Oracle Database Real Application Security Components"

About Data Security with Oracle Database Real Application Security

Effective security requires defining which application users, applications, or functions can have access to which data, to perform which kinds of operations. Thus, effective security has these three dimensions:

1. which application users

2. can perform which operations

3. on which data

You define (1) principals, (2) application privileges, and (3) objects in relation to these three dimensions, respectively. Principals are users and roles. A role can represent attributes of an application user, system state, or a piece of code.

Principals and application privileges are related in a declarative way by defining ACLs. These ACLs are then related to the data by defining Data Security policy that protects rows and columns of table data. For example, you can protect table data by using PL/SQL procedures to set controlling ACLs.

Figure 1-2 illustrates an example where the user, ProjectManager has the ModifyProject privilege on a data realm comprised of Team A's projects.

Figure 1-2 Three Dimensions of Data Security

Description of "Figure 1-2 Three Dimensions of Data Security"

Principals: Users and Roles

When discussing fine-grained database access control, a principal is an application user or an application role or a database user or a database role. An application user can be a person or an autonomous application process that accesses information in the database. An application role is a logical grouping of application privileges required to accomplish a real life task. An application role can contain other application roles, but this recursion cannot be circular. You use application roles to associate application users, both database users and application users with privileges.

Oracle Database supports the following as principals:

• Database users and database roles

  A database user is also sometimes referred to as a database schema or a user account. When a person or application logs onto the database, it uses a database user (schema) and password.

  A database role corresponds to a set of database privileges that can be granted to database users, applications, or other database roles — see "Understanding the Difference Between Database Roles and Application Roles".

• Application users and Application roles

  The term application, as used by Real Application Security, refers to the creation of an application user, application role, or session that contains only information pertinent to the application that the application user is logging onto. Application users and application roles are defined by an application, and they do not need to be tied to any database schema.

  Application users can also create heavyweight database sessions by connecting to the database directly. These are called direct login application users. See "Creating a Direct Login Application User Account". When an application user creates a heavyweight database session, the user's default schema is set to a preconfigured value meant solely for name resolution purposes, such as HR.

  An application role can only be granted to an application user or to another application role. You cannot directly grant database privileges to application users and application roles. See "Granting Database Privileges to Application Users and Application Roles" for further details.

  See Also:

  • "Configuring Application Users"

  • "Configuring Application Roles"

CREATE ROLE db_hrrep;
GRANT SELECT ON hr.employees TO db_hrrep;
GRANT db_hrrep TO HRREP;

Application Privileges

An application privilege is a particular right or permission that can be granted or denied to a principal. Application developers define application privileges in a security class.

The set of application privileges granted to a principal controls whether or not this principal can perform a given operation on the data that it protects. For example, if the principal (database user) HR wants to perform the SELECT operation on a given resource, then SELECT privileges must be granted to principal HR before the SELECT operation.

Application privileges can also be aggregated. An aggregate privilege is an application privilege that implies other application privileges. These implied privileges can be any application privileges defined by the current security class or an inherited privilege. When an aggregate privilege is granted or denied, its implied application privileges are implicitly granted or denied.

Aggregate privileges simplify usability when the number of application privileges grows. For example, instead of granting each application privilege separately, you can group related application privileges into an aggregate privilege. Then, you can use a single grant to enable a principal to access all the application privileges contained in the aggregate privilege.

Security Classes in Oracle Database Real Application Security

A security class is a scope for a set of application privileges.

A security class includes application privileges that it inherits from other security classes, and it can include application privileges that it defines.

A security class is typically associated with an access control list (ACL), and the ACL can grant application privileges in the security class to specific principals. See "Access Control List (ACL)".

Example 4-4 shows how to create a security class policy.

Access Control Entry (ACE)

An access control entry (ACE) either grants or denies application privileges to a particular principal (application user or application role).

An ACE is an element in an array named ace_list. The whole array is called by and becomes part of the access control list (ACL).

The ACE does not, itself, specify which data to protect; that is done by associating the ACL with target data, such as a set of rows in an order entry table. You can make this association by creating a data realm to restrict the user to modifying only those rows, or by using the PL/SQL procedure XS_DATA_SECURITY.SET_ACLS.

Access Control List (ACL)

An access control list (ACL) is a list of access control entries (ACEs), which permit or deny application privileges to one or more principals.

If the ACL you create relies on a set of custom application privileges that you define in your own security class, then you must explicitly associate that security class with the ACL. See Example 4-15, "Creating an Access Control List" for an example.

If the only application privileges used in the ACL are defined in the DML security class, then no security class association is needed as that is the default. See a description in "DML Security Class".

Data Security Policy

To protect data within a database table, you must create a data security policy. Database records, both at row and column level, can be protected using the fine-grained access control described in this section.

The data security policy performs the following functions:

• Specifies the data that you want to protect. The data can be indicated by a WHERE clause in a data realm of one or more rows that you design. It can also be defined using named notation by using an association operator to associate the parameter to the left of the arrow (=>) with the actual parameter to the right of the arrow. For example, in Example 5-19, each realm is defined using association operators.

  The data security policy can contain one or more data realms.

• Associates each data realm with one or more access control lists (ACLs) that specify the application privileges required to access rows and columns of the data realm to form what is called a data realm constraint. A given ACL protects a given data realm and controls access to particular application users or application roles (called principals). (See "Access Control List (ACL)" for more information about ACLs.)

• Optionally applies additional application privileges to protect a particular column to form what is known as column constraints. This is useful in cases where you need to add an extra layer of security for sensitive data.

• Associates additional custom application privileges. For example, an administrator could create an APPROVE_TRANSACTION privilege, which controls whether a user can take a particular action on the row. Assuming SELECT privilege is granted to all users, all users could see the row, but only some users can perform the transaction approval action.

In summary, the application user who logs in will only be allowed to perform operations including DML on records within the data realm, including individual rows of data, based on the application privileges in its associated ACLs. Thus, the data security policy is composed of data realm constraints and column constraints that protect the data realm by only allowing access to application users who have application privileges in the associated ACLs.

For example, suppose you have a sales table that lists all sales representatives, their regions, the products they are responsible for, product categories, and product prices. When individual sales representatives log on, each representative would see selected data for all other sales representatives, such as sales representatives for particular product categories based on data realm constraints. If you wanted to restrict the display of product prices to sales representatives by region, you could apply additional application privileges to the column listing product prices, in this case using column constraints.

Chapter 5, "Configuring Data Security" describes in detail how to protect database objects.

Basic Security HR Demo Scenario: Description and Security Requirements

Susan Mavris (SMAVRIS) is an employee in the Human Resources department. Her job title is Human Resources Representative. In this capacity, she is in charge of managing the human resources information for all employees, including department 60 (IT). She can view and update all the employee records, including the SALARY column.

David Austin (DAUSTIN) is an employee in the IT department. His job title is Assistant Department Manager. In this capacity, he can view employee records in the IT department, but he cannot view the SALARY column, except for his own salary record.

Secure authorization requires defining which application users and application roles can have access to which data, to perform which kinds of operations. These three security dimensions must be defined: protected data, principals, and application privileges. (see "About Data Security with Oracle Database Real Application Security").

In this basic scenario:

• The data to be protected is employee information and it is protected in three ways:

  • Access to an employee's own record, including the SALARY column.

  • Access to all the records in the IT department, excluding the SALARY column.

  • Access to all employee records, including the SALARY column.

• Users are allowed access to employee data in the following ways:

  • Each user can view their own record, including the SALARY column.

  • Application user DAUSTIN in his role as Assistant Department Manager is allowed to view all the records in the IT department, excluding the SALARY column.

  • Application user SMAVRIS in her role as human-resources representative is allowed to view and update all employee records, including the SALARY column.

• Database role DB_EMP is created and granted SELECT, INSERT, UPDATE, and DELETE privileges on HR.EMPLOYEES.

• Application roles are created as follows:

  • EMP_ROLE is granted to both application users DAUSTIN and SMAVRIS. Database role DB_EMP is granted to EMP_ROLE.

  • IT_ROLE is granted to only application user DAUSTIN. Database role DB_EMP is granted to IT_ROLE.

  • HR_ROLE is granted to only application user SMAVRIS. Database role DB_EMP is granted to HR_ROLE.

• The VIEW_SALARY application privilege is created to control access to the SALARY column. The HRPRIVS security class is created in which to scope the VIEW_SALARY application privilege.

• ACLs are created to define the degree of access to employee records in the following ways:

  • EMP_ACL grants the EMP_ROLE the SELECT database privilege and VIEW_SALARY application privilege to view an employee's own record, including the SALARY column.

  • IT_ACL grants the IT_ROLE only the SELECT database privilege to view the employee records in the IT department, but it does not grant the VIEW_SALARY privilege that is required for access to the SALARY column.

  • HR_ACL grants the HR_ROLE ALL privileges, which means all the privileges in the ACL's security class. In this case, ALL privileges includes SELECT, INSERT, UPDATE, and DELETE database privileges to view and update all employee's records, and granting the VIEW_SALARY application privilege to view the SALARY column.

• The HR demo secures the HR.EMPLOYEE table by creating and applying the data security policy, EMPLOYEES_DS, that has the following three data realms and column constraint:

  • An employee's own record realm. The ACL, EMP_ACL controls this realm, which grants application role EMP_ROLE privileges to access the realm, including the SALARY column.

  • All the records in the IT department realm. The ACL, IT_ACL controls this realm, which grants application role IT_ROLE privileges to access the realm, but excluding the SALARY column.

  • All the employee records realm. The ACL, HR_ACL controls this realm, which grants application role HR_ROLE privileges to access the realm, including the SALARY column.

  • A column constraint that protects the SALARY column by requiring the VIEW_SALARY privilege to view its sensitive data.

Basic HR Scenario: Implementation Overview

To implement the basic human-resources security scenario, in addition to identifying the protected data, the principals, and the application privileges, you must define the following:

• A database user as the Real Application Security Administrator and then connect as the Real Application Security Administrator to create the components.

• How the principals connect with the database to access the data.

• The access control lists (ACLs) that grant the application privilege and any database privileges to the principals.

• A data security policy that associates the ACLs with the particular data (rows) that the principals need to access.

In this basic scenario, application users SMAVRIS and DAUSTIN connect to the database directly as the principals.

The application user account that is created for application users SMAVRIS and DAUSTIN are principals in this scenario. Each application user account is granted application roles that, ultimately, has the SELECT privilege on the database table that contains the employee information. The application role is a principal in this scenario.

A database role, DB_EMP serves as intermediary between the application role and the database privilege because database privileges can be granted only to database users and roles. That is, the necessary database privileges are granted to a database role, and that role is granted to each application role (the principal).

The database SELECT privilege applies to the entire table. The principal must also be granted an Real Application Security application privilege such as the DML SELECT privilege, which can be restricted to certain rows of the database table. This restriction is implemented using an access control list (ACL) and a data security policy.

The HR scenario requires the following components for the security model:

• Protected data: Employee information is stored in the table EMPLOYEES of the sample database schema HR (delivered with Oracle Database).

• Application role: Application roles, EMP_ROLE, IT_ROLE, and HR_ROLE are created for performing tasks. The application roles are defined with the XS_PRINCIPAL.CREATE_ROLE procedure.

• Application user: Application users, SMAVRIS and DAUSTIN, are created and defined. SMAVRIS is granted the application roles EMP_ROLE and HR_ROLE. DAUSTIN is granted the application roles EMP_ROLE and IT_ROLE.

• Database access: Application users SMAVRIS and DAUSTIN are given a database password for direct database login. In order to grant SELECT, INSERT, UPDATE, and DELETE privileges on table EMPLOYEES to application roles EMP_ROLE, IT_ROLE, HR_ROLE a database role, DB_EMP, is created and granted these database privileges. The application roles are then granted this database role.

• Application Privilege: A single security class, HRPRIVS, is created which defines a single custom application privilege, VIEW_SALARY. Through inheritance, the predefined application privilege SELECT is also available in this security class. These application privileges will be used in connection with a data security policy to allow read access to employee information. The security class is created by the XS_SECURITY_CLASS.CREATE_SECURITY_CLASS procedure.

• ACL: The SELECT and VIEW_SALARY privileges are granted to application role EMP_ROLE by the access control list (ACL), EMP_ACL that is created by XS_ACL.CREATE_ACL procedure. The SELECT privilege is granted to application role IT_ROLE by the ACL, IT_ACL that is created by XS_ACL.CREATE_ACL procedure. The ALL privilege is granted to application role HR_ROLE by the ACL, HR_ACL that is created by XS_ACL.CREATE_ACL procedure. The ALL privilege means all the privileges in the ACL's security class. In this case, ALL privileges includes SELECT, INSERT, UPDATE, and DELETE database privileges to view and update all employee's records, and granting the VIEW_SALARY application privilege to view the SALARY column.

• Data Security Policy: The data security policy is defined and created with the XS_DATA_SECURITY.CREATE_POLICY procedure. This data security policy defines three data realms (an employee's own record realm that can view the realm including the SALARY column, all the records in the IT department realm that can view the IT department excluding the SALARY column, and all the employee records realm that can view the realm including the SALARY column) and a column constraint. The data security policy associates the ACLs EMP_ACL, IT_ACL, and HR_ACL with its respective data realm.

Introducing this example in this chapter provides an overview of the requirements for implementing a policy using Real Application Security. Actual implementation of these tasks requires a systematic understanding of all the Real Application Security concepts introduced in this chapter, and further discussed in subsequent chapters. The complete example, including implementation details, appears in "Real Application Security: Putting It All Together".