Explained: Templater stage

{
  "template": "create_schema.sql.j2", (1)
  "vars": { (2)
    "var_env_list": ["T"],
    "var_context": "DWH",
    "var_rl_adm_type": "ADMIN",
    "var_db": "DB_DWH_T",
    "var_sch": "DWH_0_ADMIN",
    "var_enable_managed_access": "TRUE"
  }
}

/**
 * Derived from template: {{ template }}
 *
 */

{% for var_env in var_env_list %}
/**
 * ENVIRONMENT: {{ var_env }}
 */
SET var_context              = '{{ var_context }}';
SET var_env                  = '{{ var_env }}';
SET var_rl_adm_type          = '{{ var_rl_adm_type }}';
-- We intentionally wrote this concatenated line. It will be rewritten in the next section
SET var_rl_adm               = 'RL_'||$var_context||'_'||$var_env||'_'||$var_rl_adm_type;
SET var_rl_sec               = 'SECURITYADMIN';
SET var_rl_sys               = 'SYSADMIN';
SET var_db                   = '{{ var_db }}';
SET var_sch                  = '{{ var_sch }}';

/**
 * Create schemas
 */
USE    ROLE                     IDENTIFIER($var_rl_adm);
USE    DATABASE                 IDENTIFIER($var_db);
CREATE SCHEMA IF NOT EXISTS     IDENTIFIER($var_sch){{ ' WITH MANAGED ACCESS' if var_enable_managed_access else '' }}
;

{%- endfor %}

/**
 * Derived from template: create_schema.sql.j2
 *
 */

/**
 * ENVIRONMENT: T
 */
SET var_context              = 'DWH';
SET var_env                  = 'T';
SET var_rl_adm_type          = 'ADMIN';
-- We intentionally wrote this concatenated line. It will be rewritten in the next section
SET var_rl_adm               = 'RL_'||$var_context||'_'||$var_env||'_'||$var_rl_adm_type;
SET var_rl_sec               = 'SECURITYADMIN';
SET var_rl_sys               = 'SYSADMIN';
SET var_db                   = 'DB_DWH_T';
SET var_sch                  = 'DWH_0_ADMIN';

/**
 * Create schemas
 */
USE    ROLE                     IDENTIFIER($var_rl_adm);
USE    DATABASE                 IDENTIFIER($var_db);
CREATE SCHEMA IF NOT EXISTS     IDENTIFIER($var_sch) WITH MANAGED ACCESS
;

An example from practise.

A user files an issue that he/she has insufficient privileges to drop a table. A database administrator will debug the problem as follows:

1. Find what roles that are granted to the user. If a required role is missing, than:
  - Find the script where the role should have been assigned to the user
  - Either the script is correct, and it should be (re)deployed
  - Or the script is missing the statement. Add the statement, and commit the change.
    After a review, deploy the change.
2. Find the priviliges that are granted to the user through a direct role
  - Find the script where the privilege should have been granted to one of the roles assigned to the user
  - Either the script is correct, and it should be (re)deployed
  - Or the script is missing a grant-statement. Add the statement, and commit the change.
    After a review, deploy the change.
3. Find the priviliges that are granted to the user through an implicit role
  - Find the script where the privilege should have been granted to one of the roles assigned to the user
  - Either the script is correct, and it should be (re)deployed
  - Or the script is missing a grant-statement. Add the statement, and commit the change.
    After a review, deploy the change.

In order for the database administrator to perform this task efficiently, he needs to be able to find the responsible scripts in each of the three steps in a mouse click.

Because searchability is in many data environments poorly managed, debugging a problem and correcting the correct script, is a too big of a hessle. Instead, a data administrator queries the database to see what priviliges the user is missing. He creates a manual statement and deploys this script on the TAP environments.

SET var_context              = 'DWH';
SET var_env                  = 'T';
SET var_rl_adm_type          = 'ADMIN';
-- We intentionally wrote this concatenated line. It will be rewritten in the next section
SET var_rl_adm               = 'RL_'||$var_context||'_'||$var_env||'_'||$var_rl_adm_type;

/**
 * Derived from template: create_schema.sql.j2
 *
 */

/**
 * ENVIRONMENT: T
 */

/**
 * Create schemas
 */
USE    ROLE                     IDENTIFIER('RL_DWH_T_ADMIN');
USE    DATABASE                 IDENTIFIER('DB_DWH_T');
CREATE SCHEMA IF NOT EXISTS     IDENTIFIER('DWH_0_ADMIN') WITH MANAGED ACCESS
;

{
  "naming_conventions": {
    "role_convention": "RL_{{ var_context }}_{{ var_env }}_{{ var_rl_type }}",
    "database_convention": "DB_{{var_context}}_{{var_env}}",
    "schema_convention": "{{var_context}}_{{var_sch_num}}_{{var_sch_name}}",
    "my_convention": "{{var_a}}_..._{{var_i}}_..._{{var_z}}"
  }
}

{
  "template": "create_schema.sql.j2", (1)
  "naming_conventions": { (2)
    "role_convention": "RL_{{ var_context }}_{{ var_env }}_{{ var_rl_type }}",
    "database_convention": "DB_{{var_context}}_{{var_env}}",
    "schema_convention": "{{var_context}}_{{var_sch_num}}_{{var_sch_name}}"
  },
  "vars": {
    "var_env": "T", (3)
    "var_context": "DWH", (4)
    "var_rl_adm": { "role_convention":      { "var_rl_type": "ADMIN"   } }, (5)
    "var_db":     { "database_convention":  {}}, (6)
    "var_sch":    { "schema_convention":    { "var_sch_num": "0", "var_sch_name": "ADMIN" }}, (7)
    "var_enable_managed_access": "TRUE"
  }
}