iDENTIFY Mischief App Documentation
iDENTIFY Mischief is a Snowflake native app that monitors user changes within a Snowflake account. It tracks modifications to user accounts, roles, and permissions, sending notifications when changes are detected.
Architecture
Components
1. SQL Objects
- Tables for tracking users and changes
- Stored procedures for monitoring and configuration
- Streamlit interface for setup
- Automated tasks for continuous monitoring
2. Core Tables
app.USER_TRACKING - Historical user state
app.MISCHIEF_CONFIG - App configuration
app.CHANGES_ANALYSIS - Change detection results
app.CURRENT_USERS - Current user state snapshot
app.RECENT_CHANGES - Change history log
app.PROCEDURE_STATE - Procedure execution state
3. Key Procedures
config.get_current_users() - Retrieves current user state
config.monitor_user_changes() - Detects and processes changes
config.CREATE_MONITORING_TASK() - Sets up automated monitoring
config.register_single_reference() - Manages references
Required Permissions
Account Privileges
- EXECUTE TASK
- EXECUTE MANAGED TASK
- READ SESSION
- IMPORTED PRIVILEGES ON SNOWFLAKE DB
- MANAGE WAREHOUSES
References
- CONSUMER_WAREHOUSE (USAGE, OPERATE, MONITOR)
Installation Requirements
Prerequisites
1. Notification integration:
CREATE NOTIFICATION INTEGRATION ID_MISCHIEF_INTEGRATION
TYPE = EMAIL
ENABLED = TRUE;
GRANT USAGE ON INTEGRATION ID_MISCHIEF_INTEGRATION TO APPLICATION IDENTIFY_MISCHIEF_APP;
2. Active warehouse for task execution
Installation Steps
- Install the app through Snowflake Marketplace
- Configure required permissions via Security tab
- Set up warehouse reference
- Configure notification recipients
- Select monitoring frequency
Configuration Options
Monitoring Frequency
- Every 5 Minutes (produces a lot of activity, driving up warehousing cost)
- Hourly
- Every 6 Hours
Notification Settings
- Multiple email recipients supported
- Recipients must be verified Snowflake users
- Notifications include detailed change information
Monitoring Process
Change Detection
- Captures current user state
- Compares against previous state
- Identifies:
- Added users
- Removed users
- Role changes
Notification System
- Triggered on detected changes
- Includes:
- Change counts by type
- Detailed change information
- Timestamp of changes
Data Structure
USER_TRACKING Table
USERNAME STRING
DEFAULT_ROLE STRING
DEFAULT_WAREHOUSE STRING
DEFAULT_NAMESPACE STRING
CREATED_ON TIMESTAMP
LAST_ALTERED_ON TIMESTAMP
DELETED_AT TIMESTAMP
CHANGES_ANALYSIS Table
added_count INTEGER
removed_count INTEGER
role_changes_count INTEGER
changes VARIANT
analyzed_at TIMESTAMP
Development Guidelines
Reference Management
- Single warehouse reference
- Non-multi-valued design
- Automated binding during setup
- Warehouse is selected by the user of the app
Troubleshooting
Common Issues
- Missing permissions
- Check Security tab
- Verify all required privileges
- Reference binding failures
- Ensure warehouse exists
- Verify warehouse permissions
- Notification issues
- Confirm integration setup
- Verify email configurations
Logging
- Procedure execution states stored in PROCEDURE_STATE table
- Error messages captured with timestamps
- Detailed change history in RECENT_CHANGES
Security Considerations
Access Control
- Application roles: app_user, app_admin
- Least privilege principle
- Scoped permissions to application schema
Data Protection
- No sensitive data storage
- Email notifications to verified users only
- Automated cleanup of temporary data
Best Practices
Configuration
- Use dedicated warehouse for monitoring or warehouse that is not always under heavy load
- Set appropriate monitoring frequency
- Keep email recipient list current
Maintenance
- Regular review of change logs
- Periodic verification of notifications
- Monitor procedure execution states
Support
Documentation
- Manifest.yml for app configuration
- setup.sql for installation
- app.py for UI implementation
Dependencies
name: sf_env
channels:
- snowflake
dependencies:
- snowflake-snowpark-python
- snowflake-native-apps-permission
Unify your bank data with iDENTIFY
Contact Us