CODEWITHSUNDEEP
snowflake-cloud-data-platform
Santhosh
Santhosh

Reputation: 11844

snowflake: unable to run the alter table because of insuffcient permissions

In snowflake, I have a table "dbtest"."schematest"."testtable" created by role Accountadmin.

Now i want to alter a column in this table using another role roletest;

I have given all access till table leve to roletest

# using accountadmin roles i have granted all the access

use role accountadmin
use warehouse testwarehouse

# granted database level permission to the role

GRANT USAGE ON DATABASE DBTEST TO ROLE ROLETEST;

# granted schema level permission to the rol

GRANT USAGE ON SCHEMA DBTEST.SCHEMATEST TO ROLE ROLETEST;
GRANT SELECT ON ALL VIEWS IN SCHEMA DBTEST.SCHEMATEST TO ROLE ROLETEST;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA DBTEST.SCHEMATEST TO ROLE ROLETEST;
GRANT SELECT ON ALL TABLES IN SCHEMA DBTEST.SCHEMATEST TO ROLE ROLETEST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA DBTEST.SCHEMATEST TO ROLE ROLETEST;
GRANT USAGE, CREATE FUNCTION, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE EXTERNAL TABLE, CREATE MATERIALIZED VIEW, CREATE TEMPORARY TABLE ON SCHEMA DBTEST.SCHEMATEST TO ROLE ROLETEST;

# also at table leve i have granted the permissions

GRANT INSERT, DELETE, REBUILD, REFERENCES, SELECT, TRUNCATE, UPDATE ON TABLE "DBTEST"."SCHEMATEST"."testtable" TO ROLE "ROLETEST";

Now when i try

use role roletest;
use warehouse roletest_wh;

alter table "DBTEST"."SCHEMATEST"."testtable" alter column c1 drop not null;

i get the error

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

I also tried

GRANT OWNERSHIP ON "DBTEST"."SCHEMATEST"."testtable" TO ROLE roletest;

it gives error

SQL execution error: Dependent grant of privilege 'SELECT' on securable "DBTEST"."SCHEMATEST"."testtable" to role 'SYSADMIN' 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 <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.

Upvotes: 0

Views: 1471

Answers (3)

Zabalero
Zabalero

Reputation: 1

I think you're missing MODIFY permission on the database. And MODIFY permission on schema.

Upvotes: -1

Santhosh
Santhosh

Reputation: 11844

https://docs.snowflake.com/en/sql-reference/sql/grant-ownership.html#examples

In a single step, revoke all privileges on the existing tables in the mydb.public schema and transfer ownership of the tables (along with a copy of their current privileges) to the analyst role:

grant ownership on all tables in schema mydb.public to role analyst copy current grants;

Grant ownership on the mydb.public.mytable table to the analyst role along with a copy of all current outbound privileges on the table:

grant ownership on table mydb.public.mytable to role analyst copy current grants;

Upvotes: 1

NickW
NickW

Reputation: 9818

Only the owner of an object can alter that object.

When changing ownership you need to use one of the revoke/copy grants options

Upvotes: 0

Related Questions