Skip to content

Configure and Use Snowflake Policy Synchronization Enforcement

Policy synchronization is the newest, and recommended, Okera policy enforcement mechanism for Snowflake. Using policy synchronization enforcement, Okera functions as the central policy manager, pushing universal data access policies into Snowflake. This applies Okera's fine-grained access controls onto Snowflake objects, such as roles, permissions, and row access policies, allowing Snowflake to enforce policies defined and managed in Okera, while removing Okera from the Snowflake query execution path. Your Snowflake users can continue to use the full suite of Snowflake features, including Snowflake SQL, drivers, and tools, but the data they can access is governed by Okera.

When you change the Okera permissions for a Snowflake data source, the Snowflake connection must be synchronized so the Okera policy is applied to the corresponding Snowflake databases. This synchronization occurs automatically at a specified interval, but can also be instigated manually, as needed. See Synchronize an Okera Snowflake Connection.

Policy synchronization enforcement requires special configuration steps in Snowflake and in Okera. The Snowflake configuration can be performed using a supplied configuration script or you can configure your Snowflake environment manually.

See the following sections:

Limitations and Case Considerations

The following limitations exist for Okera's policy synchronization:

  • Only SELECT access for Snowflake data is currently supported. See also Hiding Columns in Snowflake Data for related information.
  • The use of mixed case in Snowflake object names is not supported at this time. For example, a database named Sales_Database is not supported. However, both SALES_DATABASE and sales_database are supported.
  • The following characters are not supported in Snowflake object names at this time: comma(,), backtick(`), leading spaces, or trailing spaces.

Pay special attention to the case and special characters you use when setting up Snowflake policy synchronization. Make sure the case of a Snowflake object matches the case used in your Snowflake environment.

Snowflake usually uses uppercase characters for all its object names.

Configure Your Snowflake Environment Using the Supplied Script

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).
  • 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.

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.

Configure Policy Synchronization for the Okera Cluster (Optional)

Okera provides several Okera configuration parameters you can use to configure policy synchronization for the Okera cluster. Policy synchronization occurs when you synchronize the Snowflake definition in Okera. There are defaults for these parameters, so modifying these parameters is optional.

Note: If you are using a SaaS environment, these configuration parameters are not available for your use. Instead, use parameters specified directly in your Snowflake connection. See Advanced Snowflake Connection Properties.

The configuration parameters are:

  • POLICY_SYNC_INTERVAL: This specifies how often Okera synchronizes Okera policies with Snowflake. Values are specified as a combination of a number and a one or two-letter code that represent the units. Valid unit codes are ns (nanoseconds), us (microseconds), ms (milliseconds), s (seconds), m (minutes), and h (hours). For example, 1h is one hour and 5000ms is 5000 milliseconds. The default is 30m (30 minutes).

    Synchronization can be performed manually as well. See Synchronize a Snowflake Connection.

  • POLICY_SYNC_USERS_ALLOWED_LIST: This parameter specifies the users for whom Okera policies should be synced. A list of Snowflake users can be specified for this parameter. These are the users for whom Okera manages the Snowflake connection. If no list is specified, all Snowflake users are synced. For more information, see Synchronize a Snowflake Connection.

  • POLICY_SYNC_ROLE_PATTERN: This parameter specifies the Snowflake role pattern that Okera should use when syncing Okera policies. The default is OKERA_%s, where %s is replaced by the user name. See Control Okera-Generated Snowflake Role Names.

    Note: The specified role pattern must not match the Okera service role name (default SERVICE_OKERA_ROLE).

  • POLICY_SYNC_SCHEDULER_ENABLED: This parameter allows you to enable and disable Okera policy synchronization with Snowflake. By default, this parameter is set to false, disabling synchronization. To enable synchronization, set it to true.

  • AUDIT_LOGS_SYNC_FREQUENCY_MINS: This parameter specifies the frequency at which Okera syncs audit logs, in minutes. Valid values range from 1 to 180 minutes. The default is 30 minutes. If you specify a value larger than 180 minutes, Okera defaults to 180 minutes.

Select the Policy Synchronization Enforcement Mechanism in Your Okera Connection

When you create your Snowflake connection, you may need to select the Snowflake policy enforcement mechanism used for the connection.

Important

SaaS customers should not do this. SaaS customers automatically use Okera's policy synchronization enforcement for Snowflake connections and cannot use BI gateway (pushdown processing) for Snowflake. However, the policy synchronization enforcement mechanism must be explicitly selected by non-SaaS customers who want to use it.

To select the policy synchronization enforcement mechanism for Snowflake, okera.policy_sync.enabled=true must be specified as an advanced property in Okera's connection definition for Snowflake. See Create a Snowflake Connection.

Synchronize an Okera Snowflake Connection

When you change the Okera permissions for a Snowflake data source, the Snowflake connection must be synchronized with Snowflake, so the Okera policy is applied to your Snowflake accounts. This synchronization occurs automatically at a specified interval (30 minutes or 1800 seconds), but can also be instigated manually, as needed.

When policy synchronization occurs, Okera ensures that specific Snowflake roles exist for each Snowflake user, generating the Snowflake roles if needed. An Okera-generated Snowflake role incorporates a user's Okera privileges and permissions, including row-based and tag-based access controls. Each user is assigned one such role. The name of the generated role is determined using a specific Okera format (pattern).

Important

After policy synchronization has occurred, your Snowflake users should use the Okera-generated Snowflake role when working with Snowflake.

Each Okera Snowflake connection can be configured with different synchronization options. You can control:

Control Automatic Synchronization

By default, automatic policy synchronization occurs every 30 minutes. However, this can be configured.

  • You can configure whether automatic policy synchronization occurs for an individual Snowflake connection. The okera.policy_sync.scheduled advanced connection property can be used to disable or enable the automatic synchronization job. Valid values are true (automatic synchronization is enabled) and false (automatic synchronization is disabled). The default is true.

  • You can configure the frequency at which automatic synchronization occurs. Specify the POLICY_SYNC_INTERVAL configuration parameter in the cluster configuration YAML file, which is specified in seconds. The default is 1800 seconds (30 minutes). See Configuration.

Limit Synchronized Users

You can limit policy synchronization by Snowflake user. These are the users for whom Okera manages the Snowflake connection.

  • The cluster-level (global) configuration parameter POLICY_SYNC_USERS_ALLOWED_LIST, specified in the cluster YAML configuration file, allows you to specify the users for whom Okera policies should be synced.

  • The connection-level okera.policy_sync.user_allowed_list advanced property, specified in the Snowflake connection definition, overrides the cluster-level POLICY_SYNC_USERS_ALLOWED_LIST configuration parameter for the connection. For example, okera.policy_sync.user_allowed_list=EDWARD,GEORGE syncs Okera policies only for the listed users EDWARD and GEORGE.

Quotes are not required around user names, but the case of the usernames must match the case of usernames in Snowflake. So, if Edward's Snowflake username is all uppercase (EDWARD), okera.policy_sync.user_allowed_list=edward,george will not include him. However, the list is parsed by Okera as comma-separated values (CSV), so quotes can be used to insert special characters like quotes and commas.

Valid specification for both parameters is a comma-separated list of Snowflake users (with no spaces). If no list is specified, all Snowflake users are synced.

For example, POLICY_SYNC_USERS_ALLOWED_LIST: EDWARD,GEORGE,JOHN,NOEL,SANDY syncs Okera policies only for the listed users EDWARD, GEORGE, JOHN, NOEL, and SANDY. The Okera policies are not synced for all other users.

Warning

If you remove a username from POLICY_SYNC_USERS_ALLOWED_LIST, Okera no longer manages the connection for that user. If you do this after you have previously synced the user's permissions with Okera, the user's permissions remain as they were specified by Okera before you removed the user. This means that the user's data permissions will not change, even after resyncing the Okera connection. So, if the user was granted access to a database before by Okera, Okera does not remove that grant after the resync. The user will continue to have access to the database. You will need to use Snowflake itself to manage the user's permissions.

Control Okera-Generated Snowflake Role Names

When synchronization occurs, Okera ensures that specific Snowflake roles exist for each Snowflake user. This Snowflake role incorporates a user's Okera privileges. Each user is assigned one such role; if the role does not exist, Okera generates it. The role name that is generated is defined using a format, or pattern, specified in the Okera POLICY_SYNC_ROLE_PATTERN configuration parameter.

By default, the pattern used for generated role names is OKERA_%s, where %s is the Snowflake username. To change this pattern, change the POLICY_SYNC_ROLE_PATTERN configuration parameter and issue okctl update to update the Okera cluster.

Important

Be sure to use %s somewhere in your pattern to ensure the generated role names are unique for each user.

For example, the following setting would create a role named OKERA_SALLY_ROLE for Snowflake user SALLY:

POLICY_SYNC_ROLE_PATTERN: OKERA_%s_ROLE

Note: Roles are not generated for users that have been excluded from synchronization. See Limit Synchronized Users.

After Okera has synced a Snowflake connection, your Snowflake users should use Snowflake with the Okera-generated role. So, from our example above, Snowflake user SALLY should use Snowflake with the Okera-generated OKERA_SALLY_ROLE. You might even make this role the default role for user SALLY, as shown below.

Snowflake default role for Sally

Synchronize an Okera Snowflake Connection Manually

To synchronize the Okera policies manually, follow these steps.

  1. Select Connections on the Okera main menu in the UI to view the Connections list.

  2. Locate and select your Snowflake connection in the list. The Snowflake Connection Details appear.

    Snowflake connection details

  3. Select in the upper right corner of the page. Policy synchronization starts. The status of the synchronization can be viewed on the Synchronization Details tab.

    Snowflake connection synchronization status

  4. After the synchronization is complete, your Snowflake users should use Snowflake with the Okera-generated Snowflake roles that result from synchronization. For example, if Okera generated the Snowflake role named OKERA_CI_TEST_SALLY_ROLE for Snowflake user SALLY, Sally should select the OKERA_CI_TEST_SALLY_ROLE when she uses Snowflake, as shown below.

Sally selects the Okera role

You might even make this role the default role for user SALLY.

For more information about Okera-generated Snowflake roles, see Control Okera-Generated Snowflake Role Names.

Add Okera Policy Synchronization Support for a New Database

New Snowflake databases are not automatically included in Okera policy synchronization when they are added after the Okera-provided script has been run and established Okera policy synchronization in your Snowflake environment.

Run a separate Okera-supplied script to add the new Snowflake database to Okera policy management. This script is provided in this section. After tailoring the script, you can run it in a Snowsight workspace (not a regular Snowflake workspace). It grants:

  • Minimal USAGE and SELECT permissions to the Okera role (SERVICE_OKERA_ROLE) for the new database.
  • Minimal USAGE and SELECT permissions to the schemas, tables, and views in the new database.
  • The Okera role permission to create policies for any schemas in the new Snowflake database.

Complete the following steps to copy, tailor, and run the script provided in this section to add Okera policy management for a new database.

Step 1. 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.

-- The intent of this script is to execute SQL that sets
-- up a new Snowflake database for Okera policy management.

declare
  db_name string default 'YOUR_NEW_SF_DATABASE';

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

begin
      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;
  select('Database successfully added for Okera policy management!');

Step 2. Tailor the Script

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

Variable
Required to Change? Default Description
db_name Yes none The name of your new Snowflake database. This setting must be specified, or the script will not run.
okera_role No SERVICE_OKERA_ROLE Okera requires a dedicated role in Snowflake with specific Snowflake permissions. This role name is also used in Okera when you define your Snowflake connection. This role name should be the same role name used when Okera policy synchronization was first set up.

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.

Step 3. Run the Script

In your Snowsight worksheet, run all the SQL statements in the script. After the script runs successfully, the Okera policy management will be applied to the new Snowflake database when the Okera connection is resynchronized.

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

Audit Log Processing

Snowflake compliance history is logged, by default, in Okera audit logs. The Okera audit log writer asynchronously processes Snowflake's external audit logs. The Snowflake audit logs are stored with other Okera audit logs in the location specified by the OKERA_STAGING_DIR configuration parameter.

To stop this logging, change the advanced property okera.policy_sync.audit_logs in the Snowflake connection definition to false.

The frequency at which Okera synchronizes the audit logs is defined by the AUDIT_LOGS_SYNC_FREQUENCY_MINS configuration parameter, which defaults to 30 minutes and must be less than 180 minutes (3 hours). Valid values range from 1 minute to 180 minutes (3 hours). If you specify a value larger than 180 minutes, Okera defaults to 180 minutes.

Advanced Snowflake Connection Properties

Various advanced properties can be specified in the Snowflake connection itself. These properties apply to the individual Snowflake connection. They have all been described earlier on this page but are listed here for convenience.

  • okera.policy_sync.enabled: Identifies the type of policy enforcement used for the Snowflake connection. Valid values are true (policy synchronization is used) or false (BI Gateway enforcement is used). See Select the BI Gateway Enforcement Mechanism and Select the Policy Synchronization Enforcement Mechanism.

  • okera.policy_sync.scheduled: Indicates whether automatic policy synchronization occurs for a Snowflake connection. Valid values are true (enable automatic synchronization) and false (disable automatic synchronization). See Control Automatic Synchronization.

  • okera.policy_sync.user_allowed_list: Identifies users for whom the Snowflake connection should be synchronized. See Limit Synchronized Users.

  • okera.policy_sync.audit_logs: Indicates whether the Snowflake compliance history is logged in Okera's audit logs. Valid values are true (log the compliance history) and false (do not log the compliance history). See Audit Log Processing.

Hide Columns in Snowflake Data

If a user has SELECT privileges for a table in Snowflake, they will always be able to see all the columns in the table. To ensure that they cannot see the data in those columns, transform the data using one of Okera's many transformation functions (mask, hash, tokenize, encrypt, and more).