iDENTIFY Mischief App Documentation

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

1. Install the app through Snowflake Marketplace
2. Configure required permissions via Security tab
3. Set up warehouse reference
4. Configure notification recipients
5. 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

1. Captures current user state
2. Compares against previous state
3. 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

1. Missing permissions
   • Check Security tab
   • Verify all required privileges
2. Reference binding failures
   • Ensure warehouse exists
   • Verify warehouse permissions
3. 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

1. Use dedicated warehouse for monitoring or warehouse that is not always under heavy load
2. Set appropriate monitoring frequency
3. Keep email recipient list current

Maintenance

1. Regular review of change logs
2. Periodic verification of notifications
3. 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