1. Create a security manager user, sec_mgr, as follows and grant this user create session database privilege and Real Application Security xs_session_admin database role. Next, execute the xs_admin_util.grant_system_privilege call to grant the Real Application Security least system privilege PROVISION to sec_mgr as a database user. As the security manager, you can now create users and roles, set passwords, and so forth, and administer sessions using the Real Application Security least system privilege.

   sqlplus /nolog
   SQL> connect sys/password as sysdba
   SQL> grant create session, xs_session_admin to sec_mgr identified by password;
   SQL> exec sys.xs_admin_util.grant_system_privilege('provision', 'sec_mgr', sys.xs_admin_util.ptype_db);

2. Log in to SQL*Plus as a user who has either the Real Application Security PROVISION system privilege or the database CREATE USER system privilege.

   sqlplus sec_mgr
   Enter password: password
   Connected. 

   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.

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

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

   • "Creating a Simple Application User Account"

   • "About Creating a Direct Login Application User Account"

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

   sqlplus sec_mgr
   Enter password: password
   Connected. 

2. Create the application user account.

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

   BEGIN
     SYS.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
         SYS.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.

4. Create a profile named prof and assign this profile to the application user account.

   For example:

   CREATE PROFILE prof LIMIT PASSWORD_REUSE_TIME 1/1440 PASSWORD_REUSE_MAX 3 PASSWORD_VERIFY_FUNCTION Verify_Pass;
   
   BEGIN
     SYS.XS_PRINCIPAL.SET_PROFILE('lwuser1','prof');
   END;
   

   The user assigning the profile must have ALTER_USER privilege. See the "SET_PROFILE Procedure" for more information.

5. Grant the role XSCONNECT to the user to allow access to the database.
   For example:

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

CONNECT lwuser1
Password: password

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.