Managed Access Schemas
In a standard Snowflake schema, the owner of an object (the role that created the table or view) has full control over it. This includes the ability to grant access to that object to any other role.
In enterprise environments, this can lead to security nightmares. If an Analyst creates a table in a shared schema, they own it, and they could theoretically grant SELECT access to the PUBLIC role, exposing sensitive data to everyone in the account.
Managed Access Schemas solve this by centralizing grant management.
What is a Managed Access Schema?
A Managed Access Schema restricts the ability to grant privileges on objects inside the schema.
In a Managed Access Schema, only the schema owner (or a role with the global MANAGE GRANTS privilege) can grant or revoke privileges on objects in that schema. The role that actually created the table (the object owner) loses the ability to manage grants for their own table.
Hands-On Demonstration: Standard vs Managed Schemas
Let's compare the behavior of a Standard Schema and a Managed Access Schema.
1. Environment Setup
Run this in your Snowflake worksheet:
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_TRAINING_DB;
USE DATABASE BEEMS_TRAINING_DB;
-- Create two schemas: One standard, one managed
CREATE SCHEMA IF NOT EXISTS STANDARD_SCHEMA;
CREATE SCHEMA IF NOT EXISTS SECURE_MANAGED_SCHEMA WITH MANAGED ACCESS;
-- Create a functional role for a Data Engineer
USE ROLE SECURITYADMIN;
CREATE OR REPLACE ROLE DE_ROLE;
-- Grant the Data Engineer the ability to create tables in both schemas
GRANT USAGE ON DATABASE BEEMS_TRAINING_DB TO ROLE DE_ROLE;
GRANT USAGE ON SCHEMA BEEMS_TRAINING_DB.STANDARD_SCHEMA TO ROLE DE_ROLE;
GRANT CREATE TABLE ON SCHEMA BEEMS_TRAINING_DB.STANDARD_SCHEMA TO ROLE DE_ROLE;
GRANT USAGE ON SCHEMA BEEMS_TRAINING_DB.SECURE_MANAGED_SCHEMA TO ROLE DE_ROLE;
GRANT CREATE TABLE ON SCHEMA BEEMS_TRAINING_DB.SECURE_MANAGED_SCHEMA TO ROLE DE_ROLE;
2. The Danger of Standard Schemas
The Data Engineer creates a table in the standard schema and grants access to it.
USE ROLE DE_ROLE;
-- Create a table. DE_ROLE is now the owner.
CREATE OR REPLACE TABLE BEEMS_TRAINING_DB.STANDARD_SCHEMA.SENSITIVE_DATA (
CUSTOMER_ID INT,
SSN STRING
);
-- Because DE_ROLE owns the table, it can grant access to ANYONE.
GRANT SELECT ON TABLE BEEMS_TRAINING_DB.STANDARD_SCHEMA.SENSITIVE_DATA TO ROLE PUBLIC;
Result: Success. This is a massive security risk.
3. The Security of Managed Access Schemas
Now, the Data Engineer creates a table in the Managed Access schema and tries the same thing.
USE ROLE DE_ROLE;
-- Create a table. DE_ROLE is now the owner.
CREATE OR REPLACE TABLE BEEMS_TRAINING_DB.SECURE_MANAGED_SCHEMA.SENSITIVE_DATA (
CUSTOMER_ID INT,
SSN STRING
);
-- DE_ROLE tries to grant access to PUBLIC...
-- THIS WILL FAIL!
GRANT SELECT ON TABLE BEEMS_TRAINING_DB.SECURE_MANAGED_SCHEMA.SENSITIVE_DATA TO ROLE PUBLIC;
Result: Error. "Grant not executed: Insufficient privileges."
Even though DE_ROLE owns the table, the schema is a Managed Access Schema. Only the Schema Owner (SYSADMIN) or SECURITYADMIN can execute that grant.
Converting Existing Schemas
If you realize a schema should have been created as a Managed Access Schema, you can easily alter it.
USE ROLE SYSADMIN; -- Or the schema owner
-- Convert a standard schema to a managed access schema
ALTER SCHEMA BEEMS_TRAINING_DB.STANDARD_SCHEMA ENABLE MANAGED ACCESS;
-- Convert a managed access schema back to a standard schema
ALTER SCHEMA BEEMS_TRAINING_DB.SECURE_MANAGED_SCHEMA DISABLE MANAGED ACCESS;
When to Use Managed Access Schemas
- Multi-Team Environments: Whenever multiple roles have
CREATE TABLEprivileges in the same schema. - Compliance Requirements: When regulations (like HIPAA, GDPR, SOC2) dictate that access control must be strictly centralized.
- Data Governance: It prevents rogue users from accidentally exposing data by sharing it with the
PUBLICrole.
[!TIP] Best Practice: In a production data warehouse, almost all schemas should be created
WITH MANAGED ACCESS. Standard schemas should only be used for personal sandboxes where security is less strict.
Summary
- Standard Schemas: Object owners can grant access to their objects.
- Managed Access Schemas: Only the Schema Owner (or
SECURITYADMIN) can grant access to objects, centralizing security. - Create them using
CREATE SCHEMA ... WITH MANAGED ACCESS. - Convert them using
ALTER SCHEMA ... ENABLE MANAGED ACCESS.