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