6. Manage Schema Map Rules

Schema map rules define in which reporting schema the secured views of a base schema are created, e.g. the views of the PSB schema PSB_SALES in the PSV schema PSV_SALES. The rules are applied by the security refresh.

The following rules are delivered with RED:

BASE_PATTERN

BASE_REPL_PATTERN

BASE_REPL_STRING

RESULT

PSA_.*$

^PSA_(.*)

PSV_\1_RAW

PSA_SALES → PSV_SALES_RAW

^APB_.*$

^APB_

APV_

APB_SALES → APV_SALES

^PSB_.*$

^PSB_

PSV_

PSB_SALES_DR → PSV_SALES_DR

p_base_pattern selects the base schemas (regular expression), and the view schema name is derived with REGEXP_REPLACE(<base schema>, <p_base_repl_pattern>, <p_base_repl_string>).

The result is shown in SCURTY.SEC_SCHEMA_MAP: base schema, view schema, and its status (EXISTS, MISSING, or OBSOLETE). SCURTY.SEC_SCHEMA_MAP_DETAILS lists the objects per mapped schema.

6.1. Add a Schema Map Rule

Add a rule with API_SCURTY.ADD_SCHEMA_MAP_RULE.

Example: Map the Mart Schemas MRT_* to RPT_*

1EXECUTE SCRIPT API_SCURTY.ADD_SCHEMA_MAP_RULE(
2    '^MRT_.*$'          -- p_base_pattern
3   ,'^MRT_'             -- p_base_repl_pattern
4   ,'RPT_'              -- p_base_repl_string
5);

The rule is stored in SCURTY.SEC_SCHEMA_MAP_RULES. Check the resulting mapping in SCURTY.SEC_SCHEMA_MAP before the next refresh.

6.2. Change or Remove a Schema Map Rule

Change a rule with API_SCURTY.CHANGE_SCHEMA_MAP_RULE and remove it with API_SCURTY.REMOVE_SCHEMA_MAP_RULE, using the rule ID from SCURTY.SEC_SCHEMA_MAP_RULES.

Example: Remove the Rule 4

1EXECUTE SCRIPT API_SCURTY.REMOVE_SCHEMA_MAP_RULE(
2    4                   -- p_rule_id
3);

Warning

Changing or removing a rule changes the reporting schemas of existing views. Users and tools that access the old view schemas lose access with the next refresh.