Snowflake Connector

Overview

Used for OneTrust Data Discovery, the Snowflake connector works to scan structured data storage in RDBMS databases and retrieve discovered data back to OneTrust for review and analysis.

Connection Details

Pod Residency

rdbms-scanner

Connection Method

JDBC Driver connection. For more information, please referenceSnowflake's official documentation.

Note:

The Snowflake account provided should not have a default namespace. If it does, the scan will be limited to the database specified in the default namespace.

Connection Overview

  • Generate a credential with an authentication method. See Authentication for more.

  • Data source setup from Gallery

  • Supply credentials to data source

Supported Scan Actions

Scan Type

Description

Catalog

This scan type will only retrieve the metadata in the database like details of schema, table, or column.

Catalog & Scan

This scan type lets OneTrust scan both metadata and data under the tables and return the appropriate labels.

Credentials

OneTrust recommends to adhere to your organization's security and infrastructure policy and include system engineer resources or other qualified subject matter experts to set up credentials on data sources. For specific instructions for how to enable these credentials, refer to your data source's implementation documentation, knowledge base and/or support resources.

For minimal setup and auto discovery of all databases, OneTrust recommends ACCOUNTADMIN role. Scans can still be completed with the PUBLIC role as long as the user has explicit access to the given databases, schema, and tables. In the case they don't show up in the scan results, additional permission changes will be required.

For more information on the required read permissions necessary for completing these functions, please see Snowflake's official access control privileges documentation.

Sample JSON with Basic Authentication

JSON 
{
    "connectionUrl": "jdbc:snowflake://1.1.1.1:443",	
    "user": "your_user",	
    "password": "your_password"
}

Sample JSON with PAT Authentication

JSON 
{
    "connectionUrl": "jdbc:snowflake://1.1.1.1:443",
    "user": "your_user",
    "authenticator": "programmatic_access_token",
    "token": "your_pat_token"
}
Note:

For more information about the available authentication methods and their configuration parameters, see Snowflake's JDBC Parameter References documentation.

Sample JSON with Key-Value Pair Authentication

JSON 
{
    "connectionUrl": "jdbc:snowflake://1.1.1.1:443",
    "jdbc:snowflake://<HOSTNAME>/?authenticator=snowflake_jwt&private_key_file=./extlib/<privateKeyFileName>",
    "user":"<snowflake_Username>"
}

Authentication

You can authenticate OneTrust with Snowflake using a key-value pairing for improved security. The steps below are for if you need to create new keys. You can also use existing ones for this purpose.

Private Key

  1. Open a session by logging into the VM and generate a private key.

  2. To generate an unencrypted version, use the following command:

    CODE 
    openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
    Note:

    Generation of an encrypted private key is under consideration with OneTrust.

  3. The command will generate a private key in PEM format.

    CODE 
    -----BEGIN ENCRYPTED PRIVATE KEY-----
MIIE6TAbBgkqhkiG9w0BBQMwDgQILYPyCppzOwECAggABIIEyLiGSpeeGSe3xHP1wHLjfCYycUPennlX2bd8yX8xOxGSGfvB+99+PmSlex0FmY9ov1J8H1H9Y3lMWXbL
...
-----END ENCRYPTED PRIVATE KEY-----

  4. To sync the private key to the RDBMS container, navigate to the installer directory again:

    CODE 
    /home/azureuser/onetrust-dd-installer-xxxxx/data-discovery/onebox-deployment/

  5. Run the following script to sync the private key:

    CODE 
    ./deploy.sh sync-extlib

Public Key

  1. From the command line, generate the public key by referencing the private key.

  2. The following command assumes the private key is encrypted and contained in the file named rsa_key.p8.

    CODE 
    openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

  3. The command generates the public key in PEM format.

    CODE 
    -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAy+Fw2qv4Roud3l6tjPH4zxybHjmZ5rhtCz9jppCV8UTWvEXxa88IGRIHbJ/PwKW/mR8LXdfI7l/9vCMXX4mk
...
-----END PUBLIC KEY-----

  4. To assign the public key to a Snowflake user, execute an ALTER USER command:

    CODE 
    alter user <snowflakeUSERNAME> set rsa_public_key=’MIIBIjANBgkqh...’;

  5. You can retrieve the contents of the key using the following command:

    CODE 
    vi rsa_key.pub

  6. To verify the user's public key fingerprint, execute a DESCRIBE USER command:

    CODE 
    desc user <snowflakeUSERNAME>;

Store Keys on VM

  1. Copy both the keys in a secure folder on the VM. 

  2. Also, copy the private key file in the extlib folder under the deployment folder. 

  3. Navigate to the extlib folder under the deployment package:

    CODE 
    /home/azureuser/onetrust-dd-installer-xxxxx/data-discovery/onebox-deployment/extlib/

  4. Copy the private key file at this location using the below command:

    CODE 
    cp <original_path>

  5. Confirm the file is copied correctly in the extlib folder.

Sync Private Key to RDBMS Container

  1. Ensure a single pod of scanner-extlib is running:

    CODE 
    kubectl -n <namespace> scale --replicas=1 sts scanner-extlib

  2. Navigate to the directory where the certificates are located. For example, if using the installer directory:

    CODE 
    cd /home/azureuser/onetrust-dd-installer-xxxxx/data-discovery/onebox-deployment/extlib/

  3. Copy the certs/keys from the current directory into the pod:

    CODE 
    kubectl -n <namespace> cp . scanner-extlib-0:/home/ot-service/extlib/

  4. Verify that the files were copied successfully:

    CODE 
    kubectl -n <namespace> exec -it scanner-extlib-0 -- ls -al /home/ot-service/extlib/

Assign Public Key to Snowflake User

  1. Execute an ALTER USER command to assign the public key to a Snowflake user.

    CODE 
    alter user <snowflakeUSERNAME> set rsa_public_key=’MIIBIjANBgkqh...’;

  2. You can retrieve the contents of the key using the following command:

    CODE 
    vi rsa_key.pub

  3. Execute a DESCRIBE USER command to verify the user’s public key.

    CODE 
    desc user <snowflakeUSERNAME>;

Scan Profile Support

Note:

For additional details on scan profile configuration and management, see Setting Up Scan Profiles.

Limits

  • Max Number of Databases

  • Max Number of Schemas per Database

  • Max Number of Tables per Schema

  • Limitation Method for Scanning Rows per Table (Catalog & Scan type only)

    • First with Limit

    • Random Percentage

    • Random Percentage with Limit

Scope

  • Intelligent Sampling Scan

Scan Options

  • Tables

  • Views

Column Exclusion

  • Free text field for column data type

Wildcard

Wildcards are not supported for column inclusion or exclusion.

  • Example: *Name* will not work to include/exclude both firstName and lastName columns

  • Instead, you must specify the exact column names separated by commas (e.g. firstName,lastName).

Important:

Columns Excluded in the Data Dictionary

When a column is excluded, it is still cataloged by the Data Dictionary. This means:

  • Basic metadata for the column is retrieved.

  • During extraction, the actual column content is skipped based on the configuration.

Include/Exclude Path Configuration Fields

  • Database

  • Schema

  • Table

  • Column

Collected Objects & Fields

Objects

Fields

Databases

  • Name

Schemas

  • Name

Tables

  • Name

Columns

  • Name

  • Column Index (Order)

  • Comment

  • Nullable (T/F)

  • Size Estimate

  • Comment

Keys

  • Primary Key Name

  • Primary Key Columns

  • Foreign Key Name

  • Foreign Key Columns