Overview

There are situations where grants or aliases may need to be created for all objects that meet a particular criteria such as belonging to a specific schema. This can be accomplished by creating a changeset that is executed at the end of every deployment to apply the appropriate grants and aliases.

Executing pl/sql scripts for db2 can present a challenge because the default SQL terminator “;” must be changed to another character so that it does not conflict with the “;” used within the pl/sql code blocks.

There are two approaches to handling the SQL terminator:

Utilize the “Execute a SQL script file using JDBC”/“Custom SQL (External File)” change set type and set a different value for “End Delimiter”.
Create a stored procedure, then call the stored procedure from a simple script which does not require internal “;” characters.

Approach 1

Step 1 – Create a Script to Perform Grant/Alias Operations

Create a script that will generate the desired grant or alias statements. The script can utilize change log properties (https://datical-cs.atlassian.net/wiki/spaces/DDOC/pages/896569871 ) to customize values for different steps in the pipeline. The following is an example of a script to grant SELECT access to all tables in a specified schema. The criteria for CREATOR and GRANTEE will be specified by properties from the project changelog. Do not include a terminator at the end of the file. The default terminator, ';' within the code block will cause errors, so a different terminator will be specified as part of the changeset definition. Save the script to a location outside the SQL SCM for the Datical Project since we will manually add the changeset to the changelog.

BEGIN
  DECLARE v_stmt        VARCHAR(200);
  FOR v1 as
      c1 CURSOR FOR SELECT rtrim(ltrim(CREATOR)) CREATOR,NAME from SYSIBM.systables where CREATOR in ('${p_creator}')
      DO
        set v_stmt = 'grant select on '||CREATOR||'.'||NAME || ' to ' || '${p_grantee}';
        execute immediate v_stmt;
      END FOR;
END

Step 2 – Create Changelog Properties for Replacement Variables

If the script requires different values based upon context or labels, create the required properties. Go the Project Settings in the Datical GUI and select Manage Properties.

Add the properties to be used by the script along with the Context or Label where the property value should be applied. Within the script specify the Property Key using the syntax ${property_key}.

Step 3 – Add the ChangeSet to the ChangeLog

From the Datical GUI, Select “Add a Change Set”.

Scroll down and select the “Execute a SQL script file using JDBC”/“Custom SQL (External File)” change type. Note that “Execute a SQL script file using JDBC” is the newer name, and “Custom SQL (External File)” is the older name for the same change type.

Browse to select the file. Change the delimiter to “@”. Deselect Split Statements.

On the next screen select Finalize.

On the Create Change Set screen:

Enter the ChangeSet ID (any text string) and the Author.

Set the Execution Settings to:

“Redeploy this Change Set every time the contents are modified” – Allows modification of the SQL file saved to the resources directory. Without this there will be “checksum” errors if the file is modified.

“Deploy this Change Set every time a deployment is performed” – Changeset will be run for every Deployment.

Set Deploy Order to “After Other Change Sets”.

Save changes by selecting: File >> Save.

Add, Commit, and Push the changes to the SCM.

Step 5 – Test the New Change Set

Package and deploy a simple “CREATE TABLE” script. Verify that the grants are applied to the new table.

Approach 2

Step 1 – Deploy a stored procedure following the normal process.

Sample Stored Procedure

create or replace procedure myschema.table_grants
(p_creator varchar2(30),
 p_grantee varchar2(30) )
BEGIN
  DECLARE v_stmt        VARCHAR(200);
  FOR v1 as
      c1 CURSOR FOR SELECT rtrim(ltrim(CREATOR)) CREATOR,NAME from SYSIBM.systables where CREATOR in (p_creator)
      DO
        set v_stmt = 'grant select on '||CREATOR||'.'||NAME || ' to ' || p_grantee;
        execute immediate v_stmt;
     END FOR;
END

Step 2 – Create a Change Set to Call the Stored Procedure

Sample SQL Script – notice that the command is NOT terminated by “;”.

call table_grants('${p_creator}','${p_grantee}')

Follow the steps above to create changelog properties for replacement values, and use the Change Set Wizard to create a change set that runs after each deployment.

Liquibase Enterprise Knowledge Base

How To: Generate Auto Permissions and Aliases for DB2