DB2 database privileges

Set database privileges to determine the authority that you must have to create or access your data store tables for DB2® databases.

When you create database schemas using the typical installation or database scripts that are generated using the BPMConfig command-line utility, 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
Minimum privileges that are required to create objects in the database	Minimum privileges that are required to access objects in the database
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 privileges on stored procedures. See the following table for detailed DB2 database privileges for IBM® Business Process Manager components.

The following table describes more DB2 database privileges for IBM Business Process Manager components. The installation privileges are the privileges that are required to install and configure the product. The runtime privileges are the database privileges that are required to run the product.

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 The runtime user must have USAGE ON SEQUENCE privileges on all sequences in the createSchema_*.sql script for the common DB.
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 Note: Messaging Engines use the TRUNCATE TABLE SQL statement, which might require the DROP ANY TABLE privilege. See 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`.
IBM BPM document store		CONNECT ON DATABASE CREATETAB USE OF TABLESPACES SELECT on SYSIBM.SYSVERSIONS SELECT on SYSCAT.DATATYPES SELECT on SYSCAT.INDEXES SELECT on SYSIBM.SYSDUMMY1 USAGE on workload SYSDEFAULTUSERWORKLOAD IMPLICIT_SCHEMA on DATABASE When you configure your database for the IBM BPM document store, a database capability that is named EmbeddedECM is used. The privileges listed for the IBM BPM content store are required for the database in the property file containing the EmbeddedECM capability.

Use the following syntax to create a database and grant DBADM authority to a DBA user:

create database @DB_NAME@ automatic storage yes  using codeset UTF-8 territory US pagesize 32768;
connect to @DB_NAME@;
grant dbadm on database to user @DB_USER@;
UPDATE DB CFG FOR @DB_NAME@ USING LOGFILSIZ 16384 DEFERRED; 
UPDATE DB CFG FOR @DB_NAME@ USING LOGSECOND 64 DEFERRED;
connect reset;

In the above example, replace @DB_NAME@ with the name that you want to use for the created database and replace @DB_USER@ with the user name that you want to use for the database.

It is generally not recommended to grant DBADM authority to a non-DBA user. Instead, you can use the following syntax to grant the appropriate privileges to a non-DBA user without actually granting direct DBADM authority:

db2 list tables for schema @DB_USER@
grant select, update, delete, insert on @TABLE_NAME@ to user @DB_USER@;
grant references on @TABLE_NAME@ to user @DB_USER@;
create @SEQUENCE_NAME@ to user @DB_USER@;
grant usage on sequence @SEQUENCE_NAME@ to user @DB_USER@;

In the above example, replace the variables with the following values:

Replace @DB_USER@ with the name of the non-DBA user.
Replace @TABLE_NAME@ with the name of the database table.
Replace @SEQUENCE_NAME@ with the name of the sequence.