Skip to content

Synchronization Options

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 in 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 a default list of users or a tag for whom Okera policies should be synced. Valid values for this parameter are either a comma-separated list of Snowflake users (with no spaces) or a Snowflake tag (with on or off tag values). You cannot specify both a tag and a list of user names. Policies are synced for Snowflake users with the specified usernames or with the Snowflake tag on or off as specified. If no list or tag is specified, all Snowflake users are synced. The default specifications in this parameter can be overridden in the definition of the actual Snowflake connection.

  • In a specific connection, you can list the Snowflake users or a tag for which the connection should be synchronized. In the UI Synchronize permissions for specific Snowflake users entry box, specify either a comma-separated list of Snowflake users (with no spaces) or a Snowflake tag (with an on or off tag value). You cannot specify both a tag and list of user names in a single connection. An option is also provided in the UI to synchronize all users for the connection.

    If you are creating a Snowflake connection using the API, you can use the okera.policy_sync.user_allowed_list connection option to list the Snowflake users or tags for which the connection should be synchronized. Specify either a comma-separated list of Snowflake users (with no spaces) or a Snowflake tag with an on or off tag value.

    Policies are synced for Snowflake users with the specified usernames or with the Snowflake tag on or off as specified.

    These UI and API connection-level specifications override the cluster-level POLICY_SYNC_USERS_ALLOWED_LIST configuration parameter for the connection.

Only one tag can be specified per connection. The syntax for specifying a tag name is tag:<tag-name>:<on or off>. For example, tag:OKERA_UDFS.PUBLIC.OKERA_POLICY_SYNC_TAG:on. To learn how to set up tags for Snowflake users, see Tag Users in Snowflake.

Important

Synchronizing by tag might have up to a 2-hour delay because of the frequency at which Snowflake updates ACCOUNT_USAGE data. For more information, see the documentation for Snowflake TAG_REFERENCES view.

In both the UI connection definition and the POLICY_SYNC_USERS_ALLOWED_LIST configuration parameter, quotes are not required around user or tag names, but the case of the names must match the case of names 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. See Limitations and Case Considerations.

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 for all Snowflake connections, unless a specific Snowflake connection overrides these settings.

In another example, POLICY_SYNC_USERS_ALLOWED_LIST: tag:OKERA_SERVICE_DB.PUBLIC.OKERA_POLICY_SYNC_TAG:on syncs Okera policies for any Snowflake users defined with the Snowflake tag OKERA_POLICY_SYNC_TAG (defined in the OKERA_SERVICE_DB database and PUBLIC schema) set on in their Snowflake user definition. The database and schema name must be specified to fully qualify the tag name.

Warning

If you unset the tag for a Snowflake user or 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 or unset the tag. 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

You can manually synchronize your Snowflake connections.

To synchronize an Okera Snowflake connection manually:

  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.