Integrating Snowflake with the Delinea Platform (ITP)

Integrating Snowflake with the Delinea platform (ITP) provides visibility into your Snowflake users, groups, roles, permissions, and access to databases and tables. This integration improves visibility into the database infrastructure, addressing access and permissions gaps, ultimately enhancing security and compliance within the data layer's IAM management.

Prerequisites

  • Ensure you have Delinea Platform ITDR Global Admin permission.

  • Ensure that you can log into Snowflake with Account Admin privileges.

  • If you want to integrate using a certificate instead of a user password, make sure to upload that in Snowflake.

Setup in the Delinea Platform

  1. Navigate to Discovery >Source.

  2. Select Create source. The Create discovery source page opens.

  3. Select the Snowflake Threat Protection option. Select the Continue button.

  4. Enter the Snowflake Account ID, Username, and Password.

  5. (Optional) You can enter a unique name for this integration. By default, the integration will be named Snowflake.

  6. (Optional) Choose a current platform user as the app owner.

  7. Select Save.

  8. The Snowflake tile opens as a connected app. The synchronization process begins, and its status will be shown when it is completed.

Configuration in Snowflake

  1. Log in to Snowflake.

    Make sure to remember the account ID for logging in. You will need it to complete the integration.

  2. From the menu, select Worksheets.

  3. From the Worksheets page, click the + (at the top right corner of the page) and select the SQL Worksheet option. A worksheet is displayed where you can run SQL queries.

  4. Copy the following queries to the SQL worksheet:

    Copy

    Copy the following queries to the SQL worksheet: 

    USE ROLE ACCOUNTADMIN;
                            -- Create a user with the least privilege to carry out the tasks
                            CREATE OR REPLACE ROLE DELINEA_ROLE;
                            CREATE OR REPLACE USER DELINEA_USER
                            PASSWORD = '<your_password>';
                            -- Note the default role will be used during scan
                            ALTER USER DELINEA_USER SET DEFAULT_ROLE = DELINEA_ROLE;
                            -- Add user to Delinea role
                            GRANT ROLE DELINEA_ROLE TO USER DELINEA_USER;
                            -- Activities are inside views of SNOWFLAKE database
                        GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE DELINEA_ROLE;

  5. Run the queries. Replace the your_password query with a password of your choice.

  6. Create a dedicated database and warehouse to allow Delinea to run a stored procedure, which will automatically update with new databases.

    Copy

    Use the following queries:

    -- Database for creation of stored procedure
                            CREATE OR REPLACE DATABASE DELINEA_DATABASE;
                            -- Give access to database to ROLE_NAME
                            GRANT USAGE, MONITOR ON DATABASE DELINEA_DATABASE TO ROLE DELINEA_ROLE;
                            -- Create warehouse for running the stored procedure
                            CREATE OR REPLACE WAREHOUSE DELINEA_WAREHOUSE WITH
                            WAREHOUSE_SIZE = 'XSMALL'
                            WAREHOUSE_TYPE = 'STANDARD'
                            AUTO_SUSPEND = 300
                        AUTO_RESUME = TRUE;

  7. Create a new stored procedure that scans all databases in Snowflake during each connector run. This procedure grants usage permissions to all new databases. The stored procedure is created by the owner and is not modifiable, ensuring consistent actions.

    Copy

    Use the following queries:

    -- Create a stored procedure that will grant usage privileges on all databases- Initialize to execute as owner 

                            CREATE OR REPLACE PROCEDURE DELINEA_DATABASE.public.grant_usage_on_all_dbs(role_name STRING) 

                            returns varchar not null 

                            language javascript 

                            execute as owner 

                            as 

                            $$ 

                            // Function to execute a single grant statement 

                            function execute_Statement(query, dbName, ret) { 

                            ret += "\n- " + query; 

     

                            try { 

                            snowflake.execute( {sqlText: query} ); 

                            ret += "\n\t-SUCCESS-"; 

                            } catch (err) { 

                            ret += "\n\tQuery Failed for " + dbName; 

                            ret += "\n\tCode: " + err.code; 

                            ret += "\n\tState: " + err.state; 

                            ret += "\n\tMessage: " + err.message; 

                            ret += "\n\tStack Trace:\n" + err.stackTraceTxt; 

                            } 

     

                            return ret; 

                            } 

     

                            // we build up the return value string 

                            let ret = "USAGE access granted on: "; 

     

                            // Get all databases 

                            const res = snowflake.execute( {sqlText: "SHOW DATABASES;"} ); 

     

                            // Iterate through each database row 

                            while (res.next())  { 

                            // Extract the database name 

                            const dbName = '"' + res.getColumnValue(2) + '"'; 

     

                            // Add each DB processed to the return value 

                            ret += "\n\n\n- " + dbName; 

     

                            if (dbName === '"SNOWFLAKE"' || dbName === '"SNOWFLAKE_SAMPLE_DATA"') { 

                            ret += "\n\t-Imported privileges on snowflake db already added to the role-"; 

                            } else { 

                            // Create grant usage queries on database and its schemas/tables 

                            const grantDbQuery = `GRANT USAGE ON DATABASE ` + dbName + ` TO ROLE DELINEA_ROLE;`; 

                            const grantSchemaQuery = `GRANT USAGE ON ALL SCHEMAS IN DATABASE  ` + dbName + ` TO ROLE DELINEA_ROLE;`; 

                            const grantFutureSchemaQuery = `GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ` + dbName + ` TO ROLE DELINEA_ROLE;`; 

                            const grantFutureTableQuery = `GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ` + dbName + ` TO ROLE DELINEA_ROLE;`; 

     

                            // Execute each grant query and add it to the return value 

                            ret = execute_Statement(grantDbQuery, dbName, ret); 

                            ret = execute_Statement(grantSchemaQuery, dbName, ret); 

                            ret = execute_Statement(grantFutureSchemaQuery, dbName, ret); 

                            ret = execute_Statement(grantFutureTableQuery, dbName, ret); 

                            }; 

                            }; 

                            return ret; 

                            $$ 

                        ; 

    Grant the Delinea role the permission to execute the stored procedure. Use the following queries:

    Copy

    Use the following queries:

    -- Grant usage on procedure and and grant usage and operate on warehouse to the role we just created 
                            GRANT USAGE ON PROCEDURE DELINEA_DATABASE.public.grant_usage_on_all_dbs(STRING) TO ROLE DELINEA_ROLE; 
                            GRANT USAGE, OPERATE ON WAREHOUSE DELINEA_WAREHOUSE TO ROLE DELINEA_ROLE; 
     
                            -- Execute the stored procedure 
                            USE WAREHOUSE DELINEA_WAREHOUSE; 
                        CALL DELINEA_DATABASE.public.grant_usage_on_all_dbs('DELINEA_ROLE'); 

    8. If you want to run the code all at once to create the integration:

  8. Copy

    Snoflake Integration Full code

    USE ROLE ACCOUNTADMIN;

    -- Create a user with the least privilege to carry out the tasks
    CREATE OR REPLACE ROLE DELINEA_ROLE;

    CREATE OR REPLACE USER DELINEA_USER
    PASSWORD = '<your_password>';

    -- Note the default role will be used during scan
    ALTER USER DELINEA_USER SET DEFAULT_ROLE = DELINEA_ROLE;

    -- Add user to Delinea role
    GRANT ROLE DELINEA_ROLE TO USER DELINEA_USER;

    -- Activities are inside views of SNOWFLAKE database
    GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE DELINEA_ROLE;

    -- Database for creation of stored procedure
    CREATE OR REPLACE DATABASE DELINEA_DATABASE;

    -- Give access to database to ROLE_NAME
    GRANT USAGE, MONITOR ON DATABASE DELINEA_DATABASE TO ROLE DELINEA_ROLE;

    -- Create warehouse for running the stored procedure
    CREATE OR REPLACE WAREHOUSE DELINEA_WAREHOUSE WITH
        WAREHOUSE_SIZE = 'XSMALL'
        WAREHOUSE_TYPE = 'STANDARD'
        AUTO_SUSPEND = 300
        AUTO_RESUME = TRUE;

    -- Create a stored procedure that will grant usage privileges on all databases - Initialize to execute as owner
    CREATE OR REPLACE PROCEDURE DELINEA_DATABASE.public.grant_usage_on_all_dbs(role_name STRING)
        returns varchar not null
        language javascript
        execute as owner
        as
        $$
        // Retrieve the role_name parameter passed to the stored procedure
        var roleName = ROLE_NAME;

        // Function to execute a single grant statement
        function execute_Statement(query, dbName, ret) {
            ret += "\n- " + query;

            try {
                snowflake.execute({ sqlText: query });
                ret += "\n\t-SUCCESS-";
            } catch (err) {
                ret += "\n\tQuery Failed for " + dbName;
                ret += "\n\tCode: " + err.code;
                ret += "\n\tState: " + err.state;
                ret += "\n\tMessage: " + err.message;
                ret += "\n\tStack Trace:\n" + err.stackTraceTxt;
            }

            return ret;
        }

        // We build up the return value string
        var ret = "USAGE access granted on: ";

        // Get all databases
        var res = snowflake.execute({ sqlText: "SHOW DATABASES;" });

        // Iterate through each database row
        while (res.next()) {
            // Extract the database name
            var dbName = '"' + res.getColumnValue(2) + '"';

            // Add each DB processed to the return value
            ret += "\n\n\n- " + dbName;

            if (dbName === '"SNOWFLAKE"' || dbName === '"SNOWFLAKE_SAMPLE_DATA"') {
                ret += "\n\t-Imported privileges on snowflake db already added to the role-";
            } else {
                // Create grant usage queries on database and its schemas/tables
                var grantDbQuery = `GRANT USAGE ON DATABASE ${dbName} TO ROLE ${roleName};`;
                var grantSchemaQuery = `GRANT USAGE ON ALL SCHEMAS IN DATABASE ${dbName} TO ROLE ${roleName};`;
                var grantFutureSchemaQuery = `GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ${dbName} TO ROLE ${roleName};`;
                var grantFutureTableQuery = `GRANT USAGE ON FUTURE TABLES IN DATABASE ${dbName} TO ROLE ${roleName};`;

                // Execute each grant query and add it to the return value
                ret = execute_Statement(grantDbQuery, dbName, ret);
                ret = execute_Statement(grantSchemaQuery, dbName, ret);
                ret = execute_Statement(grantFutureSchemaQuery, dbName, ret);
                ret = execute_Statement(grantFutureTableQuery, dbName, ret);
            }
        }
        return ret;
        $$;

    -- Grant usage on procedure and and grant usage and operate on warehouse to the role we just created
    GRANT USAGE ON PROCEDURE DELINEA_DATABASE.public.grant_usage_on_all_dbs(STRING) TO ROLE DELINEA_ROLE;
    GRANT USAGE, OPERATE ON WAREHOUSE DELINEA_WAREHOUSE TO ROLE DELINEA_ROLE;

    -- Execute the stored procedure
    USE WAREHOUSE DELINEA_WAREHOUSE;
    CALL DELINEA_DATABASE.public.grant_usage_on_all_dbs('DELINEA_ROLE');
The user we ask to create can be modified and named as any username. All other items, delinea_role, delinea_database, and delinea_warehouse, must be named as requested.