Fundamentally I want
- my "DBA" user; the original account which has the SYSADMIN and ACCOUNTADMIN roles, to be able to see any object in the database as well as its data. It seems that at least at the level of ACCOUNTADMIN I should be able to do this.
- a "power" user; via a role (dr_uce_role) I can assign at a database level where the user can do virtually everything within a database.
I thought I had made the power user role with the following code:
--grant power to engineer
grant all privileges on database dr_ev to dr_uce_role;
grant all privileges on all schemas in database dr_ev to dr_uce_role;
grant all privileges on all tables in schema dr_ev.public to dr_uce_role;
grant all privileges on all views in schema dr_ev.public to dr_uce_role;
grant select on future tables in schema dr_ev.public to dr_uce_role;
grant select on future views in schema dr_ev.public to dr_uce_role;
grant all privileges on all tables in schema dr_ev.stg to dr_uce_role;
grant all privileges on all views in schema dr_ev.stg to dr_uce_role;
grant select on future tables in schema dr_ev.stg to dr_uce_role;
grant select on future views in schema dr_ev.stg to dr_uce_role;
This user then created objects in the schemas. However my "DBA" user at SYSADMIN could not see the objects at all. With role ACCOUNTADMIN the user can see the objects, but not query them. My understanding is that ACCOUNTADMIN is the top level account, and can take ownership of these objects anyway, so if this is supposed to be a security feature I don't really understand how it is providing much protection as it can always steal ownership?
I tried changing ownership of an object as ACCOUNTADMIN to SYSADMIN, to find it had a blocking privilege;
grant ownership on dr_ev.stg.load_opportunity to sysadmin;
SQL execution error: Dependent grant of privilege 'DELETE' on securable 'DR_EV.STG.LOAD_OPPORTUNITY' to role 'DR_UCE_ROLE' exists. It must be revoked first. More than one dependent grant may exist: use 'SHOW GRANTS' command to view them. To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON TO REVOKE CURRENT GRANTS'.`
I tried taking ownership with
grant ownership on all tables in schema dr_ev.stg to sysadmin revoke current grants;
which did work - although it left my power user unable to see the objects. So I granted them back with
grant all privileges on all tables in schema dr_ev.stg to dr_uce_role;
However I want my power user to be able to create or replace this table. I believe this requires the DROP TABLE privilege, although apparently my power user grants do not provide it, and I am unclear on how I should be providing it?
I will not say I have the greatest understanding of Snowflake privileges and am wondering if the statements above like grant all privileges on all tables in schema do not live at the schema level to blanket apply to all tables, but actually sets object level permissions and my original approach has simply been too granular as I do not actually wish to manage anything at object level. That being said, I am unclear in the doc how to manage at a higher level than object anyway if the statement is actually just a shortcut to set many object privileges. How can I accomplish my original goals?
