Prerequisites¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Before you install the Snowflake OCF connector, ensure that you have performed the following:
Enable Network Connectivity¶
Open the inbound TCP port 443 to the Snowflake server. Snowflake server-side firewall accepts inbound connections on TCP port 443 from the Alation or Agent. Similarly, Alation or Agent firewall accepts outbound connections on TCP port 443 to Snowflake server.
Create a Service Account¶
Under your Snowflake account, set up a service account for Alation. Security and account administrators for Snowflake (users with the SECURITYADMIN or ACCOUNTADMIN roles) can create users. Alation uses the service account for metadata extraction (MDE), profiling and sampling, and query log ingestion (QLI). Snowflake administrators must assign a service account with a role that has enough privileges for these extraction jobs.
Note
You can also use an existing account.
Snowflake connection information requires a warehouse and a role. Snowflake users may have multiple roles which give them different access rights. As a best practice, we recommend setting a default role and a default warehouse for the service account user. The information on creating an account and required permissions is given below:
Create Role for Alation Service Account¶
USE ROLE ACCOUNTADMIN;
CREATE ROLE <alation_role>;
Note
You must set the default role of the service account user to the newly created role. Set this in the role URI parameter.
Create Alation Service Account User¶
USE ROLE ACCOUNTADMIN;
CREATE USER <alation_user> PASSWORD='*****' DEFAULT_ROLE = <alation_role> MUST_CHANGE_PASSWORD = TRUE TYPE='SERVICE';
GRANT ROLE <alation_role> TO USER <alation_user>;
ALTER USER <alation_user> SET ROWS_PER_RESULTSET=0;
ALTER USER <alation_user> SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;
For enhanced security, Snowflake recommends that you set the TYPE property to SERVICE. This enables you to use the key-pair authentication. For details on how to configure the key-pair authentication, see Configure Key Pair Authentication in Configure Connection to Data Source.
However, if you choose to use username and password for authentication (basic authentication), set the TYPE property to LEGACY_SERVICE.
USE ROLE ACCOUNTADMIN;
CREATE USER <alation_user> PASSWORD='*****' DEFAULT_ROLE = <alation_role> MUST_CHANGE_PASSWORD = TRUE TYPE='LEGACY_SERVICE';
GRANT ROLE <alation_role> TO USER <alation_user>;
ALTER USER <alation_user> SET ROWS_PER_RESULTSET=0;
ALTER USER <alation_user> SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;
Important
For enhanced security, Snowflake recommends that you use key-pair authentication.
Note
On the service account level, set the
QUOTED_IDENTIFIERS_IGNORE_CASEparameter toFALSE. This value is required for query log ingestion configuration. Snowflake is case-sensitive, and column names and aliases in the QLI view or custom QLI queries in Alation use camel case. Learn more in Version 2.2.9 or Newer.We also recommend setting the
ROWS_PER_RESULTSETparameter to0to allow the connector to fetch all accessible metadata. This setting is relevant if a strict limit is set on the account level or if you’re not sure if such a limit exists on the account. With a strict limit applied via theROWS_PER_RESULTSETparameter, the connector may skip metadata during extraction, resulting in an incomplete extraction result.
Set Default Warehouse¶
USE ROLE ACCOUNTADMIN;
ALTER USER <alation_user> SET DEFAULT_WAREHOUSE=<warehouse_name>;
Grant Permissions for Metadata Extraction¶
Grant Access to Warehouse¶
Grant access to the warehouse from which you want to extract. Access to the warehouse is required even if it is the default warehouse of the service account.
USE ROLE ACCOUNTADMIN; GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <alation_role>;
Grant Access to ACCOUNT_USAGE¶
You can either grant access to all views in the ACCOUNT_USAGE schema or, if full access is not allowed, grant limited access using Snowflake database roles. Access to the ACCOUNT_USAGE schema enables metadata extraction (MDE) and query log ingestion (QLI).
Full Access to ACCOUNT_USAGE¶
Grant access to all views in the ACCOUNT_USAGE schema:
USE ROLE ACCOUNTADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" to <alation_role>;
Limited Access to ACCOUNT_USAGE¶
If you can only provide limited access to the ACCOUNT_USAGE schema, then grant the service account the SNOWFLAKE database roles of OBJECT_VIEWER and GOVERNANCE_VIEWER:
USE ROLE ACCOUNTADMIN; USE SNOWFLAKE; GRANT DATABASE ROLE OBJECT_VIEWER TO ROLE <alation_role>; GRANT DATABASE ROLE GOVERNANCE_VIEWER TO ROLE <alation_role>;Note
The database roles of OBJECT_VIEWER and GOVERNANCE_VIEWER grant access to these ACCOUNT_USAGE views:
- MDE:
OBJECT_VIEWER
SNOWFLAKE.ACCOUNT_USAGE.DATABASES
SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
SNOWFLAKE.ACCOUNT_USAGE.TABLES
SNOWFLAKE.ACCOUNT_USAGE.COLUMNS
SNOWFLAKE.ACCOUNT_USAGE.VIEWS
SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
GOVERNANCE_VIEWER
SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
SNOWFLAKE.ACCOUNT_USAGE.ROW_ACCESS_POLICIES
SNOWFLAKE.ACCOUNT_USAGE.MASKING_POLICIES
SNOWFLAKE.ACCOUNT_USAGE.TAGS
SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES
- QLI:
GOVERNANCE_VIEWER
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
Grant Access to Databases, Schemas, and Tables¶
Grant the service account access to the database(s), schema(s), and table(s) that you want to be cataloged in Alation after metadata extraction:
Grant USAGE on Specific Database(s)¶
GRANT USAGE ON DATABASE <database> TO <alation_role>;
Grant USAGE on Specific Schema(s)¶
Grants limited by database
GRANT USAGE ON ALL SCHEMAS IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a named schema
GRANT USAGE ON SCHEMA <database.schema> TO ROLE <alation_role>;Optional: grant access to future schemas in database
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <database> TO ROLE <alation_role>;
Grant REFERENCES on Specific Table(s)¶
Grants limited by database
GRANT REFERENCES ON ALL TABLES IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a named schema
GRANT REFERENCES ON ALL TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;Optional: grant access to future tables in database
GRANT REFERENCES ON FUTURE TABLES IN DATABASE <database> TO ROLE <alation_role>;Optional: grant access to future tables in a specific schema
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;
Grant REFERENCES on Specific View(s)¶
Grants limited by database
GRANT REFERENCES ON ALL VIEWS IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a named schema
GRANT REFERENCES ON ALL VIEWS IN SCHEMA <database.schema> TO ROLE <alation_role>;Optional: grant access to future views in database
GRANT REFERENCES ON FUTURE VIEWS IN DATABASE <database> TO ROLE <alation_role>;Optional: grant access to future views in a specific schema
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <database.schema> TO ROLE <alation_role>;
Grant Access to External Tables¶
Alation supports extraction of external tables on Amazon S3 and Azure Storage.
Note
Granting USAGE on databases and schemas is a prerequisite to extracting external tables.
Grant access to external tables, limited by database
GRANT REFERENCES ON ALL EXTERNAL TABLES in DATABASE <database> TO ROLE <alation_role>;Grant access to external tables, limited by a named schema
GRANT REFERENCES ON ALL EXTERNAL TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;Grant USAGE on stages used in external tables, limited by database
GRANT USAGE ON ALL STAGES IN DATABASE <database> TO ROLE <alation_role>;Grant USAGE on stages used in external tables, limited by a named schema
GRANT USAGE ON ALL STAGES IN SCHEMA <database.schema> TO ROLE <alation_role>;Note
An external table can be created from a stage belonging to a different database. Make sure you provide access to all required stages of all required databases.
Optional: grant access to future external tables in a database
GRANT REFERENCES ON FUTURE EXTERNAL TABLES in DATABASE <database> TO ROLE <alation_role>;Optional: grants access to future external tables in a schema
GRANT REFERENCES ON FUTURE EXTERNAL TABLES IN SCHEMA <database.schema> TO ROLE <alation_role>;
Policy and Tag Extraction¶
Permissions for Policy and Tag Extraction¶
Permissions you have granted on the ACCOUNT_USAGE views are enough for policy and tag extraction.
Permissions for Policy and Tag Synchronization¶
Grant access for policy synchronization
GRANT APPLY MASKING POLICY ON ACCOUNT TO <alation_role>;Grant access for tag synchronization
GRANT APPLY TAG ON ACCOUNT TO <alation_role>;
Enable Policy Extraction¶
Policy extraction requires additional configuration on the Alation server. You can enable it using alation_conf.
On customer-managed (on-premise) Alation instances, enable data policy extraction by setting these alation_conf flags to True:
alation.ocf.mde.policy.enable_extraction
alation.ocf.mde.policy.enable_ingestion
alation.ocf.mde.policy.enable_search
After changing the parameters, restart Alation Supervisor with the command alation_supervisor restart all.
On how to use alation_conf, see Using alation_conf.
On Alation Cloud Service instances, Alation Support will adjust the alation_conf flags in values to activate specific features. Submit a Support ticket to Alation to make these changes to your instance.
From Alation version 2024.1 and newer, for customer-managed (on-premise) and Alation Cloud Service instances, you are no longer required to enable the alation_conf flags. You need to turn on the Enable Policy Center toggle under the Admin Settings page to sync all the policy-related flags automatically.
Note
If you migrate the native data source to OCF connector, ensure that you first turn off and then turn on the Enable Policy Center toggle to enable this feature.
For more on working with policies extracted into the catalog, see Policy Center.
Note
Only policies attached to tables, columns, and views are extracted and shown in Alation. Policies linked to tags are not extracted. On connector versions before 1.1.5, metadata extraction exits with an error when it encounters a policy linked to a tag during ingestion. Connector versions 1.1.5 or newer will skip policies linked to tags and successfully extract policies attached to tables, columns, and views.
Enable Tag Extraction¶
Tag extraction requires additional configuration on the Alation server. You can enable it using alation_conf.
On customer-managed (on-premise) Alation instances, enable Snowflake tag extraction by setting these alation_conf flags to True:
alation.feature_flags.enable_snowflake_tags
alation.ocf.mde.custom_field.enable_extraction
alation.ocf.mde.custom_field.enable_ingestion
After changing the parameters, restart Alation Supervisor with the command alation_supervisor restart all.
On how to use alation_conf, see Using alation_conf.
On Alation Cloud Service instances, Alation Support will adjust the alation_conf flag values to activate specific features. Submit a Support ticket to Alation to make these changes to your instance.
From Alation version 2024.1 and newer, for customer-managed (on-premise) and Alation Cloud Service instances, you are no longer required to enable the alation_conf flags. You need to turn on the Enable Snowflake Tags toggle under the Admin Settings page to sync all the tag-related flags automatically.
Note
If you migrate the native data source to OCF connector, ensure that you first turn off and then turn on the Enable Snowflake Tags toggle to enable this feature. However, when the flag you turn off the toggle, the template loses all the custom fields, and you must add them again.
By default, Alation will extract:
All tags if the database name is not specified in the JDBC URI.
All the tags which have references in the database if the database name is specified in the JDBC URI.
For more on the format of the Snowflake JDBC URI, see Configure Connection to Data Source.
If you want to extract more specific tags, use a custom query for tag extraction. See Snowflake OCF Connector Extraction Queries.
For information about working with Snowflake tags in Alation, refer to Snowflake Tags in Alation.
Function Extraction¶
Grants limited by database
GRANT USAGE ON ALL FUNCTIONS IN DATABASE <database> TO ROLE <alation_role>;Grants limited by a schema
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA <database.schema> TO ROLE <alation_role>;
Grant Permissions for Profiling¶
Profiling requires SELECT permissions for the tables you are going to profile:
GRANT SELECT ON schema_name.table_name TO <alation_role>;
Grant Permissions for Query Log Ingestion¶
Alation supports query log ingestion (QLI) based on the ACCOUNT_USAGE.QUERY_HISTORY view of the SNOWFLAKE database.
QLI can either use a dedicated view (table-based QLI) or a query (default or query-based QLI).
For table-based QLI, you’ll need to create a view on top of the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view and grant the Alation service account these permissions:
USAGE on the database and schema where the view is created.
SELECT on the created view.
For default and query-based QLI, the permissions on the ACCOUNT_USAGE.QUERY_HISTORY view that you’ve granted the service account are enough.
Based on your connector version, see QLI for new connector versions or QLI for earlier connector versions for more information on the types of QLI.