I Built an Enterprise Key Pair Manager for Snowflake Service User in Snowflake — Architecture…

I Built an Enterprise Key Pair Manager for Snowflake Service User in Snowflake — Architecture, Security, and Code

TL;DR

Snowflake’s Named Key Pairs (GA July 2026) replace the old two-slot RSA system with named, expirable, role-scoped credentials. I built a production-grade multi-page Streamlit app with a stored procedure service layer, 4-tier RBAC, immutable audit logging, dark theme UI, reactive configuration, and automated monitoring. This post covers the full architecture, the security model, UX decisions, and the design patterns that make it production-ready.

Why I Built This

Every platform team managing 30+ service accounts in Snowflake has felt this pain:

Two keys max. Legacy RSA_PUBLIC_KEY / RSA_PUBLIC_KEY_2 — rotation is a high-wire act between two slots
No expiration. Keys live forever. Forgotten keys become permanent attack surface.
No attribution. Which key is dbt? Which is Airflow? Impossible to tell from DESCRIBE USER.
No role scoping. A leaked key inherits the service account’s default role — often overprivileged.

Named Key Pairs fix all four. But raw SQL doesn’t scale to a team of 10 engineers rotating keys quarterly across 30 service accounts. You need tooling with guardrails.

The Feature: Named Key Pair Management (GA July 15, 2026)

On July 15, 2026, Snowflake released Named Key Pair Management — a fundamental upgrade to programmatic authentication. From the release notes:

You can now use SQL to register, rotate, modify, and remove named key pairs for a user. Each key pair has its own name and supports an optional role restriction and expiration time.

New SQL Commands

| Command                        | Purpose                                                                 |
| ------------------------------ | ----------------------------------------------------------------------- |
| ALTER USER ... ADD KEY PAIR    | Register a new named key pair with optional role restriction and expiry |
| ALTER USER ... MODIFY KEY PAIR | Disable/enable a key pair, change properties                            |
| ALTER USER ... ROTATE KEY PAIR | Replace the public key (old key gets 24hr grace period)                 |
| ALTER USER ... REMOVE KEY PAIR | Permanently delete a named key pair                                     |
| SHOW USER KEY PAIRS            | List all key pairs for a user with status, fingerprint, expiry          |

Syntax Examples

-- Register a named key pair
ALTER USER SVC_ETL ADD KEY PAIR etl_pipeline_2026q3
  PUBLIC_KEY = '<base64>'
  ROLE_RESTRICTION = 'ETL_ROLE'
  DAYS_TO_EXPIRY = 90
  COMMENT = 'dbt Cloud production pipeline';

-- Rotate (old key renamed with _ROTATED_<epoch>, 24hr grace)
ALTER USER SVC_ETL ROTATE KEY PAIR etl_pipeline_2026q3
  PUBLIC_KEY = '<new_base64>';
-- Disable instantly (incident response - reversible)
ALTER USER SVC_ETL MODIFY KEY PAIR etl_pipeline_2026q3
  SET DISABLED = TRUE;
-- Re-enable
ALTER USER SVC_ETL MODIFY KEY PAIR etl_pipeline_2026q3
  SET DISABLED = FALSE;
-- Remove permanently
ALTER USER SVC_ETL REMOVE KEY PAIR etl_pipeline_2026q3;
-- Inspect all key pairs for a user
SHOW USER KEY PAIRS FOR USER SVC_ETL;

What SHOW USER KEY PAIRS Returns

| Column       | Description                              |
| ------------ | ---------------------------------------- |
| name         | Key pair name (your chosen identifier)   |
| user_name    | Owning user                              |
| fingerprint  | SHA256 fingerprint of the public key     |
| role_scope   | Role restriction (if set)                |
| status       | ACTIVE or DISABLED                       |
| comment      | Your description                         |
| created_on   | Timestamp of creation                    |
| created_by   | Who registered it                        |
| last_used_on | Last successful authentication           |
| expires_at   | Expiration timestamp                     |
| rotated_to   | Name of replacement key (after rotation) |

Key Improvements Over Legacy

| Capability              | Legacy (RSA_PUBLIC_KEY) | Named Key Pairs          |
| ----------------------- | ----------------------- | ------------------------ |
| Keys per user           | 2 max                   | Unlimited                |
| Identification          | Positional (1 or 2)     | Named (you choose)       |
| Expiration              | None                    | DAYS_TO_EXPIRY           |
| Role scoping            | None                    | ROLE_RESTRICTION         |
| Disable without removal | No                      | SET DISABLED = TRUE      |
| Rotation                | Manual slot swap        | ROTATE with grace period |
| Audit                   | Limited                 | created_by, last_used_on |

For full documentation, see: Key-pair authentication and key-pair rotation

What I Built: Feature Overview

| Page           | Role Required    | What It Does                                                                                                                 |
| -------------- | ---------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| Overview       | KEYPAIR_VIEWER+  | Health score, status badges, tabbed view (key users / non-key users), searchable and filterable inventory                    |
| Inventory      | KEYPAIR_VIEWER+  | All users summary (with/without keys), filter/search, live per-user drill-down via `SHOW USER KEY PAIRS`                     |
| Register       | KEYPAIR_MANAGER+ | In-app RSA generation, per-user role dropdown (only roles granted to selected user), policy enforcement, duplicate detection |
| Rotate         | KEYPAIR_MANAGER+ | Only key-bearing users shown, zero-downtime rotation, auto-cleanup, reason codes                                             |
| Disable/Remove | KEYPAIR_MANAGER+ | Only key-bearing users shown, instant disable (incident response), enable, permanent remove                                  |
| Audit          | KEYPAIR_AUDITOR+ | Immutable audit log with operation/status filters, before/after state                                                        |
| Admin          | KEYPAIR_ADMIN    | RBAC overview, per-task edit schedule/suspend/resume/run, per-config edit/save with reactive triggers, elevated role callout |

Screen-by-Screen Deep Dive

1. Overview (Dashboard)

The landing page gives a security posture summary at a glance. Only service accounts (matching SVC_* prefix, configurable via APP_CONFIG.SERVICE_USER_PREFIX) are shown — human users are excluded from all views.

Top metrics row (4 columns):

| Metric                  | Source                   | Description                                                                               |
| ----------------------- | ------------------------ | ----------------------------------------------------------------------------------------- |
| Key Health              | `compute_health_score()` | 0–100% score with color badge: 🟢 Healthy (≥90%), 🟠 Warning (70–89%), 🔴 Critical (<70%) |
| Service Users (`SVC_*`) | `SHOW USERS` filtered    | Service accounts matching prefix                                                          |
| Keys Tracked            | `V_KEY_HEALTH`           | Total named key pairs across all users                                                    |
| Active Keys             | `V_KEY_HEALTH`           | Keys with `HEALTH_STATUS = "HEALTHY"`                                                     |

Tabbed split view:

Tab 1: “:material/vpn_key: Users with keys (N)”

4-column status breakdown: Healthy / Warning / Critical / Disabled counts
Searchable data table with columns: USER_NAME, KEY_NAME, STATUS, HEALTH_STATUS, ROLE_SCOPE, DAYS_TO_EXPIRY
Three filter controls:
Search text input (placeholder: e.g. SVC_DBT or AIRFLOW_KEY) — matches any column value
Health status dropdown: All, HEALTHY, WARNING, CRITICAL, EXPIRED, DISABLED, STALE_ROTATED, UNSCOPED
Expiry filter: All, < 7 days, < 14 days, < 30 days

Tab 2: “:material/person_off: Users without keys (N)”

Simple table listing all users with zero named key pairs
Answers: “Who still needs onboarding?” at a glance
Shows “All users have key pairs registered” ✓ when everyone is covered

2. Inventory (All Users Summary)

Unlike Overview which focuses on key health, Inventory shows every user in the account — with or without keys — in a single searchable summary table.

Summary table columns:

| Column | Example Value        | Description                                              |
| ------ | -------------------- | -------------------------------------------------------- |
| USER   | `SVC_AIRFLOW`        | Username                                                 |
| KEYS   | `2`                  | Count of named key pairs (`0` = no keys)                 |
| STATUS | `HEALTHY`, `WARNING` | Comma-separated health statuses, or `No keys registered` |

Filter controls (2 columns):

Filter dropdown: “All users” / “With keys” / “Without keys”
Search text input (placeholder: e.g. SVC_AIRFLOW) — case-insensitive match on username

Footer: Showing 8 of 13 users (5 with keys, 8 without)

Live drill-down section (below divider):

Second dropdown: “Inspect user (live)” — lists all users
On select: executes SHOW USER KEY PAIRS FOR USER <name> in real-time
Shows full detail: name, fingerprint, role_scope, status, comment, created_on, created_by, last_used_on, expires_at, rotated_to
If user has no keys: info message “No key pairs registered for SVC_KAFKA. Use Register to add one.”

3. Register (Key Pair Creation)

Two-step guided flow. Only available to KEYPAIR_MANAGER and KEYPAIR_ADMIN roles.

Step 1 — Generate key pair (expandable section):

Auto-expanded when no key has been generated yet; auto-collapses after generation
Key size radio buttons: 2048 / 4096 (horizontal)
If selected size < policy minimum (MIN_KEY_SIZE from config): shows warning "Policy requires minimum 4096-bit keys"
“Generate key pair” button (primary) → uses cryptography library to create RSA key in-memory
Result: two columns — Download Private Key (.pem) button + “Public key ready” ✓ success badge
Private key never stored in app — exists only in session state for download

Step 2 — Register on user (form):

User selector is outside the form so the role dropdown can react to user changes:

User dropdown — defaults to users without keys (reduces noise)
Expandable section: “N user(s) already have keys” — lists each with their key names
“Show all users” checkbox — toggles full user list
Form fields (2 columns):

| Field            | Type         | Behavior                                                                                                                                                                                                                                                         |
| ---------------- | ------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Key name         | Text input   | Placeholder: `e.g. ETL_PIPELINE_2026Q3`. Validated as alphanumeric + underscore only.                                                                                                                                                                            |
| Role restriction | Selectbox    | Per-user roles only—populated via `SHOW GRANTS TO USER`, excluding `ACCOUNTADMIN`, `ORGADMIN`, `SECURITYADMIN`, and `SYSADMIN`. Shows warning if user has no custom roles. If `REQUIRE_ROLE_RESTRICTION = TRUE`, the empty option is removed (forces selection). |
| Days to expiry   | Number input | Range: `1–730`. Default from `DEFAULT_EXPIRY_DAYS` config.                                                                                                                                                                                                       |
| Comment          | Text input   | Placeholder: `e.g. dbt production deploy key for nightly builds`.                                                                                                                                                                                                |
| Public key       | Textarea     | Pre-filled from Step 1 generation. Placeholder: `e.g. MIIBIjANBgkqhkiG9w...` (use **Generate** above). Auto-stripped of whitespace/newlines before submission.                                                                                                   |
| Reason           | Selectbox    | `NEW_KEY`, `REPLACEMENT`, `MIGRATION`, `COMPLIANCE`                                                                                                                                                                                                              |

Validation on submit (in order):

User must be selected
Key name required (alphanumeric + underscore)
Public key required
Role restriction required (if policy enforced)
Key name must not already exist on user (suggests Rotate instead)

On success: Green success banner + clears inventory cache. On failure: Structured error with Title + Reason + Remedy (from format_error()).

4. Rotate (Key Replacement)

Zero-downtime key rotation with automatic cleanup of old keys. Only available to KEYPAIR_MANAGER+.

User dropdown — only shows users who have existing key pairs (populated from V_KEY_HEALTH, not all users). Eliminates confusion — can't rotate what doesn't exist.

On user select:

Displays current keys table: name, status, role_scope, expires_at
Filters to rotatable keys only — excludes keys with _ROTATED_ in their name (these are old post-rotation copies that can't be re-rotated)
If only one rotatable key exists: auto-selects it (no empty option in dropdown)
If no rotatable keys but disabled keys exist: warning “Only rotated keys remain. Register a new key instead.”

Generate new key (expandable):

“Generate” button → creates 4096-bit RSA key
Download new private key (.pem) button

Public key textarea — pre-filled from generation, placeholder: e.g. MIIBIjANBgkqhkiG9w... (use Generate above)

Options:

Auto-cleanup rotated keys (checkbox) — default from AUTO_CLEANUP_ROTATED config. When enabled, after rotation, the procedure scans for all _ROTATED_ keys on the user and removes them.
Reason dropdown: SCHEDULED, POLICY, INCIDENT_RESPONSE, COMPLIANCE

What happens on “Execute rotation”:

Validates user + key + public key
Calls SP_ROTATE_KEY → captures old fingerprint → executes ALTER USER ... ROTATE KEY PAIR → logs audit
If auto-cleanup enabled: scans for _ROTATED_ keys → removes each → returns cleaned count
Shows success: “Rotated TABLEAU_REPORTING_KEY” + “Cleaned 1 rotated key(s)” (if applicable)
Clears inventory cache

5. Disable / Remove (Incident Response)

Immediate key revocation for security incidents or decommissioning. Only available to KEYPAIR_MANAGER+.

User dropdown — only shows users who have key pairs (same as Rotate — from V_KEY_HEALTH).

On user select: Shows current keys data table (name, status, role_scope, expires_at) for context before taking action.

Three operation tabs:

Tab: “:material/block: Disable”

Purpose: Instant revocation — the key exists but can no longer authenticate. Reversible.
Key to disable: dropdown of user’s keys
Reason: INCIDENT_RESPONSE, COMPLIANCE, POLICY, MANUAL
Executes: ALTER USER ... MODIFY KEY PAIR ... SET DISABLED = TRUE
Use case: Suspected compromise — disable immediately, investigate, then enable or remove.

Tab: “:material/check_circle: Enable”

Purpose: Re-enable a previously disabled key after investigation clears it.
Key to enable: dropdown of user’s keys
Executes: ALTER USER ... MODIFY KEY PAIR ... SET DISABLED = FALSE
Use case: False positive — key was disabled during incident but deemed safe.

Tab: “:material/delete: Remove”

Purpose: Permanent deletion — irreversible. The key pair is gone.
Key to remove: dropdown of user’s keys
Reason: DECOMMISSION, INCIDENT_RESPONSE, MIGRATION, CLEANUP
Executes: ALTER USER ... REMOVE KEY PAIR ...
Use case: Service decommissioned, key compromised beyond recovery, migration complete.

Error handling: All three operations use format_error() — if the key is already in the target state, or the user/key doesn't exist, a structured message explains what happened and how to fix it.

6. Audit Log

Immutable record of every key pair operation. Available to KEYPAIR_AUDITOR and KEYPAIR_ADMIN roles.

Controls:

Row limit selector: 25, 50, 100, 500
Operation filter: All, REGISTER, ROTATE, DISABLE, ENABLE, REMOVE
Status filter: All, SUCCESS, FAILED

Table columns:

| Column        | Description                                                                     |
| ------------- | ------------------------------------------------------------------------------- |
| TIMESTAMP     | When the operation occurred                                                     |
| OPERATION     | `REGISTER`, `ROTATE`, `DISABLE`, `ENABLE`, `REMOVE`                             |
| TARGET_USER   | User the operation was performed on                                             |
| KEY_NAME      | Named key pair affected                                                         |
| EXECUTED_BY   | Who ran the command                                                             |
| EXECUTED_ROLE | What role they were using                                                       |
| REASON_CODE   | `NEW_KEY`, `SCHEDULED`, `INCIDENT_RESPONSE`, `COMPLIANCE`, `DECOMMISSION`, etc. |
| STATUS        | `SUCCESS` or `FAILED`                                                           |
| ERROR_MESSAGE | Error details (for failed operations)                                           |

Footer: “Showing 42 of 50 entries” (after filtering)

Key insight: Failed operations are logged too — if someone tried to rotate a non-existent key or disable with insufficient privileges, it’s in the audit trail. Nothing is swallowed.

7. Admin (RBAC, Tasks, Configuration)

Full system management. Only available to KEYPAIR_ADMIN and ACCOUNTADMIN.

Tab 1: “:material/admin_panel_settings: RBAC”

Role hierarchy visualization:

ACCOUNTADMIN
  └── SECURITYADMIN
        └── KEYPAIR_ADMIN (full lifecycle + grants + config + admin page)
              ├── KEYPAIR_MANAGER (register, rotate, disable — no admin/config)
              │     └── KEYPAIR_VIEWER (read-only dashboard + inventory)
              └── KEYPAIR_AUDITOR (audit log access only)

Auto-grant status indicator (🟢 Started / 🟠 Suspended) with Resume button
Explanation: “Snowflake requires MODIFY PROGRAMMATIC AUTHENTICATION METHODS granted per-user. Since there’s no GRANT ON ALL FUTURE USERS, a daily task grants this on every user.”
“Run auto-grant now” button — manual trigger for immediate coverage of new users

Tab 2: “:material/schedule: Tasks”

Each task displayed in an expandable card:

| Task                     | Default Schedule | Purpose                                                                                      |
| ------------------------ | ---------------- | -------------------------------------------------------------------------------------------- |
| `INVENTORY_REFRESH_TASK` | Hourly           | Scans all users and rebuilds the `KEY_INVENTORY` table. The dashboard reads from this table. |
| `EXPIRY_MONITOR_TASK`    | 8:00 AM, Mon–Fri | Sends email alerts for keys expiring within the configured threshold.                        |
| `AUTO_GRANT_TASK`        | Midnight daily   | Grants key management privileges on all users to `KEYPAIR_ADMIN`.                            |

Per-task controls:

🟢/🔴 status indicator + task name + state
Current schedule display
Edit schedule — CRON text input (placeholder: e.g. 0 */1 * * * America/New_York)
Three buttons:
Suspend / Resume (toggles based on current state)
Save schedule (suspends → updates → resumes)
Run now (executes the underlying procedure manually)

Tab 3: “:material/settings: Config”

Top-level role check:

If current role lacks CREATE TASK privileges: ⚠️ “Current role KEYPAIR_MANAGER may not have CREATE TASK privileges. Task-impacting config changes require ACCOUNTADMIN or KEYPAIR_ADMIN. Use the role switcher in the sidebar to switch.”

Each config in expandable card with appropriate input type:

| Config Key                  | Input Type                                                       | Notes                                                   |
| --------------------------- | ---------------------------------------------------------------- | ------------------------------------------------------- |
| `ALERT_THRESHOLD_DAYS`      | Number input                                                     | 🛡️ Task-impacting – recreates `EXPIRY_MONITOR_TASK`    |
| `AUTO_CLEANUP_ROTATED`      | `TRUE`/`FALSE` dropdown                                          | Runtime – affects rotation checkbox default             |
| `DEFAULT_EXPIRY_DAYS`       | Number input                                                     | Runtime – affects Register form default                 |
| `INVENTORY_REFRESH_MINUTES` | Number input                                                     | 🛡️ Task-impacting – recreates `INVENTORY_REFRESH_TASK` |
| `MIN_KEY_SIZE`              | `2048`/`4096` dropdown                                           | NIST SP 800–131A standard values only                   |
| `NOTIFY_EMAIL`              | Text input + ℹ️ *"must be registered and verified in Snowflake"* | 🛡️ Task-impacting – recreates `EXPIRY_MONITOR_TASK`    |
| `REQUIRE_ROLE_RESTRICTION`  | `TRUE`/`FALSE` dropdown                                          | Runtime – blocks registration without role scope        |

Per-config Save button. On save:

Updates APP_CONFIG table
If task-impacting: suspends → recreates → resumes the affected task
If runtime: shows “Applied at runtime — no task restart needed”
Reruns the page to reflect new values

8. Switch Role (Sidebar)

Available on every page. The sidebar shows:

Current role badge:

🟢 Admin (ACCOUNTADMIN, SECURITYADMIN, KEYPAIR_ADMIN)
🔵 Manager (KEYPAIR_MANAGER)
🟠 Auditor (KEYPAIR_AUDITOR)
🔴 Viewer (all other roles)

“:material/swap_horiz: Switch role” expander:

Dropdown populated from SHOW ROLES — all roles available in the account
Pre-selected to current role
If a different role is chosen: “Switch to DATA_ANALYST” primary button appears
On click: executes USE ROLE <new_role> → page reruns with new RBAC-gated navigation

What changes on role switch:

Navigation pages shown/hidden based on new role’s membership in ADMIN/MANAGER/VIEWER/AUDITOR sets
Config operations may be blocked (elevated role callout in Admin)
Sidebar badge updates to reflect new role tier

This enables demo scenarios: switch to KEYPAIR_VIEWER to show read-only experience, then back to KEYPAIR_ADMIN for full management.

Architecture: Five-Layer Design

┌─────────────────────────────────────────────────────────────────────────┐
│                         PRESENTATION LAYER                               │
│              Streamlit Multi-Page App (st.navigation)                    │
│    Dark Material Theme │ Role Toggle │ RBAC-Gated Navigation            │
├─────────┬───────────┬──────────┬────────┬──────────┬───────┬───────────┤
│Overview │ Inventory │ Register │ Rotate │ Disable  │ Audit │   Admin   │
│ Tabs:   │ Summary + │ Per-user │ Key-   │ 3 tabs:  │Filter │ 3 tabs:   │
│ keys/   │ drill-    │ role     │ bearing│ Disable/ │ by op │ RBAC/     │
│ no-keys │ down      │ dropdown │ users  │ Enable/  │ status│ Tasks/    │
│         │           │          │ only   │ Remove   │       │ Config    │
└────┬────┴─────┬─────┴────┬─────┴───┬────┴────┬─────┴──┬────┴─────┬────┘
     │          │          │         │         │        │          │
┌────▼──────────▼──────────▼─────────▼─────────▼────────▼──────────▼────┐
│                    UX INTELLIGENCE LAYER                                │
│  Smart Dropdowns: per-user roles via SHOW GRANTS TO USER               │
│  Filtered Users: only key-bearing users in Rotate/Disable              │
│  Example Placeholders: vanish on input (e.g. ETL_PIPELINE_2026Q3)      │
│  Elevated Role Callouts: warns when task-impacting config needs ADMIN  │
└──────────────────────────────┬─────────────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────────────┐
│                    SERVICE LAYER (services.py — 243 lines)              │
│  call_proc()         │ parse_error()       │ format_error()            │
│  validate_identifier()│ generate_key_pair() │ get_key_inventory()      │
│  get_roles_for_user() │ get_available_roles()│ compute_health_score()  │
│  get_all_users()      │ get_user_list()     │ get_audit_log()          │
│  get_config()         │ get_key_pairs_for_user()                       │
└──────────────────────────────┬─────────────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────────────┐
│                 STORED PROCEDURES (no EXCEPTION handlers)               │
│  SP_REGISTER_KEY(7 params)  — validates + executes + audits            │
│  SP_ROTATE_KEY(5 params)    — rotates + auto-cleanup + audits          │
│  SP_MODIFY_KEY(4 params)    — disable/enable/remove + audits           │
│  REFRESH_INVENTORY()        — scans all users, rebuilds table          │
│  CHECK_KEY_EXPIRY(2 params) — email alert for expiring keys            │
│  AUTO_GRANT_KEYPAIR_ADMIN() — grants privilege on all users            │
└──────────────────────────────┬─────────────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────────────┐
│                    DATA LAYER                                           │
│  KEY_INVENTORY (materialized) │ AUDIT_LOG (immutable)                  │
│  ROTATION_POLICIES            │ APP_CONFIG (reactive settings)         │
│  V_KEY_HEALTH (computed view: status + days_to_expiry + health badge)  │
└──────────────────────────────┬─────────────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────────────┐
│                    AUTOMATION LAYER (Snowflake Tasks)                   │
│  INVENTORY_REFRESH_TASK — configurable CRON (default hourly)           │
│  EXPIRY_MONITOR_TASK   — 8am weekdays (threshold + email from config)  │
│  AUTO_GRANT_TASK       — midnight daily (no GRANT ON ALL FUTURE USERS) │
│                                                                         │
│  Config changes auto-recreate impacted tasks (reactive triggers)        │
└─────────────────────────────────────────────────────────────────────────┘

Key architectural decision: The Streamlit app never executes inline DDL. Every mutation flows through a stored procedure that validates, executes, and audits. Procedures have no EXCEPTION handlers — errors propagate directly to the Python layer, which catches them and shows the actual Snowflake error message. Under the hood, procedures use ALTER USER … ADD KEY PAIR, ROTATE KEY PAIR, and MODIFY KEY PAIR — but the app only calls:

def get_current_user() -> str:
    """Get the real human username (not Streamlit platform user)."""
    return st.session_state.get("real_user", "UNKNOWN")
def call_proc(proc_name: str, *args) -> dict:
    """Call stored procedure. Auto-appends real username for audit trail."""
    real_user = get_current_user()
    all_args = list(args) + [real_user]  # P_EXECUTED_BY always last param
    params = ", ".join(f"'{a}'" if isinstance(a, str) else str(a) for a in all_args)
    try:
        result = session.sql(f"CALL SECURITY_OPS.KEYPAIR_MGMT.{proc_name}({params})").collect()
    except Exception as e:
        return {"success": False, "error": str(e), "parsed": parse_error(str(e))}
    return json.loads(result[0][0])
# Usage:
result = call_proc("SP_REGISTER_KEY", user, key_name, public_key, role, expiry, comment, reason)
# → Actually calls SP_REGISTER_KEY(..., 'SATISH') - real user auto-appended

Why this matters: In Streamlit Container Runtime, CURRENT_USER() returns the platform service user (STPLATSTREAMLIT505241452), not the human (SATISH). We capture the real user at startup via SYSTEM$WHO_AM_I() and pass it explicitly to every procedure, which writes it to AUDIT_LOG.EXECUTED_BY.

The parse_error() function maps Snowflake errors to user-friendly messages:

| Error Pattern            | User Sees                                                                                           |
| ------------------------ | --------------------------------------------------------------------------------------------------- |
| Role does not exist      | Reason: Role not created. Remedy: Create the role or select one from the dropdown.                  |
| Role not granted to user | Reason: Role is not assigned to this user. Remedy: Grant the role first or select a granted role.   |
| Invalid public key       | Reason: Public key is not valid Base64 DER. Remedy: Use the Generate button.                        |
| Key already exists       | Reason: Key name is already registered. Remedy: Use a different name or perform a Rotate operation. |
| Insufficient privileges  | Reason: Current role lacks the required permission. Remedy: Switch to `KEYPAIR_ADMIN`.              |
| User does not exist      | Reason: User was dropped or the name is misspelled. Remedy: Verify the user with `SHOW USERS`.      |

Security Model: 4-Tier RBAC with UI Gating

ACCOUNTADMIN
  └── SECURITYADMIN
        └── KEYPAIR_ADMIN (full lifecycle + grants + config + admin page)
              ├── KEYPAIR_MANAGER (register, rotate, disable — no admin/config)
              │     └── KEYPAIR_VIEWER (read-only dashboard + inventory)
              └── KEYPAIR_AUDITOR (audit log access only)

Pages are hidden by role. The entry point checks CURRENT_ROLE() and builds navigation dynamically. A KEYPAIR_VIEWER literally cannot see Register/Rotate/Disable pages.

The sidebar includes a role toggle — select any role granted to your user and switch without leaving the app:

with st.sidebar.expander(":material/swap_horiz: Switch role"):
    new_role = st.selectbox("Role", available_roles)
    if new_role != current_role:
        if st.button(f"Switch to {new_role}"):
            session.sql(f"USE ROLE {new_role}").collect()
            st.rerun()

Reactive Configuration (No Redeployment)

All app behavior is driven by APP_CONFIG table. The Admin page renders each config as an individual expandable card with the right input type (boolean → dropdown, numeric → number input, MIN_KEY_SIZE → 2048/4096 dropdown per NIST SP 800-131A, NOTIFY_EMAIL → text with verification reminder) and a Save button. Change a value, click Save, and the impacted system action fires automatically:

Task-impacting configs display an elevated role callout: “Saving this will recreate a task. Requires ACCOUNTADMIN or KEYPAIR_ADMIN role.” If the user’s current role lacks CREATE TASK privileges, a top-level warning banner guides them to the role switcher.

| Config Changed              | Auto-Triggered Action                                            |
| --------------------------- | ---------------------------------------------------------------- |
| `ALERT_THRESHOLD_DAYS`      | Suspends → recreates → resumes `EXPIRY_MONITOR_TASK`             |
| `NOTIFY_EMAIL`              | Suspends → recreates → resumes `EXPIRY_MONITOR_TASK`             |
| `INVENTORY_REFRESH_MINUTES` | Suspends → recreates → resumes `INVENTORY_REFRESH_TASK`          |
| `MIN_KEY_SIZE`              | Enforced on next registration (runtime, dropdown: `2048`/`4096`) |
| `REQUIRE_ROLE_RESTRICTION`  | Registration fails without role scope (runtime)                  |
| `AUTO_CLEANUP_ROTATED`      | Rotation checkbox default (runtime)                              |
| `DEFAULT_EXPIRY_DAYS`       | Register form default (runtime)                                  |

No SQL. No redeployment. Change config → system adapts immediately.

Health Score

Computed from inventory state. Penalties for: disabled keys, stale _ROTATED_ keys, missing role scope, keys expiring within 7/14 days, expired keys. Displayed as a percentage with Healthy/Warning/Critical badge.

Task Explanations

The Admin page doesn’t just list tasks — it explains each one:

INVENTORY_REFRESH_TASK — “Rebuilds KEY_INVENTORY table. Dashboard reads from this for instant loads.”
EXPIRY_MONITOR_TASK — “Scans all keys, emails alert for anything expiring within threshold.”
AUTO_GRANT_TASK — “Snowflake has no GRANT ON ALL FUTURE USERS. This task grants on every user daily.”

Each task has individual Suspend / Resume / Save Schedule / Run Now buttons — no bulk operations, full control.

Smart Dropdowns and Filtered Lists

Three UX decisions that eliminate user confusion:

Role Restriction dropdown — Shows only roles granted to the selected user (via SHOW GRANTS TO USER). System roles (ACCOUNTADMIN, ORGADMIN, SECURITYADMIN, SYSADMIN) are excluded. This prevents the "Role is not granted to user" error entirely — you can't select a role the user doesn't have.
Rotate/Disable pages — Only show users who actually have key pairs. Why show 50 users when only 5 have keys? The dropdown is populated from V_KEY_HEALTH, not from SHOW USERS.
Overview tabs — Users with keys vs. Users without keys, shown in separate tabs with counts. Instantly answers “who still needs onboarding?”

Performance: Materialized Inventory

The naive approach — SHOW USERS then loop SHOW USER KEY PAIRS per user — is O(n) queries. For enterprise accounts with 50,000 users, that's 50,000+ queries just to render a dashboard.

Solution: KEY_INVENTORY table rebuilt by scheduled task. A computed view V_KEY_HEALTH adds health status:

CREATE VIEW V_KEY_HEALTH AS
SELECT *,
  CASE
    WHEN STATUS = 'DISABLED' THEN 'DISABLED'
    WHEN KEY_NAME ILIKE '%_ROTATED_%' THEN 'STALE_ROTATED'
    WHEN DATEDIFF('day', CURRENT_TIMESTAMP(), EXPIRES_AT) < 0 THEN 'EXPIRED'
    WHEN DATEDIFF('day', CURRENT_TIMESTAMP(), EXPIRES_AT) < 7 THEN 'CRITICAL'
    WHEN DATEDIFF('day', CURRENT_TIMESTAMP(), EXPIRES_AT) < 14 THEN 'WARNING'
    WHEN ROLE_SCOPE IS NULL THEN 'UNSCOPED'
    ELSE 'HEALTHY'
  END AS HEALTH_STATUS,
  DATEDIFF('day', CURRENT_TIMESTAMP(), EXPIRES_AT) AS DAYS_TO_EXPIRY
FROM KEY_INVENTORY;

Dashboard reads from this view with @st.cache_data(ttl=300) — instant load, zero SHOW commands.

Immutable Audit Trail

Every operation — register, rotate, disable, enable, remove, auto-cleanup — writes to AUDIT_LOG:

INSERT INTO AUDIT_LOG (operation, target_user, key_name, reason_code,
                       executed_by, executed_role, session_id,
                       before_state, after_state, status, error_message)

before_state: captures fingerprint pre-rotation
after_state: new key config
reason_code: NEW_KEY, SCHEDULED, INCIDENT_RESPONSE, COMPLIANCE, POST_ROTATION, MIGRATION
Failures are logged too — never swallowed

The Audit page provides filtered views by operation type and status.

Get the Code & Youtube Demo

The full source — multi-page Streamlit app, stored procedures, deployment SQL, and documentation — is available on GitHub:

Deploy in 3 steps:

Run sql/deploy.sql as ACCOUNTADMIN
GRANT ROLE KEYPAIR_ADMIN TO USER <you>
Upload to Workspace → Run

Youtube Link:

https://medium.com/media/849fb991d658b796c73bbf493348a4d8/href

Key Lessons from Building This

Don’t use EXCEPTION handlers in SQL Scripting for user-facing errors. SQLERRM is unreliable across Snowflake contexts. Let errors propagate to the Python layer where you have full control over the message displayed to users.
ROLE_RESTRICTION requires the role to exist. If you specify a role that doesn't exist in the account, the ADD KEY PAIR command fails. Validate beforehand or let the error propagate cleanly.
Rotated keys get renamed with _ROTATED_<epoch>. Filter these out of rotation dropdowns — you cannot re-rotate an already-rotated key.
There’s no GRANT ON ALL FUTURE USERS. The only way to ensure MODIFY PROGRAMMATIC AUTHENTICATION METHODS is granted on new users is a scheduled task that runs the grant idempotently on all users.
Materialized tables beat iterative SHOW commands. For any dashboard that needs to display aggregate key state across all users, pre-compute into a table and read from a view. SHOW USER KEY PAIRS is per-user only.
Reactive config eliminates operational drift. When a config change automatically triggers task recreation, you never have stale task parameters that don’t match the config table.
CURRENT_USER() lies in Container Runtime. Streamlit in Snowflake runs as a platform service user. Use SYSTEM$WHO_AM_I() at session init to capture the real human, then pass it explicitly to stored procedures for accurate audit trails. Never rely on DEFAULT CURRENT_USER() in audit tables.

Summary

Managing Named Key Pairs at enterprise scale requires more than SQL commands. This implementation provides:

Stored procedures — DDL server-side, never in the UI layer
Immutable audit — before/after state with reason codes on every operation
Real user attribution — SYSTEM$WHO_AM_I() for accurate EXECUTED_BY in Container Runtime
4-tier RBAC — pages hidden by role, least-privilege by default
Materialized inventory — instant dashboard, no SHOW loops
Reactive config — change a value, system adapts (tasks recreated automatically)
Dark theme + role toggle — polished enterprise UX
Task automation — expiry alerts, inventory refresh, RBAC auto-grant
Graceful error handling — Snowflake errors mapped to Title + Reason + Remedy
Smart UX — per-user role dropdown, filtered user lists, example placeholders
Elevated role callouts — task-impacting configs warn and guide to role switcher
All-users inventory — summary table showing every service user’s key status with drill-down
Service account scoping — only SVC_* users shown (configurable prefix, human users excluded)

The code is open source. Run deploy.sql, upload the app, and stop worrying about key management.

References

NOTICE & ATTRIBUTION

This article — including its architecture, diagrams, documentation, examples, and written content — is original work by Satish Kumar, published through Snowflake Chronicles.

The ideas, concepts, architecture, and technical approaches described here are yours to use, discuss, extend, and build on — that’s the whole point of sharing knowledge.

If you reuse or adapt the article content, diagrams, code, SQL, or examples, please credit and link back to the original work.

Original Article: [Medium Article Link]
GitHub: [Repository Link]
Author: Satish Kumar — [LinkedIn]

Code, SQL, configurations, and implementation examples are provided for educational purposes only. Validate and test thoroughly before production use; the author is not responsible for production use.

This article represents the author’s personal views, experience, and technical perspective and does not necessarily represent those of any current or former employer.

Share knowledge. Build on ideas. Give credit. Grow the Snowflake community.

👏 Give it a clap if it added value

🔗 Share it with your team

➕ Follow for more

📘 Medium: Satish Kumar

🔗 LinkedIn: satishkumar-snowflake

See you in the next one! 👋

I Built an Enterprise Key Pair Manager for Snowflake Service User in Snowflake — Architecture… was originally published in System Weakness on Medium, where people are continuing the conversation by highlighting and responding to this story.