top of page

Getting Started

Schema Secure is a Snowflake Native App that simplifies and standardizes schema-level access control. With a single setup, it creates three schema-specific database roles:
 

  • Read-Only: grants usage and select privileges on all current and future schema objects.

  • Read/Create: inherits read-only permissions and adds object creation privileges.

  • Schema Owner: inherits read/create permissions and grants ownership of the schema (this role is also assigned to SYSADMIN).
     

To begin:
 

  1. Install the Schema Secure package from the Snowflake Marketplace.

  2. Use either the Streamlit interface or the stored procedure SCHEMA_SECURE.CORE.SET_UP_SCHEMA_ACCESS_ROLES()to create schema access roles.

  3. Grant access by executing:

     

GRANT DATABASE ROLE <qualified role name> TO ROLE <functional role name>;

Core Features & How to Use Them

  • Automated Role Creation: Quickly generate consistent read-only, read/create, and owner roles for each schema.

  • Granular Privilege Control: Enforce least privilege by mapping schema access roles to functional roles.

  • Flexible Interface: Manage access via a Streamlit UI or directly through SQL procedures.

  • Secure & Auditable: Procedures are designed with input validation, safe dynamic SQL, and follow Snowflake best practices.

    Example:

CALL SCHEMA_SECURE.CORE.SET_UP_SCHEMA_ACCESS_ROLES(['ACCOUNTING.TAX', 'ANALYTICS_DEV_DB.WEB_EVENTS_STAGE']);

GRANT DATABASE ROLE ACCOUNTING.TAX__READ_ONLY__SCHEMA_ACCESS_ROLE TO ROLE ACCOUNTING__ROLE;

Setup & Configuration

  • Prerequisites:

    • A role with MANAGE GRANTS, USAGE, and CREATE DATABASE ROLE privileges (e.g., ACCOUNTADMIN or equivalent).

  • Procedure Deployment:

    • Deploy provided procedures (e.g., CREATE_DATABASE_ROLES, GET_SCHEMA_OBJECT_NAMES) into your chosen database/schema.

  • Grant Procedure Usage:

    • Add the procedures as references using SYSTEM$REFERENCE() and SCHEMA_SECURE.CORE.UPDATE_REFERENCE.

  • Configure Edition:

CALL SCHEMA_SECURE.CORE.SET_UP_SCHEMA_ACCESS_ROLES(['ACCOUNTING.TAX', 'ANALYTICS_DEV_DB.WEB_EVENTS_STAGE']);

GRANT DATABASE ROLE ACCOUNTING.TAX__READ_ONLY__SCHEMA_ACCESS_ROLE TO ROLE ACCOUNTING__ROLE;

FAQ & Troubleshooting

Q: What privileges does Schema Secure require?
A: The app needs access to stored procedures owned by a role with MANAGE GRANTS, USAGE, and CREATE DATABASE ROLE. This lets you control its power without direct broad privilege grants.
 

Q: How can I verify what the app can do?
A: You can inspect the procedure code to review its operations—everything is transparent.
 

Q: Can I customize role names?
A: Yes, role names follow the pattern <schema>__<access level>__SCHEMA_ACCESS_ROLE. You can adjust as needed in your implementation.

CALL SCHEMA_SECURE.CORE.SET_UP_SCHEMA_ACCESS_ROLES(['ACCOUNTING.TAX', 'ANALYTICS_DEV_DB.WEB_EVENTS_STAGE']);

GRANT DATABASE ROLE ACCOUNTING.TAX__READ_ONLY__SCHEMA_ACCESS_ROLE TO ROLE ACCOUNTING__ROLE;

Updates & Release Notes

Stay tuned for updates as we expand Schema Secure’s functionality to:

  • Support new Snowflake object types.

  • Provide enhanced reporting for access reviews.

  • Include additional UI features for easier configuration.

Additional Resources

Snowflake Access Control Overview
Role Hierarchy and Privilege Inheritance
Learn how Snowflake roles are structured and how privileges are inherited through role hierarchies. This is essential to understanding how Schema Secure integrates with Snowflake’s native access control model.
 

Snowflake Database Roles and Role Hierarchies
Database Roles and Role Hierarchies
Get details on how database roles work in Snowflake, including how they can simplify privilege management and enforce least privilege — the core principle Schema Secure builds on.
 

Snowflake Data Sharing Documentation
Snowflake Data Sharing
Official guide on data sharing, useful for understanding Marketplace listings and secure data sharing practices.

bottom of page