Liquibase Enterprise was formerly known as Datical DB.

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Next »

See Database Users, Roles, and Passwords for a general discussion of database setup for Datical DB. 

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_COMPATIBILITY_VECTOR=ORA
$ db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES
$ db2stop
$ db2start

Role-Based Privileges for Service User

Datical DB 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 Datical DB 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 Datical DB.  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 Datical DB.

Granting Privileges to a Dedicated Datical DB 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 Datical DB, an administrator may create Datical DB projects and share them with Datical DB users.  The Datical DB 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 Datical DB 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)
    • 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
  • 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 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:

 Click here to expand...
  1. 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

  2. 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'
  • No labels

Copyright © Liquibase 2012-2022 - Proprietary and Confidential