4. Manage Security Columns¶
Like the objects of an object group, security (sensitive) columns are detected by discover rules. Marking a column as security column is the prerequisite for column level security: its values are only visible to users with security column access (p_scol_access when granting object group access or tenant access); all other users see NULL.
4.1. Add a Discover Rule¶
Add a discover rule using API_SCURTY.ADD_SCOL_DISCOVER_RULE. All include patterns have to be provided, while the exclude patterns and the rule comment are optional.
The following example marks the columns of all objects ending with ‘_TAB’ in the schemas starting with ‘PSV_TEST’ as security columns, except schemas ending with ‘_RAW’ or ‘_BA’ and columns containing ‘TDATE’. Comments are not restricted.
Example: Add a Security Column Rule
1EXECUTE SCRIPT API_SCURTY.ADD_SCOL_DISCOVER_RULE(
2 'PSV_TEST.*' -- p_schema_incl_pattern
3 ,'.*_(RAW|BA)' -- p_schema_excl_pattern
4 ,'.*_TAB' -- p_object_incl_pattern
5 ,null -- p_object_excl_pattern
6 ,'.*' -- p_column_incl_pattern
7 ,'.*TDATE.*' -- p_column_excl_pattern
8 ,'.*' -- p_comment_incl_pattern
9 ,null -- p_comment_excl_pattern
10 ,'scol-test' -- p_rule_comment
11);
The rule is stored in SCURTY.REP_SCOL_DISCOVER_RULES; the resulting security columns are applied with the next security refresh.
RULE_ID |
SCHEMA_INCL_PATTERN |
SCHEMA_EXCL_PATTERN |
OBJECT_INCL_PATTERN |
OBJECT_EXCL_PATTERN |
COLUMN_INCL_PATTERN |
COLUMN_EXCL_PATTERN |
COMMENT_INCL_PATTERN |
COMMENT_EXCL_PATTERN |
RULE_COMMENT |
|---|---|---|---|---|---|---|---|---|---|
43 |
PSV_TEST.* |
.*_(RAW|BA) |
.*_TAB |
(null) |
.* |
.TDATE. |
.* |
(null) |
scol-test |
4.2. Change a Discover Rule¶
To change a security column rule, use API_SCURTY.CHANGE_SCOL_DISCOVER_RULE with the rule ID from SCURTY.REP_SCOL_DISCOVER_RULES.
NULL leaves a parameter unchanged, while a blank (’ ‘) restores its default value (’.*’ for include patterns, no pattern for exclude patterns).
In the following example the schema exclude pattern is removed and the rule comment is changed; all other values stay the same.
Example: Remove the Schema Exclude Pattern
1EXECUTE SCRIPT API_SCURTY.CHANGE_SCOL_DISCOVER_RULE(
2 43 -- p_rule_id
3 ,null -- p_schema_incl_pattern
4 ,' ' -- p_schema_excl_pattern
5 ,null -- p_object_incl_pattern
6 ,null -- p_object_excl_pattern
7 ,null -- p_column_incl_pattern
8 ,null -- p_column_excl_pattern
9 ,null -- p_comment_incl_pattern
10 ,null -- p_comment_excl_pattern
11 ,'scol-test-change' -- p_rule_comment
12);
The rule in SCURTY.REP_SCOL_DISCOVER_RULES has changed:
RULE_ID |
SCHEMA_INCL_PATTERN |
SCHEMA_EXCL_PATTERN |
OBJECT_INCL_PATTERN |
OBJECT_EXCL_PATTERN |
COLUMN_INCL_PATTERN |
COLUMN_EXCL_PATTERN |
COMMENT_INCL_PATTERN |
COMMENT_EXCL_PATTERN |
RULE_COMMENT |
|---|---|---|---|---|---|---|---|---|---|
43 |
PSV_TEST.* |
(null) |
.*_TAB |
(null) |
.* |
.TDATE. |
.* |
(null) |
scol-test-change |
4.3. Remove a Discover Rule¶
To remove a rule, use API_SCURTY.REMOVE_SCOL_DISCOVER_RULE.
Example: Remove the Rule 43
1EXECUTE SCRIPT API_SCURTY.REMOVE_SCOL_DISCOVER_RULE(
2 43 -- p_rule_id
3);
The rule is removed from SCURTY.REP_SCOL_DISCOVER_RULES.