1 Introducing Oracle Database Real Application Security
Scenario: Security Human Resources (HR) Demonstration of Employee Information
About Auditing in an Oracle Database Real Application Security Environment
1.1 What Is Oracle Database Real Application Security?
Oracle Database Real Application Security is a database authorization model that:
Supports declarative security policies
Enables end-to-end security for multitier applications
Provides an integrated solution to secure database and application resources
Advances the security architecture of Oracle Database to meet existing and emerging demands of applications developed for the Internet
Traditional security was designed for client/server systems. These systems had a significantly smaller number of users than newer applications designed for the Internet. When application developers found traditional security inadequate, they often moved it from the database layer to the application layer. To accomplish this, developers frequently built their own tables and defined their own application users. Because security was encoded in the application layer, rather than in the database, application users and application roles were typically known only to the application. In other words, database users were not application-level users, hence the user identity was not known during the access control decision in the database. Furthermore, database operations were limited to DDLs and DMLs that do not represent application-level tasks or operations, hence the operation context was also not known during the access control decision in the database. These practices exposed the database to vulnerability.
Real Application Security is designed to:
Manage application security for application users rather than database users
Enable developers to manage security for application level tasks
Enable application user identity to be known during security enforcement
Enable developers to return security to the database layer, either incrementally, or all at once
This section discusses traditional security and Real Application Security, indicating how Real Application Security improves upon traditional security.
1.1.1 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
1.1.2 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 Configuring Data Security for more information about defining data security policies and access control requirements.
1.1.3 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"
1.2 Data Security Concepts Used in Real Application Security
This section describes access control terms and concepts that you need to understand before you can begin to configure Real Application Security. Using the PL/SQL administrative interfaces, you can create and manage the entities described here: application user, application role, principal, application privilege, security class, access control list (ACL), access control entry (ACE), and data realm.
When a term such as application user or application role is used here, it applies to Real Application Security; when it is important to distinguish the database type, either no qualifier is used or the qualifier database is used.
1.2.1 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:
which application users
can perform which operations
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"
1.2.2 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 "About 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
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.
184.108.40.206 Understanding the Difference Between Database Users and Application Users
Database users are also referred to as traditional users, and have these characteristics:
They are associated with schemas and passwords.
They can create heavyweight sessions to schemas with which they are associated.
Application users are defined by an application, and have these characteristics:
They do not own database schemas.
They can create application sessions to the database through the middle tier.
They can create heavyweight database sessions by connecting to the database directly. (See "About Creating a Direct Login Application User Account".)
In a heavyweight session, the user is associated with a default schema.
220.127.116.11 Understanding the Difference Between Database Roles and Application Roles
A database role is traditionally thought of as a named set of database privileges.
Database roles have these characteristics:
They are granted privileges, just as database users can be granted privileges.
They serve as intermediaries for mapping database privileges to database users (and applications) as follows: a role is granted privileges, and the role is then granted to users (giving them the privileges).
Grant privileges to database role
Grant database role to database user
The database user now has the privileges of the database role.
In traditional database terminology, a role is considered to be the same thing as the set of privileges that are granted to it.
An application role can be regarded as the set of application-defined privileges that are associated with it using the mechanism of a declarative access control list (ACL), discussed in "Access Control List (ACL)".
Application roles have these characteristics:
They use an access control list (ACL), rather than a database grant, as the intermediary that maps application privileges to users or roles.
They can be only granted to application users or application roles.
They cannot be granted to a database role, unlike a database role can be granted to an application role.
In access control terminology, application roles are classified with application users as principals.
18.104.22.168 Granting Database Privileges to Application Users and Application Roles
You cannot grant database privileges directly to application users and application roles. Instead, you grant the database privileges to a database role, and then grant the database role to the application role in these steps.
- Grant database privileges to database role.
- Grant database role to the application role.
The statements in the following code do exactly this, effectively granting the database
SELECT privilege to the application role,
CREATE ROLE db_hrrep; GRANT SELECT ON hr.employees TO db_hrrep; GRANT db_hrrep TO HRREP;
Application users already created or subsequently created, with that application role, acquire this application privilege.
1.2.3 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.
1.2.4 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.
1.2.5 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
1.2.6 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 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".
1.2.7 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
WHEREclause 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-20, each
realmis 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_TRANSACTIONprivilege, which controls whether a user can take a particular action on the row. Assuming
SELECTprivilege 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.
Configuring Data Security describes in detail how to protect database objects.
1.3 Application Session Concepts Used in Application Security
Real Application Security introduces the concept of an application session. Within the context of application sessions, there are three types of user identities:
Application session user: The user associated with the application session.
Application session access to database objects is checked against the permissions granted to this user.
Traditional (heavyweight) session user: The user that established the database session.
This user can be an application user or a database user, as long as database authentication credentials are available.
Schema owner: The database schema is the schema associated with the traditional database session and is only used for object name resolution.
Traditional database user sessions have these characteristics:
They hold their own database resources, such as transactions and cursors.
They consume many server resources.
Application sessions have these characteristics:
They contain information that is pertinent only to the application.
They can be dedicated to each end application user.
They can persist until the application user logs out of the application or the application terminates unexpectedly.
See Configuring Application Sessions for more information about application sessions.
1.4 Flow of Design and Development
You should be familiar with the concepts introduced in this chapter to take full advantage of Real Application Security.
In general, identify all tasks an application performs that require application privileges to control data access. Then, add the appropriate application privileges to a security class so that you can reference them in an ACL and grant them to the application users and application roles, as follows:
Create a default set of meaningful application roles based on the features the application provides.
Identify the tables that require data security protection based on the application table design and security requirements, and define the data realms, including column protection.
Define data security policies based on the application requirements and the rules applied on the tables.
Ensure that ACLs used in the data security policy and functional security grant the appropriate application privileges to application roles.
1.5 Scenario: Security Human Resources (HR) Demonstration of Employee Information
This section presents an example policy that provides a high-level overview of Real Application Security. It is a simple scenario aimed at explaining the basic Real Application Security concepts. You should be familiar with the following concepts, introduced in "Data Security Concepts Used in Real Application Security":
Principals – application users and application roles
Security classes and application privileges
Access control lists and entries (ACLs and ACEs)
Data security policy
This same scenario appears throughout the book, to illustrate different components of Real Application Security. It is also described in detail in Real Application Security HR Demo and Real Application Security HR Demo Files to demonstrate how to use advanced concepts of Real Application Security to handle a more complex policy.
1.5.1 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
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
Access to all the records in the IT department, excluding the
Access to all employee records, including the
Users are allowed access to employee data in the following ways:
Each user can view their own record, including the
DAUSTINin his role as Assistant Department Manager is allowed to view all the records in the IT department, excluding the
SMAVRISin her role as human-resources representative is allowed to view and update all employee records, including the
DB_EMPis created and granted
Application roles are created as follows:
EMPLOYEErole is granted to both application users
SMAVRIS. Database role
DB_EMPis granted to
IT_ENGINEERrole is granted to only application user
DAUSTIN. Database role
DB_EMPis granted to
HR_REPRESENTATIVErole is granted to only application user
SMAVRIS. Database role
DB_EMPis granted to
VIEW_SALARYapplication privilege is created to control access to the
HR_PRIVILEGESsecurity class is created in which to scope the
ACLs are created to define the degree of access to employee records in the following ways:
SELECTdatabase privilege and
VIEW_SALARYapplication privilege to view an employee's own record, including the
IT_ENGINEERrole only the
SELECTdatabase privilege to view the employee records in the IT department, but it does not grant the
VIEW_SALARYprivilege that is required for access to the
DELETEdatabase privileges to view and update all employee's records, and granting the
VIEW_SALARYapplication privilege to view the
The HR demo secures the
HR.EMPLOYEEtable 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_ACLcontrols this realm, which grants application role
EMPLOYEEprivileges to access the realm, including the
All the records in the IT department realm. The ACL,
IT_ACLcontrols this realm, which grants application role
IT_ENGINEERprivileges to access the realm, but excluding the
All the employee records realm. The ACL,
HR_ACLcontrols this realm, which grants application role
HR_REPRESENTATIVEprivileges to access the realm, including the
A column constraint that protects the
SALARYcolumn by requiring the
VIEW_SALARYprivilege to view its sensitive data.
1.5.2 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
DAUSTIN connect to the database directly as the principals.
The application user account that is created for application users
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).
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
EMPLOYEESof the sample database schema
HR(delivered with Oracle Database).
Application role: Application roles,
HR_REPRESENTATIVEare created for performing tasks. The application roles are defined with the
Application user: Application users,
DAUSTIN, are created and defined.
SMAVRISis granted the application roles
DAUSTINis granted the application roles
Database access: Application users
DAUSTINare given a database password for direct database login. In order to grant
DELETEprivileges on table
EMPLOYEESto application roles
HR_REPRESENTATIVEa 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,
HR_PRIVILEGES, is created which defines a single custom application privilege,
VIEW_SALARY. Through inheritance, the predefined application privilege
SELECTis 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
VIEW_SALARYprivileges are granted to application role
EMPLOYEEby the access control list (ACL),
EMP_ACLthat is created by
SELECTprivilege is granted to application role
IT_ENGINEERby the ACL,
IT_ACLthat is created by
DELETEprivileges are granted to application role
HR_REPRESENTATIVEby the ACL,
HR_ACLthat is created by
XS_ACL.CREATE_ACLprocedure to view and update all employee's records, and granting the
VIEW_SALARYapplication privilege to view the
Data Security Policy: The data security policy is defined and created with the
XS_DATA_SECURITY.CREATE_POLICYprocedure. This data security policy defines three data realms (an employee's own record realm that can view the realm including the
SALARYcolumn, all the records in the IT department realm that can view the IT department excluding the
SALARYcolumn, and all the employee records realm that can view the realm including the
SALARYcolumn) and a column constraint. The data security policy associates the ACLs
HR_ACLwith 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".
1.6 About Auditing in an Oracle Database Real Application Security Environment
Another aspect of security is auditing in an Oracle Database Real Application Security environment. Real Application Security administration and run-time actions can be audited by configuring and enabling unified audit policies. For information about unified auditing in an Oracle Database Real Application Security environment, see Oracle Database Security Guide.
The following static data dictionary views are defined for auditing policies specifically for Oracle Database Real Application Security:
DBA_XS_AUDIT_POLICY_OPTIONS- describes the auditing options that were defined for Real Application Security unified audit policies. See Oracle Database Reference for more information.
DBA_XS_AUDIT_TRAIL- provides detailed information about Real Application Security that were audited. See Oracle Database Reference for more information.
DBA_XS_ENB_AUDIT_POLICIES- lists users for whom Real Application Security unified audit polices are enabled. See Oracle Database Reference for more information.
1.7 Support for Pluggable Databases
The multitenant architecture enables an Oracle database to contain a portable collection of schemas, schema objects, and nonschema objects that appear to an Oracle Real Application Security application user as a separate database. A multitenant container database (CDB) is an Oracle database that includes one or more pluggable databases (PDBs).
Oracle Real Application Security can be used with Oracle Multitenant to provide increased security for consolidation.
Because Oracle Real Application Security entities are scoped within a PDB, each PDB has its own Real Application Security metadata, such as users, roles, privileges, ACLs, data security policies, and so forth. As a result, Real Application Security can prevent privileged user access inside a PDB between and among applications and between the PDB and the common privileged user at the container database.
SYS is the schema owner for Oracle Real Application Security entities, Real Application Security entities created in
root can only be accessed by the
SYS user in
root. The same is true for other operating systems in that the SYS user is the schema owner for Oracle Real Application Security entities and only the SYS user has access to these entities. Similarly, Real Application Security entities created within a local PDB, can only be accessed in the local PDB.
Since Oracle Real Application Security direct login users have a password associated with them, these users can be provisioned within a PDB, using a single
sqlnet.ora parameter to support them.
Oracle Real Application Security administration involves PDB specific administrative privileges and a schema to qualify the name for Real Application Security entities. The schema name can be common; however, entities created under the naming scope of a common schema are not common.
Oracle Real Application Security auditing is PDB specific.
An Oracle Real Application Security application user can connect to a PDB using a service whose pluggable database property has been set to the relevant PDB.
Introduction to the Multitenant Architecture and Overview of the Multitenant Architecture in Oracle Database Concepts.
Oracle Database Administrator’s Guide for more information about PDBs and for more details about configuring the services to connect to various PDBs