|Oracle® Database Real Application Security Administrator's and Developer's Guide
12c Release 1 (12.1)
|PDF · Mobi · ePub|
This chapter contains:
Traditional database users own database schemas and can create traditional heavyweight database sessions to those schemas. Application users do not own database schemas, but can create application sessions in the database.
You can either attach or connect application users to the database through the direct login to use application sessions.
This section contains:
You must have the privileges required to create, modify, or drop application users and roles. These privileges are governed by the same system privileges required to create, modify, or drop database users and roles. For more information about these and other SQL statements, see Oracle Database SQL Language Reference.
Select the appropriate type, and follow the instructions in these sections:
After you create the application user account, you can grant the account a role, which provides privileges for the application users. For more information, see "Granting an Application Role to an Existing Application User".
Note:In SQL*Plus, case sensitivity is an issue for lower case characters and special characters, so keep these guidelines in mind.
An application user whose name contains lower case or special characters must connect to SQL*Plus with the account name in double quotation marks:
CONNECT "lwuser1" Enter password: password Connected.
The name of an application role that contains lower case or special characters must be entered in SQL*Plus enclosed in double quotation marks.
GRANT cust_role TO "app_regular_role";
When you create a simple application user account, the schema argument specifies the schema name to use to resolve unqualified names. This does not give you any privileges, and it is just used for name resolution purposes. If the schema name is not specified,
XS$NULL, is used.
To create a simple application user account, do the following:
For example, if
sec_mgr has the
CREATE USER privilege, log in as follows:
sqlplus sec_mgr Enter password: password Connected.
Create the application user account.
BEGIN SYS.XS_PRINCIPAL.CREATE_USER('lwuser1'); END; /
As a user with
DBA role, you can check the user creation by querying the
DBA_XS_USERS data dictionary view as follows. See "DBA_XS_USERS" for more information.
SELECT NAME FROM DBA_XS_USERS; NAME -------------------------- XSGUEST LWUSER1
This output displays the existing application user accounts. The
XSGUEST user account is an already existing or predefined system created user account.
For detailed information about the
XS_PRINCIPAL.CREATE_USER procedure, see "CREATE_USER Procedure".
You can delete an application user account using the
XS_PRINCIPAL.DELETE_PRINCIPAL procedure, see "DELETE_PRINCIPAL Procedure".
This section contains:
You can use an application user account to directly log into the database. This is useful for users who need to perform functions such as logging directly into SQL*Plus without logging in through SSO or a Web interface. The direct login user must have a password.
To create a direct login application user account:
Log in as described in "General Procedures for Creating Application User Accounts".
Create the application user account.
BEGIN XS_PRINCIPAL.CREATE_USER (name => 'lwuser1', schema => 'HR'); END;/
Note:If the schema does not exist, the direct login fails.
When this Real Application user directly connects to the database for name resolution of unqualified database objects in queries,
HR schema is used as the default schema. For example:
SELECT COUNT(*) FROM EMPLOYEES;
BEGIN XS_PRINCIPAL.SET_PASSWORD('lwuser1', 'password'); END; /
Set the password as described in "SET_PASSWORD Procedure". When you use the
SET_PASSWORD procedure, it creates a verifier for you based on the password and the
type parameter, and then inserts the verifier and the value of the
type parameter into the dictionary table.
passwordwith a secure password. See Oracle Database Security Guide for more information about password guidelines.
Next, you are ready to assign privileges to the application user account. Go to "Granting Application Privileges to Principals".
Afterward, the user can connect to the database as follows. For example:
CONNECT lwuser1 Password: password
Optionally, you can set a password verifier for this password (a hash transformed password), enabling administrators to migrate users into Real Application Security with knowledge of the verifier and not the password. If you do not set a password verifier, the default hashing algorithm is
XS_SALTED_SHA1. For more information, see the "SET_VERIFIER Procedure".
Example 2-1 uses the
XS_PRINCIPAL.SET_VERIFIER procedure to set the password verifier to the value
6DFF060084ECE67F, using the hashing algorithm
XS_MD5 for the application user account
XS_PRINCIPAL.ADD_PROXY_USER procedure, you can add an application user to proxy another application user and assume the application roles of that application user. You can use the
DBMS_XS_SESSIONS.SWITCH_USER procedure to switch application users in a session if the user has been added as a proxy.
app_user1 has application roles
role2. Example 2-2 allows you to proxy the application roles
app_user2. The call
add_proxy_user('app_user1', 'app_user2', pxy_roles) allows
app_user2 to switch to
app_user1 and assume
role2. It does not grant the roles to
DECLARE pxy_roles XS$NAME_LIST; begin pxy_roles := XS$NAME_LIST('role1','role2'); xs_principal.add_proxy_user(target_user => 'app_user1', proxy_user => 'app_user2', target_roles => pxy_roles); end; /
As the application user with DBA role, you can create a session for
app_user2 and switch application user to
app_user1, as shown in Example 2-3.
declare sessionid raw(16); begin dbms_xs_sessions.create_session('app_user2', sessionid); dbms_xs_sessions.attach_session(sessionid); dbms_xs_sessions.switch_user('app_user1'); end; /
This example first creates a session with
app_user2 and attaches to it. Then
app_user2 switches to
app_user1 and assumes
Oracle recommends that you always validate the Real Application Security objects after administrative configuration changes. The
XS_DIAG package provides a set of validation APIs to help ensure that these changes do not damage the complicated relationships among your Real Application Security objects. To validate an application user account, use the
XS_DIAG.VALIDATE_PRINCIPAL function. The caller has invoker's rights on this package and must have
ADMIN_ANY_SEC_SECURITY privilege to run the
See the "VALIDATE_PRINCIPAL Function" for more information.
An application role is a role that can only be granted to an application user or to another application role. Application roles provide a way to group application users who must have a common application privilege, identified within an ACL, in order to access an application. The
XS_PRINCIPAL.CREATE_ROLE procedure can create regular application roles. The
XS_PRINCIPAL.CREATE_DYNAMIC_ROLE procedure can create dynamic application roles (one type of application role).
Application roles are conceptually similar to enterprise roles. An enterprise role can only be granted to an enterprise user and that grant occurs outside the database. Similarly, an application role can only be granted to an application user or application role, and that grant occurs outside of the standard database grant mechanisms. Dynamic roles cannot be granted to an application user or another application role, but can only be enabled in an application session as a parameter in an attach session call as described in "Dynamic Application Roles".
Oracle Database SQL Language Reference for more information about SQL
Oracle Database PL/SQL Language Reference for more information about PL/SQL APIs
Real Application Security allows regular and dynamic application roles.
This section contains the following topics:
A regular application role is an application role that you can grant to an application user or another application role (regular or dynamic). You can specify if you want the regular application role to be enabled by default or not.
A dynamic application role is an application role that is enabled only under certain situations, for example, when a user has logged on using SSL, or during a specific period of time, and so on. Dynamic application roles might be used, for example, if there is some application privilege granted to all application users connecting during weekdays. If that criterion is met, then the application enables those application roles.
The application determines the criteria for enabling a dynamic application role, however the criteria can be evaluated by the application or by the database at the request of the application.
When the Application Evaluates the Criteria
If the application evaluates the criteria and the application role meets it, then the application, if it is attached to an application session, can enable dynamic application roles for application users. When the application detaches from the application session, the dynamic application role is automatically disabled.
For security reasons, you cannot disable dynamic application roles during the session. This is especially important because they may infer negative application privileges.
When the Database Evaluates the Criteria
If the database evaluates the criteria and the application role meets it, then the database can enable application roles for the application user. The database can disable dynamic application roles based on two types of time-outs: one from the last time the session was accessed, and one from the last time the session was authenticated. Oracle Database checks these time-outs when the session is first attached.
You do not need to grant the dynamic application role formally to a user beforehand. There is no way to enable or disable a dynamic application role through the standard enable and disable APIs. You cannot grant dynamic application roles to other application roles, but you can grant other application roles to dynamic roles.
Example 2-4 shows how to create a regular application role called
end_date parameters specify the active start and end times for this application role. The
enable parameter is set to
DECLARE st_date TIMESTAMP WITH TIME ZONE; ed_date TIMESTAMP WITH TIME ZONE; BEGIN st_date := SYSTIMESTAMP; ed_date := TO_TIMESTAMP_TZ('2013-06-18 11:00:00 -5:00','YYYY-MM-DD HH:MI:SS XS_PRINCIPAL.CREATE_ROLE (name => 'app_regular_role', enabled => TRUE, start_date => st_date, end_date => ed_date); END; /
After you create the regular application role, you are ready to grant it to one or more application users or application roles. See the following section:
Example 2-5 shows how to create a dynamic application role called
app_dynamic_role. The optional
scope parameters specify the period of time (in minutes) the application role is active and the scope for this role, which can be either
SESSION_SCOPE (the default value) or
SESSION_SCOPE means the enabled dynamic role is still enabled when you detach the session and attach to the session again, unless you explicitly specify that it be disabled in the session reattach.
REQUEST_SCOPE means that the role is disabled after the session is detached.
BEGIN XS_PRINCIPAL.CREATE_DYNAMIC_ROLE (name => 'app_dynamic_role', duration => 40, scope => XS_PRINCIPAL.SESSION_SCOPE); END; /
In this example, the dynamic application role is active for 40 minutes, and the scope is the current application session.
Oracle recommends that you should always validate Real Application Security objects after administrative configuration changes. The
XS_DIAG package provides a set of validation APIs to help ensure that these changes do not damage the complicated relationships among your Real Application Security objects. To validate an application role, use the
XS_DIAG.VALIDATE_PRINCIPAL function. See the "VALIDATE_PRINCIPAL Function" for more information.
See Appendix D, "Troubleshooting Oracle Database Real Application Security" for troubleshooting advice.
Using predefined dynamic application roles in a Real Application Security session, application users can acquire application privileges based on their run-time states. These application roles cannot be acquired by grants.
As an example, an application role may be enabled for application users connecting from within the corporate firewall, which grants application users more application privileges than connecting from outside the firewall.
See "Roles" for a description of Real Application Security predefined regular application roles, dynamic application roles, and database roles.
Regular application roles can be granted to an application user, but dynamic application roles cannot. Dynamic application roles are enabled based on user state.
See "Regular and Dynamic Application Roles" for descriptions.
You can specify effective dates for application users, application roles, and role grants. The application user or application role is available only within the period defined by the effective start and end date. Example 2-6 shows how effective dates are specified for an application user.
DECLARE startDate TIMESTAMP := TO_TIMESTAMP ( '2012-01-01 11:00:00','YYYY-MM-DD HH:MI:SS'); endDate TIMESTAMP := TO_TIMESTAMP ( '2013-01-01 11:00:00','YYYY-MM-DD HH:MI:SS'); BEGIN XS_PRINCIPAL.CREATE_USER (name => 'lwuser1', start_date => startDate, end_date => endDate); END; /
Sometimes the effective date restriction does not need to be an attribute of an application user or application role. Instead, it is only needed to restrict the effective dates on a per role grant basis. In this case, you can specify beginning and ending effective dates for an application role grant. This only constrains that particular application role grant and allows for implementing fine-grained access control policy. Example 2-7 shows how effective dates are specified for an application role.
DECLARE startDate TIMESTAMP := TO_TIMESTAMP ('2012-01-01 11:00:00','YYYY-MM-DD HH:MI:SS'); endDate TIMESTAMP := TO_TIMESTAMP ('2013-01-01 11:00:00','YYYY-MM-DD HH:MI:SS'); BEGIN XS_PRINCIPAL.GRANT_ROLES (grantee => 'lwuser1', role => 'app_regular_role', start_date => startDate, end_date => endDate); END; /
These are the most direct consequences of effective date restrictions:
If an application user is not currently effective (that is, within the period defined by its start and end date), the session for the particular application user cannot be created.
If an application role is not currently effective, the application role (and any descendants) is not be available to the application user in the session.
For application roles that are shared children of multiple application roles, the child application roles are available as long as there is at least one parent that is effective.
If the application role grant of an application role is not currently effective, the application role (and any descendants) is not available to the application user or application role to which it is granted.
Note:The effective dates should be used in the policy after a careful consideration of the nature of the restrictions that they impose on the use of application users and application roles.
This section contains the following topics:
Example 2-8 shows how to grant an application role,
appl1_regular_role, to an application user,
lwuser1, when the application user account is created.
To find a listing of existing application roles, query the
DBA_XS_ROLES data dictionary view.
Example 2-9 shows how to grant an application role,
appl1_regular_role, to an existing application user,
lwuser1. You cannot grant dynamic application roles to an existing application user.
Example 2-10 shows how to grant a regular application role to another regular application role. You cannot grant dynamic application roles to other regular application roles, but you can grant other regular application roles to dynamic application roles. To find a listing of existing application roles, query the
DBA_XS_ROLES view (see "DBA_XS_ROLES").
Example 2-11 shows how to grant the database role,
cust_role, to the application role