Catalog ACL Policy Commands

Six SQL commands for managing access-control policies on external catalogs:

Command	Purpose
SHOW POLICIES	View the current policy snapshot on a catalog.
SHOW POLICIES DIFF	View the diff between an external catalog and Onehouse policies.
IMPORT POLICIES	Pull external-catalog policies into Onehouse.
SYNC POLICIES	Push Onehouse policies out to an external catalog.
GRANT POLICIES	Grant specific privileges to specific principals.
REVOKE POLICIES	Revoke specific privileges.

IMPORT, SYNC, GRANT, and REVOKE accept their change list via the CHANGES_PATH clause — a URL pointing to a JSON file in your cloud-storage bucket. The natural workflow is SHOW POLICIES DIFF → IMPORT POLICIES (or → SYNC POLICIES): feed the URL returned by the diff command directly back as CHANGES_PATH.

---

SHOW POLICIES

Description

Returns the full policy snapshot on a catalog.

Syntax

SHOW POLICIES CATALOG = `<catalog_name>`

Example

SHOW POLICIES CATALOG = my_unity

Required parameters

• <catalog_name>: Name of the catalog to show policies for.

Example Status API response

The result is written to your cloud-storage bucket; the response returns the path:

{
    "apiStatus": "API_OPERATION_STATUS_SUCCESS",
    "apiResponse": {
        "showPoliciesApiResponse": {
            "queryResultPath": "s3://my-bucket/onehouse/query-results/q-abc-001.json"
        }
    }
}

Fetch the result with your cloud SDK (e.g., aws s3 cp <queryResultPath> ./policies.json).

---

SHOW POLICIES DIFF

Description

Returns the changes needed to align two policy sets. The FOR clause selects direction:

• FOR IMPORT — changes to apply in Onehouse to match the external catalog.
• FOR SYNC — changes to apply in the external catalog to match Onehouse.

The URL returned by this command can be passed directly as CHANGES_PATH to IMPORT POLICIES or SYNC POLICIES.

Syntax

SHOW POLICIES DIFF CATALOG = `<catalog_name>` FOR { IMPORT | SYNC }

Examples

SHOW POLICIES DIFF CATALOG = my_unity FOR IMPORT

SHOW POLICIES DIFF CATALOG = my_unity FOR SYNC

Required parameters

• <catalog_name>: Name of the external catalog to diff against.
• FOR: One of IMPORT or SYNC.

Example Status API response

{
    "apiStatus": "API_OPERATION_STATUS_SUCCESS",
    "apiResponse": {
        "showPoliciesDiffApiResponse": {
            "queryResultPath": "s3://my-bucket/onehouse/query-results/q-abc-002.json"
        }
    }
}

The queryResultPath can be passed directly as CHANGES_PATH to IMPORT POLICIES or SYNC POLICIES.

---

IMPORT POLICIES

Description

Pulls external-catalog policies into Onehouse.

Syntax

IMPORT POLICIES CATALOG = `<catalog_name>` CHANGES_PATH = '<url>'
    [ POLICY_EXCLUDE_FILTER = '<filter>' ]

Example

IMPORT POLICIES CATALOG = my_unity
    CHANGES_PATH = 's3://my-bucket/onehouse/query-results/diff-001.json'

Required parameters

• <catalog_name>: Name of the external catalog.
• CHANGES_PATH: URL of a JSON file in your cloud-storage bucket containing the change list (typically the path returned by SHOW POLICIES DIFF ... FOR IMPORT).

Optional parameters

• POLICY_EXCLUDE_FILTER: Filter expression to skip selected entries in the change list.

See Status API response.

---

SYNC POLICIES

Description

Pushes Onehouse policies out to an external catalog.

Syntax

SYNC POLICIES CATALOG = `<catalog_name>` CHANGES_PATH = '<url>'
    [ POLICY_EXCLUDE_FILTER = '<filter>' ]

Example

SYNC POLICIES CATALOG = my_unity
    CHANGES_PATH = 's3://my-bucket/onehouse/query-results/diff-001.json'

Required parameters

• <catalog_name>: Name of the external catalog.
• CHANGES_PATH: URL of a JSON file in your cloud-storage bucket containing the change list (typically the path returned by SHOW POLICIES DIFF ... FOR SYNC).

Optional parameters

• POLICY_EXCLUDE_FILTER: Filter expression to skip selected entries in the change list.

See Status API response.

---

GRANT POLICIES

Description

Grants specific privileges on a catalog.

Syntax

GRANT POLICIES ON CATALOG `<catalog_name>` CHANGES_PATH = '<url>'

Example

GRANT POLICIES ON CATALOG my_unity
    CHANGES_PATH = 's3://my-bucket/onehouse/imports/grant-new-analyst.json'

Required parameters

• <catalog_name>: Name of the catalog.
• CHANGES_PATH: URL of a JSON file in your cloud-storage bucket describing the grants to apply. See CHANGES_PATH file format.

See Status API response.

---

REVOKE POLICIES

Description

Revokes specific privileges on a catalog.

Syntax

REVOKE POLICIES ON CATALOG `<catalog_name>` CHANGES_PATH = '<url>'

Example

REVOKE POLICIES ON CATALOG my_unity
    CHANGES_PATH = 's3://my-bucket/onehouse/imports/revoke-former-analyst.json'

Required parameters

• <catalog_name>: Name of the catalog.
• CHANGES_PATH: URL of a JSON file in your cloud-storage bucket describing the revocations to apply. See CHANGES_PATH file format.

See Status API response.

---

Status API response

IMPORT POLICIES, SYNC POLICIES, GRANT POLICIES, and REVOKE POLICIES can return any of the following apiStatus values:

• API_OPERATION_STATUS_PENDING — the request is still being processed; poll again.

• API_OPERATION_STATUS_SUCCESS — the request completed successfully. apiResponse is empty:

  {
      "apiStatus": "API_OPERATION_STATUS_SUCCESS",
      "apiResponse": {}
  }

• API_OPERATION_STATUS_FAILED — the request failed. See the Status API for the full list of apiStatus values.

---

CHANGES_PATH file format

GRANT POLICIES and REVOKE POLICIES need a JSON file in your cloud-storage bucket listing the privilege changes to apply. The simplest way to produce it is to run SHOW POLICIES DIFF first and edit the resulting file in place to keep only the rows you want — that file is already in the correct shape and you only need to point CHANGES_PATH at the edited copy.

Structure

{
  "catalog_policy_changes": [
    {
      "resource_info": {
        "resource_type": "<resource_type>",
        "database": "<database_name>",
        "table": "<table_name>"
      },
      "principal_info": {
        "principal_type": "<principal_type>",
        "principal_name": "<principal_name>"
      },
      "privilege_change_info": {
        "privilege_change_type": "<change_type>",
        "current_privilege_types": ["<privilege>", "..."]
      }
    }
  ]
}

Field values:

• resource_type: One of RESOURCE_TYPE_DATABASE, RESOURCE_TYPE_TABLE. Include only the identifier fields relevant to the level (e.g., omit table for a database-level grant).
• principal_type: One of PRINCIPAL_TYPE_USER, PRINCIPAL_TYPE_GROUP, PRINCIPAL_TYPE_ROLE.
• privilege_change_type: PRIVILEGE_CHANGE_TYPE_INSERT for grants, PRIVILEGE_CHANGE_TYPE_DELETE for revocations.
• current_privilege_types: Array of privilege names such as SELECT, INSERT, UPDATE, DELETE.

Example

Grant SELECT on the analytics.orders table to a new user:

{
  "catalog_policy_changes": [
    {
      "resource_info": {
        "resource_type": "RESOURCE_TYPE_TABLE",
        "database": "analytics",
        "table": "orders"
      },
      "principal_info": {
        "principal_type": "PRINCIPAL_TYPE_USER",
        "principal_name": "new_analyst"
      },
      "privilege_change_info": {
        "privilege_change_type": "PRIVILEGE_CHANGE_TYPE_INSERT",
        "current_privilege_types": ["SELECT"]
      }
    }
  ]
}