Skip to main content

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([督导id],[CURRENT_USER.督导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([类别],','),concat([CURRENT_USER.类别],','))>0

SQL Server Direct Connection

-- Syntax 1: Use this when the [CURRENT_USER.门店] field lengths differ:
CHARINDEX(','+[shopping_area]+',' , ','+[CURRENT_USER.所在省份]+',') > 0

-- Syntax 2: If [CURRENT_USER.工号] 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.工号]+',')>0

PostgreSQL Direct Connection

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

-- Syntax 2 (recommended):
cast([门店id] as varchar) = ANY(string_to_array([CURRENT_USER.门店],’,’))

ClickHouse Direct Connection

-- Syntax 1 (for datasets without null values):
has(splitByChar(',',[CURRENT_USER.所在地区1]),[省份])>0

-- Syntax 2 (recommended):
has(cast(splitByChar(',',[CURRENT_USER.城市]),'Array(Nullable(String))'),[城市])>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.所在地区1]), 'Array(Nullable(String))') ,[省份])>0
  1. 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))'),[人员id])>0

Oracle Direct Connection

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

TiDB Direct Connection

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

Greenplum Direct Connection

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

Doris Database Direct Connection

find_in_set([机构大区],[CURRENT_USER.机构大区])>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.城市]||',' , ','||[城市]||',') > 0

Impala Direct Connection

Example dataset field "组织" range: 上海观远数据, 观远数据, 杭州观远数据;
User attribute "所属组织" selected value: 杭州观远数据
-- Syntax 1: Fuzzy matching may occur
instr([CURRENT_USER_USER:所属组织],[组织])>0
/*
Returned matching results: 观远数据, 杭州观远数据
*/
-- Syntax 2: Exact matching
instr(','||[CURRENT_USER_USER:所属组织]||',',','||[组织]||',')>0
/*
Returned matching result: 杭州观远数据
*/
-- Syntax 3: Exact matching
find_in_set([组织],[CURRENT_USER_USER:所属组织])>0
/*
Returned matching result: 杭州观远数据
*/