6. Add HU Users And Grant Fine-Grained Access

All activities must be executed as security manager.

6.1. Create 2 Users of Type HU

We will create the user ‘CAESAR’ for the CEO and ‘HELENA’ for an HR manager from division EAST. CAESAR may see all data. HELENA is restricted to the data of division EAST and must not see the salaries of employees in the IT department.

 1execute script API_SCURTY.CREATE_USER (
 2     'caesar'                           -- p_user_name
 3     ,'HU'                              -- p_user_type
 4     ,null                              -- p_authentication (default password)
 5     ,false                             -- p_private_sbx
 6     ,null                              -- p_quota
 7     ,'CEO'                             -- p_comment
 8     ,null                              -- p_email
 9);
10
11execute script API_SCURTY.CREATE_USER (
12     'helena'                           -- p_user_name
13     ,'HU'                              -- p_user_type
14     ,null                              -- p_authentication (default password)
15     ,false                             -- p_private_sbx
16     ,null                              -- p_quota
17     ,'HR manager EAST'                 -- p_comment
18     ,null                              -- p_email
19);

As for the application manager, change the default passwords (log in as the respective user and execute the statement):

1alter user CAESAR identified by "<new password>" replace "Firstpw123+";
2alter user HELENA identified by "<new password>" replace "Firstpw123+";

6.2. Define an Object Group

We will now add an object group for HR data.

  • The group is called OG_HR and is read-only.

  • As it is used to grant access to human users, it only contains the PSV layer: all views of the schemas PSV_EMP_DR, PSV_EMP_TA, and PSV_EMP_BA.

 1execute script API_SCURTY.ADD_OBJECT_GROUP (
 2     'OG_HR'           -- p_object_group
 3     ,'HR'             -- p_description
 4     ,null             -- p_read_write
 5);
 6
 7execute script API_SCURTY.ADD_OG_OBJ_DISCOVER_RULE (
 8     'OG_HR'                      -- p_object_group
 9     ,'PSV_EMP_(DR|TA|BA)'        -- p_schema_incl_pattern
10     ,null                        -- p_schema_excl_pattern
11     ,'.*'                        -- p_object_incl_pattern
12     ,null                        -- p_object_excl_pattern
13     ,'.*'                        -- p_comment_incl_pattern
14     ,null                        -- p_comment_excl_pattern
15     ,null                        -- p_rule_comment
16);

After the next security refresh, SCURTY.REP_OG_OBJECTS shows the objects of the group.

6.3. Add Tenant Groups and Define Tenant Columns

For row level security we add two tenant groups:

  • one for the division (based on the BDomain)

  • one for the department

 1execute script API_SCURTY.ADD_TENANT_GROUP (
 2     'DIV'         -- p_tnt_group
 3     ,'Division'   -- p_tnt_group_desc
 4     ,null         -- p_all_tnt_code
 5);
 6
 7execute script API_SCURTY.ADD_TENANT_GROUP (
 8     'DEPT'        -- p_tnt_group
 9     ,'Department' -- p_tnt_group_desc
10     ,null         -- p_all_tnt_code
11);
12
13execute script API_SCURTY.ADD_TENANT (
14     'DIV'                       -- p_tnt_group
15     ,'DIV'                      -- p_tnt_name
16     ,1                          -- p_tnt_match_prio
17     ,'Division column access'   -- p_tnt_desc
18     ,'TEXT'                     -- p_tnt_data_type
19     ,'BDOMAIN'                  -- p_tnt_col_incl_pattern
20     ,null                       -- p_tnt_col_excl_pattern
21     ,'PS(A|B)_EMP_.*'           -- p_tnt_schema_incl_pattern
22     ,null                       -- p_tnt_schema_excl_pattern
23     ,'.*'                       -- p_tnt_object_incl_pattern
24     ,null                       -- p_tnt_object_excl_pattern
25     ,'.*'                       -- p_tnt_comment_incl_pattern
26     ,null                       -- p_tnt_comment_excl_pattern
27);
28
29execute script API_SCURTY.ADD_TENANT (
30     'DEPT'                      -- p_tnt_group
31     ,'DEPT'                     -- p_tnt_name
32     ,1                          -- p_tnt_match_prio
33     ,'department column access' -- p_tnt_desc
34     ,'TEXT'                     -- p_tnt_data_type
35     ,'DEPARTMENT'               -- p_tnt_col_incl_pattern
36     ,null                       -- p_tnt_col_excl_pattern
37     ,'PS(A|B)_EMP_.*'           -- p_tnt_schema_incl_pattern
38     ,null                       -- p_tnt_schema_excl_pattern
39     ,'.*'                       -- p_tnt_object_incl_pattern
40     ,null                       -- p_tnt_object_excl_pattern
41     ,'.*'                       -- p_tnt_comment_incl_pattern
42     ,null                       -- p_tnt_comment_excl_pattern
43);

The results can be checked in SCURTY.REP_AVAILABLE_TNT_COLUMNS and SCURTY.REP_TNT_OBJECT_ACCESS.

6.4. Define Security Columns

For column level security, the salary is defined as security column, since HELENA may not see it for all employees.

 1execute script API_SCURTY.ADD_SCOL_DISCOVER_RULE(
 2     'PSB_EMP_.*'         -- p_schema_incl_pattern
 3     ,null                -- p_schema_excl_pattern
 4     ,'PERSON.*'          -- p_object_incl_pattern
 5     ,null                -- p_object_excl_pattern
 6     ,'SALARY'            -- p_column_incl_pattern
 7     ,null                -- p_column_excl_pattern
 8     ,'.*'                -- p_comment_incl_pattern
 9     ,null                -- p_comment_excl_pattern
10     ,'scol-salary'       -- p_rule_comment
11);

The resulting security columns are listed in SCURTY.REP_SENSITIVE_COLUMNS.

6.5. Grant Access To the Users

 1execute script API_SCURTY.GRANT_USER_OG_ACCESS (
 2      'OG_HR'               -- p_object_group
 3     ,'CAESAR'              -- p_user_name
 4     ,null                  -- p_rw_access
 5     ,true                  -- p_scol_access
 6);
 7
 8execute script API_SCURTY.GRANT_USER_OG_ACCESS (
 9      'OG_HR'               -- p_object_group
10     ,'HELENA'              -- p_user_name
11     ,null                  -- p_rw_access
12     ,false                 -- p_scol_access
13);

At this point neither user sees any data, since the data is protected by row level security and no tenant access has been granted yet. You may check this with select * from psv_emp_dr.person as CAESAR and HELENA.

Note

The grants are applied by the SCURTY job rep_refresh, which runs every 30 minutes by default. To apply them immediately, run execute script API_SCURTY.REP_REFRESH; or, on the RED server, redctl start_db_job -s SCURTY -j rep_refresh.

Now grant tenant access for both tenant groups (‘DIV’ and ‘DEPT’). The code ‘ALL’ grants access to all tenants of a group. p_scol_access decides whether the security column (salary) is visible for the rows of these tenants:

 1--
 2-- unrestricted tenant access for CAESAR
 3--
 4execute script API_SCURTY.GRANT_USER_TNT_ACCESS (
 5      'OG_HR'               -- p_object_group
 6     ,'CAESAR'              -- p_user_name
 7     ,'DIV'                 -- p_tnt_group
 8     ,ARRAY('ALL')          -- p_tnt_code
 9     ,true                  -- p_scol_access
10);
11
12execute script API_SCURTY.GRANT_USER_TNT_ACCESS (
13      'OG_HR'               -- p_object_group
14     ,'CAESAR'              -- p_user_name
15     ,'DEPT'                -- p_tnt_group
16     ,ARRAY('ALL')          -- p_tnt_code
17     ,true                  -- p_scol_access
18);
19
20--
21-- selective access for HELENA
22--
23execute script API_SCURTY.GRANT_USER_TNT_ACCESS (
24      'OG_HR'               -- p_object_group
25     ,'HELENA'              -- p_user_name
26     ,'DIV'                 -- p_tnt_group
27     ,ARRAY('DIV_EAST')     -- p_tnt_code
28     ,true                  -- p_scol_access
29);
30
31execute script API_SCURTY.GRANT_USER_TNT_ACCESS (
32      'OG_HR'               -- p_object_group
33     ,'HELENA'              -- p_user_name
34     ,'DEPT'                -- p_tnt_group
35     ,ARRAY('HR')           -- p_tnt_code
36     ,true                  -- p_scol_access
37);
38
39execute script API_SCURTY.GRANT_USER_TNT_ACCESS (
40      'OG_HR'               -- p_object_group
41     ,'HELENA'              -- p_user_name
42     ,'DEPT'                -- p_tnt_group
43     ,ARRAY('IT')           -- p_tnt_code
44     ,false                 -- p_scol_access
45);

Use the following SCURTY views to check the configuration:

  • REP_USER_OG_ACCESS

  • REP_USER_TNT_ACCESS

  • REP_USER_ACCESS_TNT

  • REP_USER_ACCESS_SCOLS

  • REP_SEC_ACCESS_DETAILS

Note

Again, wait for the security refresh or trigger it manually (see above).

Log in as CAESAR and check that select * from psv_emp_dr.person returns all data. As HELENA you see only the rows of division EAST, and the salaries of the IT department are NULL (some columns omitted; the TDates depend on the load time):

Table 6.1 Output of select * from psv_emp_dr.person by HELENA

BDOMAIN

BDATE_FROM

BDATE_TO

EMPID

FIRSTNAME

LASTNAME

SALARY

BIRTHDAY

DEPARTMENT

DIV_EAST

2025-09-01

9999-12-31

1

Frank

Moses

(null)

1955-03-19

IT

DIV_EAST

2025-09-01

9999-12-31

2

Marvin

Boggs

(null)

1953-12-09

IT

DIV_EAST

2025-09-01

9999-12-31

3

Sarah

Ross

4750.00

1964-08-02

HR

DIV_EAST

2025-09-01

9999-12-31

4

Ivan

Somanov

4750.00

1946-06-01

HR

DIV_EAST

2025-09-01

9999-12-31

5

Joe

Matheson

(null)

1937-06-01

IT