Skip to main content

Row Permission Syntax for Each Direct Connection Database

Notes

From version 5.5, in row/column permission condition mode, a new "in(user attribute)" is added. Single or multiple user attributes can be directly matched with dataset fields. It is recommended to use "in(user attribute)" in condition mode first. Use free mode only if condition mode cannot meet the requirements. The following database function examples are for reference in free mode only.

1. MySQL Direct Connection

FIND_IN_SET([Supervisor id],[CURRENT_USER.Supervisor ID])>0

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

2. HANA Direct Connection

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

3. SQL Server Direct Connection

-- Method 1: If [CURRENT_USER.Store] field length varies:
CHARINDEX(','+[shopping_area]+',' , ','+[CURRENT_USER.Province]+',') > 0

-- Method 2: If [CURRENT_USER.Employee ID] field length is fixed:
CHARINDEX(','+[Member_Id]+',', ','+[CURRENT_USER.Employee ID]+',')>0

4. PostgreSQL Direct Connection

-- Method 1:
position([dept_name] in [CURRENT_USER.deptcode]) > 0

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

5. Clickhouse Direct Connection

-- Method 1 (for datasets without nulls):
has(splitByChar(',',[CURRENT_USER.Region1]),[Province])>0

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

[Note] Clickhouse is strict with data types, and type mismatches are common.

  1. If the referenced dataset field has nulls, you may get an error: 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)

This means the array is String, but the argument is Nullable(String). To fix, convert the user attribute to Nullable(String):

has(cast(splitByChar(',',[CURRENT_USER.Region1]), 'Array(Nullable(String))') ,[Province])>0
  1. If the dataset field is not string (e.g., personnel id is int), you may get an error. Change the permission to ensure matching data types:
has(cast(splitByChar(',',[CURRENT_USER.id]),'Array(Nullable(Int32))'),[Personnel id])>0

6. Oracle Direct Connection

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

7. TiDB Direct Connection

FIND_IN_SET([Supervisor id],[CURRENT_USER.Supervisor ID])>0

8. Greenplum Direct Connection

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

9. Doris Database Direct Connection

find_in_set([Org Region],[CURRENT_USER.Org Region])>0

[Note] For Oracle, using "> 0" may cause fuzzy matching. For exact match, add special characters before and after, e.g.:

instr(','||[CURRENT_USER.City]||',' , ','||[City]||',') > 0

10. Impala Direct Connection

Example: Dataset "Organization" field: Shanghai Guandata, Guandata, Hangzhou Guandata;
User attribute "Organization": Hangzhou Guandata
-- Method 1: Fuzzy match
instr([CURRENT_USER_USER:Organization],[Organization])>0
/*
Matches: Guandata, Hangzhou Guandata
*/
-- Method 2: Exact match
instr(','||[CURRENT_USER_USER:Organization]||',',','||[Organization]||',')>0
/*
Matches: Hangzhou Guandata
*/
-- Method 3: Exact match
find_in_set([Organization],[CURRENT_USER_USER:Organization])>0
/*
Matches: Hangzhou Guandata
*/