1 Setting Up User Accounts

1.2.1 Granting Users Access to the ocpsub Account

Users who need to submit PSUB jobs must have the ocpsub account as a proxy database account for PSUB jobs. To give this access, enter the appropriate information in the ocl_add_user.sql script; see "Running the Add User Script".

If you have user accounts you created in Oracle Clinical releases prior to 5.0, you can migrate them to the new PSUB setup by running the migration script oclupg50migrateusers.sql. PSUB users no longer need their own operating system account, and they no longer need an account name beginning with OPS$; see "Running the Migrate Users Script".

You can use ocl_grant_revoke_OCPSUB.sql to add or revoke access to ocpsub at any time; see "Adding or Revoking OCPSUB Access".

Users with access to ocpsub have the PSUB User? field checked in the Oracle Accounts window. The field is read-only.

1.2.2 About the Add User Script

The ocl_add_user.sql script performs the following tasks:

• Creates an Oracle database account for the user, with the specified password

  Note:

  If you use SQL*Plus to create a Oracle Clinical database account, do not use the IDENTIFIED EXTERNALLY clause; rather, assign an explicit password.

• Sets the user's temporary tablespace to "temp" and default tablespace to "users"

• Grants default Oracle Clinical database roles to the user; you can edit the script to assign additional database roles

• Grants the RXC_SUPER role for data access to all studies, if specified

• Grants RXC_RDC and RDC_ACCESS if access to RDC is required

• Makes the RXCLIN_MOD role a non-default role

• Creates a record in the Oracle Clinical table ORACLE_ACCOUNTS

• Creates the setting you specify for PSUB privileges

1.2.3 Running the Add User Script

1. Log on to the operating system.

2. Set environment variables for the database name and code environment; see "Setting Environment Variables on the Command Line."

3. Change to the RXC_TOOLS directory:

   cd $RXC_TOOLS

4. Connect to SQL*Plus as system:

   sqlplus system

5. Run the Add User script:

   start ocl_add_user.sql

   See Table 1-1, "Add User Script Required Parameters" and Table 1-2, "Add User Script Optional Parameters" for a description of the prompts output by the Add User script, valid entries, and examples.

1.2.4 Required Parameters

The following table describes the required Add User script parameters.

bsmith

Password

farrier

Last Name

Smith

First Name

William

Report Server Log Directory

C:\oc_srvr\users\bsmith

Grant RXC_Super Menu Role (Y/N)

Grant Super-User Status to access all studies (Y/N)

Does user require access to RDC (Y/N)

1.2.5 Optional Parameters

The following table describes the optional parameters for the Add User script. In addition, you can use the Maintain Oracle Accounts form to manage these optional parameters; see "Maintaining Oracle User and Group User Accounts".

1.5.1 Adding a User to a Group User Account

You can add all the users who should have access to the same set of studies, programs, or projects to the same group user account and assign data access to all the users at the same time through the group user account. An individual user can belong to multiple group user accounts.

To add a user to a user group:

1. Navigate to Admin, Users, and then Oracle Accounts. The system opens the Oracle Accounts window.

2. Query for the individual user you want to add to a user group.

3. Click Group Membership. The system displays the Group Membership window.

4. From the Group Membership field's list of values, select the group to which you want to assign the user. To assign the user to multiple user groups, use multiple rows.

5. Save your work.

1.6.1 Granting Data Access to Programs and Projects

Assigning program or project access to a user or group user account enables either the individual user or the set of users in the group to have access to all studies associated with the program or project.

1. Navigate to Admin, Users, and then select Oracle Accounts. The system opens the Oracle Accounts window.

2. Query for the account with which you want to work.

   Note:

   You cannot assign project or program access to a user or user group whose Super User? flag is checked. That account already has access to all programs and projects.

3. Click Programs/Projects. The Programs window displays.

4. In the Program field, from the list of values select the name of the program to which you want the user or user group to have access.

5. In the Project field, from the list of values select the program to which the user will have access. If the user should have access to all projects within a program, enter a percent sign (%) in the project field.

   To assign multiple programs to the account, or multiple (but not all) projects within a program, use multiple rows. There is no limit to the number of programs to which the user can have access.

6. Save your work.

1.6.2 Granting Data Access to a Study

To assign study data access to a user or user group:

1. Navigate to Admin, Users, and then Oracle Accounts. The system opens the Maintain Oracle Accounts window.

2. Query for the account with which you want to work.

   Note:

   You cannot assign study access to a user or user group whose Super User flag is checked. That account already has access to all studies.

3. Click Studies. The Studies window displays.

4. In the Study field, from the list of values select a study to which you want the user or user group to have access. To assign multiple studies to the account, use multiple rows. There is no limit to the number of studies you can specify.

5. Save your work.

   Note:

   If a user creates a new study and does not have access to it through the project and/or program it belongs to, the system automatically gives the user access to the study he or she has just created.

1.6.3 Superuser and Study Access Interaction

You cannot assign project or program access to a user or user group whose Super User? flag is checked. That account already has access to all programs and projects.

However, if you assign access to programs, projects, or studies to a user whose Super User? flag is not checked, and then check that user's Super User? flag, the superuser status overrides the existing specific privileges, but the existing privileges are still displayed in the Projects, Programs, and Studies windows.

If an RDC user has superuser status in the Oracle Accounts window and has access to only a subset of RDC studies in the Study Security window, the superuser status overrides the study-specific privileges and the user has access to all studies. However, you can use the Study Security window to limit the type of access to a particular study. See the Oracle Clinical Remote Data Capture Onsite Administrator's Guide for information.

1.6.4 Revoking User Access

To revoke a user's study, user group, project, or program access:

1. Navigate to Admin, Users, and then select Oracle Accounts. The system displays the Oracle Accounts window.

2. Query for the account with which you want to work.

3. Click either Studies, Programs/Projects, or Group Membership.

4. Select the study, program/project combination, or user group from which you want to remove the user.

5. From the Data menu, select Delete Record.

6. Save your work.

1.7.1 Granting Automatic Access in RDC to Studies Granted in Oracle Clinical

You can use a reference codelist setting to determine whether users who have access to data for a particular study in Oracle Clinical automatically have access to the study in RDC.

In the OCL_STATE local reference codelist, set the DMGR RDC ACCESS short value as follows:

• When set to YES, a user with no study privileges defined for RDC but with study access defined in Oracle Clinical is automatically given RDC Onsite access to the study as well, in both Test and Production modes. The user has all RDC privileges except APPROVE and VERIFY. UPD_LOCK_OC, an Oracle Clinical-specific privilege, is also excluded. You can restrict such a user's access to RDC Onsite by limiting privileges at the study or site level; see the Oracle Clinical Remote Data Capture Onsite Administrator's Guide for more information.

• When set to NO, a user granted access to a study in Oracle Clinical does not automatically have access to that study in RDC Onsite. You can use the Study Security form to assign specific privileges to the user; see the Oracle Clinical Remote Data Capture Onsite Administrator's Guide for more information.

Users with the Super User? flag selected in the Oracle Accounts form in Oracle Clinical have access to all studies in both Oracle Clinical and RDC, and in both Test and Production modes.

1.7.2 Configuring Study and Site Security Privileges

You can give RDC users specific privileges for particular studies and sites in the Study and Site Security windows, which are included in both Oracle Clinical and in the RDC Onsite Administrator's Tool; see also "Configuring Discrepancy Management in Oracle Clinical Remote Data Capture Onsite Administrator's Guide.

1.7.3 Changing the Default Access to DCIs

RDC Onsite includes a predefined set of user roles. By default, these roles have UNRESTRICTED access to all DCIs (Data Collection Instruments, which become CRFs). You can change the default access for any role to RESTRICTED, then further define which DCIs (within the user's study/site access) can be accessed by that user's role.

• Users assigned to a role with UNRESTRICTED access to DCIs can access any DCI in RDC Onsite unless access has been denied to a particular DCI in a particular study through the DCI Access window in the Oracle Clinical Definition menu; see Oracle Clinical Creating a Study for study-level information.

• Users assigned to a role with RESTRICTED access to DCIs cannot access any DCIs in RDC Onsite unless access has been granted to a particular DCI in a particular study.

You may want to create custom database roles specifically for the purpose of restricting access to certain DCIs to smaller groups of people (see "Creating Custom Database Roles" for more information and an example). If you do, you must add the new role in the Maintain DCI Access by Role window and specify a default access of RESTRICTED or UNRESTRICTED for it.

Before you can change the default DCI access for a user, the user role must exist (must be valid). You cannot change the default DCI access if the user role does not exist.

To define the DCI access for a user role:

1. Open Oracle Clinical.

2. Navigate to Admin and then select Users and Roles.

3. Select Default DCI Access by Role.

   Alternatively, you can select one of the following menu options depending upon your administrator privileges and current task:

   • Select Test Default DCI Access to try out DCI access before implementing the feature in a live study.

   • Select Query Default DCI Access by Role if you only want to view the current settings but make no changes.

4. Enter a valid user role in the User Role field. You can:

   • Type the name of a valid user role into the field.

   • Click the List of Values button, and then select a user role from the list. The list includes all the user roles currently defined in the installation reference codelist.

5. Enter the default DCI access for the selected user role. Valid entries are:

   • UNRESTRICTED — Allows study/site access to all DCIs unless otherwise restricted in the DCI Access form for the study.

   • RESTRICTED — Does not allow access to any DCIs unless you specify exceptions in the DCI Access form for the study.

   You can type a valid entry directly into the field. Alternatively, you can click the List of Values button, and then select from the list.

6. Continue to enter each user role and the type of DCI access allowed.

7. Save your changes.

For each record in the Maintain Default DCI Access by Role form, Oracle Clinical creates and maintain an audit trail.

Upon initial entry to the form, Oracle Clinical populates the form with all the user roles defined in the reference codelist. For each user role, the Default DCI Access field is set to UNRESTRICTED. You must add any new user roles that you create.

1.8.1 Additional Database Roles for RDC Users

You must explicitly grant every RDC Onsite user at least one database role. You can use the predefined database roles listed in the following table, selecting the role that matches the user's job function, or define additional database roles if you need to further fine-tune security privileges; see "Creating and Modifying Database Roles".

These database roles are mapped to user roles in the USER_GROUP_ROLES installation reference codelist. Those user roles are used to define security privileges and to customize various aspects of the user interface. See Chapter 3, "Configuring Discrepancy Management" for more information.

1.8.2 Additional Database Roles for Oracle Clinical Users

The database roles assigned to a user determine which tasks a user can perform by controlling which menu paths the user can see in the user interface—for example, Study Design, Data Entry, or Administration—and within those areas, finer distinctions such as whether the user can make changes or only view existing data. Oracle Clinical includes many predefined database roles for this purpose. You can also define your own database roles; see Chapter 2, "Oracle Clinical Menu-Based Security" for more information.

The roles in the following table apply to the Oracle Clinical discrepancy management system as well as to RDC. Give one of the above roles to each user who has one of the corresponding job functions and who will be working with discrepancies in Oracle Clinical's Maintain Discrepancy Database window.

1.11.1 UNIX

Bourne/Korn shell Edit the user's .profile file. In the following example, SAS_home is the directory where the SAS executable is located.

# Include OPA directories
PATH=$PATH:/opa_home/bin:/sas_home; export PATH
# Define RXC_LOG for command line utilities
RXC_LOG=$HOME/log; export RXC_LOG

C shell Edit the user's .cshrc file. For example:

# Include OPA directories 
set path=( $path /opa_home/bin /sas_home ) 
# Define RXC_LOG for command line utilities 
setenv RXC_LOG $HOME/log 
# Create alias for opa_setup 
source /opa_home/bin/copa_setup_alias

1.11.2 Windows

No changes to a login script are required. To run opa_setup, ensure that opapps_home/bin is in the PATH environment variable.

1.12.1 Changing the Password for a User

Users can change their own database passwords either in SQL*Plus or with the Oracle Clinical menu by choosing Admin, then Users, and then Database Password.

In the Oracle Database Password window:

1. Type your password in Enter Password text box.

2. Type it again in the Confirm Password text box.

3. Click OK.

Administrators can change the password for an Oracle Clinical user in SQL*Plus as usual:

1. In SQL*Plus, connect to the database as the SYSTEM user.

2. Reset the password for the user:

   alter user user identified by user_password;

1.12.2 Changing the Password for a Schema or Role Using the SET_PWD Utility

The following table lists the Oracle Clinical database objects that are protected by encrypted passwords stored in two locations:

• An Oracle dictionary table

• An Oracle Clinical passwords table

Use the SET_PWD utility to change the passwords for these accounts, encrypt the new password and store it in both locations:

Log in to the database server and, from the windows command prompt or UNIX command line, enter:

opa_setup
set_pwd account/account_pwd

where account is the name of the schema or role from Table 1-4, "Password-Protected Database Objects".

In rare cases you may want to change the password of the Wallet itself. See the Oracle Fusion Middleware Administrator's Guide. You must then change the passwords for the accounts.

1.12.3 Synchronizing Passwords in the WebLogic Admin Server

The passwords for the following application middle tier accounts:

• RDC_MIDTIER_PROXY

• BC4J_INTERNAL

• OPS$TMSBROWSER (if you have TMS integrated with Oracle Clinical)

must be changed to the same value in two places:

• in Oracle Clinical or TMS using set_pwd (see "Changing the Password for a Schema or Role Using the SET_PWD Utility")

• in the WebLogic Admin Server Connection Pool Manager

1.12.4 Auditing Passwords

Oracle recommends that you turn on the auditing system for roles. For information on the AUDIT command, see the Oracle SQL Reference manual.

To track failed attempts to create, alter, drop, or set a role, issue the following statement:

audit role by access whenever not successful

1.12.5 Changing the Password for the OCPSUB or RXC_DISC_REP Account

The passwords for OCPSUB and RXC_DISC_REP are stored in Oracle Wallets on the database. You cannot use the set_pwd utility to change these passwords. Instead, do the following:

1. Log on as opapps.

2. Set environment variables for the database name and code environment; see "Setting Environment Variables on the Command Line."

3. Open an MS-DOS window.

4. Set the server environment; see "Setting Environment Variables on the Command Line."

5. Enter the following command, where wallet_location is the full path to the wallet:

   mkstore -wrl wallet_location - modifyCredential tns_names_entry OCPSUB_or_RXC_DISC_REP  new_password
   

6. The system prompts you for the wallet password.

1.13.1 Default Behavior

The DEFAULT profile, which applies to all users by default in an Oracle Database installation, includes the following password-related requirements in Release 11gR2:

• Password life time = 180 days

• Password grace time = 7 days

• Password reuse time and password reuse max = Unlimited

• Failed login attempts = 10

  Note:

  In RDC, the default Failed Login Attempts setting of 10 in fact only allows for 5 failed login attempts before locking the account due to an incorrect password. This is because RDC uses proxy connections and tries to connect twice for each login attempt.

  In Oracle Clinical, this is not the case; a setting of 10 gives users 10 attempts.

• Password lock time = 1 day

If these settings are acceptable, you can do nothing and they will apply to all accounts. However, be sure to update internal account passwords; see "Synchronizing Passwords in the WebLogic Admin Server".

1.13.2 Creating a Profile

To change the default settings, you can create a new profile for this purpose and assign it to all users. Use the CREATE PROFILE command to define a profile to specify your password policy.

Example 1-1 shows the SQL statements that create the OCL_USER_PROF profile and define the following password policies:

• Password life time — Sets the number of days the same password can be used for authentication to 60 days.

• Password grace time — Sets the time between expiration and lockout to 10 days.

• Password reuse time and password reuse max — Indicates that the user can never reuse a password.

• Failed login attempts

  Note:

  Set the number of failed login attempts to two times the number of failed attempts that you really want to allow. This is because Oracle Clinical uses proxy connections and tries the internal connection two times for each user attempt before locking the account due to an incorrect password.

  So in this example, FAILED_LOGIN_ATTEMPTS is set to 6, but this effectively results in 3 failed user attempts resulting in locking the account.

• Password lock time — Locks the account for 2 days if there are 3 failed login attempts.

• Allowed characters — Oracle Clinical and RDC do not support the use of special characters in passwords, which may cause errors. Use password profiles to prevent the use of special characters. Also see Section 1.12, "Changing Passwords".

For additional information, see the Oracle Database 2 Day + Security Guide 11g Release 2 (11.2) at http://docs.oracle.com/cd/B19306_01/network.102/b14266/policies.htm#i1006575

CREATE PROFILE OCL_USER_PROF LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1200
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 6
PASSWORD_LOCK_TIME 2

1.13.3 Assigning a Profile to Users

Assign the profile to users:

• For new users, you can add a line in the ocl_add_user.sql to assign the password enforcing profile as you create each user account.

• For existing PSUB users, you can add the same line to oclupg50migrateusers.sql to give all of them the profile at the same time that you migrate their account to the new PSUB requirements.

• For other existing PSUB users, assign the profile individually or work with Oracle Support to develop a script to migrate them to the new profile.

The SQL statement required to assign a profile to a user is:

alter user user_name profile profile_name;

alter user jjsmith profile ocl_user_prof;

1.13.4 Getting More Information

For more information about how to use password management and protection, see http://docs.oracle.com/cd/E11882_01/network.112/e16543/authentication.htm#CHDCGJED in the Oracle® Database Security Guide 11g Release 2 (11.2).