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. XML Functions Reference
  9. Glossary

Replace Table Example

Replace Table Example

You want to use the Replace Table rule action to mask a variety of SQL requests.
You want to mask incoming requests to the EMP, DEPARTMENT, and LOCATION tables. In a previous security rule, you defined the global symbol (\mySymbol) with the following value:
SELECT * FROM BONUS WHERE ENAME LIKE ‘S%’
The following figure shows the Replace Table rule action parameters that you define:
The Replace Table action has a row with Table Name set to .*EMP.* and Alternative Object or Query set to (SELECT * FROM \(table) WHERE DEPARTMENT_ID=30), a row with Table Name set to .*DEPARTMENT.* and Alternative Object or Query set to (SELECT * FROM \(prefix)DEPARTMENT WHERE DEPARTMENT_ID=20), a row with Table Name set to .*LOCATION.* and Alternative Object or Query set to [MyDB].[mySchema].\(tableOnly), and a row with Table Name set to .*Bonus.* and Alternative Ojbect or Query set to (\(mySymbol)).
The following table describes sample SQL requests and how Dynamic Data Masking alters the requests based on the Replace Table rule action parameters:
Original Statement
Replaced Statement
SELECT * FROM [HR].[DBO].employee
SELECT * FROM ((SELECT * FROM "HR"."DBO"."EMPLOYEE" WHERE DEPARTMENT_ID=30)) "EMPLOYEE"
SELECT * FROM [HR]..EMP1
SELECT * FROM ((SELECT * FROM "HR".."EMP1" WHERE DEPARTMENT_ID=30)) "EMP1"
SELECT * FROM [HR].[DBO].Department
SELECT * FROM ((SELECT * FROM "HR"."DBO".DEPARTMENT WHERE DEPARTMENT_ID=30)) "DEPARTMENT"
SELECT * FROM Department
SELECT * FROM ((SELECT * FROM DEPARTMENT WHERE DEPARTMENT_ID=30)) "DEPARTMENT"
select * from [hr].[dbo].location
select * from [MyDB].[mySchema]."LOCATION" "LOCATION"
select * from location
select * from [MyDB].[mySchema]."LOCATION" "LOCATION"
SELECT * FROM BONUS
SELECT * FROM (SELECT * FROM BONUS WHERE ENAME LIKE ‘S%’) “BONUS”