Table of Contents

Search

  1. Preface
  2. Introduction to the Stored Procedure Accelerator for Oracle
  3. Masking Stored Procedures and User-Defined Table Functions
  4. Stored Procedure Accelerator Setup
  5. Stored Procedure Accelerator Rules

Stored Procedure Accelerator Guide for Oracle

Stored Procedure Accelerator Guide for Oracle

Stored Procedure Accelerator Example

Stored Procedure Accelerator Example

You want to mask data called by a stored procedure.
You have a stored procedure named Proc_Dept_Emp, which has the following parameters:
emp_id IN NUMBER, job_id IN NUMBER, salary_rec OUT SYS_REFCURSOR
The SYS_REFCURSOR out parameter of the stored procedure selects the following columns in the Dept_Emp table:
  • EMPLOYEE_ID
  • Last_Name
  • FIRST_NAME
  • JOB_ID
  • Job_Title
  • SALARY
  • COMM
  • DEPARTMENT_ID
You set up the stored procedure accelerator and create a masking rule set. You have a masking rule that masks the Proc_Dept_Emp stored procedure. The following table describes the columns and masking functions that you define in the masking rule:
Column
Masking Function
.*SALARY
999
.*COMM
\(col)-FLOOR(\(col) / 7)*7
.*Last_Name
substr(\(col),1,2)
When you create the masking rule, you must enter the column name preceded by .*, as in the table.
You send the following request to the database:
CALL Proc_Dept_Emp(25,10, ?)
The ? is a placeholder for the REF_CURSOR as OUT parameter.
Dynamic Data Masking creates a temporary stored procedure in the temporary schema that you defined in the Dynamic Data Masking ORACLE_DDM_TEMPDB symbol. The temporary stored procedure uses the DDM_SP_MASKING package and sends the call to the following temporary stored procedure:
CALL TEMPDB.P1436942931536_2 (25,10,? )
The temporary stored procedure executes the original stored procedure. The temporary stored procedure then creates and populates the global temporary table, TEMPDB.T_1436942931536, with the result set data and changes the out REF_CURSOR parameter to the following statement according to the masking rules:
SELECT EMPLOYEE_ID , substr(LAST_NAME ,1,2) LAST_NAME , FIRST_NAME , MIDDLE_NAME , JOB_ID , MANAGER_ID , HIREDATE , 999 SALARY , COMM -FLOOR(COMM / 7)*7 COMM , DEPARTMENT_ID , EMAIL FROM TEMPDB . T_1436942931536 ;

0 COMMENTS

We’d like to hear from you!