Granting Data Access

The Genesis Data Agents can help you analyze your data in Snowflake. To do so, you need to grant this application access to your data. The helper procedure below can help you grant access to read all tables and views in a database to this application.

---

NOTE: Any Genesis Data Agent with the Database Tools will be able to access this data, and when such an agent is deployed to Slack, some agents may be accessible by all Slack users, unless they are configured by Eve to only be usable by select Slack users.

So grant data in this manner only to non-sensitive data that is ok for any Slack user to view, or first have Eve limit the access to the Database Tools-enabled agents to only select users on Slack.

In order to take full advantage of the Genesis Data Agents, you must allow them access to specific objects in your Snowflake account. Click on the “Grant Data Access” page in the side menu to open up a set of helper scripts.

The scripts will create a stored procedure that accepts a database name and Genesis Data Agents name as arguments and will grant SELECT access to all tables and views in all schemas of that database.

The procedure will be created in the GENESIS_LOCAL_DB.SETTINGS schema, which was created as a part of the installation and configuration process.

Complete Setup Script

Run this complete script in a Snowflake worksheet to set up the grant procedure and grant access to your databases. The script includes:

• Creation of the helper procedure
• Commands to grant access to local databases
• Commands for shared databases (commented out)
• Commands for SNOWFLAKE database access (commented out)

Copy the code below & run it in a Snowflake Snowsight worksheet.

-- use an authorized role

-- set the name of the installed application
set APP_DATABASE = 'GENESIS_DATA_AGENTS';

CREATE DATABASE IF NOT EXISTS GENESIS_LOCAL_DB;
CREATE SCHEMA IF NOT EXISTS GENESIS_LOCAL_DB.SETTINGS;
USE SCHEMA GENESIS_LOCAL_DB.SETTINGS;
USE WAREHOUSE XSMALL; -- or use your warehouse if not XSMALL

CREATE OR REPLACE PROCEDURE GENESIS_LOCAL_DB.SETTINGS.grant_schema_usage_and_select_to_app(database_name STRING, APP_NAME STRING)    
RETURNS STRING 
LANGUAGE JAVASCRIPT  
AS $$
    var dbName = `"${DATABASE_NAME.replace(/"/g, '')}"`;
    var connection = snowflake.createStatement({
        sqlText: `SELECT '"' || REPLACE(replace(SCHEMA_NAME,'"',''), '.', '"."') || '"' as SCHEMA_NAME FROM ${dbName}.INFORMATION_SCHEMA.SCHEMATA`
    });
    var result = connection.execute();
    
    while (result.next()) {
        var schemaName = result.getColumnValue(1);
        if (schemaName === '"INFORMATION_SCHEMA"') {
            continue;
        }
        var sqlCommands = [
            `GRANT USAGE ON DATABASE ${dbName} TO APPLICATION ${APP_NAME}`,
            `GRANT USAGE ON SCHEMA ${dbName}.${schemaName} TO APPLICATION ${APP_NAME}`,
            `GRANT SELECT ON ALL TABLES IN SCHEMA ${dbName}.${schemaName} TO APPLICATION ${APP_NAME}`,
            `GRANT SELECT ON ALL VIEWS IN SCHEMA ${dbName}.${schemaName} TO APPLICATION ${APP_NAME}`,
        ];
        
        for (var i = 0; i < sqlCommands.length; i++) {
            try {
                var stmt = snowflake.createStatement({sqlText: sqlCommands[i]});
                stmt.execute();
            } catch(err) {
                // Return error message if any command fails
                return `Error executing command: ${sqlCommands[i]} - ${err.message}`;
            }
        }
    }  
    return "Successfully granted USAGE and SELECT on all schemas, tables, and views to role " + APP_NAME;
$$;

-- see your databases
show databases;

-- To use on a local database in your account, call with the name of the database to grant 
-- 
-- Note! any agent with the Database Tools will be able to access this data, and when such an agent is deployed to Slack, 
-- some agents may be accessible by all Slack users, unless they are configured by Eve to only be usable by select Slack
-- users. So grant data in this manner only to non-sensitive data that is ok for any Slack user to view, or first have 
-- Eve limit the access to the Database Tools-enabled agents to only select users on Slack.

-- Replace <your db name> with the name of your database you want to grant. Note, the database name is case-sensitive
call GENESIS_LOCAL_DB.SETTINGS.grant_schema_usage_and_select_to_app('<your db name>',$APP_DATABASE);

-- If you want to grant data that has been shared to you via Snowflake data sharing, use this process below instead
-- the above:

-- see inbound shares 
show shares;

-- to grant an inbound shared database to the Genesis application 
-- (uncomment this by removing the // and put the right shared DB name in first)
// grant imported privileges on database <inbound_share_db_name> to application IDENTIFIER($APP_DATABASE);

-- If you want to to grant access to the SNOWFLAKE share (Account Usage, etc.) to the Genesis application 
-- uncomment this by removing the // and run it:
// grant imported privileges on database SNOWFLAKE to application IDENTIFIER($APP_DATABASE);

--- once granted, Genesis will automatically start to catalog this data so you can use it with Genesis agents

In the worksheet, ensure that the APP_DATABASE parameter is set to the correct Genesis Data Agents application name.

Set the role, context, and warehouse before creating the stored procedure.

The procedure accepts the application name (APP_NAME) and database name (database_name) as parameters. It will find all of the schemas that exist in the database, excluding the INFORMATION_SCHEMA schema, and loop through each, granting the following privileges to the Genesis Data Agents application:

• USAGE on the database
• USAGE on each schema
• SELECT on all tables in each schema
• SELECT on all views in each schema

Granting privileges to the Genesis Data Agents application does not provide the Genesis provider access to your data or metadata. Only the Genesis Data Agents application will be able to take advantage of the privileges granted.

The stored procedure is meant to help grant the Genesis Data Agents application access to appropriate data in your Snowflake account. However, it can be modified, as necessary, to include or exclude specific schemas, objects, etc.

Individual Grant Scenarios

Option 1: Grant Access to Local Databases

After running the setup script above, use this command to grant access to a specific database. Replace <your db name> with the name of your database (case-sensitive).

-- To use on a local database in your account, call with the name of the database to grant 
-- 
-- Note! any agent with the Database Tools will be able to access this data, and when such an agent is deployed to Slack, 
-- some agents may be accessible by all Slack users, unless they are configured by Eve to only be usable by select Slack
-- users. So grant data in this manner only to non-sensitive data that is ok for any Slack user to view, or first have 
-- Eve limit the access to the Database Tools-enabled agents to only select users on Slack.

-- Replace <your db name> with the name of your database you want to grant. Note, the database name is case-sensitive
call GENESIS_LOCAL_DB.SETTINGS.grant_schema_usage_and_select_to_app('<your db name>',$APP_DATABASE);

Option 2: Grant Access to Shared Databases

If you want to grant data that has been shared to you via Snowflake data sharing, use this process instead:

• First run show shares; to see available shares
• Then uncomment and modify the grant command below
• Replace <inbound_share_db_name> with your share name

-- If you want to grant data that has been shared to you via Snowflake data sharing, use this process below instead
-- the above:

-- see inbound shares 
show shares;

-- to grant an inbound shared database to the Genesis application 
-- (uncomment this by removing the // and put the right shared DB name in first)
// grant imported privileges on database <inbound_share_db_name> to application IDENTIFIER($APP_DATABASE);

Option 3: Grant Access to SNOWFLAKE Database

To grant access to the SNOWFLAKE share (Account Usage, Organization Usage, etc.), uncomment and run this command:

-- If you want to to grant access to the SNOWFLAKE share (Account Usage, etc.) to the Genesis application 
-- uncomment this by removing the // and run it:
// grant imported privileges on database SNOWFLAKE to application IDENTIFIER($APP_DATABASE);

--- once granted, Genesis will automatically start to catalog this data so you can use it with Genesis agents

Next Steps: Once granted, Genesis will automatically start to catalog this data so you can use it with Genesis agents. The data will become available in the agent interfaces within a few minutes.

Application Name: GENESIS_DATA_AGENTS - This is the name of your Genesis application instance that will receive the grants.