Dynamic Data masking in Azure PostgreSQL – Flexible Server for migrated Oracle workloads

introduction

Dynamic data masking is essential when sharing data with third parties or when you need to protect sensitive information such as PII while transferring data from production to downstream environments. By replacing confidential data with virtual or altered data, this technology enhances security and privacy by ensuring that real PII cannot be accessed by developers, vendors, or external partners.

This article provides guidance for customers transitioning from Oracle to Azure PostgreSQL (Flexible Server) who want to mask PII or PCI data in a low-level environment. details how to enable dynamic masking for a user or role.

Enabling server-level parameters

To enable server-level parameters, navigate to the left panel of the Azure portal and select ‘‘Server Parameters’ In the Settings section.

Search for “.azure. expansion” Then click the checkbox in the Value section. PGCRYPTO and soon Select Save.

Once the above is done, search for “shared_preload_libraries”, click the ANON checkbox in the Value section, then click Save.

The above steps will ask you to restart the server.

Enable dynamic data masking

Once the server restarts, log into the database using PgAdmin or via psql.

Below is an example of how to enable dynamic masking.

1. Create a table.

   CREATE TABLE people_new ( id TEXT, firstname TEXT, lastname TEXT, phone TEXT);

2. Please insert a new record.

   INSERT INTO people_new VALUES ('E1',David, 'Miller','0609110911');
   INSERT INTO people_new VALUES ('E2','Robert', 'Bruce','0708910911');
   

3. SELECT * FROM people_new;

1. To initialize dynamic masking, run the following statement:

   SELECT anon.start_dynamic_masking();

2. Create user/role for masked user

   CREATE USER masked_user WITH PASSWORD 'masked_user';

3. Assign anonymous masking to users/roles

   SECURITY LABEL FOR anon on ROLE masked_user IS 'MASKED';

4. Create dynamic masking for the phone column in the people_new table.

   SECURITY LABEL FOR anon ON COLUMN people_new.phone IS 'MASKED WITH FUNCTION anon.partial(phone,2,$$*****$$,2)';

5. Grant full permissions on the people_new table to ‘masked_user’.

   GRANT ALL ON TABLE people_new to masked_user;

6. If you run the select command as the POSTGRES user, you will see the following results without masking the phone column:

10. Log in as ‘masked_user’ and run SELECT * FROM people_new; Since the Phone column is masked, you will find results like this:

Disable dynamic masking

To disable dynamic masking, follow these steps:

1. Issue the stop dynamic masking command as the POSTGRES user.

SELECT anon.stop_dynamic_masking();

2. Log in as ‘masked_user’ and check the values ​​in the people_new table.

SELECT * FROM people_new;

You can now see that the column is unmasked.

3. To remove assigned roles and masked capabilities, run the following command:

SELECT anon.remove_masks_for_all_roles();

This will completely remove any created features and any roles assigned to the ‘masked_user’ user.

Feedback and Suggestions

you If you have any feedback or suggestions for improving this data migration asset, please email us. Database Platform Engineering Team.