Granting Data Access

    -- select role to use, generally ACCOUNTADMIN.  See documentation for required permissions if not using ACCOUNTADMIN.
    use role ACCOUNTADMIN;

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

    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 EXECUTE AS CALLER
    AS $$
        var connection = snowflake.createStatement({
            sqlText: `SELECT SCHEMA_NAME FROM ${DATABASE_NAME}.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 ${DATABASE_NAME} TO APPLICATION ${APP_NAME}`,
                `GRANT USAGE ON SCHEMA ${DATABASE_NAME}.${schemaName} TO APPLICATION ${APP_NAME}`,
                `GRANT SELECT ON ALL TABLES IN SCHEMA ${DATABASE_NAME}.${schemaName} TO APPLICATION ${APP_NAME}`,
                `GRANT SELECT ON ALL VIEWS IN SCHEMA ${DATABASE_NAME}.${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 bot with the Database Tools will be able to access this data, and when such a bot is deployed to Slack,
    -- some bots 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 bots to only select users on Slack.
    -- Replace <your app name> with the name of your database you want to grant
    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 bots