Amazon Redshift

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

<validio_user>                                         -- Name of validio-user. It's not recommended to use an alread existing user.
<Some_Password123>                                     -- Password for validio-user.
<user_creating_tables> [, <user2_creating_tables>...]  -- One or more users who will create future tables validio-user should immediately get access to.
<schema_to_monitor> [, <schema2_to_monitor>...]        -- Optionally filter access to one or more schemas 

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

-- An admin-user such as awsuser is necessary to perform this setup. 
SET SESSION AUTHORIZATION 'awsuser';

-- Create Validio-user with necessary grants
CREATE USER <validio_user> PASSWORD '<Some_Password123>';   -- Create a separate Validio-user

-- Grant user access to metadata
ALTER USER <validio_user> SYSLOG ACCESS UNRESTRICTED;   -- Grant access to query logs.
GRANT SELECT ON svv_table_info TO <validio_user>;       -- Grant access to table metadata.

-- 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 in your database, or filtered to specific schemas 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.
SELECT p1 || schema_name || p2 || CASE WHEN o = 0 THEN ':    --------------' ELSE ' TO <validio_user>;' END AS statement
FROM (
    SELECT 0 AS o, '--------------    Privileges for schema ' AS p1, '' AS p2 UNION ALL
    SELECT 1, 'GRANT USAGE ON SCHEMA ', '' UNION ALL
    SELECT 2, 'GRANT SELECT ON ALL TABLES IN SCHEMA ', '' UNION ALL
    SELECT 3, 'ALTER DEFAULT PRIVILEGES FOR USER <user_creating_tables> [, <user2_creating_tables...>...] IN SCHEMA ', ' GRANT SELECT ON TABLES'
) AS s,
information_schema.schemata
--    WHERE schema_name IN ('<schema_to_monitor>' [, '<schema2_to_monitor>'...]) -- Uncomment and edit this line to limit the result to specific schemas
ORDER BY schema_name, o;

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

<validio_user>       -- Name of validio-user you want to remove.
<validio_group>      -- Name of validio-group you want to remove (if you have set up a group for Validio)

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

-- An admin-user such as 'awsuser' is necessary to perform this setup. 
SET SESSION AUTHORIZATION 'awsuser';

-- 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 Catalog. We recommend you to carefully look through the statements before you run then to make sure you really want to remove each entity.
SELECT 'REVOKE ALL ON SCHEMA ' || schema_name || ' FROM <validio_user>;' AS statement FROM pg_catalog.svv_all_schemas UNION ALL  -- Revoke all permissions on schemas from Validio-user...
SELECT 'REVOKE ALL ON ALL TABLES IN SCHEMA ' || schema_name || ' FROM <validio_user>;' FROM pg_catalog.svv_all_schemas UNION ALL  --..and tables and views
SELECT 'REVOKE ALL ON SCHEMA ' || schema_name || ' FROM GROUP <validio_group>;' AS statement FROM pg_catalog.svv_all_schemas UNION ALL  -- Revoke all permissions on schemas from Validio-group... (Remove this row if you haven't set up a dedicated Validio-group)
SELECT 'REVOKE ALL ON ALL TABLES IN SCHEMA ' || schema_name || ' FROM GROUP <validio_group>;' FROM pg_catalog.svv_all_schemas UNION ALL  --..and tables and views (Remove this row if you haven't set up a dedicated Validio-group)
SELECT 'DROP GROUP <validio_group>;' UNION ALL  -- DROP Validio-group (Remove this row if you haven't set up a Validio-group)
SELECT 'DROP USER IF EXISTS <validio_user>;';  -- DROP Validio-user