Skip to content

Row-Level Access How-to

Manage row-level access policies⚓︎

Row-level access control allows administrators to define filters limiting which data rows users can query. See Row-level Access Control for a description of the feature.

This page demonstrates how to create, modify, delete, and verify row policies which implement the row-level access control feature.

Row-level access overview⚓︎

Data administrators define and group sets of filters and associate them to roles.

When receiving a query, the Hydrolix cluster combines all row policies for all roles associated with the querying account.

The query system enforces data access permissions by returning only the rows in the raw Hydrolix partitions that pass the combined filter.

Before you begin⚓︎

Accompanying the instructions on this page are a tiny sample transform and data set for familiarizing yourself with row-level access controls.

Consider also reading Policy design and Combining row policies when working with production-grade systems.

Required permissions⚓︎

The account used to configure row policies requires several permissions.

  • Row policy management permissions.
  • add_rowpolicy
  • change_rowpolicy
  • view_rowpolicy
  • delete_rowpolicy
  • add_role_rowpolicy

  • remove_role_rowpolicy

  • Role management permissions for role creation.

  • add_role

  • For using the UI the following are useful

  • view_role
  • view_table

Row policy requirements⚓︎

Each row policy must be uniquely named in a table, must include a valid filter, and refer to an existing role.

Action
Create a row policy create using UI create using API
View a row policy view using UI view using API
Update a row policy update using UI update using API
Delete a row policy delete using UI delete using API

Demonstration table⚓︎

The demonstration table for the examples in this how-to is news.requests. The table holds multi-CDN request level data for different sections of a media site. The example row policies install filters to limit access to the Sports section.

Minimal demonstration data and transform are available. This informational table describes the contents of the columns, but especially those used in filter criteria.

Column Type Description
section string A media section, like Business, Lifestyle, Culture, Sports
client_ip ip The requester's IPv4 or IPv6 address; perhaps anonymized or truncated
client_country_iso_code string An ISO-3166-2 country code
response_status_code uint8 The logged numeric HTTP status code
response_bytes_total uint32 Number of bytes in the HTTP response payload sent to the client

The examples below demonstrate row policy management using the above table.

Manage row policies in the UI⚓︎

(Optional) Create role in UI⚓︎

Here, we demonstrate creating an empty role that will serve only as a container for the row policies on the news.requests table.

  1. In the top right corner of the screen, click the + Add new button.
  2. Select the Role option in the right sidebar.

  1. Enter a unique role name, and any description.
  2. Click Create role.

Create in UI⚓︎

  1. In the top right corner of the screen, click the + Add new button.
  2. Select the Row policy option in the right sidebar.
  3. Select an existing Table to which the new row policy will apply.
  4. Enter a unique Name for this row policy in this table.
  5. Enter any valid SQL expression, that evaluates to TRUE to allow users to see the corresponding rows.
  6. Use Add Roles to attach the row policy to one or more existing roles.
  7. Select Restrictive or not. See Combining row policies.
  8. Click Create row policy.

View in UI⚓︎

  1. Click Security in the left sidebar.
  2. Click Row Policies in the top bar.
  3. Select the Table for the row policies you wish to view.

Update in UI⚓︎

  1. Click Security in the left sidebar.
  2. Click Row Policies in the top bar.
  3. Select the Table holding the row policies.
  4. Click any text in the desired row or use the vertical ellipsis (⋮) and select the Edit option to open the row policy editor.
  5. Use the Edit Row Policy page to make changes to the row policy.
  6. Click Save Changes.

Delete in UI⚓︎

  1. Click Security in the left sidebar.
  2. Click Row Policies in the top bar.
  3. Select the Table for the row policies you wish to view.
  4. Use the vertical ellipsis (⋮) and select the Delete option on the row policy you wish to remove.
  5. Cilck Delete on the confirmation dialog.

Deleting a row policy changes data access rules immediately

Deleting a row policy removes it from roles and changes access rules immediately. This could allow broader or unfiltered table access to accounts formerly constrained by the row policy.

Manage row policies with the API⚓︎

General workflow⚓︎

  1. Acquire an auth token using Login to Hydrolix. See also acquiring auth tokens.
  2. Use Create role or choose an existing role to which to assign the row policies.
  3. Create a row policy with the desired filter, assigned roles, and restrictive setting.
  4. Wait a few seconds for the row policies to go into effect in the query system.

Login to the API⚓︎

Get the bearer token, which is good for the next 24 hours, to authenticate future API calls. This command assumes you've set the $HDX_HOSTNAME, $HDX_USER and $HDX_PASSWORD environment variables:

export HDX_TOKEN=$( curl -v -X POST -H "Content-Type: application/json" \ https://$HDX_HOSTNAME/config/v1/login/ \ -d "{ \"username\":\"$HDX_USER\", \"password\":\"$HDX_PASSWORD\" }" | jq -r ".auth_token.access_token" )

(Optional) Create role with API⚓︎

Row policies must be associated with an existing role. See Roles and row policies. In this example, we're creating an empty role.

Use the Create role endpoint.

1
2
3
4
5
POST /config/v1/roles/
{
  "name": "news_requests_sports",
  "policies": []
}

1
2
3
4
5
{
  "id": 10,
  "name": "news_requests_sports",
  "policies": []
}

Create with API⚓︎

A new row policy object must contain

  • A row policy name unique for this table
  • A valid filter. See Filters and composition
  • A list of roles. You must supply at least one role and all specified roles must already exist

Use the Create a row policy endpoint.

1
2
3
4
5
6
7
POST /config/v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/rowpolicies/
{
    "name": "section-sports",
    "filter": "section = 'Sports'",
    "roles": ["news_requests_sports"],
    "restrictive": false
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
  "uuid": "362d993e-2e1b-4db3-8b58-ed337e00cb9d",
  "created": "2025-08-27T15:24:05.592837Z",
  "modified": "2025-08-27T15:24:05.592852Z",
  "roles": [
    "news_requests_sports"
  ],
  "table": "news.requests",
  "name": "section-sports",
  "filter": "section = 'Sports'",
  "restrictive": false
}

An error example⚓︎

The Config API returns an HTTP 400 status code for the following row policy.

There are three problems with the following update:

  • Row policy named section-sports exists
  • Thefilter has an unmatched ending parenthesis ); it's invalid SQL
  • The roles list is empty

Any one of these problems would be reason for an HTTP 400.

1
2
3
4
5
6
7
POST  /config/v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/rowpolicies/
{
    "name": "section-sports",
    "filter": "section = 'Sports')",
    "roles": [],
    "restrictive": false
}

View with API⚓︎

List all row policies⚓︎

This example shows multiple row policies. Creation of isocode-us-fr isn't demonstrated on this page, but is shown in the output below.

Use the Get row policies endpoint.

1
GET  /config/v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/rowpolicies/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
{
  "next": 0,
  "previous": 0,
  "current": 1,
  "num_pages": 1,
  "count": 2,
  "results": [
    {
      "uuid": "362d993e-2e1b-4db3-8b58-ed337e00cb9d",
      "created": "2025-08-27T15:24:05.592837Z",
      "modified": "2025-08-27T15:24:05.592852Z",
      "roles": [
        "news_requests_sports"
      ],
      "table": "news.requests",
      "name": "section-sports",
      "filter": "section = 'Sports'",
      "restrictive": false
    },
    {
      "uuid": "be53f5f7-55d7-4925-b804-52b7c660c860",
      "created": "2025-08-27T17:43:24.894197Z",
      "modified": "2025-08-27T17:43:24.894210Z",
      "roles": [
        "news_requests_sports"
      ],
      "table": "news.requests",
      "name": "isocode-us-fr",
      "filter": "client_country_iso_code IN ('FR', 'US')",
      "restrictive": false
    }
  ]
}

Get a single row policy⚓︎

Use the Get a row policy endpoint.

1
GET  /config/v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/rowpolicies/362d993e-2e1b-4db3-8b58-ed337e00cb9d

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
  "uuid": "362d993e-2e1b-4db3-8b58-ed337e00cb9d",
  "created": "2025-08-27T15:24:05.592837Z",
  "modified": "2025-08-27T15:24:05.592852Z",
  "roles": [
    "news_requests_sports"
  ],
  "table": "news.requests",
  "name": "section-sports",
  "filter": "section = 'Sports'",
  "restrictive": false
}

Update with API⚓︎

Change an existing row policy⚓︎

This example switches the filter to use the SQL IN operator. All other fields remain untouched.

Use Partially update a row policy to change an existing row policy.

1
2
3
4
PATCH  /config/v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/rowpolicies/362d993e-2e1b-4db3-8b58-ed337e00cb9d
{
  "filter": "section IN ('Sports')"
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
  "uuid": "362d993e-2e1b-4db3-8b58-ed337e00cb9d",
  "created": "2025-08-27T15:24:05.592837Z",
  "modified": "2025-08-27T22:40:40.976790Z",
  "roles": [
    "news_requests_sports"
  ],
  "table": "news.requests",
  "name": "section-sports",
  "filter": "section IN ('Sports')",
  "restrictive": false
}

Replace an existing row policy⚓︎

This example renames the row policy to include the table name to demonstrate replacing an existing policy completely.

Use the Update a row policy endpoint to replace the entire policy.

1
2
3
4
5
6
7
PUT  /config/v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/rowpolicies/362d993e-2e1b-4db3-8b58-ed337e00cb9d
{
    "name": "news-requests-section-sports",
    "filter": "section IN ('Sports')",
    "roles": ["news_requests_sports"],
    "restrictive": false
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
{
  "uuid": "362d993e-2e1b-4db3-8b58-ed337e00cb9d",
  "created": "2025-08-27T15:24:05.592837Z",
  "modified": "2025-08-28T13:57:43.993920Z",
  "roles": [
    "news_requests_sports"
  ],
  "table": "news.requests",
  "name": "news-requests-section-sports",
  "filter": "section IN ('Sports')",
  "restrictive": false
}

Delete with API⚓︎

This example removes the row policy created earlier on this page.

Use Delete a row policy to remove a row policy from the system completely.

1
DELETE  /config/v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/rowpolicies/362d993e-2e1b-4db3-8b58-ed337e00cb9d

Upon success, the server returns HTTP status code 204 and no content.

Verification steps⚓︎

Changes to row policies are applied immediately to accounts querying the tables. There is a brief delay as the updated row policies are combined, distributed to the query services, and applied to new incoming queries.

To verify the installed row policies match your intended data access plan, apply the roles to an account and issue queries using that account. A service account is convenient for programmatic verification.

The single row policy associated with the role news_requests_sports allows accounts with that role to see only rows where the section column contains exactly the value Sports. Here's an example of a simple verification step.

1
2
3
$ echo "SELECT DISTINCT section FROM news.requests;" \
  | mysql 2>/dev/null --host "${HDX_HOSTNAME}" --skip-column-names  --batch --user __api_token__ --port 9004 --password="${HDX_TOKEN}"
Sports

1
2
3
4
5
6
$ echo "SELECT DISTINCT section FROM news.requests;" \
  | mysql 2>/dev/null --host "${HDX_HOSTNAME}" --skip-column-names  --batch --user __api_token__ --port 9004 --password="${HDX_TOKEN}"
Sports
Lifestyle
Culture
Business

Troubleshoot⚓︎

Problem: Expected row policy not in effect⚓︎

If a row policy doesn't seem to affect a user as expected, verify the role associated to the row policy matches one the user's roles.

  1. Locate the row policies for the table, and identify the roles for the row policies.
  2. Visit Security > Row Policies in the UI, and select the desired table.
  3. Use List tables and Get row policies
  4. Examine the roles associated with a user.
  5. Visit Security > Users in the UI.
  6. Use List users or Get user to see the user's roles.
  7. Confirm the association of the row policies to a role associated with the user.

Demonstration samples⚓︎

Here is an example transform and several rows of data used in illustrating the row policies feature.

Demonstration transform⚓︎

=== "A Transform For news.requests" 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
{
  "transform": {
    "type": "json",
    "settings": {
      "output_columns": [
        {
          "name": "timestamp",
          "datatype": {
            "type": "datetime",
            "primary": true,
            "format": "2006-01-02 15:04:05 MST",
            "resolution": "ms",
            "source": {
              "from_automatic_value": "current_time"
            }
          }
        },
        {
          "name": "section",
          "datatype": {
            "type": "string"
          }
        },
        {
          "name": "client_country_iso_code",
          "datatype": {
            "type": "string"
          }
        },
        {
          "name": "client_ip",
          "datatype": {
            "type": "ip"
          }
        },
        {
          "name": "response_status_code",
          "datatype": {
            "type": "uint8"
          }
        },
        {
          "name": "response_bytes_total",
          "datatype": {
            "type": "uint32"
          }
        }
      ]
    }
  }
}

Demonstration data⚓︎

=== "Sample Data To Construct Table" 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
[
    {
      "section": "Sports",
      "client_country_iso_code": "US",
      "client_ip": "71.57.62.50",
      "response_status_code": 200,
      "response_bytes_total": 80707
    },
    {
      "section": "Sports",
      "client_country_iso_code": "US",
      "client_ip": "24.27.228.131",
      "response_status_code": 200,
      "response_bytes_total": 9407
    },
    {
      "section": "Sports",
      "client_country_iso_code": "IN",
      "client_ip": "61.11.4.75",
      "response_status_code": 200,
      "response_bytes_total": 176767
    },
    {
      "section": "Sports",
      "client_country_iso_code": "IN",
      "client_ip": "61.11.3.99",
      "response_status_code": 200,
      "response_bytes_total": 12820
    },
    {
      "section": "Sports",
      "client_country_iso_code": "FR",
      "client_ip": "80.12.11.178",
      "response_status_code": 200,
      "response_bytes_total": 56871
    },
    {
      "section": "Sports",
      "client_country_iso_code": "FR",
      "client_ip": "80.12.14.207",
      "response_status_code": 200,
      "response_bytes_total": 131531
    },
    {
      "section": "Sports",
      "client_country_iso_code": "DE",
      "client_ip": "37.50.61.111",
      "response_status_code": 200,
      "response_bytes_total": 41414
    },
    {
      "section": "Sports",
      "client_country_iso_code": "DE",
      "client_ip": "62.214.238.149",
      "response_status_code": 200,
      "response_bytes_total": 53225
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "US",
      "client_ip": "71.56.168.94",
      "response_status_code": 200,
      "response_bytes_total": 83390
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "US",
      "client_ip": "16.191.1.197",
      "response_status_code": 200,
      "response_bytes_total": 154
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "IN",
      "client_ip": "115.112.145.207",
      "response_status_code": 200,
      "response_bytes_total": 74171
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "IN",
      "client_ip": "61.11.1.136",
      "response_status_code": 200,
      "response_bytes_total": 189135
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "FR",
      "client_ip": "80.12.6.219",
      "response_status_code": 200,
      "response_bytes_total": 71261
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "FR",
      "client_ip": "2.12.104.84",
      "response_status_code": 200,
      "response_bytes_total": 19601
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "DE",
      "client_ip": "62.214.243.241",
      "response_status_code": 200,
      "response_bytes_total": 75508
    },
    {
      "section": "Lifestyle",
      "client_country_iso_code": "DE",
      "client_ip": "2.162.119.239",
      "response_status_code": 200,
      "response_bytes_total": 24924
    },
    {
      "section": "Business",
      "client_country_iso_code": "US",
      "client_ip": "71.56.54.252",
      "response_status_code": 200,
      "response_bytes_total": 41596
    },
    {
      "section": "Business",
      "client_country_iso_code": "US",
      "client_ip": "24.27.231.214",
      "response_status_code": 200,
      "response_bytes_total": 13349
    },
    {
      "section": "Business",
      "client_country_iso_code": "IN",
      "client_ip": "115.112.160.225",
      "response_status_code": 200,
      "response_bytes_total": 177977
    },
    {
      "section": "Business",
      "client_country_iso_code": "IN",
      "client_ip": "61.11.11.250",
      "response_status_code": 200,
      "response_bytes_total": 173828
    },
    {
      "section": "Business",
      "client_country_iso_code": "FR",
      "client_ip": "80.12.10.255",
      "response_status_code": 200,
      "response_bytes_total": 128701
    },
    {
      "section": "Business",
      "client_country_iso_code": "FR",
      "client_ip": "2.12.78.25",
      "response_status_code": 200,
      "response_bytes_total": 92159
    },
    {
      "section": "Business",
      "client_country_iso_code": "DE",
      "client_ip": "2.175.131.2",
      "response_status_code": 200,
      "response_bytes_total": 43755
    },
    {
      "section": "Business",
      "client_country_iso_code": "DE",
      "client_ip": "2.171.167.248",
      "response_status_code": 200,
      "response_bytes_total": 29180
    },
    {
      "section": "Culture",
      "client_country_iso_code": "US",
      "client_ip": "24.27.228.54",
      "response_status_code": 200,
      "response_bytes_total": 33120
    },
    {
      "section": "Culture",
      "client_country_iso_code": "US",
      "client_ip": "16.191.1.201",
      "response_status_code": 200,
      "response_bytes_total": 182454
    },
    {
      "section": "Culture",
      "client_country_iso_code": "IN",
      "client_ip": "115.112.230.109",
      "response_status_code": 200,
      "response_bytes_total": 184091
    },
    {
      "section": "Culture",
      "client_country_iso_code": "IN",
      "client_ip": "115.112.66.224",
      "response_status_code": 200,
      "response_bytes_total": 16422
    },
    {
      "section": "Culture",
      "client_country_iso_code": "FR",
      "client_ip": "80.12.8.83",
      "response_status_code": 200,
      "response_bytes_total": 98296
    },
    {
      "section": "Culture",
      "client_country_iso_code": "FR",
      "client_ip": "80.12.0.253",
      "response_status_code": 200,
      "response_bytes_total": 178469
    },
    {
      "section": "Culture",
      "client_country_iso_code": "DE",
      "client_ip": "2.163.213.161",
      "response_status_code": 200,
      "response_bytes_total": 95524
    },
    {
      "section": "Culture",
      "client_country_iso_code": "DE",
      "client_ip": "62.214.244.253",
      "response_status_code": 200,
      "response_bytes_total": 146540
    }
]