Skip to content

Add warehouse and database revocations

Taylor A Murphy requested to merge (removed):revocations-all-perms into master

Merge Request Checklist

  • Link to an issue #1476 (closed)
  • Include the proposed fix or feature
  • Include and update tests for the modified code
  • Include a documentation change
  • Add a CHANGELOG.md entry in the Unreleased section

Once your merge request is complete, please assign it a Meltano maintainer for review cycle. Once the review cycle finished, the reviewer shall approve the merge request so it can be merged by any Meltano maintainer.

This MR follows from !1202 (merged) which added support to match role permissions for role membership. This MR adds the same capabilities for warehouses and databases.

This also moves a spec check from the grant portion to the actual spec loader file. The check will now assert that any database referenced in the schema or table privileges must be referenced in the database privileges. I did this to simplify the grant function for tables and views as well as to provide stronger guarantees that we're "seeing" all of the databases.

For databases (and presumably I'll need to do this for tables and schemas) the grants script goes through read, then write, and then a new "revoke" section for databases. I did this b/c when I'm comparing the state of the permissions on snowflake I have a single object but the spec has two (read/write). I first consolidate the privileges across both read/write and then revoke all the write privileges for the databases that have privileges but aren't in the spec. I confirmed that revoking privileges that a role doesn't have on an object that does exist works fine (i.e. it doesn't return an error and is essentially a no-op).

What I did to test.

  • Warehouse grants

I altered the analyst_people role to have analyst_xl as the warehouse. It actually has privileges on analyst_xs. The generated commands were

GRANT usage ON warehouse analyst_xl TO ROLE analyst_people;
GRANT operate ON warehouse analyst_xl TO ROLE analyst_people;
REVOKE usage ON warehouse analyst_xs FROM ROLE analyst_people;
REVOKE operate ON warehouse analyst_xs FROM ROLE analyst_people;

  • I ran the snowflake_spec_with_entity_errors schema and successfully generated the new validation errors - analytics and loader are referenced in schemas and tables but not in databases. Errors were generated for both "databases". This new test also caught this error in our existing production spec for bamboohr 😄

  • Databases

I altered the analyst_people role to remove analytics and snowflake database references from the role and I altered the bamboohr role to remove references to analytics. The generated commands were:

REVOKE usage, monitor, create schema ON database analytics FROM ROLE analyst_people;
REVOKE imported privileges ON database snowflake FROM ROLE analyst_people;
REVOKE usage, monitor, create schema ON database analytics FROM ROLE bamboohr;
Edited by Melty Bot

Merge request reports