Snowflake ...Alter table is not working in managed schema in snowflake

Question

Application ; SNowflake Database : test_dev_db schema : test_dev_sch --> this is managed schema schema owner : dataadmin table owner : dba

DBA role has created table TEST in the schema TEST_dev_db.TEST_DEV_SCH(owner role is DATAADMIN), Since this is a managed schema the owner of the table should be DATAADMIN however the owner is being showed as DBA. Now DBA wants to alter the table and it is not allowing DBA to alter the table and it is also not allowing DATAADMIN saying SQL access control error: Insufficient privileges to operate on table

Why don't you check the information_schema.table to see the ownership and also check the show grants to the role to validate if the operating role has alter grant given to them or not. If not, then alter or all grant to be given to the role which operating to the table. — Data Engineering Simplified

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-05-13T15:12:41

Let me correct a misunderstanding:

Since this is a managed schema the owner of the table should be DATAADMIN however the owner is being showed as DBA.

No, it's not correct. The owner should be the role DBA in your case. The difference between regular and managed schemas is how the privileges of the objects are managed:

In regular schemas, the owner of an object (i.e. the role that has the OWNERSHIP privilege on the object) can grant further privileges on their objects to other roles. In managed schemas, the schema owner manages all privilege grants, including future grants, on objects in the schema. Object owners retain the OWNERSHIP privileges on the objects; however, only the schema owner can manage privilege grants on the objects.

https://docs.snowflake.com/en/sql-reference/sql/create-schema.html#optional-parameters

So it's expected that the role DBA will be the owner of the table, but it will not be able to manage the privileges of the table. On the other hand, the role DBA can alter the table.

I tried to reproduce the issue, but as I see, everything is working as expected:

use role accountadmin;
create database test_dev_db;
create schema TEST_DEV_SCH with MANAGED ACCESS;
create role dataadmin;
create role dba;
create role test_role; -- dummy role for testing

-- required grants for accessing DB
grant usage on database test_dev_db to dataadmin;
grant ownership on schema TEST_DEV_SCH to role dataadmin;

-- required grants for creating table, and accessing the schema
grant usage on database test_dev_db to dba;
grant usage, CREATE TABLE on schema TEST_DEV_SCH to dba;

-- required grants to switch these users
grant role dba to user gokhan;
grant role dataadmin to user gokhan;

Now I switch to the role DBA, create a new table, and then add a new column (for testing if we can alter the table):

use role dba;

create table TEST ( id number);

alter table TEST add column v variant; -- successful

While I'm still using the role DBA, I will try to grant SELECT privilege to the role TEST_ROLE:

grant select on TEST to role TEST_ROLE; -- failed

SQL execution error: Not authorized in a managed access schema. Use the schema owner role or a role with the MANAGE GRANTS privilege.

It fails as expected. So I switch to the role DATAADMIN (the owner of the managed schema), and try to alter the table and then grant SELECT privilege to the role TEST_ROLE:

use role dataadmin;

alter table TEST add column v2 variant; -- failed

SQL access control error: Insufficient privileges to operate on table 'TEST'

Again, as expected, the owner of the schema can not alter the table, but we can grant SELECT privilege to another role as we are the owner of the table:

grant select on TEST to role TEST_ROLE; -- successful

show grants on table TEST; 

+-----------+--------------+--------------+------------+
| privilege | grantee_name | grant_option | granted_by |
+-----------+--------------+--------------+------------+
| OWNERSHIP | DBA          | true         | DBA        |
| SELECT    | TEST_ROLE    | false        | DBA        |
+-----------+--------------+--------------+------------+

Although the SELECT privilege has granted by the role DATAADMIN, it is shown as "DBA".

Snowflake ...Alter table is not working in managed schema in snowflake

2 Answers