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.