Prerequisite

DB2 managed databases should be configured for Oracle compatibility mode before you can use Datical to forecast and deploy stored logic objects and/or to use the Stored Logic Validity Check feature. 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 Datical Role and granting privileges to a single Datical User.

Granting Privileges to a Datical Role

Granting privileges to a Datical 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 Datical 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. We strongly recommend to use runtime credentials because it is more secure than stored credentials (due to the possibility of the stored password being decoded).

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.
- SQLADM
  - Grants the authority required to monitor and tune SQL statements. (Recommended for roles on the reference database to report on active connections that cause the backup and restore operations to fail).
Schema Privileges (use “with grant option” if user/role needs to grant permissions to other users/roles)

Privileges for the REF Database

You must have SYSADM, SYSCTRL, or SYSMAINT authority to use the backup utility which is required by the Datical 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.

Steps to setup DB2 on Linux user with SYSADM authority:

Create user, e.g., datical_user using these steps:
1. Log in as root to the computer with IDM DB installed. To create a local user (linux limit up to and including 8 chars), at a command prompt, type the following command:
  useradd DATDB_USER
  passwd DATDB_USER
Identify the SYSADM group by typing the following command:
db2 get dbm cfg | grep group
1. Output may look like this:
2. Notice "db2iadm1" is listed as SYSADM group. You need to add user "DATDB_USER" to this group at the OS level (in Linux)
3. Log in as root to the computer with IBM DB2 installed.
  usermod -a -G db2iadm1 DATDB_USER

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

-- If the database is a reference database used in packaging, grant the 'SQLADM' privilege to monitor active connections during backup and retore
GRANT SQLADM 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'

Roles and Permissions for Liquibase Enterprise on DB2 LUW