Row Permission Syntax for Direct Databases

Notes

Starting from version 5.5, condition mode for row and column permissions adds in(user attribute). Single-value or multi-value user attributes can be directly matched with dataset fields. Prefer in(user attribute) in condition mode. Use free mode only when condition mode cannot meet the requirement. The following database function syntax is for free mode reference only.

MySQL Direct Connection

FIND_IN_SET([Supervisor_ID],[CURRENT_USER.Supervisor_ID])>0

Function reference: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

HANA Direct Connection

locate(concat([Category],','),concat([CURRENT_USER.Category],','))>0

SQL Server Direct Connection

-- Syntax 1: Use this when the [CURRENT_USER.Store] field lengths differ:
CHARINDEX(','+[shopping_area]+',' , ','+[CURRENT_USER.Province]+',') > 0

-- Syntax 2: If [CURRENT_USER.Employee_ID] field lengths are equal, and cases such as Member_Id being 10, 100, or 1000 do not exist, use this simplified syntax:
CHARINDEX(','+[Member_Id]+',', ','+[CURRENT_USER.Employee_ID]+',')>0

PostgreSQL Direct Connection

-- Syntax 1:
position([dept_name] in [CURRENT_USER.DeptCode]) > 0

-- Syntax 2 (recommended):
cast([Store_ID] as varchar) = ANY(string_to_array([CURRENT_USER.Store],','))

ClickHouse Direct Connection

-- Syntax 1 (for datasets without null values):
has(splitByChar(',',[CURRENT_USER.Region_1]),[Province])>0

-- Syntax 2 (recommended):
has(cast(splitByChar(',',[CURRENT_USER.City]),'Array(Nullable(String))'),[City])>0

Note: ClickHouse checks data types strictly, so type mismatch issues can occur easily.

1. If the referenced dataset field contains null values, the following error may occur: Types of array and 2nd argument of function \"has\" must be identical up to nullability, cardinality, numeric types, or Enum and numeric type. Passed: Array(String) and Nullable(String)

The error message indicates that the array type is String, while the passed parameter is Nullable(String). In other words, the referenced dataset field contains null values, so the two types do not match. To solve this problem, convert the user attribute to Nullable(String) as well.

has(cast(splitByChar(',',[CURRENT_USER.Region_1]), 'Array(Nullable(String))') ,[Province])>0

2. If the dataset field used for row and column permissions is not string, such as an employee ID field of int type, an error may also occur. In that case, modify the row and column permission formula as follows to ensure the data formats are the same.

has(cast(splitByChar(',',[CURRENT_USER.ID]),'Array(Nullable(Int32))'),[Employee_ID])>0

Oracle Direct Connection

instr([CURRENT_USER.GID],[STORE_GID]) > 0

TiDB Direct Connection

FIND_IN_SET([Supervisor_ID],[CURRENT_USER.Supervisor_ID])>0

Greenplum Direct Connection

[order_id] in (select unnest(string_to_array([CURRENT_USER.order_id],',')))

Doris Database Direct Connection

find_in_set([Organization_Region],[CURRENT_USER.Organization_Region])>0

Note: Using Oracle direct connection as an example, writing directly as > 0 may cause fuzzy matching. If exact matching is required, add special separator characters before and after the values. For example: instr(','||[CURRENT_USER.City]||',' , ','||[City]||',') > 0

Impala Direct Connection

Example dataset field "Organization" range: Shanghai Guandata, Guandata, Hangzhou Guandata;
User attribute "Organization" selected value: Hangzhou Guandata
-- Syntax 1: Fuzzy matching may occur
instr([CURRENT_USER_USER:Organization],[Organization])>0
/*
Returned matching results: Guandata, Hangzhou Guandata
*/
-- Syntax 2: Exact matching
instr(','||[CURRENT_USER_USER:Organization]||',',','||[Organization]||',')>0
/*
Returned matching result: Hangzhou Guandata
*/
-- Syntax 3: Exact matching
find_in_set([Organization],[CURRENT_USER_USER:Organization])>0
/*
Returned matching result: Hangzhou Guandata
*/