Oracle database privileges

Edit online

Draft comment:
This topic only applies to BAW, and is located in the BAW repository. Last updated on 2025-03-13 12:15

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

In addition to the detailed privileges listed on this page, every IBM® Business Automation Workflow user must have the following privileges:

The connect privilege
Quota (preferably unlimited) on the table spaces of tables the user owns
Every BPM user must have the ability to recover XA transactions. If required, the underlying application server infrastructure triggers XA recovery automatically, which requires the following privileges. If you do not want to assign these XA privileges to the regular users, you can specify an XA authentication alias at the data source level and assign the XA privileges to the dedicated user:
- Select on pending_trans$
- Select on dba_2pc_pending
- Select on dba_pending_transactions
- Execute on dbms_xa

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 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. See the following table for detailed Oracle database privileges for IBM Business Automation Workflow and WebSphere® Enterprise Service Bus components.

The following table describes more Oracle 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.

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 2. Detailed Oracle database privileges
Component	Installation and upgrade privileges	Runtime privileges
Common DB	The following privileges are for the situation when a DBA creates users and all the database objects for those users. The DBA requires the following privileges: CREATE USER, ALTER USER, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, CREATE ANY INDEX, DROP ANY INDEX, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY PROCEDURE, DROP ANY PROCEDURE, CREATE ANY SEQUENCE The following privileges are for the situation when a DBA has created a Common DB user and that user creates the database objects, owns them, and uses them at runtime: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE	The following privileges are for the situation when a DBA has created a Common DB user and that user creates the database objects, owns them, and uses them at runtime. explicit privileges: CREATE PROCEDURE CREATE VIEW implicit privileges (included in the ownership of the database objects): SELECT INSERT UPDATE DELETE The runtime user must have USAGE ON SEQUENCE privileges on all sequences in the createSchema_*.sql script for the common DB.
Business Process Choreographer	The following privileges are for the situation when a DBA creates users and all the database objects for those users. The DBA requires the following privileges: CREATE USER, ALTER USER, CREATE TABLESPACE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, CREATE ANY INDEX, DROP ANY INDEX, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY PROCEDURE, DROP ANY PROCEDURE The following privileges are for the situation when a DBA has created a Business Process Choreographer user and that user creates the database objects, owns them, and uses them at runtime: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE.	The following privileges are for the situation when a DBA has created a Business Process Choreographer user and that user creates the database objects, owns them, and uses them at runtime. explicit privileges: CREATE PROCEDURE CREATE VIEW implicit privileges (included in the ownership of the database objects): SELECT INSERT UPDATE DELETE
Messaging Engines	The following privileges are for the situation when a DBA creates users and all the database objects for those users. The DBA requires the following privileges: CREATE USER, ALTER USER, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, CREATE ANY INDEX, DROP ANY INDEX The following privileges are for the situation when a DBA has created a Messaging Engines user and that user creates the database objects, owns them, and uses them at runtime: CREATE TABLE	The following privileges are for the situation when a DBA has created a Messaging Engines user and that user creates the database objects, owns them, and uses them at runtime. explicit privileges: DROP ANY TABLE implicit privileges (included in the ownership of the database objects): SELECT INSERT UPDATE DELETE Tip: Messaging Engines use the TRUNCATE TABLE SQL statement, which might require the DROP ANY TABLE privilege. See Database privileges . If you prefer not to give the user this privilege, you can avoid it, at a performance cost, by using tuning. See Database privileges in WebSphere Application Server .
Process Server	The following privileges are for the situation when a DBA creates users and all the database objects for those users. The DBA requires the following privileges: CREATE USER, ALTER USER, CREATE TABLESPACE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE, CREATE ANY INDEX, DROP ANY INDEX, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY PROCEDURE, DROP ANY PROCEDURE, CREATE ANY SEQUENCE, DROP ANY SEQUENCE The following privileges are for the situation when a DBA has created a Process Server user and that user creates the database objects, owns them, and uses them at runtime. explicit privileges: CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE VIEW implicit privileges (included in the ownership of the database objects): INSERT, UPDATE, DELETE , ALTER, DROP	The following privileges are for the situation when a DBA created a Process Server user and that user creates the database objects, owns them, and uses them at runtime. explicit privileges: CREATE TABLE CREATE PROCEDURE CREATE SEQUENCE CREATE VIEW implicit privileges (included in the ownership of the database objects): SELECT INSERT UPDATE DELETE
Performance Data Warehouse	The following privileges are for the situation when a DBA creates users and all the database objects for those users. The DBA requires the following privileges: CREATE USER, ALTER USER, CREATE TABLESPACE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE, CREATE ANY INDEX, DROP ANY INDEX, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY PROCEDURE, DROP ANY PROCEDURE, CREATE ANY SEQUENCE, DROP ANY SEQUENCE The following privileges are for the situation when a DBA has created a Performance Data Warehouse user and that user creates the database objects, owns them, and uses them at runtime. explicit privileges: CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE VIEW implicit privileges (included in the ownership of the database objects): INSERT, UPDATE, DELETE, ALTER, DROP	The following privileges are for the situation when a DBA created a Performance Data Warehouse user and that user creates the database objects, owns them, and uses them at runtime. explicit privileges: CREATE TABLE CREATE PROCEDURE CREATE SEQUENCE CREATE VIEW implicit privileges (included in the ownership of the database objects): SELECT INSERT UPDATE DELETE All schemas or users that are 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 that is 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.
Content	The following privileges are for the situation when a DBA creates users and all the database objects for those users. The DBA requires the following privileges: CREATE USER, ALTER USER, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE, CREATE ANY INDEX, DROP ANY INDEX, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY PROCEDURE, DROP ANY PROCEDURE, CREATE ANY SEQUENCE The following privileges are for the situation when a DBA has created a Content DB user and that user creates the database objects, owns them, and uses them at runtime: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE	CREATE SESSION CREATE TABLE CREATE VIEW CREATE SEQUENCE Alter user set QUOTA UNLIMITED on all tablespaces used by db user SELECT on pending_trans$ SELECT on dba_2pc_pending SELECT on dba_pending_transactions SELECT on DUAL SELECT on product_component_version SELECT on USER_INDEXES EXECUTE on dbms_xa
BPM document store		CREATE SESSION CREATE TABLE CREATE VIEW CREATE SEQUENCE Alter user set QUOTA UNLIMITED on all tablespaces used by db user SELECT on pending_trans$ SELECT on dba_2pc_pending SELECT on dba_pending_transactions SELECT on DUAL SELECT on product_component_version SELECT on USER_INDEXES EXECUTE on dbms_xa 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.

For information about assigning rights to an Oracle user, see the topic Configuring profiles, databases, and deployment environments for Oracle on Windows.