外部権限テーブルで権限項目を設定

1. Application scenarios

The same report, different users login, see different data.

For example, people in Beijing branch can only see the data in Beijing, while people in Shanghai branch can only see the data in Shanghai.

2. Implementation ideas

The external permission table is introduced to filter the data permission directly in the database query statement.

1. Understanding $fine_username

$fine_username: this parameter is used to obtain the login name of the decision making platform. For details, please refer to Parameter control of Permission granularity.

2. Create external permission table

Create an external permission table in the database. The user_code in table corresponds to the user name of the decision making platform, for example: Anna is the username.

Note: if there is a corresponding table of users and cities before, you don't need to create this corresponding table.

3. Reference external permission table in designer

Reference the external permission table created in II.2 in designer, and write the filter conditions in the data source code. Data filtering can be divided into the following two situations:

1) If all users want to filter permissions, it is relatively simple and can be written directly in the condition.

Note: the following code takes MySQL database as an example:

2) If some users do not need filtering permission, and some users need filtering permission, they need to judge.

Note: the following code takes MySQL database as an example:

Note: the following code takes SQL Server 2014 database as an example:

3) After the report is completed, it is deployed to the decision-making platform and logged in with the Anna account. Only the data of Beijing can be seen. If you log in with Alice, you can only see the data of Shanghai.

1. multi level user external permission table

In general, there is more than one level of users viewing reports, and there may be multiple levels, such as provincial company users, branch company users, business representatives, etc. At this time, you need to adjust the external permission table and add some fields to distinguish different users.

2. Reference multi level user external permission table in designer

Note: the following code takes MySQL database as an example:

SELECT t.prov_name AS 'first_level_org',
       t.city_name AS 'second_level_org',
       t.sales_name AS 'Manager',
       t.int_value AS 'Count'
FROM yl_order_table t
WHERE 1 = 1
-- (1)filter 1_level_org
  AND CASE WHEN (SELECT COUNT(1) 
                   FROM fr_user_city t 
                  WHERE t.valid = 'T' 
                    AND t.level IN (1,2,3)   --Note: different permissions are filtered here
                    AND t.user_code = '${fine_username}'
                 )<>0 
           THEN t.prov_name IN 
                (SELECT distinct t.org1_name --Note: different permissions are filtered here
                   FROM fr_user_city t 
                  WHERE t.valid = 'T' 
                    AND t.level IN (1,2,3)   --Note: different permissions are filtered here
                    AND t.user_code = '${fine_username}'
                 )
           ELSE 1=1 
      END
  -- (2)filter 2_level_org
  AND CASE WHEN (SELECT COUNT(1) 
                   FROM fr_user_city t 
                  WHERE t.valid = 'T' 
                    AND t.level IN (2,3)     --Note: different permissions are filtered here
                    AND t.user_code = '${fine_username}'
                 )<>0 
           THEN t.city_name IN 
                (SELECT distinct t.org2_name  --Note: different permissions are filtered here
                   FROM fr_user_city t 
                  WHERE t.valid = 'T' 
                    AND t.level IN (2,3)      --Note: different permissions are filtered here
                    AND t.user_code = '${fine_username}'
                 )
           ELSE 1=1 
      END
  -- (3)filter 3_level_org
  AND CASE WHEN (SELECT COUNT(1) 
                   FROM fr_user_city t 
                  WHERE t.valid = 'T' 
                    AND t.level = 3           --Note: different permissions are filtered here
                    AND t.user_code = '${fine_username}'
                 )<>0 
           THEN t.sales_name IN 
                (SELECT distinct t.org3_name  --Note: different permissions are filtered here
                   FROM fr_user_city t 
                  WHERE t.valid = 'T' 
                    AND t.level = 3           --Note: different permissions are filtered here
                    AND t.user_code = '${fine_username}'
                 )
           ELSE 1=1 
      END
;


コードを展開

Different databases will have certain differences, please adjust according to the actual situation.