Catalog ACL Policy Commands

Seven 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.
`RESET POLICIES`	Clear all imported policies for the project, so an import can be re-run.

RESET POLICIES takes no parameters and applies to the whole project. The other six are catalog-scoped; of those, 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.

RESET POLICIES

Description

Clears every access-control policy Onehouse holds for the project, across all catalogs.

This is the un-import: it removes only Onehouse's copy of the policies and does not modify your external catalog. Nothing is revoked, and no principal loses access in Glue, Unity, or anywhere else — the policies simply stop being tracked on the Onehouse side.

Use it when an import brought in the wrong policy set. After a reset, SHOW POLICIES DIFF reports a full difference against the external catalog again, so IMPORT POLICIES can be re-run from a clean state.

warning

This cannot be undone. Onehouse keeps no copy of the cleared policies. Recovery means re-running IMPORT POLICIES against the external catalog.

Syntax

RESET POLICIES

Example

RESET POLICIES

Required parameters

None. The command applies to the whole project and takes no catalog target — unlike the other policy commands, which are scoped to a single catalog.

See Status API response.

Status API response

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

RESET POLICIES completes synchronously, so it reaches API_OPERATION_STATUS_SUCCESS or API_OPERATION_STATUS_FAILED immediately rather than passing through PENDING. The other four run as background workflows and will report PENDING until they finish.

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"]
      }
    }
  ]
}

SHOW POLICIES​

Description​

Syntax​

Example​

Required parameters​

Example Status API response​

SHOW POLICIES DIFF​

Description​

Syntax​

Examples​

Required parameters​

Example Status API response​

IMPORT POLICIES​

Description​

Syntax​

Example​

Required parameters​

Optional parameters​

SYNC POLICIES​

Description​

Syntax​

Example​

Required parameters​

Optional parameters​

GRANT POLICIES​

Description​

Syntax​

Example​

Required parameters​

REVOKE POLICIES​

Description​

Syntax​

Example​

Required parameters​

RESET POLICIES​

Description​

Syntax​

Example​

Required parameters​

Status API response​

CHANGES_PATH file format​

Structure​

Example​

SHOW POLICIES

Description

Syntax

Example

Required parameters

Example Status API response

SHOW POLICIES DIFF

Description

Syntax

Examples

Required parameters

Example Status API response

IMPORT POLICIES

Description

Syntax

Example

Required parameters

Optional parameters

SYNC POLICIES

Description

Syntax

Example

Required parameters

Optional parameters

GRANT POLICIES

Description

Syntax

Example

Required parameters

REVOKE POLICIES

Description

Syntax

Example

Required parameters

RESET POLICIES

Description

Syntax

Example

Required parameters

Status API response

CHANGES_PATH file format

Structure

Example