Snowflake Managed Access Schemas: Centralized Control for Enhanced Security

In the world of data management, ensuring the security and proper governance of your data is paramount. Snowflake, a leading cloud data platform, offers "managed grants" features to help you achieve this. In this blog post, we will explore what managed access schemas are, their benefits, and how to implement them with detailed examples. I will primarily focus on the fundamental concept.

Things to cover

• What is managed schema?
• Why do we need it?
• Step by step Scenario based implementation
• Conclusion

What Are Managed Access Schemas?

Managed access schemas in Snowflake provide a centralized and controlled way to manage permissions for objects within a schema. Unlike regular schemas, where object owners can manage grants on their objects, in managed access schemas, only the schema owner or roles with the MANAGE GRANTS privilege can grant or revoke privileges on objects. This centralized control helps in maintaining strict security policies and ensuring consistent access management practices.

Why Use Managed Access Schemas?

1. Centralized Control: Only designated roles can manage grants, reducing the risk of unauthorized access.
2. Enhanced Security: Limits the ability to grant or revoke privileges to trusted roles, ensuring tighter security.
3. Simplified Governance: Easier to audit and manage access permissions centrally.

Step-by-Step Guide to Managed Access Schemas

Step 1: Creating a Managed Access Schema

First, let's create a managed access schema. The following code demonstrates how to create a schema with managed access. Usually database and schema are created by a system admin.

USE ROLE SYSADMIN; 
USE DATABASE DEMO3A_DB;
CREATE OR REPLACE SCHEMA MSCHEMA WITH MANAGED ACCESS;        

In managed access schemas, object owners are unable to issue grant privileges. Instead, only the schema owner or a role with the MANAGE GRANTS privilege assigned to it can manage the grant privileges.The SECURITYADMIN and ACCOUNTADMIN roles inherently have the MANAGE GRANTS privilege. Thus, both roles can manage the grant privileges on all managed schemas.

This below point is for the confused and curious mind!!!

In Snowflake, the SYSADMIN role does not have the MANAGE GRANTS privilege by default, which means it cannot manage grants on objects within a managed access schema. However, SYSADMIN can still create managed access schemas because creating a schema and defining its access type is a separate operation from managing grants on objects within that schema.

Step 2: Granting Privileges to a normal user.

Next, we need to grant the necessary privileges to a normal user in our organization who belongs to finance department , so they can create objects within this schema and do their development.

USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE DEMO3A_DB TO ROLE finance_role;
GRANT USAGE ON SCHEMA DEMO3A_DB.MSCHEMA TO ROLE finance_role;
GRANT CREATE TABLE ON SCHEMA DEMO3A_DB.MSCHEMA TO ROLE finance_role;
GRANT CREATE VIEW ON SCHEMA DEMO3A_DB.MSCHEMA TO ROLE finance_role;        

Now let's switch to finance user

Let finance user login . The finance user with the finance_role can now create objects within the managed access schema.

USE ROLE finance_role;
USE DATABASE demo3a_db;
USE SCHEMA mschema;
CREATE TABLE sample_table (id INT, data STRING);
CREATE VIEW sample_view AS
SELECT
    *
FROM
    sample_table;        

Now let's say another user in our organization wants to access this object. While this finance user can create tables and views and owner of the object, they still do not have the ability to manage grants on these objects to other roles. This ensures that only authorized roles can control access to these objects.

Let's see what happens if he/she executes this , it would throw an error

GRANT SELECT ON TABLE sample_table TO ROLE FIVETRAN_ROLE;         

Managing Grants in a Managed Access Schema

To illustrate the power and control of managed access schemas, let's explore how grants are managed.

Example: Granting SELECT Privilege on a Table

Only the schema owner or roles with the MANAGE GRANTS privilege can grant access to objects within the schema. Here’s how the SECURITYADMIN role can grant SELECT privilege on the sample_table to a specific role:

USE ROLE SYSADMIN;
GRANT USAGE ON DATABASE DEMO3A_DB TO ROLE FIVETRAN_ROLE;
GRANT USAGE ON SCHEMA DEMO3A_DB.MSCHEMA TO ROLE FIVETRAN_ROLE;

GRANT
SELECT
    ON TABLE DEMO3A_DB.MSCHEMA.sample_table TO ROLE FIVETRAN_ROLE;        

As I mentioned above , either schema owner or a one with managed grant privilege can manage access of the of the object in this schema. So SYSADMIN being the owner of the schema can grant the privilege too as we can see in the above command. Though it's not good practice. Instead use the security admin role .

USE ROLE SECURITYADMIN;
GRANT USAGE ON DATABASE DEMO3A_DB TO ROLE FIVETRAN_ROLE;
GRANT USAGE ON SCHEMA DEMO3A_DB.MSCHEMA TO ROLE FIVETRAN_ROLE;

GRANT
SELECT
    ON TABLE DEMO3A_DB.MSCHEMA.sample_table TO ROLE FIVETRAN_ROLE;        

So a legit user who possess the fivetran_role would be able to access the object in the msschema.

Pre-requisite
USE ROLE SECURITYADMIN;
GRANT ROLE FIVETRAN_ROLE TO USER INTEGRATION_USER;
GRANT USAGE ON WAREHOUSE FIVETRAN_WAREHOUSE TO ROLE FIVETRAN_ROLE;        

The INTEGRATION_USER can log in and select FIVETRAN_ROLE and it should be able to query the table now . As there is no data it is showing zero rows in my case.

Benefits of Using Managed Access Schemas

1. Centralized Control Over Access Permissions

In a managed access schema, the control over granting and revoking privileges is centralized. This reduces the risk of unauthorized access and ensures that only trusted roles can manage permissions. For example, the security team can maintain strict control over who has access to sensitive data by using managed access schemas.

2. Enhanced Security and Compliance

By limiting the ability to manage grants to only those roles with the MANAGE GRANTS privilege, organizations can ensure that access control policies are consistently enforced. This is particularly important for compliance with data protection regulations and internal security policies.

3. Simplified Governance and Auditing

With centralized control, auditing and tracking access permissions becomes simpler. Organizations can easily review who has access to what data and ensure that only authorized users have the necessary permissions. This simplifies governance and enhances the overall security posture.

Conclusion

Managed access schemas in Snowflake provide a robust and secure way to manage access permissions. By centralizing the control of grants and limiting the ability to manage permissions to trusted roles, organizations can enhance their security and maintain consistent access control policies.

Whether you are a data administrator looking to enforce strict security policies or a data governance aiming to ensure regulatory compliance, managed access schemas offer a powerful tool to achieve these goals.

Implementing managed access schemas may require a bit more setup and careful planning, but the benefits in terms of security and control far outweigh the initial effort. If you haven't explored this feature yet, it's time to consider how managed access schemas can enhance your Snowflake environment.

Feel free to reach out with any questions or share your experiences with managed access schemas in the comments below!