REGISTER_SCOL

Registers a column of a sandbox object as a sensitive column.

This procedure checks if the column already exists inside the sandbox and if it is not yet registered as a sensitive column.

Using this procedure, columns are registered as sensitive columns regardless how access is performed. There is no difference if they are queried as part of a sandbox or if they are queried as part of a “normal” object group outside a sandbox.

This procedure is provided to enable sandbox masters to register sensitive columns. Users holding the SCURTY DESIGNER role may register sensitive columns inside or outside of sandboxes using procedures p_rep_designer.register_scol or p_rep_designer.add_scol_discover_rule.

This procedure allows to set data redaction (masking) parameters for the column. Data redaction (masking) is currently not implemented for sandboxes. This means, data redaction parameters have no effect if an object is queried as part of a sandbox. However, they are effective if the same object is queried outside the sandbox as part of an object group.

See procedure p_rep_designer.register_scol for details on data redaction parameters.

Redaction policies of columns that were defined as sensitive columns using this procedure can be added, changed or removed using procedure p_rep_designer.set_scol_dr_params.

In contrast to procedure p_rep_designer.register_scol, parameter p_keep_on_drop is not exposed in the API of this procedure and is always set to ‘false’. This parameter determines if the definition should be kept when the physical column is dropped.

This procedure inserts a row into table REP_SENSITIVE_COLUMNS. Sensitive columns registered using this procedure have column SOURCE set to ‘SBX’ in table REP_SENSITIVE_COLUMNS.

Parameter Name

Required

DefaultValue

Values

Description

p_table_name

yes

The name of the table.

The name is case sensitive.

p_column_name

yes

The name of the sensitive column.

The name is case sensitive.

p_dr_mode

yes

N

Data redaction mode if no access to the sensitive column is allowed.

Possible values:

  • ‘F’: full redaction

  • ‘P’: partial redaction

  • ‘R’: random values

  • ‘E’: redaction based on regular expressions

  • ‘N’: no data redaction for this column (NULL values are shown)

p_dr_regexp_pattern

no

Only to be used if parameter p_dr_mode = ‘E’.

Search pattern (regular expression) to identify the text to be redacted.

See procedure p_rep_designer.register_scol for details on data redaction parameters.

Maximum 255 characters.

p_dr_regexp_replace_string

no

Only to be used if parameter p_dr_mode = ‘E’.

String replacing the text found by parameter p_dr_regexp_pattern. Back-references to subexpressions of parameter p_dr_regexp_pattern (e.g. 1) may be used.

See procedure p_rep_designer.register_scol for details on data redaction parameters.

Maximum 255 characters.

p_dr_regexp_position

no

1

Only to be used if parameter p_dr_mode = ‘E’.

Search start position within the text.

See procedure p_rep_designer.register_scol for details on data redaction parameters.

Possible values: positive Integer.

Default value: 1 (= first character)

p_dr_regexp_occurrence

no

0

Only to be used if parameter p_dr_mode = ‘E’.

Which occurrence within the search hits should be replaced?

See procedure p_rep_designer.register_scol for details on data redaction parameters.

Possible values: 0 (meaning all occurrences) or any positive Integer (e.g. 2 means: the second search hit will be replaced)

p_dr_regexp_match_parameter

no

Only to be used if parameter p_dr_mode = ‘E’.

Matching behavior of the search expression.

See procedure p_rep_designer.register_scol for details on data redaction parameters.

Possible values (one or more comma-separated values):

  • ‘i’: case-insensitive matching.

  • ‘c’: case-sensitive and accent-sensitive matching. (Accent sensitive means accented and unaccented versions of letters are considered different.)

  • ‘n’: the regexp wildcard ‘.’ also matches newline characters.

  • ‘m’: the source string might consist of multiple lines. ‘^’ starts a new line, ‘$’ terminates a line.

  • ‘x’: ignore whitespace characters. By default, whitespace characters match themselves.

There is no default value.

p_dr_function_parameters

no

Only to be used if parameter p_dr_mode = ‘P’.

The partial masking parameters.

See procedure p_rep_designer.register_scol for details on data redaction parameters.

There is no default value.

Example

Add column ‘SALARY’ in table ‘EMPLOYEES’ inside the sandbox as a sensitive column. Data redaction is not configured for this column, as there are no data redaction parameters set (parameter p_dr_mode defaults to ‘N’ = no data redaction):

EXEC scurty_sbx.register_scol
    ( 'EMPLOYEES'
    , 'SALARY' );