Db2 database privileges

Edit online

Set database privileges to determine the authority that you must have to create or access your data store tables for IBM® 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.

There are minimum database privileges that are required to access objects in the database. For DBA users, you can grant DBADM authority. For non-DBA users, you can use the following commands to grant them the appropriate privileges: 
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@;
grant usage on sequence @SEQUENCE_NAME@ to user @DB_USER@;
The following table describes more Db2 database privileges for IBM Business Automation Workflow 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 1. Detailed DB2® database privileges
Component Installation and upgrade 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 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.
Content CREATE TABLE, CREATE INDEXTYPE, ALTER TABLE, INSERT, CREATE SEQUENCE, CREATE USER, ALTER USER, CREATE TABLESPACE
  • 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
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
Notes:
  • When you configure your database for the BPM document store, a database capability that is named EmbeddedECM is used. The privileges listed for the BPM content store are required for the database in the property file containing the EmbeddedECM capability.
  • The Global Configuration Database (GCD) tables and the object stores' tables are created by FileNet® at run time, not by the Business Automation Workflow database scripts.
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 IMMEDIATE;
connect reset;

In the previous 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@;
grant usage on sequence @SEQUENCE_NAME@ to user @DB_USER@;

In the previous 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.