Implementation Guide: Setting Up Database Permissions

Creating Database Passwords for Datical User Credentials

Available Characters for Database Passwords

• All alphabetic characters: a-z and A-Z
• All numeric characters: 0-9
• The following special characters: ~#$%*()-_+[]{}.?

Using any other characters (including spaces) will cause:

• Errors during packaging
• Errors when deploying any scripts packaged from the data_dml, ddl_direct, and sql_direct folders.
• Errors when deploying any scripts packaged with the data_dml, ddl_direct, and direct package methods.

Suggested Roles for Datical User Accounts

Datical recommends that clients create up to 4 database roles for use by the user accounts that are used to connect to the database using Datical.

DATICAL_ROLE - Used in all databases.

DATICAL_PACKAGER_ROLE - Used only in the reference / packaging database.  Has additional permissions required to access the trace file and perform backup & restore.

DATICAL_ROLE_SCHEMA_OWNER - (Oracle, single-schema only).  Used for when connecting as the schema owner.

DATICAL_PACKAGER_SCHEMA_OWNER - (Oracle, single-schema only).  Used only in the reference / packaging database.  Used for when connecting as the schema owner.

DATICAL_TRACK_ROLE - Used in all databases to create and update the Datical tracking tables.

DATICAL_AUDIT_ROLE - Used in the AuditDB to create and update the Datical audit tables.

Oracle

Roles and Permissions for Liquibase Enterprise on Oracle Database

SQL Server

Create Login User (and ensure the user has access to read the metadata)

USE [master]
GO
CREATE LOGIN [datical_login] WITH PASSWORD=N'password_goes_here', DEFAULT_DATABASE=[datical_db], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
GRANT VIEW ANY DEFINITION TO datical_login
GO

Create User (datical_user) for Databases Under Datical DB Management

USE [database_1]
GO
CREATE USER [datical_user] FOR LOGIN [datical_login]
GO
EXEC sp_addrolemember N'db_ddladmin', N'datical_user'
GO
EXEC sp_addrolemember N'db_datareader', N'datical_user'
GO
EXEC sp_addrolemember N'db_datawriter', N'datical_user'
GO
EXEC sp_addrolemember N'db_securityadmin', N'datical_user'
GO
EXEC sp_addrolemember N'db_backupoperator', N'datical_user'
GO
GRANT VIEW DATABASE STATE to [datical_user]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [datical_user]
GO

DB2

Overview of Datical DB User & Role Configuration for DB2

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 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 the Datical DB Deployment Plans 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).

Recommended Minimum Privileges for Datical DB Roles & Users

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.
• 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
    • Allows the specified authorization-names to GRANT the privileges to others.
• Data Privileges
  • DATAACCESS
    • 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 

Additional Permissions Needed to Run Datical Packager

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. 

Permissions needed to perform GRANTS

To perform grants - if you don't use the "WITH GRANT OPTIONS" on CREATIN/ALERIN/DROPIN, 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.

Sample Commands to Enable a Datical DB Role for a Schema

-- 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
 
-- 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

Sample Commands to Enable a Datical DB User for a Schema

-- 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
 
-- 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

Viewing User Permissions

-- Database permissions
select * from SYSCAT.DBAUTH  where GRANTEE = 'DATDB_USER'

-- Retrieve the privilege granted along with the object name, schema and type,
SELECT * FROM SYSIBMADM.PRIVILEGES WHERE AUTHIDTYPE = 'U' AND AUTHID = 'DATDB_USER'

Postgres

You can either execute packaging and deployment as a SUPERUSER, or as a limited user account.

If setting up a limited user account, you will need to do the following:

• Packaging (REF DBs) - The user account needs to be the database owner so it can backup and restore the database
• Deployment (All Other DBs) - The user account needs to have access to the database (schemas) but does not need to own it (them)

Permissions for SUPERUSER

ALTER USER myuser WITH SUPERUSER;

Permissions for non-superuser DATICAL_ROLE

GRANT USAGE ON SCHEMA my_schema TO DATICAL_ROLE;

<add list of all possible base schema permissions>

Permissions for non-superuser DATICAL_PACKAGER_ROLE

GRANT USAGE ON SCHEMA my_schema TO DATICAL_PACKAGER_ROLE;

<add list of additional permissions needed for packaging>