This document reflects roles and permissions for DB2 on Linux/Unix/Windows platforms.

Prerequisite

DB2 managed databases should be configured for Oracle compatibility mode before you can use Liquibase Enterprise's Stored Logic Validity Check. See IBM documentation.

$ su - db2inst1
$ db2start
$ db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES
$ db2stop
$ db2start

Role-Based Privileges for Service User

Liquibase Enterprise does not require special privileges above and beyond what is required to create, alter, and drop the most common database object types that support your database applications. There are two common implementation patterns for configuring Liquibase Enterprise access schemes in DB2: granting privileges to the Enterprise Role and granting privileges to a single Enterprise User.

Granting Privileges to a Enterprise Role

Granting privileges to a Enterprise Role is typically chosen in environments where individual team members will use unique credentials to access the database through Liquibase Enterprise. This simplifies the privilege management process by centralizing where GRANTS & REVOKES are performed. The role is then assigned to the user accounts that require database access with Liquibase Enterprise.

Granting Privileges to a Dedicated Liquibase Enterprise User (Service User)

Granting privileges to a dedicated Enterprise User allows an organization to drastically reduce the number of individuals with access to the database password. Because the database password is encoded in Liquibase Enterprise, an administrator may create Liquibase Enterprise projects and share them with Liquibase Enterprise users. The Liquibase Enterprise users can then perform database change tasks without having access to the database password.

Privileges for Managed Databases

To validate and automate database deployments Liquibase Enterprise needs to be able to connect to a database and to perform routine CREATE, ALTER, & DROP statements in the appropriate schema.

Database Privileges
- CONNECT
  - Grants the authority to access the database.
Schema Privileges (use “with grant option” if user/role needs to grant permissions to other users/roles)

- CREATEIN
  - Grants the privilege to create objects in the schema. Other authorities or privileges required to create the object (such as CREATETAB) are still required. The owner of an explicitly created schema automatically receives CREATEIN privilege. An implicitly created schema has CREATEIN privilege automatically granted to PUBLIC.
- ALTERIN
  - Grants the privilege to alter or comment on all objects in the schema. The owner of an explicitly created schema automatically receives ALTERIN privilege.
- DROPIN
  - Grants the privilege to drop all objects in the schema. The owner of an explicitly created schema automatically receives DROPIN privilege.
- WITH GRANT OPTION (so the user can grant permissions to other users/roles)
  - Allows the specified authorization-names to GRANT privileges to others.
    - Schema level CREATEIN, ALTERIN, DROPIN With Grant Option must be granted to the user for the schema. This will ensure the user has the privilege to grant permissions for any newly created objects.
    - To manage permissions for existing objects, the following privileges must be granted (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000966.html)
      - Object level CREATEIN, ALTERIN, DROPIN With Grant Option must be granted to the user for the appropriate objects on the database, or
      - Object level CONTROL privilege must be granted to the user for the appropriate objects on the database
Data Privileges
- DATAACCESS (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0053934.html)
  - Access and update data in user tables, views, and materialized query tables in a DB2 subsystem
  - Execute plans, packages, functions, and procedures.
  - Select privilege on all system catalog tables and views.
Tablespace Access
- GRANT USE OF TABLESPACE <tablespace_name> TO ROLE <role_name>
  - Provides access to the desired tablespace

Privileges for the REF Database

You must have SYSADM, SYSCTRL, or SYSMAINT authority to use the backup utility which is required by the Liquibase Enterprise Packager.

This permission is only needed on user accounts involved in packaging changes in the REF databases. It is not needed in any of the other databases in the environment.

Permissions needed to perform GRANTS

To perform grants - if you don't use the "WITH GRANT OPTIONS" on CREATEIN/ALTERIN/DROPIN or grant CONTROL, you will also need to grant ACCESSCTRL as documented in https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0053933.html.

Example: Creating a Role to Contain Privileges

In the following example you create the DATDB_ROLE, and grant privileges to it, then grant the DATDB_ROLE to user DATDB_USER.

-- Create the role ‘DATDB_ROLE’ and grant the ‘CONNECT’ privilege for the DB to be managed
CREATE ROLE DATDB_ROLE
GRANT CONNECT ON DATABASE TO ROLE DATDB_ROLE
  
-- Grant ‘ALTERIN’,’CREATEIN’ & ‘DROPIN’ privileges to ‘DATDB_ROLE’ for the desired schema
--
-- “with grant option” can be optionally added to the following statements if the role needs
-- to grant permissions to other users/roles
GRANT ALTERIN ON SCHEMA MYAPPSCHEMA TO ROLE DATDB_ROLE WITH GRANT OPTION
GRANT CREATEIN ON SCHEMA MYAPPSCHEMA TO ROLE DATDB_ROLE WITH GRANT OPTION
GRANT DROPIN ON SCHEMA MYAPPSCHEMA TO ROLE DATDB_ROLE WITH GRANT OPTION
 
-- To be able to grant permissions to existing objects, you will need to either
--   Provide object level CREATEIN, ALTERIN, DROPIN With Grant Option privileges, or
--   Provide object level CONTROL privilege
-- to the DATDB_ROLE
GRANT ALTERIN, CREATEIN, DROPIN ON TABLE1 TO ROLE DATDB_ROLE WITH GRANT OPTION
--GRANT CONTROL ON TABLE1 TO ROLE DATDB_ROLE WITH GRANT OPTION
 
-- Grant data access on a database to a specific role
GRANT DATAACCESS ON DATABASE TO ROLE DATDB_ROLE
  
-- Grant tablespace access to role
GRANT USE OF TABLESPACE <tablespace_name> TO ROLE DATDB_ROLE
  
-- Grant the role ‘DATDB_ROLE’ to user ‘DATDB_USER’
GRANT ROLE DATDB_ROLE TO USER DATDB_USER

Example: Granting Privileges Directly to a User

In this example you create user DATDB_USER and grant privileges directly to the user.

-- Grant the ‘CONNECT’ privilege for the DB to be managed
GRANT CONNECT ON DATABASE TO USER DATDB_USER
  
-- Grant ‘ALTERIN’,’CREATEIN’ & ‘DROPIN’ privileges to ‘DATDB_ROLE’ for the desired schema
--
-- “with grant option” can be optionally added to the following statements if the user needs
-- to grant permissions to other users/roles
GRANT ALTERIN ON SCHEMA MYAPPSCHEMA TO USER DATDB_USER WITH GRANT OPTION
GRANT CREATEIN ON SCHEMA MYAPPSCHEMA TO USER DATDB_USER WITH GRANT OPTION
GRANT DROPIN ON SCHEMA MYAPPSCHEMA TO USER DATDB_USER WITH GRANT OPTION
 
-- To be able to grant permissions to existing objects, you will need to either
--   Provide object level CREATEIN, ALTERIN, DROPIN With Grant Option privileges, or
--   Provide object level CONTROL privilege
-- to the DATDB_USER
GRANT ALTERIN, CREATEIN, DROPIN ON TABLE1 TO ROLE DATDB_USER WITH GRANT OPTION
--GRANT CONTROL ON TABLE1 TO ROLE DATDB_USER WITH GRANT OPTION
 
-- Grant data access on a database to a specific role
GRANT DATAACCESS ON DATABASE TO USER DATDB_USER
  
-- Grant tablespace access to role
GRANT USE OF TABLESPACE <tablespace_name> TO USER DATDB_USER

Example: Viewing User Permissions

The following example shows two ways to view privileges for user DATDB_USER.

-- Retrieve database permissions for user DATDB_USER
select * from SYSCAT.DBAUTH  where GRANTEE = 'DATDB_USER'
 
-- Retrieve privilege granted to DATDB_USER along with the object name, schema, and type
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE AUTHIDTYPE = 'U' AND AUTHID = 'DATDB_USER'

Liquibase Enterprise Documentation Home

DB2 on zOS Roles and Permissions for Liquibase Enterprise