User Guide

9.9
- 9.9.4
- 9.9.3
- 9.9.2
- 9.9.1
- 9.8.4
- 9.8.3
- 9.8.2
- 9.8.1
- 9.8.0

Back Next

PL/SQL Function Matcher Examples

Use the PL/SQL Function matcher examples as guidelines to create PL/SQL functions and corresponding security rules.

Text Value Example

You want to mask the SALARY column of the EMP table based on the user that sends the request to the database. If the user is a senior manager, the result set is unmasked. If the user is not a senior manager, the result set contains a zero (0) in the SALARY column.

You create a PL/SQL function with the following code:

CREATE OR REPLACE FUNCTION test_senior_manager (position VARCHAR) 
RETURN INTEGER AS
manager_type VARCHAR(50) ;
BEGIN
  manager_type := retrieve_manager_type(position) ;
  IF manager_type = 'SENIOR' THEN
     RETURN 0;
  ELSE 
     RETURN 1;
  END IF;
END ;

The PL/SQL function returns

if the user is a senior manager and returns

if the user is not a senior manager.

Create the following security rule:

The rule uses the PL/SQL Function matching method, defines match_manager as the Function Name, and has Include Text Value selected. The rule uses the Mask rule action, with emp as the Table Name, salary as the Column Name, and '0' as the Masking Function.

If the user is a senior manager and the PL/SQL function returns

, Dynamic Data Masking does not apply the rule action and the result set is unmasked. If the user is not a senior manager and the PL/SQL function returns

, the rule action masks the values in the SALARY column according to the masking function.

Text Value With Multiple Arguments Example

You want to mask data based on the user that sends the request and whether the statement returns columns that contain a specific type of data.

You create a PL/SQL function with the following code:

CREATE OR REPLACE TYPE LIST_TYPE IS VARRAY (5) OF VARCHAR(50);
CREATE OR REPLACE FUNCTION test_usage_type (db_user VARCHAR2, statement VARCHAR2, usages VARCHAR2) 
RETURN INTEGER AS
  usage_list LIST_TYPE;
BEGIN
  IF db_user = 'SCOTT' THEN
    IF is_usage_type_col_in_tables(statement) THEN
      usage_list := spiltSemicolonList(usages);
      IF check_usages(usage_list) THEN
        RETURN 1; -- If there is a usage in security rule that implies masking is needed then return 1
      ELSE  
        RETURN 0;
      END IF ;
    ELSE
      RETURN 0;
    END IF;
  ELSE
    RETURN 0;
  END IF;
END;

The PL/SQL statement returns

if the user is SCOTT and the returned columns contain data from the usage_list. Otherwise, the PL/SQL statement returns

You create the following security rule:

$The rule uses the PL/SQL Function matching method, defines usr1.test_usage_type as the Function Name, and has Include Database Username, Include Statement, and Include Text Value selected. The Value to Use field has TEST;LEARNING entered in the text box. The rule uses the Mask rule action, with .*EMP.* as the Table Name, .*NAME.* as the Column Name, and substr(\(col),1,2)||'xxxx' as the Masking Function.$

The rule contains Include Database Username and Include Statement parameters, which are fixed value arguments that you define in the PL/SQL function. The Value to Use parameter contains a list of delimiters that are separated by a semicolon. In this example, the delimiters are TEST and LEARNING. If the PL/SQL function matcher matches the request, the rule masks the NAME column of the EMP table.

OS User and Statement Example

You want to mask data in the production database based on the operating system user that sends the request to the database.

You create a PL/SQL function with the following code:

CREATE OR REPLACE FUNCTION test_a_few_params (os_user VARCHAR2, statement VARCHAR2) 
RETURN INTEGER AS
stmt_contains_prod_tables BOOLEAN ;
os_user_is_su BOOLEAN ;
needs_masking BOOLEAN ;
BEGIN
  stmt_contains_prod_tables := is_stmt_contains_prod_tables (statement) ;
  os_user_is_su := is_su (os_user) ;
  needs_masking := os_user_is_su AND stmt_contains_prod_tables;
  IF needs_masking THEN
     RETURN 1;
  ELSE 
     RETURN 0;
  END IF;
END ;

The PL/SQL function returns

if the operating system user is su and the request contains production tables. Otherwise, the function returns

You create the following security rule:

The rule uses the PL/SQL Function matching method, defines us1.test_a_few_params as the Function Name, and has Include OS User and Include Statement selected. The rule uses the Mask rule action, with emp as the Table Name, address as the Column Name, and 'XXXX' as the Masking Function.

If the operating system user is su and the request contains production tables, Dynamic Data Masking applies the masking function in the rule action. If the operating system user is not su or the request does not contain production tables, the PL/SQL function returns 0 and Dynamic Data Masking does not apply the rule action.

Rename Saved Search

Table of Contents

User Guide

User Guide

PL/SQL Function Matcher Examples

PL/SQL Function Matcher Examples