Configure Row and Column Privileges

Pre-validate and apply table query privileges along with row and column filtering rules for an ordinary workspace role, then verify the database permission update through its durable operation receipt.

Task flow

Workspace IAM handle + row/column mutation JSON
                      │
                      ▼
1. Read current data privilege version and pre-validate (validate only, no DB DCL)
                      │
                      ▼
2. Apply the same mutation request ──▶ Query operation receipt by request ID
                      │
                      ▼
3. Re-read role data privilege view to confirm updated version

Prerequisites

  1. Complete the endpoint, personal access token, and workspace setup in Use the SDK.

  2. Obtain an editable ordinary role ID by listing workspace roles as shown in Manage Roles and Object Permissions.

  3. Obtain the numeric table ID and column names: query selectable tables through the IAM handle’s permission resource picker (passing resource type table and action table.query), or verify table IDs and schemas via Manage Catalogs, Databases, and Tables. Numeric table IDs are required in grant mutations.

  4. Prepare a JSON mutation file and a unique request ID DATA_PRIVILEGE_REQUEST_ID, retaining that ID until the operation reaches a confirmed state.

1. Read the current version and pre-validate

Read the role’s current data privilege version first, combine it with your mutation list, and execute pre-validation. Pre-validation returns field-level errors, warnings, and a canonical preview; proceed to apply only after confirming valid is true.

import json
import os
from pathlib import Path

from google.protobuf.json_format import MessageToDict, ParseDict
import moi_product_sdk as sdk

client = sdk.new_with_personal_access_token(
    os.environ["PRODUCT_API_BASE_URL"],
    os.environ["PRODUCT_API_KEY"],
)
iam = client.workspace(os.environ["WORKSPACE_ID"]).iam()
role_id = os.environ["TARGET_ROLE_ID"]
request_id = os.environ["DATA_PRIVILEGE_REQUEST_ID"]

view = iam.data_privileges(role_id)
mutations = json.loads(Path(os.environ["MUTATIONS_FILE"]).read_text(encoding="utf-8"))
request = ParseDict(
    {
        "request_id": request_id,
        "expected_data_privilege_version": view.data_privilege_version,
        "mutations": mutations,
    },
    sdk.IAMDataPrivilegeMutationRequest(),
)
print("Request:", request_id, "Expected version:", view.data_privilege_version)

validation = iam.validate_data_privileges(role_id, request)
print(MessageToDict(validation, preserving_proto_field_name=True))
if not validation.valid:
    raise SystemExit("Resolve validation errors before applying this request")
package main

import (
	"context"
	"fmt"
	"os"

	sdk "github.com/matrixorigin/matrixflow/sdk/go-sdk"
	"google.golang.org/protobuf/encoding/protojson"
)

func main() {
	ctx := context.Background()
	client, err := sdk.NewWithPersonalAccessToken(
		os.Getenv("PRODUCT_API_BASE_URL"),
		os.Getenv("PRODUCT_API_KEY"),
	)
	if err != nil {
		panic(err)
	}
	defer client.Close()
	workspace, err := client.Workspace(os.Getenv("WORKSPACE_ID"))
	if err != nil {
		panic(err)
	}
	iam := workspace.IAM()
	roleID := os.Getenv("TARGET_ROLE_ID")
	requestID := os.Getenv("DATA_PRIVILEGE_REQUEST_ID")

	view, err := iam.DataPrivileges(ctx, roleID)
	if err != nil {
		panic(err)
	}
	raw, err := os.ReadFile(os.Getenv("MUTATIONS_FILE"))
	if err != nil {
		panic(err)
	}
	request := &sdk.IAMDataPrivilegeMutationRequest{}
	if err := protojson.Unmarshal(append(append([]byte(`{"mutations":`), raw...), '}'), request); err != nil {
		panic(err)
	}
	request.RequestId = requestID
	request.ExpectedDataPrivilegeVersion = view.GetDataPrivilegeVersion()
	fmt.Println("Request:", requestID, "Expected version:", view.GetDataPrivilegeVersion())

	validation, err := iam.ValidateDataPrivileges(ctx, roleID, request)
	if err != nil {
		panic(err)
	}
	encoded, err := protojson.MarshalOptions{UseProtoNames: true}.Marshal(validation)
	if err != nil {
		panic(err)
	}
	fmt.Println(string(encoded))
	if !validation.GetValid() {
		panic("Resolve validation errors before applying this request")
	}
}

MUTATIONS_FILE contains a JSON array of mutation items. The following example grants select queries on table 1001, masks the salary column, and excludes rows where region is blocked or unknown:

[
  {
    "operation": "grant",
    "action_id": "table.query",
    "resource_type": "table",
    "resource_id": "1001",
    "privilege": "select",
    "row_column": {
      "mode": "set",
      "draft": {
        "black_column_list": [
          "salary"
        ],
        "rule_list": [
          {
            "column": "region",
            "relation": "and",
            "expression_list": [
              {
                "operator": "not in",
                "expression": [
                  "blocked",
                  "unknown"
                ]
              }
            ]
          }
        ]
      }
    }
  }
]

Always set row_column.mode explicitly on table query grants: use set to write new rules, keep to retain existing rules, or clear to remove existing rules. Omitting the row and column block does not clear active restrictions.

2. Apply mutations and verify the receipt

After reviewing pre-validation results, submit the same request object, then query the durable operation receipt by DATA_PRIVILEGE_REQUEST_ID to inspect overall state, per-resource outcomes, and session invalidation fields before re-reading the role’s data privilege version.

operation = iam.apply_data_privileges(role_id, request)
print(MessageToDict(operation, preserving_proto_field_name=True))

confirmed = iam.data_privilege_operation(role_id, request_id)
print(MessageToDict(confirmed, preserving_proto_field_name=True))
if confirmed.state != "succeeded":
    raise RuntimeError("Inspect operation receipt and per-resource errors before proceeding")

current = iam.data_privileges(role_id)
print("Current data privilege version:", current.data_privilege_version)
operation, err := iam.ApplyDataPrivileges(ctx, roleID, request)
if err != nil {
	panic(err)
}
encoded, err = protojson.MarshalOptions{UseProtoNames: true}.Marshal(operation)
if err != nil {
	panic(err)
}
fmt.Println(string(encoded))

confirmed, err := iam.DataPrivilegeOperation(ctx, roleID, requestID)
if err != nil {
	panic(err)
}
encoded, err = protojson.MarshalOptions{UseProtoNames: true}.Marshal(confirmed)
if err != nil {
	panic(err)
}
fmt.Println(string(encoded))
if confirmed.GetState() != "succeeded" {
	panic("Inspect operation receipt and per-resource errors before proceeding")
}

current, err := iam.DataPrivileges(ctx, roleID)
if err != nil {
	panic(err)
}
fmt.Println("Current data privilege version:", current.GetDataPrivilegeVersion())

Warning

If a request times out or partially fails, underlying database statements may already have executed. Query the operation receipt using the original DATA_PRIVILEGE_REQUEST_ID and inspect per-resource errors instead of resubmitting under a new request ID. To reconcile the permission projection with current database facts, call the IAM handle’s data privilege repair method with the original request ID and expected data privilege version; repair reconciles current database state for affected resources without re-executing grants or revokes.

Session invalidation policies may apply only to new database connections; even when the receipt reaches succeeded, inspect the session invalidation status in the receipt to determine whether existing database sessions have refreshed.

Next steps

Last updated on