Skip to content
Last updated

Database-Scoped Column-Level Access Control

Database-scoped column-level access control lets you apply column rules to specific databases rather than every database in the account. For example, you can allow only non-PII columns in db1, while allowing every column except PII in db2.

Before using this guide, read Policy-Based Column-Level Access Control Permissions for the permission model and policy setup. This guide focuses on configuring the additional scope setting through the Permission Policy API.

Prerequisites

Set Database-Scoped Rules on a Policy

The examples on this page use the US endpoint https://api.treasuredata.com. For other sites, see Treasure AI Sites and API Endpoints.

Add "scope": {"type": "db", "names": ["db1"]} to a column rule to apply it only to db1. To use the same rule in several databases, list their names in names. Specify database names, not IDs. If you rename a database, update scope.names to match. Without scope, the rule applies to every database.

PATCH /v3/access_control/policies/{policy_id}/column_permissions replaces every column permission rule in the policy, scoped and unscoped. Include all existing rules you want to keep. Use the same endpoint to change or remove rules: update scope.names to change the databases, omit scope or set it to null to apply a rule to every database, or leave a rule out of the list to remove it.

Replace <YOUR_ADMIN_API_KEY> with your administrator API key and <POLICY_ID> with the policy ID you find in step 1. The following example sets two rules:

  • In db1, users can view only columns tagged non-PII.
  • In db2, users can view every column except columns tagged PII, including untagged columns.
  1. Find your policy ID by listing the policies:

    curl -s -H "Authorization: TD1 <YOUR_ADMIN_API_KEY>" \
      https://api.treasuredata.com/v3/access_control/policies

    Find the policy by its name in the response and use its id as <POLICY_ID>. See Permission Policy API.

  2. Get the policy's current column permissions so you can keep the rules you still need.

    curl -s -H "Authorization: TD1 <YOUR_ADMIN_API_KEY>" \
      "https://api.treasuredata.com/v3/access_control/policies/<POLICY_ID>/column_permissions"
  3. Send the rules you want to apply. The example below replaces the policy's column rules with the two scoped rules; add any existing rules you need to retain.

    curl -s -X PATCH \
      -H "Authorization: TD1 <YOUR_ADMIN_API_KEY>" \
      -H "Content-Type: application/json" \
      -d '{
        "column_permissions": [
          {"tags": ["non-PII"], "except": false, "scope": {"type": "db", "names": ["db1"]}},
          {"tags": ["PII"], "except": true, "scope": {"type": "db", "names": ["db2"]}}
        ]
      }' \
      "https://api.treasuredata.com/v3/access_control/policies/<POLICY_ID>/column_permissions"

    The response returns the saved rules, including their database scopes.

  4. Confirm the result by repeating the GET request from step 2. The response lists both rules with their scope objects.

  5. Apply the policy to the intended users if it isn't already assigned.

After the policies refresh, this example gives the following access, provided the user has database permissions and no other column rules grant additional access:

ColumnIn db1In db2In any other database
Tagged non-PIIViewViewNone
Tagged PIINoneNoneNone
UntaggedNoneViewNone

The time column stays readable, as with all column-level access control.

Usage Notes

Mixing scoped and unscoped rules is not recommended when you need strict per-database access. For example, a user has both of these rules in their assigned policies:

[
  {"tags": ["non-PII"], "except": false, "scope": {"type": "db", "names": ["db1"]}},
  {"tags": ["PII"], "except": true}
]

The first rule allows only non-PII-tagged columns in db1. However, the second rule allows columns without PII in every database, including untagged columns in db1. The user can therefore read untagged columns in db1, despite the first rule.

To allow only non-PII-tagged columns in db1, remove the second rule or add "scope": {"type": "db", "names": ["db2"]} to it so it applies only to db2. Check that no other policy assigned to the user allows additional columns in db1.

Limitations

  • API only. You can't view, create, edit, or remove database-scoped rules in Treasure Console. Use PATCH /v3/access_control/policies/{policy_id}/column_permissions.
  • Database names. Each names list takes 1 to 100 database names. Database names follow the standard Treasure AI database naming rules.