Understanding Snowflake Managed Schemas
Come On a Journey on My Own Learning of the Snowflake Managed Schemas and its purpose
Over the past month I have been studying to renew my Snowflake SnowPro Advanced Architect exam, and I did not know as much as I should about Managed Schemas in Snowflake, so I decided to take this on as a project and write about it.
What are Managed Snowflake Schemas?
A Schema is a logical grouping of database objects like tables and views. Each schema belongs to a single database. You can use the usual commands CREATE CLONE, ALTER, DROP, UNDROP, USE SCHEMA, as well as SHOW SCHEMAS.
There are two kinds of Schemas objects, Regular and Managed, and the purpose of using managed access schemas is to improve security. Managed schemas lock down the privileges provided to objects.
In regular schemas, an object owner, or someone with OWNERSHIP privilege on an object, can grant access to their objects, including the ability to to manage grants. For example, If AUGUSTO created a table in a regular schema they will automatically be the object owner and will have full power to grant privileges to other roles.
With managed access schemas, the object owner no longer has the ability to grant privileges. Only the schema owner, aka, the role with the ownership privilege on the schema, or a role with the MANAGE GRANTS privilege can grant privilegers. The benefit of a managed access schema is that it centralizes privilege management into the role that has OWNERSHIP of the schema.
Let's make a practical exercise out of this. Let me be clear that I did not have to reinvent the wheel and copied a lot of these scripts from the sources below. Credit goes to them, but I did clean the scripts and added more explanations. I did this for my learning.
Regular Schema Creation
--Creating a Regular Schema
USE ROLE sysadmin;
DROP DATABASE if exists testdb;
CREATE OR REPLACE DATABASE testdb;
CREATE OR REPLACE SCHEMA testdb.test_schema;
CREATE OR REPLACE WAREHOUSE TEST_WH;
USE ROLE securityadmin;
CREATE OR REPLACE ROLE DEV_ROLE;
CREATE OR REPLACE ROLE DEV_ANALYST_ROLE;
CREATE OR REPLACE ROLE DEV_INGEST_ROLE;
USE ROLE sysadmin;
GRANT USAGE on database testdb TO ROLE DEV_ROLE;
GRANT USAGE on schema testdb.test_schema TO ROLE DEV_ROLE;
GRANT CREATE TABLE ON SCHEMA testdb.test_schema TO ROLE DEV_ROLE;
GRANT USAGE on database testdb TO ROLE DEV_INGEST_ROLE;
GRANT USAGE on schema testdb.test_schema TO ROLE DEV_INGEST_ROLE;
GRANT CREATE TABLE ON SCHEMA testdb.test_schema TO ROLE DEV_INGEST_ROLE;
-- The Analyst does not get any table permissions as they will be assigned later
GRANT USAGE on database testdb TO ROLE DEV_ANALYST_ROLE;
GRANT USAGE on schema testdb.test_schema TO ROLE DEV_ANALYST_ROLE;
GRANT USAGE ON WAREHOUSE TEST_WH TO ROLE DEV_ROLE;
GRANT USAGE ON WAREHOUSE TEST_WH TO ROLE DEV_ANALYST_ROLE;
GRANT USAGE ON WAREHOUSE TEST_WH TO ROLE DEV_INGEST_ROLE;
USE ROLE securityadmin;
GRANT ROLE DEV_ROLE to USER AUGUSTO;
GRANT ROLE DEV_ANALYST_ROLE to USER AUGUSTO;
GRANT ROLE DEV_INGEST_ROLE to USER AUGUSTO;
USE ROLE DEV_ROLE;
CREATE OR REPLACE TABLE employee(employeeid number, employeename varchar, salary float);
SELECT current_ROLE();
GRANT SELECT ON employee TO ROLE DEV_ANALYST_ROLE;
GRANT ALL ON employee TO ROLE DEV_INGEST_ROLE;
-- Testing granting different grants to other places by the sysadmin ROLE
USE ROLE sysadmin;
GRANT SELECT ON testdb.test_schema.employee TO ROLE DEV_ANALYST_ROLE;
GRANT SELECT ON employee TO ROLE DEV_ANALYST_ROLE;Managed Schema Creation
-- Creating a Managed Schema
USE ROLE sysadmin;
DROP DATABASE IF EXISTS testdb2;
CREATE DATABASE testdb2;
CREATE SCHEMA managed_schema WITH MANAGED ACCESS;
--create ROLE DEV_ROLE;
--create ROLE DEV_ANALYST_ROLE;
USE ROLE securityadmin;
GRANT USAGE ON DATABASE testdb2 TO ROLE DEV_ROLE;
-- Let's switch to the owner of the schema
USE ROLE sysadmin;
GRANT USAGE ON SCHEMA testdb2.managed_schema TO ROLE DEV_ROLE;
GRANT CREATE TABLE ON SCHEMA testdb2.managed_schema TO ROLE DEV_ROLE;
USE ROLE securityadmin;
GRANT USAGE ON DATABASE testdb2 TO ROLE DEV_INGEST_ROLE;
-- Let's switch to the owner of the schema
USE ROLE sysadmin;
GRANT USAGE ON SCHEMA testdb2.managed_schema TO ROLE DEV_INGEST_ROLE;
GRANT CREATE TABLE ON SCHEMA testdb2.managed_schema TO ROLE DEV_INGEST_ROLE;
USE ROLE securityadmin;
GRANT USAGE ON DATABASE testdb2 TO ROLE DEV_ANALYST_ROLE;
-- Let's switch to the owner of the schema
USE ROLE sysadmin;
GRANT USAGE ON SCHEMA testdb2.managed_schema TO ROLE DEV_ANALYST_ROLE;
GRANT USAGE ON WAREHOUSE TEST_WH TO ROLE DEV_ROLE;
GRANT USAGE ON WAREHOUSE TEST_WH TO ROLE DEV_INGEST_ROLE;
GRANT USAGE ON WAREHOUSE TEST_WH TO ROLE DEV_ANALYST_ROLE;
GRANT ROLE DEV_ROLE TO USER AUGUSTO;
GRANT ROLE DEV_INGEST_ROLE TO USER AUGUSTO;
GRANT ROLE DEV_ANALYST_ROLE TO USER AUGUSTO;
-- DEV_ROLE can manage the schema
USE ROLE DEV_ROLE;
USE DATABASE testdb2;
USE SCHEMA managed_schema;
CREATE OR REPLACE TABLE employee(employeeid number, employeename varchar, salary float);Lets do some testing on the Managed Schema to see how it behaves.
-- If we try to grant select to a different role, Snowflake won't allow it
GRANT SELECT ON employee TO ROLE DEV_ANALYST_ROLE;
-- If we try this again with sysadmin, it is allowed as sysadmin is the owner of the schema
USE ROLE sysadmin;
GRANT SELECT ON testdb2.managed_schema.employee TO ROLE DEV_ANALYST_ROLE;
-- These are still not allowed as the schema owner did not provide those rights
DROP TABLE testdb2.managed_schema.employee;
SELECT * FROM testdb2.managed_schema.employee;
-- On the other hand, the DEV_ROLE, which is the schema owner, can do this
USE ROLE DEV_ROLE;
SELECT * FROM testdb2.managed_schema.employee;
DROP TABLE testdb2.managed_schema.employee;
-- Clean Up DATABASE, ROLES, and WAREHOUSE
USE ROLE ACCOUNTADMIN;
DROP DATABASE testdb;
DROP DATABASE testdb2;
DROP ROLE DEV_ROLE;
DROP ROLE DEV_ANALYST_ROLE;
DROP ROLE DEV_INGEST_ROLE;
DROP WAREHOUSE TEST_WH;Conclusion
I have to be honest and say that I am really not sure why anyone with any semi-serious security concerns or who needs a proper RBAC, would ever use regular schemas. Managed schemas should be the default. It is a much better way to control RBAC in Snowflake. Why would we allow people to provide GRANTS? It sounds like a recipe for disaster, but this is my opinion.
I’m Augusto Rosa, VP of Engineering for Infostrux Solutions. Thanks for reading my blog post. You can follow me on LinkedIn. And subscribe to Infostrux Medium Blogs https://medium.com/infostrux-solutions for the most interesting Data Engineering and Snowflake news.
