Reference_Usage on Shares

[BenKentzer]

Hello!

I've tried a few different things, but I keep hitting a brick wall.

Essentially, I have 3 databases:

AccountA-MasterDB --> Shared to --> AccountB-Share --> AccountB-UserArea (using secure views).

I only want the users to be able to see the AccountB-UserArea database. There is no user access to AccountA at all, so this part is covered.

AccountB-Share is created by AccountAdmin role - I couldn't see a way of doing this in SnowDDL
AccountB-UserArea is managed by SnowDDL, with the views created by dbt.

I could do this manually by granting reference usage on the share database to the SnowDDL_Admin role (and to the dbt runner role), but I'd rather manage it through SnowDDL if I can.

Any thoughts?

Thanks

Ben

[littleK0i]

How about the following approach?

1. Grant share read privilege to schema owner role of schema with secure views:

owner_share_read:
  - MY_SHARE_NAME

2. Create secure views referencing share using SnowDDL or DBT.

3. Grant schema reader role to business role attached to end users:

schema_read:
  - MY_DB.MY_SCHEMA

End users will have access to secure views, but no access to share itself.

[BenKentzer]

Thank you - all working perfectly!