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.
Table 1. Database privileges
Header 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® Business Process Manager 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 Business Process Manager 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 Business Process Manager.

See Table 4 for detailed Oracle database privileges for IBM Business Process Manager and WebSphere Enterprise Service Bus components.
SQL Server The user ID ideally requires DB OWNER privileges on the data stores used for IBM Business Process Manager. 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 Business Process Manager and WebSphere Enterprise Service Bus components.
Table 2 describes additional DB2 database privileges for IBM Business Process Manager components.
Table 2. 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
Business Process Choreographer Explorer reporting function CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE TABLESPACE, CREATE FUNCTION SELECT, UPDATE, DELETE, INSERT, and EXECUTE ON FUNCTION privileges on the functions listed in the createFunctionsSql_Observer.sql script
Messaging Engines CREATE TABLE, CREATE INDEXTYPE SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE
Note: Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges.
Process Server
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
  • 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 above 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.
Performance Data Warehouse
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 Business Process Manager components.
Table 3. 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
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
Business Process Choreographer Explorer reporting function CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE TABLESPACE, CREATE FUNCTION SELECT, UPDATE, DELETE, INSERT, and EXECUTE ON FUNCTION privileges on the functions listed in the createFunctionsSql_Observer.sql script.
Messaging Engines CREATE TABLE, CREATE INDEXTYPE SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE
Note: Messaging Engines use the TRUNCATE TABLE SQL statement, which may require the DROP ANY TABLE privilege. Refer to Database privileges.
Process Server
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
  • 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 above 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.
Performance Data Warehouse
Table 4 describes additional Oracle database privileges for IBM Business Process Manager components.
Important: 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.
Table 4. 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
Business Process Choreographer Explorer reporting function CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE TABLESPACE, CREATE FUNCTION SELECT, UPDATE, DELETE, INSERT, and EXECUTE ON FUNCTION privileges on the functions listed in the createFunctionsSql_Observer.sql script.
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
Note: 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, 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 Business Process Manager components.

The IBM Business Process Manager database user should be assigned to the following three roles:
Note: The database must be created by the database administrator who can then assign these roles to the database user for IBM Business Process Manager.
  • 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.
Table 5. 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
Note: 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
Business Process Choreographer Explorer reporting function is not supported on SQL Server.
Syntax for assigning rights to an SQL user:
USE database_name
GO
CREATE USER user_name FOR LOGIN user_name WITH DEFAULT_SCHEMA=user_name
GO
CREATE SCHEMA user_name AUTHORIZATION user_name
GO 
EXEC sp_addrolemember 'db_ddladmin', 'user_name';
EXEC sp_addrolemember 'db_datareader', 'user_name';
EXEC sp_addrolemember 'db_datawriter', 'user_name';</p>

For more information, see the WebSphere Application Server page in the related reference.

Parent topic: Planning your database configuration
Related reference:
Database privileges in WebSphere Application Server