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. See Limitations and Case Considerations.

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.