如何在Snowflake中授予用户/角色类似SQL Server ddl_admin的删表权限？

Got it, let's break down how to grant a user or role the ability to drop any table in a specific Snowflake schema—regardless of who owns the table—matching the behavior of SQL Server's ddl_admin role at the schema level.

Snowflake doesn't have a direct equivalent to ddl_admin, but we can build the required permissions using granular, targeted grants. Here's the step-by-step process:

1. Create a dedicated custom role (best practice) #

Instead of granting permissions directly to a user, create a role for this specific task. This makes permission management cleaner and more scalable:

CREATE ROLE IF NOT EXISTS SCHEMA_TABLE_DROP_ADMIN;

2. Grant prerequisite USAGE permissions #

Before the role can interact with tables in the schema, it needs basic access to the parent database and target schema:

-- Grant access to the database containing the schema
GRANT USAGE ON DATABASE YOUR_TARGET_DB TO ROLE SCHEMA_TABLE_DROP_ADMIN;

-- Grant access to the specific schema where tables need to be dropped
GRANT USAGE ON SCHEMA YOUR_TARGET_DB.YOUR_TARGET_SCHEMA TO ROLE SCHEMA_TABLE_DROP_ADMIN;

3. Grant the critical DROP ANY TABLE permission #

This is the core grant that lets the role drop any table in the schema, no matter who owns it. This is what replicates the ddl_admin behavior you're looking for:

GRANT DROP ANY TABLE ON SCHEMA YOUR_TARGET_DB.YOUR_TARGET_SCHEMA TO ROLE SCHEMA_TABLE_DROP_ADMIN;

4. Assign the role to your target user #

Finally, give the user access to the role so they can leverage these permissions:

GRANT ROLE SCHEMA_TABLE_DROP_ADMIN TO USER YOUR_TARGET_USER;

Key Notes #

• Stick to minimal privileges: Only grant this permission to the exact schema(s) needed. Avoid granting DROP ANY TABLE at the database level unless absolutely necessary—this limits accidental data loss risk.
• Test the setup: Have the user switch to the role to verify the capability:

  USE ROLE SCHEMA_TABLE_DROP_ADMIN;
  USE DATABASE YOUR_TARGET_DB;
  USE SCHEMA YOUR_TARGET_SCHEMA;
  
  -- Try dropping a table owned by another user to confirm access
  DROP TABLE TABLE_OWNED_BY_OTHER_USER;
  

• Extend if needed: If you want the role to drop other objects (like views), add similar grants e.g., GRANT DROP ANY VIEW ON SCHEMA YOUR_TARGET_DB.YOUR_TARGET_SCHEMA TO ROLE SCHEMA_TABLE_DROP_ADMIN;.

内容的提问来源于stack exchange，提问作者SMDBA