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. While CEASAR may see all data HELENA will be restricted to data of division EAST and she must not see the salary of employees in the IT department.

 1execute script API_SCURTY.CREATE_USER (
 2     'caesar'                           -- p_user_name
 3     ,'HU'                              -- p_user_type
 4     ,'password'                        -- p_authentication
 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     ,'password'                        -- p_authentication
15     ,false                             -- p_private_sbx
16     ,null                              -- p_quota
17     ,'HR manager EAST'                 -- p_comment
18     ,null                              -- p_email
19);

As with the application manager before reset the default passwords (login as this very user and then execute the alter statement):

1alter user CAESAR identified by "sx123" REPLACE "Firstpw123+";
2alter user HELENA identified by "sx123" REPLACE "Firstpw123+";

Note

‘Firstpw123+’ is the RED installation default password for HUs. You should change it by setting the parameter sec_hu_def_passwd in SCURTY.ADM_PARAMS.

6.2. Define an Object Group

We will now add an object group for HR data.

  • The group is called OG_HR and it should be read only.

  • Since we will use it to grant access to HUs it should contain only the PSV layer.

  • All views in all ‘PSV_EMP_%’ schemas should be included

 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);

Check the view SCURTY.REP_OG_OBJECTS to see which objects are finally included.

6.3. Add tenants for RLS and CLS

6.4. Add a Tenant Group and Define Tenant Columns

We will add 2 tenant groups

  • one for the division (we can use the BDomain for this)

  • another 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.RE_AVAILABLE_TNT_COLUMNS and SCURTY.REP_TNT_OBJECT_ACCESS.

6.5. Define Sensitive Columns

The salary must be defined as sensitive 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 list of sensitive columns can be found in SCURTY.REP_SENSITIVE_COLUMNS.

6.6. 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 none of the users would see any data since the data are row level protected and we have not granted any tenant access. You may check this by select * from psv_emp_dr.person as CAESAR and HELENA.

Note

You must wait for the next run of the SCURTY job REP_REFRESH which refreshes all necessary grants in the background. The default interval for this job is 1 hour.

If you want to refresh immediately you may execute the refresh dirctly via execute script API_SCURTY.REP_REFRESH or via redctl start_db_job -s SCURTY -j REP_REFRESH

Let’s grant appropriate tenant access to both defined tenant groups (‘DIV’ and ‘DEPT’):

 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 folling SCURTY objects to check your configuration:

  • REP_USER_OG_ACCESS

  • REP_USER_TNT_ACCESS

  • REP_USER_ACCESS_TNT

  • REP_USER_ACCESS_SCOLS

  • REP_SEC_ACCESS_DETAILS

Again wait for SCURTY to refresh the security setting or trigger REP_REFRESH manually (see above).

Login as CAESAR and check that a select * from psv_emp_dr.person returns all data. If you login as HELENA you should see the following output (TDates depend on your effective load time):

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

BDOMAIN

BDATE_FROM

BDATE_TO

TDATE_FROM

TDATE_TO

ID

FIRSTNAME

LASTNAME

SALARY

BIRTHDATE

DEPARTMENT

EAST

2025-09-01

9999-12-31

2025-09-02 08:15:35,029

9999-12-31 00:00:00.000

001

Frank

Moses

1955-03-19

IT

EAST

2025-09-01

9999-12-31

2025-09-02 08:15:35,029

9999-12-31 00:00:00.000

002

Marvin

Boggs

1953-12-09

IT

EAST

2025-09-01

9999-12-31

2025-09-02 08:15:35,029

9999-12-31 00:00:00.000

003

Sarah

Ross

4.750,00

1964-08-02

HR

EAST

2025-09-01

9999-12-31

2025-09-02 08:15:35,029

9999-12-31 00:00:00.000

004

Ivan

Somanov

4.750,00

1946-06-01

HR

EAST

2025-09-01

9999-12-31

2025-09-02 08:15:35,029

9999-12-31 00:00:00.000

005

Joe

Matheson

1937-06-01

IT