Creating users for Oracle databases

You must create the users for Oracle databases before you install IBM® Business Process Manager. Create the cell-scoped user, the deployment environment-level user, the Process Server user, and the Performance Data Warehouse user. The Process Server user and the Performance Data Warehouse user are not needed for an Advanced-only deployment environment.

Before you begin

Before you install your system in Oracle database, ensure that the minimum initial settings in the following table are met for IBM BPM database schema users:

Table 1. Minimum initial settings for BPM database schema users
Tuning item	Minimum memory for initial settings (MB)
Buffer cache	2048
Shared pool size	1024

Note: You might want to enlarge the memory for the preceding settings depending on the capacity of your BPM system. For more tuning details, consult your database administrator.

About this task

The default database names are BPMDB for the Process database, PDWDB for the Performance Data Warehouse database, and CMNDB for the Common database. In the case of an Advanced or Advanced-only deployment environment, the Common database has two parts: one is scoped to the cell and the other is scoped to the deployment environment. Both parts can be defined to use CMNDB (which is the default) or they can use separate databases.

You can use a single instance of Oracle for configuring IBM Business Process Manager. The Oracle instance must exist and be available for access. Consult the Oracle documentation to create an Oracle instance. If you use a single Oracle instance, make sure that you use different user IDs for the three different IBM Business Process Manager databases.

Procedure

Use one of the following methods to create each database user that you require:

Create and run the createUser.sql file as described in the following substeps:

Save the following SQL statements into a file named createUser.sql:

-- create a new user
CREATE USER @DB_USER@ IDENTIFIED BY @DB_PASSWD@;

-- allow the user to connect to the database
grant connect to @DB_USER@;

-- provide quota on all tablespaces with BPM tables
grant unlimited tablespace to @DB_USER@;

-- grant privileges to create database objects:
grant resource to @DB_USER@;
grant create view to @DB_USER@;

-- grant access rights to resolve lock issues
grant execute on dbms_lock to @DB_USER@;

-- grant access rights to resolve XA related issues:
grant select on pending_trans$ to @DB_USER@;
grant select on dba_2pc_pending to @DB_USER@;
grant select on dba_pending_transactions to @DB_USER@;
-- If using Oracle 10.2.0.3 or lower JDBC driver, un-comment the following statement:
-- grant execute on dbms_system to @DB_USER@;
-- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
grant execute on dbms_xa to @DB_USER@;

For information about recovering Oracle database transactions, see the topic Configuring XA transactions for Oracle in a network environment on Windows.

In the SQL statements, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the password for that user.

Run the following command to create the database user:

sqlplus oracle_user_ID/oracle_password@db_name @createUser.sql

Run SQL statements in a command editor as described in the following substeps:

Copy the following SQL statements into a command editor:

-- create a new user
CREATE USER @DB_USER@ IDENTIFIED BY @DB_PASSWD@;

-- allow the user to connect to the database
grant connect to @DB_USER@;

-- provide quota on all tablespaces with BPM tables
grant unlimited tablespace to @DB_USER@;

-- grant privileges to create database objects:
grant resource to @DB_USER@;
grant create view to @DB_USER@;

-- grant access rights to resolve lock issues
grant execute on dbms_lock to @DB_USER@;

-- grant access rights to resolve XA related issues:
grant select on pending_trans$ to @DB_USER@;
grant select on dba_2pc_pending to @DB_USER@;
grant select on dba_pending_transactions to @DB_USER@;
-- If using Oracle 10.2.0.3 or lower JDBC driver, un-comment the following statement:
-- grant execute on dbms_system to @DB_USER@;
-- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
grant execute on dbms_xa to @DB_USER@;

In the SQL statements, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the password for that user.
Run the SQL statements in the command editor.