CODEWITHSUNDEEP
snowflake-cloud-data-platform
user2821300
user2821300

Reputation: 319

Snowflake view privileges

I have two Snowflake databases/schemas: database.schema.A and database.schema.B. Database.schema.A has its own user - UserA. I need to create or replace a view in database.schema.B using UserA's privileges. UserA can perform select, insert, and deletes on tables in database.schema.B but not on the view in database.schema.B.
Shouldn't UserA be able to create or replace the view in database.schema.B if UserA has full/all privileges? If not, is there another way for UserA to drop and recreate the view when updated records are available?

Upvotes: 0

Views: 1117

Answers (3)

Suzy Lockwood
Suzy Lockwood

Reputation: 1180

The role that has OWNERSHIP permission on the view is the role who can recreate/drop the view. This is the same for any object.

Additionally, the role will need to need USAGE on the view's database/schema AND access to the underlying data (e.g., if the view is created off a table, they need access to the table including USAGE on database and schema for the table).

Upvotes: 0

Simon D
Simon D

Reputation: 6279

Users cannot be assigned privileges in Snowflake, only roles can. Assuming that you mean RoleA and RoleB rather than UserA and UserB then:

  • If RoleB created the view database.schema.B then only RoleB (the "owner"), or a role that contains it as a member (higher in the role hierarchy), can drop or replace it. Even if you provide all privileges to another role, it won't be able to drop it.

  • Users can assume different roles. If you log in with UserA it can assume RoleB if it needs to perform drop/replace operations that RoleB has ownership of. Of course, this means you need to assign UserA to RoleB.

Upvotes: 0

Jagadish Chigurupati
Jagadish Chigurupati

Reputation: 1

UserA can create or replace view in database.schema.B provided if UserA has privileges to create views in that database. Grant UserA privileges and can update the views.

Upvotes: 0

Related Questions