Skip to content

Data Access Policy Examples

This page illustrates data access policies and the composability of permissions, row- and column-level policies.

Setup for examples⚓︎

For all of the examples in this page

  • project news contains tables requests and another
  • table news.requests holds exactly 1000 rows of fabricated CDN request-style data for different sections of a media site
  • table news.another holds no rows and exists only to demonstrate permissions and scoping interactions when composing data access policies
  • table hydro.logs is a system table mentioned to demonstrate scope applicability. See Hydrologs
  • role empty exists, has no permissions, row, or column policies
  • roles and policies are combined to illustrate access controls on table news.requests

Each example describes a scenario involving one or more roles and different combinations of row and column policies.

A fabricated simple table called news.requests holding CDN-like rows for a media site serves as example data.

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
cciso string An ISO-3166-2 country code
response_code uint8 The logged numeric HTTP status code
response_bytes uint32 Number of bytes in the HTTP response payload sent to the client

Minimal demonstration data and a transform are available.

Summary of example roles⚓︎

Hypothetical data access roles include

  • Media specialists who have access to the Sports section, or all client traffic in France but can't see the client IP
  • Network operations personnel who are permitted to see the client IP addresses but can't see the media sections.
  • Business analysts who can see everything in the table. Here the role is defined separately to demonstrate role composition interactions.

Click on the role link to jump to a section demonstrating the access control.

Role Scope Permissions Row Filter Blocked Columns Notes
nr_sports_sp news.requests select_sql section = 'Sports' client_ip only Sports; client_ip inaccessible
nr_fr_sp news.requests select_sql cciso = 'FR' client_ip only FR; client_ip inaccessible
nr_netops_sp news.requests select_sql None section all rows; section inaccessible
nr_analyst_sp news.requests select_sql True None all rows, columns; row filter
nr_read news.requests select_sql None None all rows, columns; table scope
nr_sports None None section = 'Sports' client_ip only Sports; client_ip allowed, see warning
read_only Global select_sql, etc. None None all rows, columns; global preset
empty n/a None None None no access

Always define scoped permissions in roles with column policies

If the select_sql permission is not present on the same role as a column policy, the column policy doesn't apply. See explanation of example role nr_sports.

Single role examples⚓︎

Default-deny⚓︎

Without explicit permissions, an account has no access and can't see anything.

Description
Account roles empty
Permissions None
Column policies None
Row policies None 
1
2
3
4
5
GET /query query=="SELECT COUNT() FROM news.requests"
{
    "error": "Code: 497. DB::Exception: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT() FROM news.requests"
}

Unfettered global access⚓︎

With the global preset read_only role, an account has permissions to read all tables, including news.requests.

Description
Account roles read_only
Permissions Global preset permissions: select_sql, show_columns_sql, etc.
Column policies None
Row policies None 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
1000

The broad scope of the read_only role grants access to other tables.

1
2
GET /query query=="SELECT COUNT() FROM news.another"
0

1
2
GET /query query=="SELECT COUNT() FROM hydro.logs WHERE timestamp > NOW() - INTERVAL 1 HOUR"
388559

Unfettered table scope⚓︎

With table-scoped select_sql permissions, an account can see the whole table, but no other tables.

Description
Account roles nr_read
Permissions table-scoped select_sql
Column policies None
Row policies None 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
1000

1
2
3
4
5
GET /query query=="SELECT COUNT() FROM news.another"
{
    "error": "Code: 497. DB::Exception: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON news.another. (ACCESS_DENIED)",
    "query": "SELECT COUNT() FROM news.another"
}

Sports only, empty permissions⚓︎

Without any scoped permissions, an account has no access.

Description
Account roles nr_sports
Permissions None
Column policies One: blocked = client_ip
Row policies One: section = 'Sports' 
1
2
3
4
5
GET /query query=="SELECT COUNT() FROM news.requests"
{
    "error": "Code: 497. DB::Exception: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT() FROM news.requests"
}

Sports only, scoped permissions⚓︎

With table-scoped permissions and corresponding row and column policies, an account has tightly controlled access. This is an example of strict composition strategy. Other tables aren't accessible.

Description
Account roles nr_sports_sp
Permissions table-scoped select_sql
Column policies One: blocked = client_ip
Row policies One: section = 'Sports' 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
252

1
2
3
4
5
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
{
    "error": "Code: 497. DB::Exception: username@example.com: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(client_ip) ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT(client_ip) FROM news.requests"
}

France only, scoped permissions⚓︎

This is another example of limiting access to a subset of rows in the news.requests table and forbidding accessibility of the sensitive client_ip column. Other tables are also inaccessible to account with solely this role. This is an example of strict composition strategy.

Description
Account roles nr_fr_sp
Permissions table-scoped select_sql
Column policies One: blocked = client_ip
Row policies One: cciso = 'FR' 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
250

1
2
3
4
5
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
{
    "error": "Code: 497. DB::Exception: username@example.com: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(client_ip) ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT(client_ip) FROM news.requests"
}

Network operations⚓︎

Network personnel require the client_ip for troubleshooting. The column policy blocking the section column illustrated here is arbitrary but becomes relevant when combined with other column policies as shown in Row filters and two column policies.

Accounts with solely this role aren't permitted to see the section column.

Description
Account roles nr_netops_sp
Permissions table-scoped select_sql
Column policies One: blocked = section
Row policies None 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
1000

1
2
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
1000

1
2
3
4
5
GET /query query=="SELECT COUNT(section) FROM news.requests"
{
    "error": "Code: 497. DB::Exception: username@example.com: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(section) ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT(section) FROM news.requests"
}

Analyst⚓︎

An analyst should be able to see the whole table. The row filter is unnecessary in this single-role example. It becomes relevant when combined with other row policies as shown in Row filters and an empty column policy.

Description
Account roles nr_analyst_sp
Permissions table-scoped select_sql
Column policies None
Row policies One: True 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
1000

1
2
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
1000

Role combination examples⚓︎

Click on the role combination link to jump to a section demonstrating the access control.

Combination COUNT() Blocked columns hydro.logs? Notes
nr_sports, read_only 252 None readable only Sports
nr_sports_sp, read_only 252 client_ip readable only Sports
nr_sports_sp, nr_fr_sp 439 client_ip inaccessible only Sports or client in FR
nr_fr_sp, nr_netops_sp 439 None inaccessible only Sports or client in FR
nr_sports_sp, nr_analyst_sp 1000 client_ip inaccessible all rows

Read-only and unscoped role⚓︎

In this example, the broadly scoped read_only global preset combines with a row filter in nr_sports.

The column client_ip is allowed because role nr_sports lacks any permissions scope. To forbid access to client_ip see the Read-only and scoped role example. See also combining column policies.

Role Role
Account roles nr_sports read_only
Permissions None select_sql, etc.
Column policies One: blocked = client_ip None
Row policies One: section = 'Sports' None

Effective access, after combination

Description
Permissions broadly scoped select_sql, etc.
Blocked columns None
Combined row filter (section = 'Sports') 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
252

1
2
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
252

The broader scope of the read_only role grants access to other tables.

1
2
GET /query query=="SELECT COUNT() FROM news.another"
0

1
2
GET /query query=="SELECT COUNT() FROM hydro.logs WHERE timestamp > NOW() - INTERVAL 1 HOUR"
388559

Read-only and scoped role⚓︎

In this example, the broadly scoped read_only global preset combines with a row filter in nr_sports_sp. Column client_ip is blocked.

Role Role
Account roles nr_sports_sp read_only
Permissions table-scoped select_sql select_sql, etc.
Column policies One: blocked = client_ip None
Row policies One: section = 'Sports' None

Effective access, after combination

Description
Permissions broadly scoped select_sql, etc.
Blocked columns client_ip
Combined row filter (section = 'Sports') 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
252

1
2
3
4
5
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
{
    "error": "Code: 497. DB::Exception: username@example.com: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(client_ip) ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT(client_ip) FROM news.requests"
}

The broader scope of the read_only role grants access to other tables.

1
2
GET /query query=="SELECT COUNT() FROM news.another"
0

1
2
GET /query query=="SELECT COUNT() FROM hydro.logs WHERE timestamp > NOW() - INTERVAL 1 HOUR"
388559

Different row filters, same column policy⚓︎

Several prior examples compose with the broadly-scoped read_only role. This example uses table-scoped permissions in each role employing the strict composition strategy.

The intermediate row filters in the two roles are joined with an SQL OR to form the final filter expression. The client_ip column isn't allowed since both roles block it.

Role Role
Account roles nr_sports_sp nr_fr_sp
Permissions table-scoped select_sql table-scoped select_sql
Column policies One: blocked = client_ip One: blocked = client_ip
Row policies One: section = 'Sports' One: cciso = 'FR'

Effective access, after combination

Description
Permissions table scoped select_sql
Blocked columns client_ip
Combined row filter (section = 'Sports') OR (cciso = 'FR') 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
439

1
2
3
4
5
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
{
    "error": "Code: 497. DB::Exception: username@example.com: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(client_ip) ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT(client_ip) FROM news.requests"
}

The strict scope of permisions prohibits access to other tables.

1
2
3
4
5
GET /query query=="SELECT COUNT() FROM news.another"
{
    "error": "Code: 497. DB::Exception: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON news.another. (ACCESS_DENIED)",
    "query": "SELECT COUNT() FROM news.another"
}

1
2
3
4
5
GET /query query=="SELECT COUNT() FROM hydro.logs WHERE timestamp > NOW() - INTERVAL 1 HOUR"
{
    "error": "Code: 497. DB::Exception: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON hydro.logs. (ACCESS_DENIED)",
    "query": "SELECT COUNT() FROM hydro.logs WHERE timestamp > NOW() - INTERVAL 1 HOUR"
}

Two column policies and one row filter⚓︎

In this example, two column policies block different columns. The intersection of blocked columns is the empty set, so all columns in the table are allowed.

The final filter expression is tighter than nr_netops_sp itself because there is no row policy defined. For policy construction, compare this example with the nr_analyst_sp role as shown in Row filters and an empty column policy.

Role Role
Account roles nr_netops_sp nr_fr_sp
Permissions table-scoped select_sql table-scoped select_sql
Column policies One: blocked = section One: blocked = client_ip
Row policies None One: cciso = 'FR'

Effective access, after combination

Description
Permissions table scoped select_sql
Blocked columns None
Combined row filter (cciso = 'FR') 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
250

1
2
GET /query query=="SELECT COUNT(client_ip), COUNT(section) FROM news.requests"
250     250

Row filters and an empty column policy⚓︎

This example demonstrates a permissive final row filter expression and a column policy blocking a sensitive column, client_ip.

  • The absence of a column policy on nr_analyst_sp means that the column policy from nr_sports_sp applies. See combining column policies.
  • The row filter True in the nr_analyst_sp grants access to all rows, regardless of other row policies. See combining row policies.
Role Role
Account roles nr_sports_sp nr_analyst_sp
Permissions table-scoped select_sql table-scoped select_sql
Column policies One: blocked = client_ip None
Row policies One: section = 'Sports' One: True

Effective access, after combination

Description
Permissions broadly scoped select_sql, etc.
Blocked columns client_ip
Combined row filter (section = 'Sports') or (True) 
1
2
GET /query query=="SELECT COUNT() FROM news.requests"
1000

1
2
3
4
5
GET /query query=="SELECT COUNT(client_ip) FROM news.requests"
{
    "error": "Code: 497. DB::Exception: username@example.com: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(client_ip) ON news.requests. (ACCESS_DENIED)",
    "query": "SELECT COUNT(client_ip) FROM news.requests"
}

Policy data⚓︎

API response fragments⚓︎

Column policies for news.requests⚓︎

Column policies 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
GET /config/v1/orgs/${HDX_ORGID}/projects/${HDX_PROJECTID}/tables/${HDX_TABLEID}/columnpolicies/
{
  "next": 0,
  "previous": 0,
  "current": 1,
  "num_pages": 1,
  "count": 2,
  "results": [
    {
      "uuid": "3fa4bb50-ed53-4482-be36-be0d61cee5d9",
      "created": "2026-02-12T16:16:26.833044Z",
      "modified": "2026-02-12T16:16:26.833064Z",
      "roles": [
        "nr_fr_sp",
        "nr_sports",
        "nr_sports_sp"
      ],
      "table": "news.requests",
      "name": "cp_nr_no_client_ip",
      "blocked_columns": [
        "client_ip"
      ]
    },
    {
      "uuid": "b2a1828d-780d-4a66-83b3-c5bd9f0868c6",
      "created": "2026-02-10T20:51:15.994563Z",
      "modified": "2026-02-10T21:04:49.159158Z",
      "roles": [
        "nr_netops_sp"
      ],
      "table": "news.requests",
      "name": "cp_nr_no_section",
      "blocked_columns": [
        "section"
      ]
    }
  ]
}

Row policies for news.requests⚓︎

Row policies 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
GET /config/v1/orgs/${HDX_ORGID}/projects/${HDX_PROJECTID}/tables/${HDX_TABLEID}/rowpolicies/
{
  "next": 0,
  "previous": 0,
  "current": 1,
  "num_pages": 1,
  "count": 3,
  "results": [
    {
      "uuid": "dd868a7c-4522-4aab-ba67-855e38003f42",
      "created": "2026-02-10T20:47:11.519581Z",
      "modified": "2026-02-10T20:59:12.954422Z",
      "roles": [
        "nr_sports",
        "nr_sports_sp"
      ],
      "table": "news.requests",
      "name": "rp_nr_sports",
      "filter": "section = 'Sports'",
      "restrictive": false
    },
    {
      "uuid": "a2776383-fa20-424d-aff2-92bd51ef70b2",
      "created": "2026-02-10T20:47:44.963904Z",
      "modified": "2026-02-10T20:59:21.155375Z",
      "roles": [
        "nr_fr_sp"
      ],
      "table": "news.requests",
      "name": "rp_nr_fr",
      "filter": "cciso = 'FR'",
      "restrictive": false
    },
    {
      "uuid": "1129bbc9-2207-49ad-8db5-a9d512f2fb9d",
      "created": "2026-02-10T20:48:38.476625Z",
      "modified": "2026-02-10T21:27:06.914961Z",
      "roles": [
        "nr_analyst_sp"
      ],
      "table": "news.requests",
      "name": "rp_nr_unfiltered",
      "filter": "True",
      "restrictive": false
    }
  ]
}

Role nr_sports⚓︎

Role nr_sports

Observe the absence of any permissions at all in the policies attribute of this role. This role is empty.

1
2
3
4
5
6
GET /config/v1/roles/21/
{
    "id": 21,
    "name": "nr_sports",
    "policies": []
}

Role nr_sports_sp⚓︎

Role nr_sports_sp

This role includes table-scoped permissions allowing select_sql for table news.requests.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
GET /config/v1/roles/39/
{
    "id": 39,
    "name": "nr_sports_sp",
    "policies": [
        {
            "permissions": [
                "select_sql"
            ],
            "scope_deployment_id": null,
            "scope_id": "bc6f6d63-a284-4685-a5cf-0544cccf6437",
            "scope_name": "requests",
            "scope_type": "table"
        }
    ]
}

Role nr_fr_sp⚓︎

Role nr_fr_sp

This role includes table-scoped permissions allowing select_sql for table news.requests.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
GET /config/v1/roles/40/
{
    "id": 40,
    "name": "nr_fr_sp",
    "policies": [
        {
            "permissions": [
                "select_sql"
            ],
            "scope_deployment_id": null,
            "scope_id": "bc6f6d63-a284-4685-a5cf-0544cccf6437",
            "scope_name": "requests",
            "scope_type": "table"
        }
    ]
}

Role nr_netops_sp⚓︎

Role nr_netops_sp

This role includes table-scoped permissions allowing select_sql for table news.requests.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
GET /config/v1/roles/41/
{
    "id": 41,
    "name": "nr_netops_sp",
    "policies": [
        {
            "permissions": [
                "select_sql"
            ],
            "scope_deployment_id": null,
            "scope_id": "bc6f6d63-a284-4685-a5cf-0544cccf6437",
            "scope_name": "requests",
            "scope_type": "table"
        }
    ]
}

Role nr_analyst_sp⚓︎

Role nr_analyst_sp

This role includes table-scoped permissions allowing select_sql for table news.requests.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
GET /config/v1/roles/42/
{
    "id": 42,
    "name": "nr_analyst_sp",
    "policies": [
        {
            "permissions": [
                "select_sql"
            ],
            "scope_deployment_id": null,
            "scope_id": "bc6f6d63-a284-4685-a5cf-0544cccf6437",
            "scope_name": "requests",
            "scope_type": "table"
        }
    ]
}

Sample table data⚓︎

Example COUNT() queries for complete access to news.requests

The counts in the queries below match examples of policy combinations illustrated elsewhere in this document.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# All rows visible
mysql> SELECT COUNT() FROM news.requests;
1000

# Should match count when applying this single row filter
mysql> SELECT COUNT() FROM news.requests WHERE section = 'Sports'
252

# Should match count when applying this single row filter
mysql> SELECT COUNT() FROM news.requests WHERE cciso = 'FR'
250

# Should match count when applying non-restrictive row filters
mysql> SELECT COUNT() FROM news.requests WHERE section = 'Sports' OR cciso = 'FR'
439

# Should match count when applying restrictive row filters
mysql> SELECT COUNT() FROM news.requests WHERE section = 'Sports' AND cciso = 'FR'
63