Skip Headers
Oracle® Database Real Application Security Administrator's and Developer's Guide
12c Release 1 (12.1)

E10479-19
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

2 Configuring Application Users and Application Roles

This chapter contains:

Configuring Application Users

This section contains the following topics:

About Application User Accounts

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:

General Procedures for Creating Application User Accounts

The general procedure for creating an application user account is as follows:

  1. Log in to SQL*Plus as a user who has the CREATE USER system privilege.

    See "XS_PRINCIPAL Package" for more information about the XS_PRINCIPAL package and specifically the "CREATE_USER Procedure".

    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.

  2. Create the application users with the XS_PRINCIPAL.CREATE_USER procedure.

    Select the appropriate type, and follow the instructions in these sections:

Other Tasks

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".

Creating a Simple Application User Account

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:

    For example:

    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.

    For example:

    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:

  1. Log in.

    For example, if sec_mgr has the CREATE USER privilege, log in as follows:

    sqlplus sec_mgr
    Enter password: password
    Connected. 
    
  2. Create the application user account.

    For example:

    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".

Creating a Direct Login Application User Account

This section contains:

Creating Direct Login Application User Accounts

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.

Procedure for Creating the Direct Login Application User Account

To create a direct login application user account:

  1. Log in as described in "General Procedures for Creating Application User Accounts".

  2. Create the application user account.

    For example, to create an application user account, lwuser1, whose default database schema is HR:

    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;
    
  3. Create a password for the application user account.

    For example:

    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.

    Note:

    Replace password with 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

Setting a Password Verifier for Direct Application User Accounts

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 LWUSER1.

Example 2-1 Setting the Password Verifier

BEGIN
XS_PRINCIPAL.SET_VERIFIER('lwuser1',
 '6DFF060084ECE67F',
  XS_PRINCIPAL.XS_MD5);
END;
/

Configuring an Application User Switch

Using the 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.

Assume app_user1 has application roles role1 and role2. Example 2-2 allows you to proxy the application roles role1 and role2 of app_user1 to app_user2. The call add_proxy_user('app_user1', 'app_user2', pxy_roles) allows app_user2 to switch to app_user1 and assume app_user1's roles, role1 and role2. It does not grant the roles to app_user2.

Example 2-2 Configuring a Proxy Application User

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.

Example 2-3 Creating a Session and Switching an Application User

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 app_user1's roles, role1 and role2.

Validating an Application User

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 XS_DIAG package.

See the "VALIDATE_PRINCIPAL Function" for more information.

Configuring Application Roles

This section contains the following topics:

About Application Roles

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".

See Also:

Regular and Dynamic Application Roles

Real Application Security allows regular and dynamic application roles.

This section contains the following topics:

Regular Application Roles

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.

Dynamic Application Roles

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.

Configuring an Application Role

This section contains the following topics:

Creating a Regular Application Role

To create a regular application role, log into SQL*Plus as user sec_mgr with the CREATE ROLE system privilege, and then use the XS_PRINCIPAL.CREATE_ROLE procedure.

Example 2-4 shows how to create a regular application role called app_regular_role. The start_date and end_date parameters specify the active start and end times for this application role. The enable parameter is set to TRUE.

Example 2-4 Creating a Regular Application Role

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:

"Granting an Application Role to an Application User"

Creating a Dynamic Application Role

To create a dynamic application role, log into SQL*Plus as user sec_mgr with the CREATE ROLE system privilege and then use the XS_PRINCIPAL.CREATE_DYNAMIC_ROLE procedure.

Example 2-5 shows how to create a dynamic application role called app_dynamic_role. The optional duration and 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 REQUEST_SCOPE. 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.

Example 2-5 Creating a Dynamic Application Role

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.

Validating an Application Role

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.

Predefined Regular Application Roles and Dynamic Application Roles

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.

Effective Dates for Application Users and Application Roles

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.

Example 2-6 Setting Effective Dates 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.

Example 2-7 Setting Effective Dates for an Application Role of 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.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.

Granting Application Privileges to Principals

This section contains the following topics:

Granting an Application Role to an Application User

This section contains the following topics:

Creating a New Application User and Granting This User an Application Role

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-8 Creating a New Application User and Granting This User an Application Role

BEGIN
  XS_PRINCIPAL.CREATE_USER('lwuser1');
  XS_PRINCIPAL.GRANT_ROLES('lwuser1', 'appl1_regular_role');
END;
/

Granting an Application Role to an Existing Application User

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.

You can find a listing of existing application user accounts by querying the DBA_XS_USERS view.

Example 2-9 Granting an Application Role to an Existing Application User

BEGIN
  XS_PRINCIPAL.GRANT_ROLES('lwuser1', 'appl1_regular_role');
END;
/

Granting an Application Role to Another Application Role

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-10 Granting a Regular Application Role to Another Regular Application Role

BEGIN
  XS_PRINCIPAL.GRANT_ROLES(grantee => 'app_regular_role', role => 'appl1_regular_role');
END;
/

Granting a Database Role to an Application Role

To grant a database role to an application role, use the SQL GRANT statement. You can find a listing of existing database roles by querying the DBA_ROLES data dictionary view.

Example 2-11 shows how to grant the database role, cust_role, to the application role app_regular_role.

Example 2-11 Granting a Database Role to an Application Role

GRANT cust_role TO app_regular_role;