Examples: Block GRANT Changes on Production Objects

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 gate dangerous GRANT patterns in prod.

Files to commit

.lexega/
  custom_rules.yaml      # defines detection logic (or skip — many built-ins cover this)
  policy.yaml            # references rule IDs for enforcement
  exceptions.yaml        # optional

1) Detection: built-ins or custom rules?

Most dangerous grant patterns are covered by built-in rules that ship with Lexega — you can reference them directly from your policy without writing any detection logic. The relevant built-ins:

If you need patterns the built-ins don't cover, author your own rules against the same StatementFacts fact tree. The examples below show both shapes.

# .lexega/custom_rules.yaml
# yaml-language-server: $schema=https://lexega.com/schemas/v1/custom_rules.schema.json

rules:
  # Strictest: any GRANT in prod requires approval. Useful if your policy
  # ledger wants every grant to be a deliberate, ticketed change.
  - id: ACME-GRANT-ANY
    risk_level: high
    message: "Privilege grant detected on {privilege.target.name.canonical}. All GRANT changes require approval in prod."
    triggers:
      kind: grant

  # GRANT TO PUBLIC — also covered by built-in GRT-TO-PUBLIC; shown here
  # to illustrate the predicate shape.
  - id: ACME-GRANT-TO-PUBLIC
    risk_level: critical
    message: "GRANT TO PUBLIC detected. This makes data accessible to ALL users in the account."
    triggers:
      all_of:
        - kind: grant
        - privilege.grantees:
            exists:
              name.normalized: PUBLIC

  # WITH GRANT OPTION — built-in GRT-WITH-OPT covers this verbatim.
  - id: ACME-GRANT-WITH-OPTION
    risk_level: critical
    message: "WITH GRANT OPTION detected. Grantee can delegate privileges to others."
    triggers:
      all_of:
        - kind: grant
        - privilege.with_grant_option: true

  # ALL PRIVILEGES — built-in GRT-ALL-PRIV covers this verbatim.
  - id: ACME-GRANT-ALL-PRIVS
    risk_level: high
    message: "ALL PRIVILEGES granted on {privilege.target.name.canonical}. Consider granting only required privileges."
    triggers:
      all_of:
        - kind: grant
        - privilege.all_privileges: true

  # Ownership transfer — built-in GRT-OWNER-XFER covers this.
  - id: ACME-GRANT-OWNERSHIP
    risk_level: critical
    message: "GRANT OWNERSHIP detected on {privilege.target.name.canonical}. Object control transferred."
    triggers:
      all_of:
        - kind: grant
        - privilege.privileges:
            contains: ownership

  # ON FUTURE — built-in PRIV-ON-FUTURE covers this.
  - id: ACME-GRANT-ON-FUTURE
    risk_level: high
    message: "ON FUTURE grant detected. This affects all future objects in the schema/database."
    triggers:
      all_of:
        - kind: grant
        - privilege.on_future: true

2) Policy: block dangerous grants in prod

Reference rule IDs — policies don't define detection logic. Mix built-in and custom IDs freely:

# .lexega/policy.yaml
# yaml-language-server: $schema=https://lexega.com/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 in prod
  - rule_id: GRT-TO-PUBLIC
    description: Never allow GRANT TO PUBLIC in prod
    envs: [prod]
    action: block
    requires_exception: true

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

  - rule_id: GRT-OWNER-XFER
    description: Block ownership transfers in prod
    envs: [prod]
    action: block
    requires_exception: true

  - rule_id: SNW-GRT-PRIV-ROLE
    description: Block grants of Snowflake privileged system roles
    envs: [prod]
    action: block
    requires_exception: true

  # HIGH: warn on these (or block if you're strict)
  - rule_id: GRT-ALL-PRIV
    description: Warn on ALL PRIVILEGES grants
    envs: [prod]
    action: warn

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

  # Optional: gate every grant in prod (uses the custom rule above)
  # - rule_id: ACME-GRANT-ANY
  #   description: All GRANT statements require approval 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=https://lexega.com/schemas/v1/exceptions.schema.json
schema_version: 1

exceptions:
  - exception_id: EX-2026-GRANT-001
    policy_id: grant-safety
    rule_id: GRT-TO-PUBLIC
    approved_by: [email protected]
    approved_at: "2026-05-22T00: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-12-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 facts available on the predicate surface (under privilege.*):

• privilege.with_grant_option — WITH GRANT OPTION enabled
• privilege.all_privileges — GRANT ALL PRIVILEGES
• privilege.privileges (Vec) — explicit privilege names; use contains: ownership / contains_any: [...]
• privilege.grantees (Vec) — quantify with exists: { name.normalized: PUBLIC } or exists: { kind: share }
• privilege.on_future — ON FUTURE grants
• privilege.target.kind — table, schema, database, role, catalog, … (snake-cased ObjectKind)
• privilege.target.name.name.normalized — case-normalized object name; pair with in: [...] or matches: <glob>
• privilege.role_grant_impact.* — catalog-derived expansion: inherited_privileges, affected_users, parent_role.normalized / child_role.normalized (match privileged roles yourself with in: [...])

See /docs/rule-reference for the full built-in rule list and /docs/custom-rules for the predicate-DSL reference.