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.
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. |
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:
Required to populate the database with our schemas and
stored procedures:
Additional required privileges on the created tables:
|
Required privileges on the tables in the
Process Server and Performance Data Warehouse databases:
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 |
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. |
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.
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.