Dataset Row Permission Use Cases

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

Business Scenario

For scenarios similar to store management, where there are many users and clear user levels, such as store manager, city supervisor, and city manager, all three user types can view only the performance of the stores they manage. One person may manage multiple stores.

Solution

1. Configure user attributes: Add the Store attribute in user attributes and enter the store code corresponding to the user. If one person manages multiple stores, enter multiple store codes separated by a delimiter such as a comma.

2. Configure row permissions: On the dataset Data Security page, set row permissions. In condition mode, add a condition, select in(user attribute), and configure it through the UI. This feature is available in version 5.5 and later.

3. For versions earlier than 5.5, use free mode to configure permission conditions.

Row permission syntax for non-direct datasets:

array_contains(split( [CURRENT_USER.门店] ,','),[门店编码])

ClickHouse syntax:

[门店编码] in splitByChar(',',[CURRENT_USER.门店])

Notes

User attributes are in string format. If the dataset field is not string format, convert the field to string format before setting row permissions.
Prefer in(user attribute) in condition mode. Use free mode only when condition mode cannot meet the requirement.
For syntax in other databases, see Row Permission Syntax for Direct Databases.

Case 2: Multi-value User Attributes + Logical Judgment + Fuzzy Matching

Business Scenario

User attributes include region and city. For headquarters employees, the region attribute has one or more values, such as East China, while city is empty. They can view all data in their region. For branch employees, region is empty, while city may have one or more values, such as Shanghai and Hangzhou. They can view only data for the cities they manage. Headquarters and branch employees belong to the same large user group.

For a non-direct, non-accelerated dataset, fields include region, province, and city. Region can be matched exactly, but province and city use full names, such as Shanghai City, Hangzhou City, and Inner Mongolia Autonomous Region, which cannot be exactly matched with user attributes.

Row Permission Syntax

case when [CURRENT_USER.大区]<> '' then array_contains(split([CURRENT_USER.大区],','),[大区]) 
when [CURRENT_USER.大区]= '' and [CURRENT_USER.城市]<> '' then array_contains(split([CURRENT_USER.城市],','),regexp_replace([城市],'市|地区','')) or array_contains(split([CURRENT_USER.城市],','),replace([省份],'市')) 
else null end

Logic

-- Determine headquarters users and match the "Region" field
case when [CURRENT_USER.大区]<> '' then array_contains(split([CURRENT_USER.大区],','),[大区])
-- Determine branch users
when [CURRENT_USER.大区]= '' and [CURRENT_USER.城市]<> ''
-- Remove suffixes such as "市" or "地区" from the city field before matching. If some city names contain other suffixes such as "自治州", nest the replace function or use the regexp_replace regular expression to remove the suffix.
then array_contains(split([CURRENT_USER.城市],','),regexp_replace([城市],'市|地区',''))
-- If a municipality city name is in the province field, match the "Province" field.
or array_contains(split([CURRENT_USER.城市],','),replace([省份],’市’))

Notes

If the user attribute is a single string value, you can directly use exact matching such as [CURRENT_USER.大区]=[大区], or fuzzy matching such as instr([城市],[CURRENT_USER.城市])>0.
For multiple values, use the array_contains(split()) function to match values one by one. This also works for single values. However, arrays do not support fuzzy matching and cannot use like, so dataset fields need to be processed. If the position and length are the same, use substr instead of replace, such as array_contains(split([CURRENT_USER.城市],','),substr([城市],1,2)).

ClickHouse Syntax

case when [CURRENT_USER.大区]<> '' then has(splitByChar(',',[CURRENT_USER.大区]),[大区]) 
when [CURRENT_USER.大区]='' and [CURRENT_USER.城市]<>''then has(splitByChar(',',[CURRENT_USER.城市]),replaceRegexpOne([城市],'市|地区','')) or has(splitByChar(',',[CURRENT_USER.城市]),replaceOne([省份],'市','')) 
else null end

Final effect:

Example: User attribute City: Shanghai, Hangzhou, Kashgar

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

Business Scenario

The user attribute city is multi-value. Each user has at least one city. In general, users can view data for all cities in their attributes. However, some datasets allow users to view only data for the city where they are located. The located city is the first value in the city attribute, so dataset fields need to match only the first city.
Using the same dataset as Case 1, for a non-direct, non-accelerated dataset, province and city are full names, such as Shanghai City, Hangzhou City, and Inner Mongolia Autonomous Region, which cannot be exactly matched with user attributes. Common city names need suffixes such as "市" and "地区" removed. Municipalities need "市" removed before matching with province.

Row Permission Syntax 1

array_position(split([CURRENT_USER.城市],','),regexp_replace([城市],'市|地区',''))=1 or array_position(split([CURRENT_USER.城市],','),replace([省份],'市'))=1

Logic:

Use split([CURRENT_USER.城市],',') to split cities into an array.
array_position()=1 ensures that the first element in the array is extracted. This also applies when the user attribute is a single value.

Row Permission Syntax 2

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

Logic:

Process the user attribute City as a string.
Use case when to determine whether it is a single value or multiple values. For multiple values, extract the city name before the first delimiter. For a single value, directly match city/province.

ClickHouse Syntax

arrayElement(splitByChar(',',[CURRENT_USER.城市]),1) = replaceOne([省份],'市','') or arrayElement(splitByChar(',',[CURRENT_USER.城市]),1) =replaceRegexpOne([城市],'市|地区','')

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

Final effect:

Example: User attribute City: Shanghai, Hangzhou, Kashgar

Case 4: Allow Specific Users to View Only Data from the Last 180 Days

Business Scenario

You want to add a permission for a group of BI users so that they can view only data from the last 180 days, or half a year.

Row Permission Syntax

(current_date()-INTERVAL 180 day) <= [日期]

Logic

1. Use the current_date() function to get the current date. INTERVAL can add or subtract date and time. Here, (current_date()-INTERVAL 180 day) or (current_date()-INTERVAL 6 month) obtains the specific date 180 days, or 6 months, before the current date. Comparing it as less than or equal to the date data in the dataset controls visible data within 180 days.

Note

This method requires a date field in the dataset, and the dataset must stay updated daily to work properly.

2. When multiple rules need to take effect at the same time, connect them with and/or. For example:

array_contains(split([CURRENT_USER.属性A] ,','),[分组]) and (current_date()-INTERVAL 180 day)<=[日期]

Case 5: Dynamically Modify Global Parameters Through User Session

Business Scenario

A banking customer embeds the Guandata BI platform in a third-party system and has very granular control over data row permissions. The customer's third-party system already has its own permission configuration logic, which needs to take effect in BI. For the same BI dashboard, different users should see different data and only data they have permission to view. For the same user, when accessing different BI dashboards, the user should also see only data they have permission to view.

In addition, users in the third-party system have two roles, A and B. When a user logs in to the third-party system as role A, all data displayed should correspond to role A.

For example, when a user logs in as a first-level branch, they can view only data for their own first-level branch in Report A, but can view data for their own first-level branch and benchmark branches in Report B.

Operation Steps

Overall Description

Dataset row permission rules in Guandata BI support configuration through global parameters.
Administrators can generate uIdToken, or session, for general users to log in to and access the platform.
Administrators can configure global parameter values for uIdToken, or session, through APIs. Session configuration has the highest priority. Session configuration values can dynamically replace the default values of global parameters, enabling dynamic configuration of dataset row permissions for specified users.
When general users access the platform with a uIdToken whose permission rules have been configured by an administrator, data displayed in page cards for the corresponding dataset can be controlled by the administrator through specified rules in the session.
When a user saves a Smart ETL, the creator's user session information is saved to ETL metadata and used for permission judgment during scheduled runs. Editing and saving ETL, or transferring ownership, updates session information.

Case Practice

The dataset can control row permissions based on first-level branch and second-level branch fields. When a user logs in as a first-level branch to access a report, they can view data for permitted first-level branches. When a user logs in as a second-level branch to access a report, they can view data for permitted second-level branches. During ETL scheduled runs, permissions configured in the session at the time the ETL was saved are used.

Step 1: The administrator configures global parameters

Entry: Management Center > Resource Management > Global Parameters

Configure the global parameters: first-level branch number and second-level branch number.

Step 2: Configure dataset row permissions

Entry: Data Preparation > Dataset > Dataset Details > Data Security

Configure row permissions and select the in(global parameter) type in condition mode. When users log in with different identities, they can view only data rows corresponding to the first-level or second-level branches they have permission to access. The specific first-level or second-level branch data they can view is dynamically determined by the global parameter values.

Step 3: Build uIdToken and inject it into the browser cookie

When a user logs in as a first-level branch and accesses a report, they can view only data rows for first-level branches they have permission to access. The list of visible first-level branch numbers is determined by the global parameter values passed in the session.

When a user logs in as a second-level branch and accesses a report, they can view only data rows for second-level branches they have permission to access. The list of visible second-level branch numbers is determined by the global parameter values passed in the session.

1. Build ssoToken by referring to Guandata BI SSO Integration. This is implemented by the integrator backend.

Java code example

RSAUtil utility class

import org.apache.commons.codec.binary.Base64;

import javax.crypto.Cipher;
import java.io.ByteArrayOutputStream;
import java.security.*;
import java.security.interfaces.RSAPrivateKey;
import java.security.interfaces.RSAPublicKey;
import java.security.spec.InvalidKeySpecException;
import java.security.spec.PKCS8EncodedKeySpec;
import java.security.spec.X509EncodedKeySpec;
import java.util.HashMap;
import java.util.Map;
public class RSAUtil {
 public static final String CHARSET = "UTF-8";
 public static final String RSA_ALGORITHM = "RSA";
 public static final int KEY_SIZE = 1024;
 public static Map<String, String> createKeys() {
 // Create a KeyPairGenerator object for the RSA algorithm
 KeyPairGenerator kpg;
 try {
 kpg = KeyPairGenerator.getInstance(RSA_ALGORITHM);
 } catch (NoSuchAlgorithmException e) {
 throw new IllegalArgumentException("No such algorithm-->[" +
RSA_ALGORITHM + "]");
 }
 // Initialize the KeyPairGenerator object and key length
 kpg.initialize(KEY_SIZE);
 // Generate the key pair
 KeyPair keyPair = kpg.generateKeyPair();
 // Get the public key
 Key publicKey = keyPair.getPublic();
 String publicKeyStr = Base64.encodeBase64String(publicKey.getEnco
ded());
 // Get the private key
 Key privateKey = keyPair.getPrivate();
 String privateKeyStr = Base64.encodeBase64String(privateKey.getEn
coded());
 Map<String, String> keyPairMap = new HashMap<String, String>();
 keyPairMap.put("publicKey", publicKeyStr);
 keyPairMap.put("privateKey", privateKeyStr);
 return keyPairMap;
 }
 
 public static RSAPublicKey getPublicKey(String publicKey) throws NoSu
chAlgorithmException, InvalidKeySpecException {
 // Get the public key object through the X509-encoded key instruction
 KeyFactory keyFactory = KeyFactory.getInstance(RSA_ALGORITHM);
 X509EncodedKeySpec x509KeySpec = new X509EncodedKeySpec(Base64.de
codeBase64(publicKey));
 RSAPublicKey key = (RSAPublicKey) keyFactory.generatePublic(x509K
eySpec);
 return key;
 }
 public static RSAPrivateKey getPrivateKey(String privateKey) throws N
oSuchAlgorithmException, InvalidKeySpecException {
 // Get the private key object through the PKCS#8-encoded key instruction
 KeyFactory keyFactory = KeyFactory.getInstance(RSA_ALGORITHM);
 PKCS8EncodedKeySpec pkcs8KeySpec = new PKCS8EncodedKeySpec(Base6
4.decodeBase64(privateKey));
 RSAPrivateKey key = (RSAPrivateKey) keyFactory.generatePrivate(pk
cs8KeySpec);
 return key;
 }
 public static String privateEncrypt(String data, RSAPrivateKey privat
eKey) {
 try{
 Cipher cipher = Cipher.getInstance(RSA_ALGORITHM);
 cipher.init(Cipher.ENCRYPT_MODE, privateKey);
 return Base64.encodeBase64String(rsaSplitCodec(cipher, Ciphe
r.ENCRYPT_MODE, data.getBytes(CHARSET), privateKey.getModulus().bitLength
()));
 }catch(Exception e){
 throw new RuntimeException("An exception occurred while encrypting string [" + data + "]", e);
 }
 }
 public static String publicDecrypt(String data, RSAPublicKey publicKe
y) {
 try{
 Cipher cipher = Cipher.getInstance(RSA_ALGORITHM);
 cipher.init(Cipher.DECRYPT_MODE, publicKey);
 return new String(rsaSplitCodec(cipher, Cipher.DECRYPT_MODE,
Base64.decodeBase64(data), publicKey.getModulus().bitLength()), CHARSET);
 }catch(Exception e){
 throw new RuntimeException("An exception occurred while decrypting string [" + data + "]", e);
 }
 }
 private static byte[] rsaSplitCodec(Cipher cipher, int opmode, byte
[] datas, int keySize) {
 int maxBlock = 0;
 if(opmode == Cipher.DECRYPT_MODE){
 maxBlock = keySize / 8;
 }else{
 maxBlock = keySize / 8 - 11;
 }
 ByteArrayOutputStream out = new ByteArrayOutputStream();
 int offSet = 0;
 byte[] buff;
 int i = 0;
 try{
 while(datas.length > offSet){
 if(datas.length-offSet > maxBlock){
 buff = cipher.doFinal(datas, offSet, maxBlock);
 }else{
 buff = cipher.doFinal(datas, offSet, datas.length-off
Set);
 }
 out.write(buff, 0, buff.length);
 i++;
 offSet = i * maxBlock;
 }
 } catch(Exception e){
 e.getMessage();
 }
 byte[] resultDatas = out.toByteArray();
 try {
 out.close();
 } catch(Exception e){
 e.getMessage();
 }
 return resultDatas;
 }
 public static String toHexString(String s) {
 String str="";
 for (int i=0;i<s.length();i++)
 {
 int ch = s.charAt(i);
 str += Integer.toHexString(ch);
 }
 return str;
 }
}
	

Generate SSO Token

public class Demo {
 public static void main(String[] args) {
 String privateKey =
 "MIICdQIBADANBgkqhkiG9w0BAQEFAASCAl8wggJbAgEAAoGBAJPmp56tu
r+vOjpuke0xbId+GCmycImd/iIqJFb9b+tIJD2qgsYZHouhvEV2qorfyLs87eQYJGGf+CGHg83
DQG/SasFn46/NR+dAF33wvBhsq+lrqg7zW8ILy5cF33zHM3mCTqAUEPThRqmj28xkkwD5X2DVt
C5gLputWfRikZrDAgMBAAECgYAyhQ0ohItPwpkPMImkDcfWmFnElHEgcNlH7kEvfa5nHeNTNRU
qeZYXeA2JZLloanx0iKFx0lVLC4uEWHfLJzCw/KQ9OQM9bOLoPMuHRh70cBLaBgJepgD/I/hgW
GNKo+u61GQ0U0AbzYJU9fJ+yp4n53Gj84E6pOT71HEki++SAQJBAMdZXu37ONYgMtG4EOnnsQK
BLVHwZykwKH1szqkvfR3VlO/w0wWuKleQnh1DOoHK5Qhl4C4fBlBxp8y7/3i1OIECQQC97nBMk
lNr+oVXnkx2NgNLb5Ohjd85z1LH5b5QEQ2lIZ43wsUr+gFL6TP7bfOxvAbtUgWdtiiEemlOUfd
qw1FDAkA2HDcdR8y0qobA0EKfCwnMET44+JU349+JtAggekhu2bOUsXzGFPFfVVzluoLeCjHC5
sxEGJ3BJiiS9RCyNhaBAkA/8LiPnqdE77baM2mMVkyvpaVuuuNOg/RbZYW3ULZmRDYOkZxtXKH
5G04rs+1ZhXJTjMxlNsDXMJqpCkEgCRcfAkBJBU7Cy+p/HjBzTVLnlo8x+4io0OMjfu9BFJVqc
o2QCSmdZrW0ACiFoc5a5TJU7y+6pqw1GcM4am1vuAeR+qax";
 try {
 JSONObject plainData = new JSONObject();
 plainData.put("domainId", "demo");
 plainData.put("externalUserId", "hello@world.com");
 plainData.put("timestamp", new Date().getTime());
 String cipherData = RSAUtil.privateEncrypt(plainData.toJSONStr
ing(), RSAUtil.getPrivateKey(privateKey));
 System.out.println(RSAUtil.toHexString(cipherData));
 } catch (Exception ex){
 ex.printStackTrace();
 }
 }
}

The method result is an ssoToken string.

2. Generate uIdToken by calling the /backend/sso/sign-in API based on ssoToken. This is implemented by the integrator backend.

The following is a curl or Postman request example. The specific implementation must be completed by the integrator backend.

curl --location --request POST 'https://bi-address.com/backend/sso/sign-i
n' \
--header 'User-Agent: Apifox/1.0.0 (https://apifox.com)' \
--data-raw '{
"provider":"demo",
"info":{
 "ssoToken":
 "65324869576b62476e6f75374b43386f485a41554762565765753358305030304a
2f727866697732695646524b50727749697a6d6a7444337772696a55326f705575424c50343
63254634f3067436e6c345a6f39714356585362373631326c56616c6a2f7550554245327255
304b4b4d384a414d6f2b7954426976547757796b79596c674b6a4c385151697364767349644
86846424f6830774a784738425459573055372f496f427a6e6f3d"
}
}'

Postman request example:

The token returned by the API request is the uIdToken string.

3. Configure parameters for uIdToken. This is implemented by the integrator backend. Public API definition for session creation or update
Route: POST

/public-api/session/createOrUpdate

Request parameters:

Parameter	Value Description	Location	Type	Required	Remarks
token	Application token	body	String	Yes	Application token, used for authentication
uIdToken	uIdToken	body	String	Yes	uIdToken used to generate the session
bizType	Business type	body	String	Yes	GLOBAL_PARAMETER
data	Session data	body	Json Array	Yes	Configuration data corresponding to the session

token is the request credential for calling Guandata BI public APIs. Obtain it in Guandata BI under Management Center > System Integration > Unified Account Integration.

The uIdToken parameter is the uIdToken generated by the API in the previous step. The value of data is a JSON array of data objects.

Parameter	Value Description	Location	Type	Required	Remarks
dsId	Dataset ID	body	String	No	Dataset ID that needs data row permissions
dpId	Global parameter ID	body	String	No	Global parameter ID to be dynamically replaced
dpName	Global parameter name	body	String	No	Global parameter name to be dynamically replaced
value	Global parameter replacement value	body	String	Yes	Global parameter value to be dynamically replaced

The data value example is as follows. Choose either dpId or dpName. Passing dpId is recommended.

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

The following is a curl or Postman request example. The specific implementation must be completed by the integrator backend.
curl request example:

curl --location --request POST 'http://bi-server:9000/public-api/session/createOrUpdate' \
--header 'User-Agent: Apifox/1.0.0 (https://apifox.com)' \
--header 'Content-Type: application/json' \
--data-raw '{
    "token": "h96e55f6ad3144d428d592b7",
    "uIdToken": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJkZXZpY2VUeXBlIjoiUEMiLCJzdWIiOiIxLWpUUktIVSsxNlNTdlVYdmNsMkpEZFJEZmxPV0VON01Ob3pZTzc3WXdzZ0VzcHB5YmY4Vm9LR2poaEI1SHlXODh4QXZiK0dTMnZVTGFrTnFLaDlaMHF0Qy9HSnNIOU1Uam42OWVibUpjUlFzQTNSNXciLCJhdXRvTG9nb3V0T25DbG9zZUVuYWJsZWQiOmZhbHNlLCJpc3MiOiJndWFuZGF0YS5jb20iLCJleHAiOjE3Mjk5MDYxNzYsImlhdCI6MTcyODY5NjU3OCwiaW5pdFRpbWUiOiIyMDI0LTEwLTEyIDA5OjI5OjM2LjQwNiIsImp0aSI6IjNhODY5YjY0MzI0MGRmMzUzOTQzZGUwZmY5ZmUzMjk1OWIxYTY0NmNmYmIxNDI3NjI5YTU0NjAzOTk5OGI3ODRmZjVlYjFmMWVmMmJiZjViNDNmNmY0Y2ZjOGIwNTY4NzMzMTY0ODkwMGI0ZjZhMjJlN2MzNTYwMTA5NWY5ZjBhNmJiOTJiNjU1ZjAyYzM4YjYwZWU5YjdiOGZlMjA3YjdjM2MwMDRiYTMzZTgwYjNkYjQxZDNiNzBiMzZlMDJjYWU2OTA1MTI0NGQ1OWM2ZTY1OGY4YjYxZTA5NGNlZDJiOGFhYzBhODU4OTg1NDJhMmQ2NGIwYjQ4NTc2ZDNjYzIiLCJwd2RWZXJzaW9uIjowLjB9.oBIv_VDHbJaeHcLBRW83H6_Gj4hedE2356RvUqCMW6Q",
    "bizType": "GLOBAL_PARAMETER",
    "data": [
        {
        "dsId": "k5652f88f33a4494abb7c97b",
        "dpId": "w0288f3ba99274e92b71891a",
        "value": "12345"
        }
    ]
}'

Postman request example:

After the API call succeeds, session configuration is complete. This API supports multiple calls. The first call creates session configuration, and later calls update session configuration.

Parameter priority: Values passed through session have the highest priority, higher than values configured on the card or dataset.

Global parameter values passed through session take effect wherever global parameters are referenced in BI, including scenarios such as view dataset model structures.

4. Inject uIdToken into the browser Cookie. This is implemented by the integrator frontend.
In the embedded system, the integrator frontend adds the parameter ?loginToken={uIdToken} after the BI page link to be accessed. This applies the session configuration. Embedded link example: http://domain/xxx?loginToken={uIdToken}

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

Business Scenario​

Solution​

Notes​

Case 2: Multi-value User Attributes + Logical Judgment + Fuzzy Matching​

Business Scenario​

Row Permission Syntax​

Logic​

Notes​

ClickHouse Syntax​

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

Business Scenario​

Row Permission Syntax 1​

Row Permission Syntax 2​

ClickHouse Syntax​

Case 4: Allow Specific Users to View Only Data from the Last 180 Days​

Business Scenario​

Row Permission Syntax​

Logic​

Case 5: Dynamically Modify Global Parameters Through User Session​

Business Scenario​

Operation Steps​

Overall Description​

Case Practice​

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

Business Scenario

Solution

Notes

Case 2: Multi-value User Attributes + Logical Judgment + Fuzzy Matching

Business Scenario

Row Permission Syntax

Logic

Notes

ClickHouse Syntax

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

Business Scenario

Row Permission Syntax 1

Row Permission Syntax 2

ClickHouse Syntax

Case 4: Allow Specific Users to View Only Data from the Last 180 Days

Business Scenario

Row Permission Syntax

Logic

Case 5: Dynamically Modify Global Parameters Through User Session

Business Scenario

Operation Steps

Overall Description

Case Practice