Table of Contents

Search

  1. Preface
  2. Introduction to Dynamic Data Masking
  3. Rules
  4. Connection Rules
  5. Security Rules
  6. Security Rule Set Simulator
  7. Masking Functions
  8. Stored Procedure Result Set Masking
  9. Integration with Informatica Products
  10. XML Functions Reference
  11. Glossary

PL/SQL Function Matcher Examples

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
0
if the user is a senior manager and returns
1
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
0
, 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
1
, 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
1
if the user is SCOTT and the returned columns contain data from the usage_list. Otherwise, the PL/SQL statement returns
0
.
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
1
if the operating system user is su and the request contains production tables. Otherwise, the function returns
0
.
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.