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).
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:
- Whether the scheduled automatic synchronization job is enabled. See Control Automatic Synchronization.
- The frequency at which automatic synchronization occurs. See Control Automatic Synchronization.
- The Snowflake user accounts for which Okera policy automatic or manual synchronization occurs. See Limit Synchronized Users.
- The format, or pattern, used to generate each user's Snowflake role for Okera. See Control Okera-Generated Snowflake Role Names.
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.scheduledadvanced 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
You can configure the frequency at which automatic synchronization occurs. Specify the
POLICY_SYNC_INTERVALconfiguration parameter in the cluster configuration YAML file, which is specified in seconds. The default is 1800 seconds (30 minutes). See Configuration. Values are specified as a combination of a number and a one or two-letter code that represent the units. Valid unit codes are
h(hours). For example,
1his one hour and
5000msis 5000 milliseconds. The default is
You can configure the frequency at which automatic synchronization occurs for a specific Snowflake connection. The
okera.policy_sync.intervaladvanced connection property specifies how often Okera synchronizes Okera policies with Snowflake during process synchronization. Values are specified as a combination of a number and a one or two-letter code that represent the units. Valid unit codes are
h(hours). For example,
1his one hour and
5000msis 5000 milliseconds. For example,
okera.policy_sync.interval=5msets the interval to five minutes.
Note: If the setting for
POLICY_SYNC_INTERVALis greater than the setting for
okera.policy_sync.intervalon a connection, the
POLICY_SYNC_INTERVALsetting is used.
In addition, if a custom synchronization interval has been set for a Snowflake connection, you can determine what interval was set by locating the
okera.policy_sync.intervalsetting in the Advanced Properties list on the Connection Details tab for the connection.
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
offtag 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
offtag 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_listconnection 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
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_LISTconfiguration 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.
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.
POLICY_SYNC_USERS_ALLOWED_LIST: EDWARD,GEORGE,JOHN,NOEL,SANDY syncs Okera policies only for the listed users
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.
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
%s is the Snowflake username. To change this pattern, change the
POLICY_SYNC_ROLE_PATTERN configuration parameter and issue the
helm upgrade command to update the Okera cluster.
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
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.
Synchronize an Okera Snowflake Connection Manually¶
You can manually synchronize your Snowflake connections.
To synchronize an Okera Snowflake connection manually:
Select Connections on the Okera main menu in the UI to view the Connections list.
Locate and select your Snowflake connection in the list. The Snowflake connection details page appears.
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.
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_ROLEfor Snowflake user
SALLY, Sally should select the
OKERA_CI_TEST_SALLY_ROLEwhen she uses Snowflake, as shown below.
You might even make this role the default role for user
For more information about Okera-generated Snowflake roles, see Control Okera-Generated Snowflake Role Names.