Examples: Block GRANT Changes on Production Objects

Name: Lexega
Author: Lexega

Goal: prevent GRANT/REVOKE changes from being deployed to production without explicit approval.

GRANT statements can escalate privileges, expose data to unauthorized users, or break access controls. This example shows how to block dangerous GRANT patterns in prod.

Files to commit

.lexega/
  custom_rules.yaml      # defines detection logic
  policy.yaml            # references rule IDs for enforcement
  exceptions.yaml        # optional

1) Custom Rules: detect dangerous GRANT patterns

Lexega generates signals for GRANT statements including:

PrivilegeGranted — any privilege grant
GrantToPublic — grant to PUBLIC role (dangerous!)
WithGrantOption — enables privilege delegation
AllPrivileges — grants all privileges on object
OwnershipGrant — transfers ownership
FutureGrant — ON FUTURE objects (affects future tables)

Define custom rules to catch the patterns you care about:

# .lexega/custom_rules.yaml
# yaml-language-server: $schema=/schemas/v1/custom_rules.schema.json
metadata:
  name: GRANT Safety Rules
  version: 1.0.0

rules:
  # Block ANY grant in prod (strictest policy)
  - id: GRANT-BLOCK-ALL
    name: Any privilege grant detected
    risk_level: High
    message: "Privilege grant detected on {affected_object}. All GRANT changes require approval in prod."
    triggers:
      - statement_type: GrantStatement
        categorical_signal:
          category: SECURITY
          surface: grant
          condition: privilege_granted

  # Block GRANT TO PUBLIC (very dangerous)
  - id: GRANT-TO-PUBLIC
    name: Grant to PUBLIC role
    risk_level: Critical
    message: "GRANT TO PUBLIC detected. This makes data accessible to ALL users in the account."
    triggers:
      - statement_type: GrantStatement
        categorical_signal:
          category: SECURITY
          surface: grant
          condition: grant_to_public

  # Block WITH GRANT OPTION (privilege escalation)
  - id: GRANT-WITH-OPTION
    name: Grant with delegation enabled
    risk_level: Critical
    message: "WITH GRANT OPTION detected. Grantee can delegate privileges to others."
    triggers:
      - statement_type: GrantStatement
        categorical_signal:
          category: SECURITY
          surface: grant
          condition: with_grant_option

  # Block ALL PRIVILEGES grants
  - id: GRANT-ALL-PRIVILEGES
    name: All privileges granted
    risk_level: High
    message: "ALL PRIVILEGES granted. Consider granting only required privileges."
    triggers:
      - statement_type: GrantStatement
        categorical_signal:
          category: SECURITY
          surface: grant
          condition: all_privileges

  # Block ownership transfers
  - id: GRANT-OWNERSHIP
    name: Ownership transfer
    risk_level: Critical
    message: "GRANT OWNERSHIP detected on {affected_object}. Object control transferred."
    triggers:
      - statement_type: GrantStatement
        categorical_signal:
          category: SECURITY
          surface: grant
          condition: ownership_grant

  # Block future grants (affects objects not yet created)
  - id: GRANT-ON-FUTURE
    name: Future grant detected
    risk_level: High
    message: "ON FUTURE grant detected. This affects all future objects in the schema/database."
    triggers:
      - statement_type: GrantStatement
        categorical_signal:
          category: SECURITY
          surface: grant
          condition: future_grant

2) Policy: block dangerous grants in prod

Reference the rule IDs — policies don't define detection logic:

# .lexega/policy.yaml
# yaml-language-server: $schema=/schemas/v1/policy.schema.json
schema_version: 1
policy_id: grant-safety
policy_version: 1.0.0

default_action: allow

policies:
  # CRITICAL: Always block these patterns
  - rule_id: GRANT-TO-PUBLIC
    description: Never allow GRANT TO PUBLIC in prod
    envs: [prod]
    action: block
    requires_exception: true

  - rule_id: GRANT-WITH-OPTION
    description: Block privilege delegation in prod
    envs: [prod]
    action: block
    requires_exception: true

  - rule_id: GRANT-OWNERSHIP
    description: Block ownership transfers in prod
    envs: [prod]
    action: block
    requires_exception: true

  # HIGH: Warn on these patterns (or block if strict)
  - rule_id: GRANT-ALL-PRIVILEGES
    description: Warn on ALL PRIVILEGES grants
    envs: [prod]
    action: warn

  - rule_id: GRANT-ON-FUTURE
    description: Warn on future grants
    envs: [prod]
    action: warn

  # Optional: Block ALL grants in prod (very strict)
  # - rule_id: GRANT-BLOCK-ALL
  #   description: Block all GRANT statements in prod
  #   envs: [prod]
  #   action: block
  #   requires_exception: true

3) Run review with policy (CI gate)

lexega-sql review main..HEAD models/ -r \
  --custom-rules .lexega/custom_rules.yaml \
  --policy .lexega/policy.yaml \
  --env prod \
  --decision-out decision.json \
  --format markdown > review.md

Gate on the decision artifact:

ALLOWED=$(jq -r '.outcome.allowed' decision.json)
test "$ALLOWED" = "true"

4) Exception (optional)

If a GRANT is reviewed and approved:

# .lexega/exceptions.yaml
# yaml-language-server: $schema=/schemas/v1/exceptions.schema.json
schema_version: 1

exceptions:
  - exception_id: EX-2025-GRANT-001
    policy_id: grant-safety
    rule_id: GRANT-TO-PUBLIC
    approved_by: security-team@company.com
    approved_at: "2025-12-31T00:00:00Z"
    reason: "Reviewed GRANT TO PUBLIC on ANALYTICS.PUBLIC_METRICS - intentional public dashboard"
    ticket: SEC-1234
    scope:
      scoped:
        path_prefixes:
          - "models/public/"
        expires_at: "2026-03-01T00:00:00Z"

Then run review with exceptions:

lexega-sql review main..HEAD models/ -r \
  --custom-rules .lexega/custom_rules.yaml \
  --policy .lexega/policy.yaml \
  --exceptions .lexega/exceptions.yaml \
  --env prod \
  --decision-out decision.json

Key Concepts

GRANT signal types available:

privilege_granted — any GRANT statement

grant_to_public — GRANT TO PUBLIC (critical risk)

grant_to_share — GRANT TO SHARE (data sharing)

with_grant_option — WITH GRANT OPTION enabled

all_privileges — ALL PRIVILEGES granted

privileged_role_grant — granting ACCOUNTADMIN, SECURITYADMIN, etc.

ownership_grant — GRANT OWNERSHIP

future_grant — ON FUTURE objects

All signals use category: SECURITY, surface: grant.