/*--------------------------------------- Parameters used in this script ---------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<validio_role>                                       -- Name of validio-role. It's not recommended to use an already existing role.
<validio_user>                                       -- Name of validio-user. It's not recommended to use an already existing user.
<some_password>                                      -- Password for vaidio-user.
<validio_warehouse>                                  -- Name of validio-warehouse. It's not recommended to use an already existing warehouse.
<database_to_monitor> [, <database2_to_monitor>...]  -- Optionally filter access to one or more databases

*/------------------------------------------------ Script -----------------------------------------------------

-- Use role useradmin (or equivalent) for user / role steps
USE ROLE useradmin;

-- Create Validio role
CREATE ROLE IF NOT EXISTS <validio_role>;
GRANT ROLE <validio_role> TO ROLE sysadmin;

-- Create Validio user
CREATE USER IF NOT EXISTS <validio_user>
PASSWORD = <some_password>
DEFAULT_ROLE = <validio_role>
DEFAULT_WAREHOUSE = <validio_warehouse>;
GRANT ROLE <validio_role> TO USER <validio_user>;

-- Use role sysadmin (or equivalent) for warehouse steps
USE ROLE sysadmin;

-- Create Validio warehouse to separate workload and costs. Increase warehouse size if needed.
CREATE WAREHOUSE IF NOT EXISTS <validio_warehouse> WAREHOUSE_SIZE='XSMALL' INITIALLY_SUSPENDED=TRUE AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
GRANT USAGE ON WAREHOUSE <validio_warehouse> TO ROLE <validio_role>;

-- Use role accountadmin (or equivalent) for account steps
USE ROLE accountadmin;

-- Grant access to the SNOWFLAKE database. Validio uses this information to be able to provide comprehensive and optimized data quality monitoring functionality.
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE <validio_role>;

-- Grant access to the data to validate. You can either write your own statements, or to simplify the process use the following query which generates grant statements for all
-- existing and future tables etc. in your account, or filtered to specific databases if you like. Run the query and then copy&paste the result (the generated statements) into 
-- a worksheet. Review the generated statements before you run them, to ensure the grants will apply as you prefer.
-- Note that future grants are set on database-level. This means they have no effect in case future grants for the same object types also exist on schema-level. In such case we recommend 
-- you to manually tailor each future grant statement as you wish. More info here: https://docs.snowflake.com/en/sql-reference/sql/grant-privilege#considerations
SELECT p || DATABASE_NAME || IFF(o = 0, ':    --------------', ' TO ROLE <validio_role>;') AS statement
FROM (
    SELECT 0 AS o, '--------------    Privileges for database ' AS p UNION ALL
    SELECT 1 AS o, 'GRANT USAGE ON DATABASE ' AS p UNION ALL
    SELECT 2 AS o, 'GRANT USAGE ON ALL SCHEMAS IN DATABASE ' AS p UNION ALL
    SELECT 3 AS o, 'GRANT SELECT ON ALL TABLES IN DATABASE ' UNION ALL
    SELECT 4 AS o, 'GRANT SELECT ON ALL VIEWS IN DATABASE ' UNION ALL
    SELECT 5 AS o, 'GRANT SELECT ON ALL EXTERNAL TABLES IN DATABASE ' UNION ALL
    SELECT 6 AS o, 'GRANT SELECT ON ALL STREAMS IN DATABASE ' UNION ALL
    SELECT 7 AS o, 'GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE ' UNION ALL
    SELECT 8 AS o, 'GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ' UNION ALL
    SELECT 9 AS o, 'GRANT SELECT ON FUTURE TABLES IN DATABASE ' UNION ALL
    SELECT 10 AS o, 'GRANT SELECT ON FUTURE VIEWS IN DATABASE ' UNION ALL 
    SELECT 11 AS o, 'GRANT SELECT ON FUTURE EXTERNAL TABLES IN DATABASE ' UNION ALL
    SELECT 12 AS o, 'GRANT SELECT ON FUTURE STREAMS IN DATABASE ' UNION ALL
    SELECT 13 AS o, 'GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE '
),
SNOWFLAKE.INFORMATION_SCHEMA.DATABASES
--    WHERE DATABASE_NAME IN ('<database_to_monitor>' [, '<database2_to_monitor>'...]) -- Uncomment and edit this line to limit the result to specific databases
ORDER BY DATABASE_NAME, o
;

/*--------------------------------------- Parameters used in this script -------------------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<validio_role>         -- Name of validio-role you want to remove.
<validio_user>         -- Name of validio-user you want to remove.
<validio_warehouse>    -- Name of validio-warehouse you want to remove.

*/------------------------------------------------ Script -----------------------------------------------------

-- Use role accountadmin (or equivalent) to when removing
USE ROLE accountadmin;

-- This script generates statements to remove Validio. Run the script, then copy&paste the statements into a worksheet and run them to remove all Validio-related entities (see inline-comments below for more details) from this Snowflake-account. We recommend you to carefully look through the statements before you run then to make sure you really want to remove each entity.
SELECT 'DROP ROLE IF EXISTS <validio_role>;'  UNION ALL      	-- Drop-statement for Validio-role
SELECT 'DROP USER IF EXISTS <validio_user>;'  UNION ALL      	-- Drop-statement for Validio-user
SELECT 'DROP WAREHOUSE IF EXISTS <validio_warehouse>;'; 			-- Drop-statement for Validio-warehouse