Add Copy Grants to Options in Table and View Materialization's

[Carolus-Holman]

Add Copy Grants to View and Table Config Statement

Typically with Snowflake Tables and Views we use a Copy Grants switch to maintain grants on current View and Tables in Snowflake. Adding this will alleviate some of the Post-Hook commands to add Grants to existing objects. If the object has no grants this is ignored by snowflake.

Alternative Approach using Post-Hooks

Adding Post-Hook Events to Re-Apply Grants. grant select on table to role myrole

Snowflake Feature Only

This feature is for the Snowflake Adapter only. If developers are not responsible for maintaining security (as in roles, as we should all be thinking about security) this would alleviate additional work by the Administrator by maintaining the current security on the object

Benefits?

This will benefit Developers and Admins when maintaining current access permissions to an object. Also it will help to create objects that need to maintain roles and are re-initialized outside of DBT. The Copy Grants switch will be present in the DDL of the Snowflake object when viewing the source through the GET_DDL command or viewing the View source.

Sample Code Output

CREATE OR REPLACE View myView /* Added Copy Grants Here */ COPY GRANTS as ( Select 1 as Id );

[drewbanin]

Hey @Carolus-Holman - cool idea! How do you imagine that grants will be added to these tables/views initially? Is that a manual process? And what happens for net-new models -- those will be created without any grants at all, right?

I think dbt users should be able to administrate permissions on their tables/views however they'd like, so I'm happy to add this functionality to dbt. I also think it might lead to some pretty confusing permissions issues! In particular, I wonder how this will work with dev/staging/prod envs -- does your workflow for setting grants encompass setting grants appropriately in all of these different environments?

[Carolus-Holman]

Initially (Net-New) the COPY GRANTS Functionality will not affect the object if there are no Grants on the object. It is meant to either Maintain existing Grants or Grants that placed on the object in the future. Using a switch it would be optional similar to the Secure Switch. We have Views that have dozens of roles on them, and using the GUI it takes quite sometime to replace those. (though they can be scripted). Since the Copy Grants is on the object contextually in the db you don’t need to identify the roles. From: Drew Banin <[email protected]> Sent: Thursday, September 12, 2019 9:29 AM To: fishtown-analytics/dbt <[email protected]> Cc: Carolus Holman <[email protected]>; Mention <[email protected]> Subject: Re: [fishtown-analytics/dbt] Add Copy Grants to Options in Table and View Materialization's (#1744) Hey @Carolus-Holman<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_Carolus-2DHolman&d=DwMCaQ&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=cGUsYTIA9hWUxMz_Z6vNK0clp0WE7M1VgX5Tf_8y8YY&m=jlz6jIc3V5zSfyyvTCrV1cefYdpTlA0x0hLUvCVRkSo&s=jpc3ZP-unBAioOUhvoZ9bqndDXUFEyBDeGwWELpMPMU&e=> - cool idea! How do you imagine that grants will be added to these tables/views initially? Is that a manual process? And what happens for net-new models -- those will be created without any grants at all, right? I think dbt users should be able to administrate permissions on their tables/views however they'd like, so I'm happy to add this functionality to dbt. I also think it might lead to some pretty confusing permissions issues! In particular, I wonder how this will work with dev/staging/prod envs -- does your workflow for setting grants encompass setting grants appropriately in all of these different environments? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_fishtown-2Danalytics_dbt_issues_1744-3Femail-5Fsource-3Dnotifications-26email-5Ftoken-3DACV2EVXCKBYC3YYAADHJY4TQJJGZDA5CNFSM4IWFG5JKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6SCRYQ-23issuecomment-2D530852066&d=DwMCaQ&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=cGUsYTIA9hWUxMz_Z6vNK0clp0WE7M1VgX5Tf_8y8YY&m=jlz6jIc3V5zSfyyvTCrV1cefYdpTlA0x0hLUvCVRkSo&s=CYfzD1Hi5jC6ws8Z-c_3lsLkGvjfitNBgJ1Rjj7yP5Y&e=>, or mute the thread<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_ACV2EVWWDTOCJKB7VJES3Z3QJJGZDANCNFSM4IWFG5JA&d=DwMCaQ&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=cGUsYTIA9hWUxMz_Z6vNK0clp0WE7M1VgX5Tf_8y8YY&m=jlz6jIc3V5zSfyyvTCrV1cefYdpTlA0x0hLUvCVRkSo&s=JA_RoZCKSmu0gdqLs5MCZpIDLVUUkNLMJYyKXCD-QW0&e=>.

[drewbanin]

fixed by #1747