Row Permission Use Case Sharing

Case 1: "One Person, Multiple Stores" - Multi-value User Attribute Matching

Business Scenario

For scenarios like store managers, where there are many users and clear user hierarchies: store manager - city supervisor - city manager, all three types of users can only view the performance of the stores they manage, and one person may manage multiple stores.

Solution

Configure user attributes: Add the "Store" attribute in user attributes, and fill in the store codes for each user. If one person manages multiple stores, fill in multiple store codes separated by a delimiter (such as a comma).
Row permission configuration: In the dataset "Data Security" interface, set row permissions. In condition mode, add a condition, select "in(user attribute)", and configure via the UI (available in version 5.5 and above).

For versions before 5.5, use free mode to configure permission conditions.

Non-direct connection dataset row permission syntax:

array_contains(split([CURRENT_USER.Store],','),[Store Code])

Clickhouse syntax:

[Store Code] in splitByChar(',',[CURRENT_USER.Store])

Notes

User attributes are string type. If the dataset field is not string, it is recommended to convert the field to string before setting row permissions.
Prefer using "in(user attribute)" in condition mode; use free mode only if condition mode cannot meet the requirements.
For other database syntax, refer to: [Row Permission Syntax for Each Direct Connection Database](2-Row Permission Syntax for Each Direct Connection Database.md).

Case 2: Multi-value User Attribute + Logic Judgment + Fuzzy Matching

Business Scenario

User attributes include region and city. Headquarters employees have values in the region attribute (one or more, e.g., East China), and city is empty; they can view all data in their region. Subsidiary employees have an empty region and one or more cities (e.g., Shanghai, Hangzhou); they can only view data for their managed cities. Both types of employees belong to the same user group.

For non-direct (non-accelerated) datasets, fields include region, province, and city. Region can be matched exactly, but province and city are full names (e.g., Shanghai City, Hangzhou City, Inner Mongolia Autonomous Region), which cannot be exactly matched with user attributes.

Row Permission Syntax

case when [CURRENT_USER.Region]<> '' then array_contains(split([CURRENT_USER.Region],','),[Region]) 
when [CURRENT_USER.Region]= '' and [CURRENT_USER.City]<> '' then array_contains(split([CURRENT_USER.City],','),regexp_replace([City],'市|地区','')) or array_contains(split([CURRENT_USER.City],','),replace([Province],'市')) 
else null end

Logic Explanation

-- For headquarters users, match the "Region" field
case when [CURRENT_USER.Region]<> '' then array_contains(split([CURRENT_USER.Region],','),[Region])
-- For subsidiary users
when [CURRENT_USER.Region]= '' and [CURRENT_USER.City]<> ''
-- Remove suffixes like "市" or "地区" from the city field before matching. For cities with other suffixes (e.g., "Autonomous Prefecture"), use replace or regexp_replace to remove the suffix.
then array_contains(split([CURRENT_USER.City],','),regexp_replace([City],'市|地区',''))
-- For direct-controlled cities in the province field, match "Province"
or array_contains(split([CURRENT_USER.City],','),replace([Province],'市'))

Notes

If the user attribute is a single string value, you can use [CURRENT_USER.Region]=[Region] for exact match, or instr([City],[CURRENT_USER.City])>0 for fuzzy match.
For multiple values, use array_contains(split()) to match each value (also works for single values). Arrays do not support fuzzy matching, so process the dataset field as needed. If position and length are the same, use substr, e.g., array_contains(split([CURRENT_USER.City],','),substr([City],1,2)).

Clickhouse Syntax

case when [CURRENT_USER.Region]<> '' then has(splitByChar(',',[CURRENT_USER.Region]),[Region]) 
when [CURRENT_USER.Region]='' and [CURRENT_USER.City]<>''then has(splitByChar(',',[CURRENT_USER.City]),replaceRegexpOne([City],'市|地区','')) or has(splitByChar(',',[CURRENT_USER.City]),replaceOne([Province],'市','')) 
else null end

Final Effect:

Example: User attribute "City": Shanghai, Hangzhou, Kashgar

Case 3: Multi-value User Attribute + Match Only First Value + Fuzzy Matching

Business Scenario

User attribute "City" is multi-value; each user has at least one city. Normally, users can view data for all cities in their attribute. However, some datasets only allow users to view data for their location city, which is the first value in the city attribute.

For the same dataset as Case 1, non-direct (non-accelerated) dataset, province and city are full names (e.g., Shanghai City, Hangzhou City, Inner Mongolia Autonomous Region), which cannot be exactly matched with user attributes. Ordinary city names need to remove suffixes like "市" or "地区"; direct-controlled cities need to remove "市" and match with province.

Row Permission Syntax 1

array_position(split([CURRENT_USER.City],','),regexp_replace([City],'市|地区',''))=1 or array_position(split([CURRENT_USER.City],','),replace([Province],'市'))=1

Logic:

Use split([CURRENT_USER.City],',') to split the city into an array.
array_position()=1 ensures the first element is extracted, also works for single values.

Row Permission Syntax 2

case when INSTR([CURRENT_USER.City],',')>1 then SUBSTR([CURRENT_USER.City],0,INSTR([CURRENT_USER.City],',')-1) in (regexp_replace([City],'市|地区',''),replace([Province],'市')) 
else [CURRENT_USER.City] in (regexp_replace([City],'市|地区',''),replace([Province],'市')) end

Logic:

Treat the user attribute "City" as a string.
Use case when to determine single or multiple values. For multiple, extract the city name before the first delimiter; for single, match city/province directly.

Clickhouse Syntax

arrayElement(splitByChar(',',[CURRENT_USER.City]),1) = replaceOne([Province],'市','') or arrayElement(splitByChar(',',[CURRENT_USER.City]),1) =replaceRegexpOne([City],'市|地区','')

case when position([CURRENT_USER.City],',')>1 then substring([CURRENT_USER.City],1,position([CURRENT_USER.City],',')-1) = replaceRegexpOne([City],'市|地区','') or substring([CURRENT_USER.City],1,position([CURRENT_USER.City],',')-1) = replaceOne([Province],'市','') 
else [CURRENT_USER.City]= replaceRegexpOne([City],'市|地区','') or [CURRENT_USER.City]= replaceOne([Province],'市','') end

Final Effect:

Example: User attribute "City": Shanghai, Hangzhou, Kashgar

Case 4: Only Allow Specific Users to View Data Within 180 Days

Business Scenario

A group of BI users should only be allowed to view data from the last 180 days (or half a year).

Row Permission Syntax

(current_date()-INTERVAL 180 day) <= [Date]

Logic Explanation

Use current_date() to get the current date. INTERVAL can add or subtract date/time. (current_date()-INTERVAL 180 day) or (current_date()-INTERVAL 6 month) gets the date 180 days (or 6 months) ago. Compare with the dataset's date field to restrict data to the last 180 days.

Note: This requires a date field in the dataset and daily updates for proper use.

For multiple rules, use and/or to combine. For example:

array_contains(split([CURRENT_USER.AttributeA] ,','),[Group]) and (current_date()-INTERVAL 180 day)<=[Date]

Case 5: Dynamically Modify Global Parameters via User Session

Business Scenario

A bank customer has embedded the Guandata BI platform in a third-party system, with fine-grained row permission control. The third-party system already has its own permission logic, which needs to take effect in BI. For the same BI dashboard, different users should see only the data they have permission for; for the same user, different dashboards should show only the data they have permission for.

In addition, users in the third-party system have roles A and B. When logging in as role A, the user should see only data for role A.

For example, when a user logs in as a primary branch, they can only see data for that branch in report A, but can see data for both the primary branch and its peer branches in report B.

Steps

Overview

Guandata BI dataset row permission rules support configuration with global parameters
Admins can generate uIdToken (session) for regular users for platform login
Admins can configure global parameter values for uIdToken (session) via API. Session config has the highest priority and can dynamically replace global parameter default values for dynamic row permission configuration
When regular users access the platform with the configured uIdToken, the dataset card data display is controlled by the session rules
When saving in Smart ETL, the creator's user session info is saved in ETL metadata, and permission is checked during scheduled runs. Editing/saving or transferring ownership updates session info

Case Practice

The dataset can control row permissions by primary and secondary branch fields. When a user logs in as a primary branch, they can view data for their authorized primary branch; as a secondary branch, they can view data for their authorized secondary branch. When ETL runs on schedule, it uses the session config at ETL save time.

Step 1: Admin configures global parameters

Path: Management Center > Resource Management > Global Parameters

Configure global parameters for primary and secondary branch numbers.

Step 2: Configure dataset row permissions

Path: Data Preparation > Dataset > Dataset Details > Data Security

Configure row permissions, select in(global parameter) in condition mode. When users log in with different identities, they can only view data for their authorized primary/secondary branch, determined by the global parameter value in the session.

Step 3: Build uIdToken and set it in browser cookie

When a user logs in as a primary branch, they can only view data for their authorized primary branch, determined by the global parameter value in the session.

When a user logs in as a secondary branch, they can only view data for their authorized secondary branch, determined by the global parameter value in the session.

1) Refer to Guandata BI-SSO integration documentation to build ssoToken [backend implementation]

0-Guandata BI-SSO Integration

Java code example

RSAUtil utility class

// ... (Java code unchanged, omitted for brevity)

Generate SSO Token

// ... (Java code unchanged, omitted for brevity)

The result is an ssoToken string

2) Use ssoToken to call /backend/sso/sign-in API to generate uIdToken [backend implementation]

Example curl or postman request (backend implementation required)

// ... (curl example unchanged, omitted for brevity)

Postman example:

The API response token is the uIdToken string.

3) Configure parameters for uIdToken [backend implementation] Session create/update public API definition
Route: POST

/public-api/session/createOrUpdate

Request parameters:

Parameter	Description	Location	Type	Required	Note
token	App token	body	String	Yes	App token for authentication
uIdToken	uIdToken	body	String	Yes	uIdToken for session creation
bizType	Business type	body	String	Yes	GLOBAL_PARAMETER
data	Session data	body	Json Array	Yes	Session config data

token is the credential for Guandata BI public API, available at Management Center > System Integration > Unified Account Integration

uIdToken is generated by the previous API; data is a Json Array of objects

Parameter	Description	Location	Type	Required	Note
dsId	Dataset ID	body	String	No	Dataset ID for row permission
dpId	Global parameter ID	body	String	No	Global parameter ID to replace
dpName	Global parameter name	body	String	No	Global parameter name to replace
value	Replacement value	body	String	Yes	Value to replace global parameter

data example (dpId and dpName, choose one; dpId recommended):

[
        {
        "dsId": "k5652f88f33a4494abb7c97b",
        "dpId": "w0288f3ba99274e92b71891a",
        "dpName": "testDataset",
        "value": "12345"
        }
    ]

Example curl or postman request (backend implementation required):

// ... (curl example unchanged, omitted for brevity)

Postman example:

After a successful call, the session is configured. The API supports multiple calls; the first creates the session, subsequent calls update it.

Priority: session config > card config > dataset config.

The global parameter value in the session takes effect everywhere in BI, including view dataset model structure, etc.

4) Set uIdToken in browser cookie [frontend implementation]
In the embedded system, add ?loginToken={uIdToken} to the BI page link to apply the session config. Example: http://domain/xxx?loginToken={uIdToken}

Case 1: "One Person, Multiple Stores" - Multi-value User Attribute Matching​

Business Scenario​

Solution​

Notes​

Case 2: Multi-value User Attribute + Logic Judgment + Fuzzy Matching​

Business Scenario​

Row Permission Syntax​

Logic Explanation​

Notes​

Clickhouse Syntax​

Case 3: Multi-value User Attribute + Match Only First Value + Fuzzy Matching​

Business Scenario​

Row Permission Syntax 1​

Row Permission Syntax 2​

Clickhouse Syntax​

Case 4: Only Allow Specific Users to View Data Within 180 Days​

Business Scenario​

Row Permission Syntax​

Logic Explanation​

Case 5: Dynamically Modify Global Parameters via User Session​

Business Scenario​

Steps​

Overview​

Case Practice​

Case 1: "One Person, Multiple Stores" - Multi-value User Attribute Matching

Business Scenario

Solution

Notes

Case 2: Multi-value User Attribute + Logic Judgment + Fuzzy Matching

Business Scenario

Row Permission Syntax

Logic Explanation

Notes

Clickhouse Syntax

Case 3: Multi-value User Attribute + Match Only First Value + Fuzzy Matching

Business Scenario

Row Permission Syntax 1

Row Permission Syntax 2

Clickhouse Syntax

Case 4: Only Allow Specific Users to View Data Within 180 Days

Business Scenario

Row Permission Syntax

Logic Explanation

Case 5: Dynamically Modify Global Parameters via User Session

Business Scenario

Steps

Overview

Case Practice