This directory contains all Data Definition Language (DDL) scripts for creating and managing the Snowflake RBAC Framework metadata tables and supporting objects.
The RBAC Framework uses two core metadata tables referenced in the RBAC_Framework_Handbook.md:
audit.adw_rbac_metadata - Configuration table for permission mappingsaudit.adw_rbac_audit_log - Audit trail for all operationsComplete installation script that creates all objects in one operation.
Contents:
ADW_CONTROL and schema auditUsage:
-- Execute as SYSADMIN or ACCOUNTADMIN role
USE ROLE SYSADMIN;
EXECUTE IMMEDIATE (SELECT GET_DDL('PROCEDURE', 'audit.USP_GRANT_RBAC()'));
-- Then run the script
Standalone DDL for the RBAC metadata configuration table.
Table: audit.adw_rbac_metadata
| Column | Type | Description |
|---|---|---|
rbac_id |
NUMBER(38) IDENTITY | Auto-incrementing primary key |
database_name |
VARCHAR(100) | Target database name |
schema_name |
VARCHAR(100) | Target schema name |
table_name |
VARCHAR(100) | Target table name |
role_name |
VARCHAR(100) | Role to grant permissions to |
permission_type |
VARCHAR(50) | SELECT, INSERT, UPDATE, DELETE, ALL |
effective_start_date |
DATE | When permission becomes effective |
effective_end_date |
DATE | When permission expires (optional) |
description |
VARCHAR(500) | Justification/description |
record_status_cd |
VARCHAR(1) | A=Active, I=Inactive |
record_created_by |
VARCHAR(50) | Creator user |
record_create_ts |
TIMESTAMP_NTZ(9) | Creation timestamp |
record_updated_by |
VARCHAR(50) | Last updater user |
record_updated_ts |
TIMESTAMP_NTZ(9) | Last update timestamp |
Indexes Created:
idx_adw_rbac_metadata_uk - Unique composite keyidx_adw_rbac_metadata_db - Database name lookupsidx_adw_rbac_metadata_role - Role-based queriesidx_adw_rbac_metadata_dates - Effective date range queriesidx_adw_rbac_metadata_status - Status filteringView Created:
vw_active_rbac_metadata - Active permissions based on effective datesStandalone DDL for the RBAC audit log table.
Table: audit.adw_rbac_audit_log
| Column | Type | Description |
|---|---|---|
log_id |
NUMBER(38) IDENTITY | Auto-incrementing primary key |
operation_type |
VARCHAR(50) | GRANT, REVOKE, DRY_RUN, etc. |
database_name |
VARCHAR(100) | Database involved |
schema_name |
VARCHAR(100) | Schema involved |
table_name |
VARCHAR(100) | Table involved |
role_name |
VARCHAR(100) | Role involved |
permission_type |
VARCHAR(50) | Permission type |
sql_statement |
VARCHAR(4000) | Actual SQL executed |
execution_status |
VARCHAR(20) | SUCCESS, FAILED, PENDING |
error_message |
VARCHAR(4000) | Error details if failed |
execution_time |
TIMESTAMP_NTZ(9) | When operation executed |
record_status_cd |
VARCHAR(1) | A=Active, I=Inactive |
record_created_by |
VARCHAR(50) | Operation user |
record_create_ts |
TIMESTAMP_NTZ(9) | Log creation time |
record_updated_by |
VARCHAR(50) | Last updater |
record_updated_ts |
TIMESTAMP_NTZ(9) | Last update time |
Indexes Created:
idx_adw_rbac_audit_status - Status and timestamp queriesidx_adw_rbac_audit_operation - Operation type queriesidx_adw_rbac_audit_role - Role-based audit queriesidx_adw_rbac_audit_db - Database/schema/table queriesidx_adw_rbac_audit_timestamp - Timeline queriesViews Created:
vw_successful_rbac_operations - Successful operations audit trailvw_failed_rbac_operations - Failed operations for troubleshootingvw_rbac_operations_summary - Daily operation summary-- Connect to your Snowflake account
USE ROLE SYSADMIN;
-- Copy and paste the contents of INSTALL_RBAC_METADATA.ddl
-- The script will create:
-- - Database: ADW_CONTROL
-- - Schema: ADW_CONTROL.audit
-- - Tables and indexes
-- - Views
-- - Permissions
-- Check tables exist
SHOW TABLES IN SCHEMA ADW_CONTROL.audit;
-- Verify table structure
DESC TABLE ADW_CONTROL.audit.adw_rbac_metadata;
DESC TABLE ADW_CONTROL.audit.adw_rbac_audit_log;
-- Check views
SHOW VIEWS IN SCHEMA ADW_CONTROL.audit;
-- Insert sample metadata
INSERT INTO ADW_CONTROL.audit.adw_rbac_metadata
(database_name, schema_name, table_name, role_name, permission_type, description, record_created_by, record_updated_by)
VALUES
('ADW_PROD', 'ADS', 'T_MBR_DIM', 'FIN_ANALYST_ROLE', 'SELECT', 'Read access for Finance analysts', 'ADMIN_USER', 'ADMIN_USER');
-- Verify insertion
SELECT * FROM ADW_CONTROL.audit.vw_active_rbac_metadata;
These DDL scripts directly implement the database objects described in RBAC_Framework_Handbook.md:
The Streamlit application in /app/main.py uses these metadata tables as its data source:
adw_rbac_metadataadw_rbac_audit_log-- Archive old audit records
DELETE FROM ADW_CONTROL.audit.adw_rbac_audit_log
WHERE record_create_ts < DATEADD(MONTH, -12, CURRENT_TIMESTAMP());
-- Deactivate expired permissions
UPDATE ADW_CONTROL.audit.adw_rbac_metadata
SET record_status_cd = 'I'
WHERE effective_end_date <= CURRENT_DATE()
AND record_status_cd = 'A';
-- Review failed operations
SELECT * FROM ADW_CONTROL.audit.vw_failed_rbac_operations
WHERE record_create_ts > DATEADD(DAY, -7, CURRENT_DATE());
For questions or issues related to these DDL scripts, refer to: