This chapter contains:
See Also:
This section contains the following topics:
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:
The general procedure for creating an application user account is as follows:
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.
Create the application users with the XS_PRINCIPAL.CREATE_USER procedure.
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:
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:
Log in.
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.
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".
This section contains:
Procedure for Creating the Direct Login Application User Account
Setting a Password Verifier for Direct 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.
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.
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;
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:
Replacepassword with a secure password. See Oracle Database Security Guide for more information about password guidelines.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.
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_SHA512. 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_SHA512 for the application user account LWUSER1.
As the security administrator, sec_mgr, you have the dba and xs_session_admin privileges. Example 2-2 shows how the security administrator can reset the password for user lwuser2 using the SQL*Plus PASSWORD command.
Example 2-2 DBA Resets the Password with a Password Change Operation for User lwuser2 When Not Explicitly Attached to a Session
sqlplus sec_mgr Enter password: password Connected. SQL> BEGIN 2 SYS.XS_PRINCIPAL.CREATE_USER('lwuser2'); 3 END; 4/ PL/SQL orocedure successfully completed. SQL> PASSWORD lwuser2 Changing password for lwuser2 New password: password Retype new password: password Password changed
However, if you as user lwuser2, perform a self password change using the SQL*Plus PASSWORD command invoked from an explicitly attached session (a session attached using the ATTACH_SESSION procedure or the attachSession() method in Java), the session must have the ALTER USER privilege and the user name must be provided with the PASSWORD command.
Example 2-3 shows how the application user lwuser2 explicitly attached to a session, performs a self password change that fails because the users session does not have the ALTER USER privilege.
Example 2-3 User lwuser2 Performs a Self Password Change that Fails When Explicitly Attached to a Session Because the Session Lacks the ALTER USER Privilege
sqlplus sec_mgr Enter password: password Connected. SQL> DECLARE 2 SESSIONID RAW(16); 3 BEGIN 4 SYS.DBMS_XS_SESSIONS.CREATE_SESSION('lwuser2', sessionid); 5 SYS.DBMS_XS_SESSIONS.ATTACH_SESSION(sessionid); 6 END; 7 / PL/SQL procedure successfully completed. SQL> CONNECT lwuser2 Enter password: password Connected. SQL> SELECT SYS.XS_SYS_CONTEXT('XS$SESSION','USERNAME') FROM DUAL; SYS.XS_SYS_CONTEXT('XS$SESSION','USERNAME') -------------------------------------------------------------------------------- LWUSER2 SQL> PASSWORD lwuser2 Changing password for lwuser2 Old password: password New password: password Retype new password: password ERROR: ORA-01031: insufficient privileges Password unchanged
Example 2-4 shows how an application user lwuser2 explicitly attached to a session having the ALTER USER privilege can perform a self password change. The user's self password change is successful.
Example 2-4 A Self Password Change Succeeds When Explicitly Attached to a Session and User lwuser2's Session Has the ALTER USER Privilege
sqlplus sec_mgr Enter password: password Connected. SQL> CREATE ROLE pwdchg; Role created. SQL> GRANT ALTER USER TO pwdchg; Grant succeeded. SQL> EXEC SYS.XS_PRINCIPAL.CREATE_ROLE(NAME => 'resetpwd_role', ENABLED => TRUE); PL/SQL procedure successfully completed. SQL> GRANT pwdchg TO resetpwd_role; Grant succeeded. SQL> EXEC SYS.XS_PRINCIPAL.GRANT_ROLES('lwuser2','resetpwd_role'); PL/SQL procedure successfully completed. SQL> CONNECT lwuser2 Enter password: password Connected. SQL> SELECT SYS.XS_SYS_CONTEXT('XS$SESSION','USERNAME') FROM DUAL; SYS.XS_SYS_CONTEXT('XS$SESSION','USERNAME') -------------------------------------------------------------------------------- LWUSER2 SQL> PASSWORD lwuser2 Changing password for lwuser2 Old password: password New password: password Retype new password: password Password changed SQL>
If the user's session has the ALTER USER privilege, you can reset the password for any application user from any application user's session (including an explicitly attached and a direct logon session) or the database user session if that session has the ALTER USER privilege. The PASSWORD command never prompts for the old password if you are changing another application user's password.
An application user can also change his or her own password using the SET_PASSWORD procedure. The SET_PASSWORD procedure will always prompt for the old password for a self password change, but if the session has the ALTER USER privilege, then the old password can be omitted.
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-5 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-5 Configuring a Proxy Application User
DECLARE
pxy_roles XS$NAME_LIST;
begin
pxy_roles := XS$NAME_LIST('role1','role2');
sys.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-6.
Example 2-6 Creating a Session and Switching an Application User
declare
sessionid raw(16);
begin
sys.dbms_xs_sessions.create_session('app_user2', sessionid);
sys.dbms_xs_sessions.attach_session(sessionid);
sys.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.
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.
This section contains the following topics:
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:
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.
This section contains the following topics:
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-7 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-7 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');
SYS.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:
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-8 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-8 Creating a Dynamic Application Role
BEGIN SYS.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-9 shows how effective dates are specified for an application user.
Example 2-9 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
SYS.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-10 shows how effective dates are specified for an application role.
Example 2-10 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
SYS.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:
This section contains the following topics:
Creating a New Application User and Granting This User an Application Role
Granting an Application Role to an Existing Application User
Example 2-11 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-12 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-13 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").
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-14 shows how to grant the database role, cust_role, to the application role app_regular_role.