.::: Enable Dynamic Data Masking (DDM) SQL Server MSSQL 2025 include using view :::.

 

1. Tabel awal

 
CREATE TABLE customer(
    KODE_CUSTOMER CHAR(6),
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    PRIMARY KEY(KODE_CUSTOMER)
);
 

2. Tambahkan Dynamic Data Masking for telepon & alamat 

ALTER TABLE customer
ALTER COLUMN TELEPON_CUSTOMER
ADD MASKED WITH (FUNCTION = 'partial(0,"XXXXXXX",4)');

ALTER TABLE customer
ALTER COLUMN ALAMAT_CUSTOMER
ADD MASKED WITH (FUNCTION = 'partial(3,"XXXX",3)');

ALTER TABLE customer
ALTER COLUMN KOTA_CUSTOMER
ADD MASKED WITH (FUNCTION = 'default()');
  

3. Coba SELECT

SELECT * FROM customer;
 
4. Buat user untuk simulasi

CREATE USER user_app WITHOUT LOGIN;

GRANT SELECT ON dbo.customer TO user_app;

EXECUTE AS USER = 'user_app';

SELECT * FROM customer;

REVERT;
 

5. DBA bisa melihat data asli dengan unmask

CREATE USER user_dba WITHOUT LOGIN;

GRANT SELECT ON dbo.customer TO user_dba;
GRANT UNMASK ON dbo.customer TO user_dba;

EXECUTE AS USER = 'user_dba';

SELECT * FROM customer;

REVERT;

REVOKE UNMASK ON dbo.customer FROM user_dba;
 

6. check table masked

SELECT
    SCHEMA_NAME(t.schema_id) AS schema_name,
    t.name AS table_name,
    mc.name AS column_name,
    mc.is_masked,
    mc.masking_function
FROM sys.tables AS t
JOIN sys.masked_columns AS mc
    ON t.object_id = mc.object_id
WHERE mc.is_masked = 1
ORDER BY
    schema_name,
    table_name,
    mc.column_id;
 

7. check Query audit Dynamic Data Masking yang lebih lengkap

SELECT
    DB_NAME() AS dbname,
    SCHEMA_NAME(t.schema_id) AS schema_name,
    t.name AS table_name,
    c.column_id,
    c.name AS column_name,
    TYPE_NAME(c.user_type_id) AS data_type,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    mc.is_masked,
    mc.masking_function
FROM sys.tables AS t
INNER JOIN sys.columns AS c
    ON t.object_id = c.object_id
INNER JOIN sys.masked_columns AS mc
    ON c.object_id = mc.object_id
    AND c.column_id = mc.column_id
WHERE mc.is_masked = 1
ORDER BY
    schema_name,
    table_name,
    c.column_id;
 

8. Kalau ingin audit semua kolom, termasuk yang belum di-mask

SELECT
    DB_NAME() AS dbname,
    SCHEMA_NAME(t.schema_id) AS schema_name,
    t.name AS table_name,
    c.name AS column_name,
    TYPE_NAME(c.user_type_id) AS data_type,
    c.max_length,
    CASE
        WHEN mc.is_masked = 1 THEN 'MASKED'
        ELSE 'NOT MASKED'
    END AS masking_status,
    mc.masking_function
FROM sys.tables AS t
INNER JOIN sys.columns AS c
    ON t.object_id = c.object_id
LEFT JOIN sys.masked_columns AS mc
    ON c.object_id = mc.object_id
    AND c.column_id = mc.column_id
ORDER BY
    schema_name,
    table_name,
    c.column_id;
 

9. Bonus: cek izin UNMASK user

 
SELECT
    USER_NAME(grantee_principal_id) AS grantee,
    permission_name,
    state_desc,
    class_desc
FROM sys.database_permissions
WHERE permission_name = 'UNMASK'
ORDER BY grantee;

 10. if using view 

  

CREATE VIEW dbo.v_customer_masked
AS
SELECT
    KODE_CUSTOMER,
    NAMA_CUSTOMER,
    CONCAT(
        LEFT(ALAMAT_CUSTOMER, 3),
        '********'
    ) AS ALAMAT_CUSTOMER,
    KOTA_CUSTOMER,
    CONCAT(
        '########',
        RIGHT(TELEPON_CUSTOMER, 4)
    ) AS TELEPON_CUSTOMER,
    '********' AS TELEPON_CUSTOMER_ALL
FROM dbo.customer;
GO