Database privileges

IBM BPM, V8.0.1, All platforms > Install IBM BPM > Plan for IBM BPM > Plan the database configuration
Database privileges

Set database privileges to determine the authority that you must have to create or access your data store tables for each supported database management system.

When you create schemas with the installer, Profile Management Tool, database design tool, or scripts, your user ID must have the authority to create tables. When the tables are created, you must have the authority to select, insert, update, and delete information in the tables.
The following table describes the database privileges that are needed to access the data stores.

Database privileges
Database type Minimum privileges required to create objects in the database Minimum privileges required to access objects in the database
DB2 The user ID needs CREATETAB authority on the database and CREATETS to create the table space. The user ID also needs CREATEIN and DROPIN privilege on the schema. The user ID needs system privileges CREATEDBA and CREATEDBC. The user ID also needs ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, and UPDATE privileges on the created tables. The user ID needs SELECT, INSERT, UPDATE, and DELETE privileges on the tables. The user ID also needs EXECUTE ON PROCEDURE on stored procedures.
Refer to Table 2 for detailed DB2 database privileges for IBM BPM and WebSphere Enterprise Service Bus components.
DB2 for z/OS The user ID needs CREATETAB authority on the database and CREATETS to create the table space. The user ID also needs CREATEIN and DROPIN privilege on the schema. To create storage groups for the database, the user ID needs CREATESG, CREATEDBA, and CREATEDBC system privileges. The user ID also needs ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, and UPDATE privileges on the created tables. The user ID needs SELECT, INSERT, UPDATE, and DELETE privileges on the tables. The user ID also needs EXECUTE ON PROCEDURE on stored procedures.
Refer to Table 3 for detailed DB2 for z/OS database privileges for IBM BPM and WebSphere Enterprise Service Bus components.
Oracle The user ID needs sufficient privilege to create relational tables and indexes in the data store schema. The database also needs a space quota in the default table space of the owner of that schema. The user ID needs the SESSION privilege to connect to the database. If the same user ID owns both the data store schema, and the component that is connecting to the database, the user ID has sufficient privilege to manipulate the tables. Otherwise, the user ID needs SELECT, INSERT, UPDATE, ALTER, and DELETE object privileges on the tables that make up the data store, and the DROP ANY TABLE system privilege to enable the use of the TRUNCATE TABLE statement. The user ID also requires the CREATE INDEX privilege.
You must create the Oracle database using a UTF-8 character set, which supports the other customer character sets that are supported by IBM BPM.
See Table 4 for detailed Oracle database privileges for IBM BPM and WebSphere Enterprise Service Bus components.
SQL Server The user ID ideally requires DB OWNER privileges on the data stores used for IBM BPM. Configure the SQL Server for SQL Server and Windows authentication so that authentication to be based on an SQL server login ID and password. The user ID must be the owner of the tables, or a member of a group that has sufficient authority to issue TRUNCATE TABLE statements.
See Table 5 for detailed SQL Server database privileges for IBM BPM and WebSphere Enterprise Service Bus components.

In the following tables, the installation privileges are the privileges that are required to install the product. The runtime privileges are the database privileges required to run the product.
Table 2 describes additional DB2 database privileges for IBM BPM components.

Detailed DB2 database privileges
Component Installation privileges Runtime privileges
Common DB CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, USAGE ON SEQUENCE
Business Space CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Process Choreographer CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE TABLESPACE, CREATE USER, CREATE PROCEDURE SELECT, UPDATE, DELETE, INSERT
Messaging Engines CREATE TABLE, CREATE INDEXTYPE SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE
Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges.
Process Server or Performance Data Warehouse
Required to create the database:

CREATEDBA
CREATEDBC

Required to populate the database with our schemas and stored procedures:

CREATETAB
CREATEIN
DROPIN

Additional required privileges on the created tables:

ALTER
DELETE
INDEX
INSERT
REFERENCES
SELECT
UPDATE

Required privileges on the tables in the Process Server and Performance Data Warehouse databases:

DELETE
INSERT
REFERENCES
SELECT
UPDATE

The runtime user must have EXECUTE ON PROCEDURE privileges on the six stored procedures in the createProcedure_ProcessServer.sql script.
The runtime user requires all of the listed privileges on the Performance Tracking Server database as well. In addition, the user also must be able to create new tables in the Performance Tracking database, requiring the CREATETAB privilege.
To read the system metadata tables, the SELECT permission is required on syscat.tables, syscat.views, syscat.columns, syscat.tabconst, and sysibm.sysdummy1.

Syntax for creating a database and assigning rights to a DB2 user:
create database database_name automatic storage yes  using codeset UTF-8 territory US pagesize 32768;
connect to database_name;
grant dbadm on database to user user_name;
UPDATE DB CFG FOR database_name USING LOGFILSIZ 4096 DEFERRED;
UPDATE DB CFG FOR database_name USING LOGSECOND 64 DEFERRED;
connect reset;
Table 3 describes additional DB2 for z/OS database privileges for IBM BPM components.

Detailed DB2 for z/OS database privileges
Component Installation privileges Runtime privileges
Common DB CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, USAGE ON SEQUENCE, USAGE
Business Space CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, USAGE ON SEQUENCE
Business Process Choreographer CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE TABLESPACE, CREATE USER, CREATE PROCEDURE SELECT, UPDATE, DELETE, INSERT
Messaging Engines CREATE TABLE, CREATE INDEXTYPE SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE
Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges.
Process Server or Performance Data Warehouse
Required to create the database:

CREATESG
CREATEDBA
CREATEDBC

Required to populate the database with our schemas and stored procedures:

CREATETS
CREATETAB
CREATEIN
DROPIN

Additional required privileges on the created tables:

ALTER
DELETE
INDEX
INSERT
REFERENCES
SELECT
UPDATE

Required privileges on the tables in the Process Server and Performance Data Warehouse databases:

DELETE
INSERT
REFERENCES
SELECT
UPDATE

The runtime user must have EXECUTE ON PROCEDURE privileges on the six stored procedures in the createProcedure_ProcessServer.sql script.
The runtime user requires all of the listed privileges on the Performance Tracking Server database as well. In addition, the user also must be able to create new tables in the Performance Tracking database, requiring the CREATETS and CREATETAB privileges.
To read the system metadata tables, the SELECT permission is required on sysibm.systables, sysibm.sysviews, sysibm.syscolumns, sysibm.syschecks, sysibm.sysrels, sysibm.systabconst, sysibm.systablespace, and, sysibm.sysdummy1.

Table 4 describes additional Oracle database privileges for IBM BPM components.
If you configure all the following components for a single Oracle database, you can create a superset of all the privileges that are specified for each component. If you configure the four components for numerous databases, you can set different privileges for each.
Detailed Oracle database privileges
Component Installation privileges Runtime privileges
Common DB CREATE TABLE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Space CREATE TABLE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Process Choreographer CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE TABLESPACE, CREATE USER, CREATE PROCEDURE SELECT, UPDATE, DELETE, INSERT
Common Event Infrastructure (CEI) CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, CREATE VIEW, ALTER SESSION, SELECT, UPDATE, DELETE, INSERT, CREATE TABLESPACE, CREATE PROFILE CREATE ROLE, CREATE PROCEDURE, CREATE TEMPORARY TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE PROCEDURE
Messaging Engines CREATE TABLE SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE
Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges. If you prefer not to give the user this privilege, you can avoid it, at a performance cost, using tuning. Refer to Setting tuning properties.
Process Server CREATE TABLE, SELECT, INSERT, UPDATE, ALTER LOCK TABLE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW, CREATE SEQUENCE
Performance Data Warehouse
CREATE TABLE, SELECT, INSERT, UPDATE, ALTER LOCK TABLE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE
SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW, CREATE SEQUENCE
All schemas or users creating or migrating IBM BPM profiles must have access to the DBMS_LOCK package. Set the Execute permission on the DBMS_LOCK package for the Oracle user or schema using the performance database as shown in the following example:
GRANT execute ON DBMS_LOCK TO < schema_name>
In this example, schema_name is the user ID that is used for the performance database.
Syntax for assigning rights to an Oracle user:
CREATE USER user_name IDENTIFIED BY user_password;
grant connect, resource, unlimited tablespace to user_name;
grant create view to user_name;
grant javauserpriv to user_name;
grant execute on dbms_lock to user_name;
Table 5 describes additional SQL Server database privileges for IBM BPM components.
The IBM BPM database user should be assigned to the following three roles:
The database must be created by the database administrator who can then assign these roles to the database user for IBM BPM.

db_ddladmin
db_datawriter
db_datareader
For information regarding the permissions that are provided by these roles, refer to Microsoft-specific documentation.
The following table describes the permissions required of each component for both install and runtime activities.

Detailed SQL Server database privileges
Component Installation privileges Runtime privileges
Common DB CREATE TABLE, ALTER TABLE, INSERT, CREATE USER, ALTER USER SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Space CREATE TABLE, ALTER TABLE, INSERT, CREATE USER, ALTER USER SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Process Choreographer CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE USER, CREATE PROCEDURE SELECT, UPDATE, DELETE, INSERT
Messaging Engines CREATE TABLE SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE
Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges.
Process Server CREATE TABLE, SELECT, INSERT, UPDATE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE USER, ALTER USER SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW
Performance Data Warehouse CREATE TABLE, SELECT, INSERT, UPDATE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE USER, ALTER USER SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW

Syntax for assigning rights to an SQL user and creating the schema for the PDWDB database:
USE  database_name
GO
CREATE USER  user_name FOR LOGIN  user_name
GO
CREATE SCHEMA  user_name AUTHORIZATION  user_name
GO
ALTER USER  user_name WITH DEFAULT_SCHEMA= user_name
Important: For Performance Data Warehouse users, note the following restrictions:

The user cannot be assigned to the system administrator (sysadmin) role.
The user must be mapped to the PDWDB database with the db_owner role.
The user must be mapped to the master database with the SqlJDBCXAuser role.

See the WebSphere Application Server page in the related reference.

User ID or schema name privileges
During the installation of IBM BPM, you can use the default schema name and user ID privileges to install your databases. However, your database design might require separate user ID or schema name privileges.

Plan the database configuration

Related reference:

Database privileges in WebSphere Application Server

Database type	Minimum privileges required to create objects in the database	Minimum privileges required to access objects in the database
DB2	The user ID needs CREATETAB authority on the database and CREATETS to create the table space. The user ID also needs CREATEIN and DROPIN privilege on the schema. The user ID needs system privileges CREATEDBA and CREATEDBC. The user ID also needs ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, and UPDATE privileges on the created tables.	The user ID needs SELECT, INSERT, UPDATE, and DELETE privileges on the tables. The user ID also needs EXECUTE ON PROCEDURE on stored procedures. Refer to Table 2 for detailed DB2 database privileges for IBM BPM and WebSphere Enterprise Service Bus components.
DB2 for z/OS	The user ID needs CREATETAB authority on the database and CREATETS to create the table space. The user ID also needs CREATEIN and DROPIN privilege on the schema. To create storage groups for the database, the user ID needs CREATESG, CREATEDBA, and CREATEDBC system privileges. The user ID also needs ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, and UPDATE privileges on the created tables.	The user ID needs SELECT, INSERT, UPDATE, and DELETE privileges on the tables. The user ID also needs EXECUTE ON PROCEDURE on stored procedures. Refer to Table 3 for detailed DB2 for z/OS database privileges for IBM BPM and WebSphere Enterprise Service Bus components.
Oracle	The user ID needs sufficient privilege to create relational tables and indexes in the data store schema. The database also needs a space quota in the default table space of the owner of that schema.	The user ID needs the SESSION privilege to connect to the database. If the same user ID owns both the data store schema, and the component that is connecting to the database, the user ID has sufficient privilege to manipulate the tables. Otherwise, the user ID needs SELECT, INSERT, UPDATE, ALTER, and DELETE object privileges on the tables that make up the data store, and the DROP ANY TABLE system privilege to enable the use of the TRUNCATE TABLE statement. The user ID also requires the CREATE INDEX privilege. You must create the Oracle database using a UTF-8 character set, which supports the other customer character sets that are supported by IBM BPM. See Table 4 for detailed Oracle database privileges for IBM BPM and WebSphere Enterprise Service Bus components.
SQL Server	The user ID ideally requires DB OWNER privileges on the data stores used for IBM BPM.	Configure the SQL Server for SQL Server and Windows authentication so that authentication to be based on an SQL server login ID and password. The user ID must be the owner of the tables, or a member of a group that has sufficient authority to issue TRUNCATE TABLE statements. See Table 5 for detailed SQL Server database privileges for IBM BPM and WebSphere Enterprise Service Bus components.

Component	Installation privileges	Runtime privileges
Common DB	CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, USAGE ON SEQUENCE
Business Space	CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Process Choreographer	CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE TABLESPACE, CREATE USER, CREATE PROCEDURE	SELECT, UPDATE, DELETE, INSERT
Messaging Engines	CREATE TABLE, CREATE INDEXTYPE	SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges.
Process Server or Performance Data Warehouse	Required to create the database: CREATEDBA CREATEDBC Required to populate the database with our schemas and stored procedures: CREATETAB CREATEIN DROPIN Additional required privileges on the created tables: ALTER DELETE INDEX INSERT REFERENCES SELECT UPDATE	Required privileges on the tables in the Process Server and Performance Data Warehouse databases: DELETE INSERT REFERENCES SELECT UPDATE The runtime user must have EXECUTE ON PROCEDURE privileges on the six stored procedures in the createProcedure_ProcessServer.sql script. The runtime user requires all of the listed privileges on the Performance Tracking Server database as well. In addition, the user also must be able to create new tables in the Performance Tracking database, requiring the CREATETAB privilege. To read the system metadata tables, the SELECT permission is required on syscat.tables, syscat.views, syscat.columns, syscat.tabconst, and sysibm.sysdummy1.

Component	Installation privileges	Runtime privileges
Common DB	CREATE TABLE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Space	CREATE TABLE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Process Choreographer	CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE TABLESPACE, CREATE USER, CREATE PROCEDURE	SELECT, UPDATE, DELETE, INSERT
Common Event Infrastructure (CEI)	CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, CREATE VIEW, ALTER SESSION, SELECT, UPDATE, DELETE, INSERT, CREATE TABLESPACE, CREATE PROFILE CREATE ROLE, CREATE PROCEDURE, CREATE TEMPORARY TABLESPACE	SELECT, UPDATE, DELETE, INSERT, CREATE PROCEDURE
Messaging Engines	CREATE TABLE	SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges. If you prefer not to give the user this privilege, you can avoid it, at a performance cost, using tuning. Refer to Setting tuning properties.
Process Server	CREATE TABLE, SELECT, INSERT, UPDATE, ALTER LOCK TABLE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW, CREATE SEQUENCE
Performance Data Warehouse	CREATE TABLE, SELECT, INSERT, UPDATE, ALTER LOCK TABLE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW, CREATE SEQUENCE All schemas or users creating or migrating IBM BPM profiles must have access to the DBMS_LOCK package. Set the Execute permission on the DBMS_LOCK package for the Oracle user or schema using the performance database as shown in the following example: GRANT execute ON DBMS_LOCK TO < schema_name> In this example, schema_name is the user ID that is used for the performance database.