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. Appendix A: XML Functions Reference
  11. Appendix B: Glossary

Masking an SQL Batch

Masking an SQL Batch

If you use Dynamic Data Masking with a Sybase or Microsoft SQL Server database, you can mask an SQL batch.
Dynamic Data Masking masks the following SQL batch use cases:
  • SQL batch without a delimiter.
    For example:
    select * from account select * from employee
  • SQL batch with a delimiter.
    For example:
    select * from account; select * from employee;
  • SQL batch that contains an IF ... ELSE block.
    For example:
    if (select 123) < 7000 select * from EMPLOYEE where EMPLOYEE_ID = 15120 else select EMPLOYEE_ID from EMPLOYEE
  • SQL batch that contains an IF ... ELSE block and a BEGIN ... END statement.
    For example:
    if (select EMPLOYEE_ID from employee where EMPLOYEE_ID=123) < 7000 BEGIN select * from account; END else BEGIN select * from employee; END
  • SQL batch that contains an IF EXISTS or IF NOT EXISTS statement.
    For example:
    select * from employee select top 2 * from employee select a.Last_name as name from employee a select distinct a.first_name as name from employee a inner join employee b on a.first_name=b.first_name if exists (select distinct (last_name) from employee where last_name = 'SMITH') select top 3 a.last_name as name from employee a left outer join employee b on a.last_name=b.last_name select last_name from employee group by last_name
  • SQL batch that contains an IF EXISTS or IF NOT EXISTS statement and a BEGIN ... END statement.
    For example:
    if exists (select distinct (empid) from employee where empid = 101) BEGIN select * from employee select * from account END
  • SQL batch that contains a GO statement.
    For example:
    select * from account select * from employee GO select * from account
  • SQL batch that contains a GO statement with an argument.
    For example:
    select * from account select * from employee GO 3
Dynamic Data Masking does not mask the following SQL batch use cases:
  • SQL batch that contains an anonymous SQL block.
    For example:
    BEGIN select * from employee select * from account END
  • SQL batch that declares a stored procedure.
    For example:
    Create procedure SP1 as select * from employee
  • SQL batch that contains a SET command.
    For example:
    SET QUOTED_IDENTIFIER OFF select * from employee
  • SQL batch that contains a USE command.
    For example:
    USE DDM select * from employee
  • SQL batch that declares a statement.
    For example:
    DECLARE @return_value int SET @return_value = 101 SELECT * FROM EMPLOYEE where EMPID = @return_value
  • SQL batch that calls a stored procedure.
    For example:
    select * from employee EXEC SP1 10,20 select * from account