Skip to content

Configure Your Snowflake Environment

This section describes the steps required to configure your Snowflake environment for Okera policy enforcement processing.

Overview

Okera provides a script you can use to configure your Snowflake environment for Okera policy management. The script is provided in this section. After tailoring the script, you can run it in a Snowsight workspace (not a regular Snowflake workspace). It creates a:

  • Snowflake database in which to store Okera user-defined functions (default database name OKERA_UDFS). Okera privacy and security functions are implemented in Snowflake as UDFs. See Privacy and Security Functions for a summary of the functions and their usage.
  • Snowflake user dedicated for use by Okera (default user name SERVICE_OKERA_USER). This user is the same user identified in the Okera Snowflake connection.
  • Snowflake role dedicated for use by Okera (default role name SERVICE_OKERA_ROLE). This role is the same role used in the Okera Snowflake connection.

The script grants:

  • Minimal USAGE and SELECT permissions to the Okera role for all the existing databases, schemas, tables, and views in your Snowflake warehouse.
  • The Okera role (SERVICE_OKERA_ROLE) permission to create policies for your Snowflake schemas.
  • Okera access to the SNOWFLAKE database in the Snowflake account to read and parse SQL statements executed there. The script does this in a single run.

Note: If you add a new Snowflake database after this script is run, you must run a separate Okera-supplied script to add the new Snowflake database to Okera policy management. See Add Okera Policy Synchronization Support for a New Database.

Complete the following steps to copy, tailor, and run the script provided in this section to set up your Snowflake environment for Okera policy management.

Step 1. Review the Prerequisites and Limitations

Currently, this script does not support Snowflake database, schema, table, or view names that include single or double quotes. For example, if you have a Snowflake database named SARAH'S_DATABASE, the script will fail. If any of your database, schema, table, or view names include single or double quotes, you must configure your Snowflake environment manually. Contact Okera for these manual steps.

In addition, the following prerequisites must be met to use this script:

  1. You must be logged into Snowsight as a user with a Snowflake account operating with the ACCOUNTADMIN role.
  2. You must tailor the script for your Snowflake environment. This is explained in Step 3. Tailor the Script.

Step 2. Copy the Script

Copy the script below to your clipboard by selecting the icon in the upper right corner. Then paste the script into a Snowsight worksheet. Be sure you are using the ACCOUNTADMIN role.

Warning

We recommend that you only modify this script as described in Step 3. Tailor the Script. Modifying it in other ways is not recommended and the results might not be optimal or correct.

-- This SQL script is a complete script that sets up Okera in Snowflake
-- in one step with configurable inputs and reasonable defaults.

declare
-- IMPORTANT!!! Your Snowflake warehouse name must be specified. 
  sf_warehouse_name string default 'YOUR_SF_WAREHOUSE_NAME';

-- Specify the Snowflake role dedicated for Okera.
  okera_role string default 'SERVICE_OKERA_ROLE';

-- Specify the Snowflake role used by all your Snowflake users. Snowflake's default is PUBLIC.
  public_role string default 'PUBLIC';

-- Specify an existing Snowflake user to whom the Snowflake SERVICE_OKERA_ROLE should be GRANTed.
  okera_user string default 'SERVICE_OKERA_USER';

-- Specify the default password for the Okera service user.
  okera_user_password string default 'CHANGE_ME!';

-- Specify whether a password change should be required for the Okera service user on first login.
  require_password_change boolean default FALSE;

-- Specify the region for the S3 release bucket for your Okera release cluster. 
  release_bucket string default 'okera-release-uswest';

-- The version specified here must match the version number of your Okera cluster.  
  version string default '2.10.0';

-- These variables must match the settings of the Okera configuration parameters 
-- EXTERNAL_OKERA_SECURE_POLICY_DB (default OKERA_UDFS) and
-- EXTERNAL_OKERA_SECURE_POLICY_SCHEMA (default PUBLIC). These variables are used to create
-- the Snowflake database and schema dedicated to Okera and used to store Okera UDFs.
  okera_db string default 'OKERA_UDFS';
  okera_schema string default 'PUBLIC';

-- DO NOT CHANGE ANY VARIABLES BEYOND THIS POINT

  okera_schema_full string default '"' || :okera_db || '"' || '.' || '"' || :okera_schema || '"';
  okera_warehouse_metering_view string default :okera_schema_full || '.OKERA_WAREHOUSE_METERING_VIEW';
  okera_stage string default :okera_schema_full || '.s3_okera_release_client';
  stage_url string default 's3://' || :release_bucket || '/' || :version || '/client';
  imports_path string default '@' || :okera_db || '.' || :okera_schema || '.s3_okera_release_client/okera-hive-udfs.jar';

  fn_name string;
  fn_stmt string;
begin

  CREATE ROLE IF NOT EXISTS identifier(:okera_role);
  GRANT USAGE ON WAREHOUSE identifier(:sf_warehouse_name) TO ROLE identifier(:okera_role);
  fn_stmt := $$CREATE USER IF NOT EXISTS identifier('$$ || :okera_user || $$')
        PASSWORD = '$$ || :okera_user_password || $$'
        MUST_CHANGE_PASSWORD = $$ || :require_password_change || $$
        DEFAULT_WAREHOUSE = '$$ || :sf_warehouse_name || $$'
        DEFAULT_ROLE = '$$ || :okera_role || $$'
        DEFAULT_NAMESPACE = '$$ || :okera_schema_full || $$'
        COMMENT = 'Okera policy service user'
        $$;
  execute immediate :fn_stmt;
  GRANT ROLE identifier(:okera_role) TO USER identifier(:okera_user);

  GRANT MANAGE GRANTS ON ACCOUNT TO identifier(:okera_role);
  GRANT CREATE ROLE ON ACCOUNT TO identifier(:okera_role);
  GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE identifier(:okera_role);
  GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE identifier(:okera_role);
  GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE identifier(:okera_role);

  -- Add a filter to show databases statement if one is needed in your Snowflake environment.
  -- For example: show databases like 'OKERA_TRIAL%';
  show databases;
  declare
    db_name string;
    -- Filter out all dbs with external origin - we can't really apply grants to those
    c1 cursor for select * from table(result_scan(last_query_id())) WHERE "origin" = '';
  begin
    for rec in c1 do
      db_name := '"' || rec."name" || '"';
      GRANT USAGE ON DATABASE identifier(:db_name) TO ROLE identifier(:okera_role);
      GRANT USAGE ON FUTURE SCHEMAS IN DATABASE identifier(:db_name) TO ROLE identifier(:okera_role);

      show schemas in identifier(:db_name);
      declare
        name string;
        -- Filter out Information schemas
        c2 cursor for select "database_name", "name" from table(result_scan(last_query_id())) where "name" != 'INFORMATION_SCHEMA';
      begin
        for rec in c2 do
          name := '"' || rec."database_name" || '"' || '.' || '"' || rec."name" || '"';
          GRANT USAGE ON SCHEMA identifier(:name) TO ROLE identifier(:okera_role);
          GRANT CREATE ROW ACCESS POLICY ON SCHEMA identifier(:name) TO ROLE identifier(:okera_role);
          GRANT CREATE MASKING POLICY ON SCHEMA identifier(:name) TO ROLE identifier(:okera_role);
          GRANT SELECT ON ALL TABLES IN SCHEMA identifier(:name) TO ROLE identifier(:okera_role);
          GRANT SELECT ON FUTURE TABLES IN SCHEMA identifier(:name) TO ROLE identifier(:okera_role);
          GRANT SELECT ON FUTURE VIEWS IN SCHEMA identifier(:name) TO ROLE identifier(:okera_role);
          GRANT SELECT ON ALL VIEWS IN SCHEMA identifier(:name) TO ROLE identifier(:okera_role);
        end for;
      end;
    end for;
  end;

  -- Starting here, we create the secure DB and grant the service role
  -- (the one used for the connection) to use it.
  CREATE DATABASE IF NOT EXISTS identifier(:okera_db);
  CREATE SCHEMA IF NOT EXISTS identifier(:okera_schema_full);
  GRANT ALL ON DATABASE identifier(:okera_db) TO ROLE identifier(:okera_role);
  GRANT ALL ON SCHEMA identifier(:okera_schema_full) to ROLE identifier(:okera_role);
  CREATE VIEW IF NOT EXISTS identifier(:okera_warehouse_metering_view) as SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY;


  -- Create the Okera S3 stage, which we to reference our Okera
  -- UDF jar file from S3. This will be used when registering the actual
  -- UDFs in subsequent calls.

  fn_stmt := 'CREATE OR REPLACE stage identifier(?)
    url=' || :stage_url;
  execute immediate :fn_stmt using (okera_stage);

  --$$$$

  -- Starting from here, we register our core UDFs (and their per-type
  -- overloads) and we grant the service role (the one used for the connection)
  -- to use the secure DB, as well as the UDFs in it. Since the service role
  -- "owns" (in Snowflake terms) the UDFs and the policy objects that are created
  -- dynamically, this allows for proper usage of them without needing to grant
  -- them on a per-user basis.

  fn_name := :okera_schema_full || '.phi_age';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input int)
  returns int
  language java
  imports = ('$$ || :imports_path || $$')
  handler='com.okera.core.udf.PhiAgeUDF.evaluateInteger'$$;
  execute immediate :fn_stmt;

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(int) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;


  --$$$$
  fn_name := :okera_schema_full || '.phi_date';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input Date)
  returns Date
  language java
  imports = ('$$ || :imports_path || $$')
  handler='com.okera.core.udf.PhiDateUDF.evaluateDate'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(Date) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$
  fn_name := :okera_schema_full || '.phi_dob';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input Date)
  returns Date
  language java
  imports = ('$$ || :imports_path || $$')
  handler='com.okera.core.udf.PhiDobUDF.evaluateDate'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(Date) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.phi_zip3';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input string)
  returns string
  language java
  imports = ('$$ || :imports_path || $$')
  handler='com.okera.core.udf.PhiZip3UDF.evaluateString'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.mask_ccn';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input string) RETURNS string
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.MaskCcnUDF.evaluateString'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.mask';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input string) RETURNS string
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.MaskUDF.evaluateString'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input boolean) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateBoolean'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(boolean) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input tinyint) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateShort'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(tinyint) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input smallint) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateShort'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(smallint) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input int) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateInteger'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(int) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input bigint) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateLong'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(bigint) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input string) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateString'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input float) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateFloat'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(float) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input double) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateDouble'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(double) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input date) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateDate'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(date) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.sha2';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input timestamp) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.Sha2UDF.evaluateTimestamp'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(timestamp) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input boolean, seed string) RETURNS boolean
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateBoolean'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(boolean, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input tinyint, seed string) RETURNS tinyint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateShort'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(tinyint, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input smallint, seed string) RETURNS smallint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateShort'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(smallint, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input int, seed string) RETURNS int
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateInteger'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(int, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input bigint, seed string) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateLong'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(bigint, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input string, seed string) RETURNS string
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateString'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(string, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input float, seed string) RETURNS float
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateFloat'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(float, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input double, seed string) RETURNS double
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateDouble'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(double, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input date, seed string) RETURNS date
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateDate'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(date, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.tokenize';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input timestamp, seed string) RETURNS timestamp
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.TokenizeUDF.evaluateTimestamp'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(timestamp, string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input boolean) RETURNS boolean
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateBoolean'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(boolean) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input tinyint) RETURNS tinyint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateShort'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(tinyint) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input smallint) RETURNS smallint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateShort'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(smallint) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input int) RETURNS int
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateInteger'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(int) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input bigint) RETURNS bigint
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateLong'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(bigint) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input string) RETURNS string
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateString'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(string) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input float) RETURNS float
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateFloat'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(float) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input double) RETURNS double
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateDouble'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(double) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input date) RETURNS date
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateDate'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(date) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;

  --$$$$

  fn_name := :okera_schema_full || '.zero';
  fn_stmt := $$CREATE OR REPLACE FUNCTION identifier('$$ || :fn_name || $$')(input timestamp) RETURNS timestamp
  LANGUAGE java
  imports = ('$$ || :imports_path || $$')
  HANDLER='com.okera.core.udf.ZeroUDF.evaluateTimestamp'$$;
  execute immediate :fn_stmt using (fn_name,imports_path);

  fn_stmt := $$GRANT USAGE ON FUNCTION identifier('$$ || :fn_name || $$')(timestamp) TO ROLE identifier('$$ || :public_role || $$')$$;
  execute immediate :fn_stmt;
end;
select('Installation was successful!');

Step 3. Tailor the Script

To tailor the script, review the variables at the start of the script. Make appropriate changes for these variables in the script as described in the following table.

Variable
Required to Change?
Default
Description
sf_warehouse_name Yes none Your Snowflake warehouse name must be specified. If this setting is not specified, the script will not run and Okera policy synchronization will not work.
okera_role No SERVICE_OKERA_ROLE Okera requires a dedicated role in Snowflake with specific Snowflake permissions. This role name should also be used in the Snowflake connection definition in Okera.

Note: If you plan on specifying a role pattern (using configuration parameter POLICY_SYNC_ROLE_PATTERN) for Okera to use when creating its role names for synchronization, the role pattern must not match this service role name.
public_role No PUBLIC The role named by this variable is the Snowflake role used in your Snowflake environment for all Snowflake users. Snowflake's default name for this role is PUBLIC, but if your Snowflake environment uses a different role name, specify it in this variable.
okera_user No SERVICE_OKERA_USER Okera requires a dedicated Snowflake user to whom the SERVICE_OKERA_ROLE must be granted. This user name should also be used in the Snowflake connection definition in Okera.
okera_user_password No CHANGE_ME! An initial password for the SERVICE_OKERA_USER is provided. Changing it is not required, but if you do, be sure to change this variable setting as well.
require_password_change No FALSE This variable indicates that the supplied password for SERVICE_OKERA_USER does not need to be changed. Changing this to TRUE would require a password change, but since this script is only run once, when you initially set up Okera policy management in Snowflake, it is not necessary to change this setting.
release_bucket No okera-release-useast Specify the region in which the S3 release bucket for your Okera cluster resides. Valid values for this variable are okera-release-uswest and okera-release-useast.
version Yes 2.10.0 Specify the full version number of your Okera cluster. This can be found on the System Information page in the Okera UI. See System Information.
okera_db No OKERA_UDFS Specify the name of the Snowflake database dedicated for Okera. Okera uses this database to store its user-defined functions (UDFs) and other metadata. The value of this variable must match the setting of the Okera configuration parameter EXTERNAL_OKERA_SECURE_POLICY_DB (default OKERA_UDFS). If you have set or otherwise changed this configuration parameter in the Okera configuration file, the change should be reflected in this script variable.
okera_schema No PUBLIC Specify the name of the default schema in the Snowflake database dedicated for Okera. Okera uses this schema to store its user-defined functions (UDFs) and other metadata. The value of this variable must match the setting of the Okera configuration parameter EXTERNAL_OKERA_SECURE_POLICY_SCHEMA (default PUBLIC). If you have set or otherwise changed this configuration parameter in the Okera configuration file, the change should be reflected in this script variable.

Note: Okera recommends that all Snowflake object (role, user, database, schema, and view) names be specified in the case used in your Snowflake environment. Typically, this is uppercase. Currently, Okera does not support Snowflake object names in mixed case. See Limitations and Case Considerations.

Step 4. Run the Script

In your Snowsight worksheet, run all the SQL statements in the script. After the script runs successfully, Okera policy management is installed and will be applied to existing databases and schemas in your Snowflake environment when your Snowflake connection is synchronized in Okera.

For information on using a Snowsight worksheet, see Using Worksheets in the Snowsight documentation.