5. Manage Sensitive Columns

Like objects as members of an object group sensitive columns are detected using dicovery rules. To mark a column as sensitive is a prerequisite for column level security (CLS).

5.1. Add a discovery rule

Add a discovery rule using API_SCURTY.ADD_SCOL_DISCOVER_RULE. All of the include patterns have to be provided, while the exclude patterns and the rule comment are optional.

In the example below we want to include all schemas starting with ‘PSV_TEST’, however schemas ending with ‘_RAW’ or ‘_BA’ schould be excluded. Furthermore, all objects ending with ‘_TAB’ should be included and none of the objects should be excluded. For the columns we want to include all of them except for those containing TDATE in their name. Comment-wise we do not want to specifically include or exclude any of them.

Example

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

After issuing the command above an entry is added to the table REP_SCOL_DISCOVER_RULES.

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

5.2. Change a discovery rule

To change any of the parameters of a sensitive columns rule use the script API_SCURTY.CHANGE_SCOL_DISCOVER_RULE. Providing NULL leaves the parameters as is, while a blank (’ ‘) restores the default value.

In the example below, all of the parameters stay the same except for the schema exclusion pattern, which will be set to NULL and the rule comment. The rule id can be found in the table REP_SCOL_DISCOVER_RULES.

Example

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

After this command has been issued the changes can be seen in the table REP_SCOL_DISCOVER_RULES.

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

5.3. Removing a discovery rule

To remove a rule use the script API_SCURTY.REMOVE_SCOL_DISCOVER_RULE.

Example

1EXECUTE SCRIPT API_SCURTY.REMOVE_SCOL_DISCOVER_RULE(
2     43   -- p_rule_id
3);

After issuing the script above the entry for a sensitive column rule with the id 43 will be removed from the table REP_SCOL_DISCOVER_RULES.