Sample: Column-level security using Dataverse Web API (PowerShell)

This sample shows how to perform column-level security operations using Dataverse Web API with PowerShell.

Prerequisites

Before running this sample, you should read these articles that explain concepts and patterns used by Dataverse PowerShell samples:

• Quick Start Web API with PowerShell and Visual Studio Code
• Use PowerShell and Visual Studio Code with the Dataverse Web API

This sample requires:

• Visual Studio Code. Download Visual Studio Code

• PowerShell extension for Visual Studio Code. Install PowerShell for Visual Studio Code

• PowerShell 7.4 or higher. See Install PowerShell on Windows, Linux, and macOS

• Az PowerShell module version 11.1.0 or higher. See How to install Azure PowerShell

  To update an existing installation to the latest version, use Update-Module -Name Az -Force

• Access to Dataverse with system administrator privileges.

• An application user account with Basic User access. See the Configure users section for instructions about how to create this user.

How to run this sample

1. Download or clone the Samples repo so that you have a local copy.

2. Open the ColumnLevelSecurity folder using Visual Studio Code.

3. Create a file named .env using the data found in the .env.example file.

4. In the .env file, replace the placeholder values:

   # The environment this application will connect to.
   BASE_URL=https://<yourorg>.api.crm.dynamics.com/
   # The application user application id
   CLIENT_ID=00001111-aaaa-2222-bbbb-3333cccc4444
   # The application user secret
   CLIENT_SECRET=Aa1Bb~2Cc3.-Dd4Ee5Ff6Gg7Hh8Ii9_Jj0Kk1Ll2
   # The Entra tenant id
   TENANT_ID=aaaabbbb-0000-cccc-1111-dddd2222eeee
   

5. Set the BASE_URL to the URL of the environment you want to run the sample against

6. See the Configure users section for instructions to set the CLIENT_ID, CLIENT_SECRET, and TENANT_ID values.

7. Press F5 to run the sample. The .vscode/launch.json file is configured to execute the ColumnLevelSecurity.ps1 file.

When the sample runs, you're prompted in the default browser to select an environment user account and enter a password.

What this sample does

This sample demonstrates the capabilities described in Column-level security with code:

• Discover which columns can be secured in a Dataverse environment
• Discover which columns are currently secured
• Secure columns in a Dataverse environment
• Grant read or write access to selected fields to individual users
• Modify access to secured fields for individual users
• Revoke access to selected fields for individual users
• Provide read and write access to specific groups of users
• Enable masking of secured columns
• Retrieve unmasked values for secured columns

Sample files

The code for this sample is in the following files:

This sample is designed to be resilient when errors occur so you should be able to run the sample again if it failed previously.

How this sample works

In order to create the scenario described in What this sample does, the sample does the following operations:

Setup

The Setup function in this sample does the following operations:

1. Create a solution publisher named ColumnLevelSecuritySamplePublisher with customization prefix of sample if it doesn't exist.

2. Create a solution named ColumnLevelSecuritySampleSolution associated to the publisher if it doesn't exist.

   All subsequent solution-aware items are created in the context of this solution.

3. Create a table named sample_Example if it doesn't exist.

4. Create four string columns in the sample_Example table if they don't exist.

   The table schema names are:

   • sample_Email
   • sample_GovernmentId
   • sample_TelephoneNumber
   • sample_DateOfBirth

5. Remove any existing sample data in the sample_Example table.

6. Add three rows of sample data with information in each column of the sample_Example table.

7. Create a new security role named Column-level security sample role.

8. Add privileges for the sample_Example table to the security role.

9. Associate the user to the security role.

10. Create a Field Security Profile record named Example Field Security Profile that is used in the Manage access to secure column data to groups section of the sample.

11. Associate the application user to the field security profile.

12. Wait 30 seconds for the cache to catch up with the new objects created.

Demonstrate

The Run function in this sample does the following operations:

Retrieve information about columns

1. Use the Dump-ColumnSecurityInfo-Example function to download a CSV file with data about which columns in the system can be secured.
2. Use the Get-SecuredColumnList-Example function to retrieve and show a list of environment columns that are already secured.

Secure columns

1. Demonstrate that the application user can retrieve data from all the columns in the sample_Example table.
2. Use the Set-ColumnIsSecured-Example function to secure the four columns
3. Demonstrate that the application user can no longer retrieve data from the secured columns in the sample_Example table.

Grant access to secure column data to individuals

1. Use the Grant-ColumnAccess-Example function to grant the application users read access to specific record field values by creating a Field Sharing (PrincipalObjectAttributeAccess) record.
2. Demonstrate that the application user can now retrieve data from specific secured record fields in the sample_Example table.
3. Demonstrate that the application user isn't allowed to write data to the secured columns.
4. Use the Modify-ColumnAccess-Example function to grant write access to a specific record field.
5. Demonstrate that the application user is now allowed to write data to the specific record field.
6. Use the Revoke-ColumnAccess-Example function to delete the PrincipalObjectAttributeAccess records that gave the application user access to the secured columns.

Manage access to secure column data to groups

1. Add field permissions to the Example Field Security Profile record that was created in Setup by creating Field Permission (FieldPermission) records
2. Demonstrate that the application user can view only the secured columns specified in the field permission records.
3. Demonstrate that the application user isn't allowed to write data to the specific record field not enabled with field permissions.

Masking

1. Retrieve ID values for existing masking rules. Create new Secured Masking Column (AttributeMaskingRule) records to specify masking rules for columns of the sample_Example table.
2. Update the canreadunmasked column values of the Field Permission (FieldPermission) records created earlier.
3. Wait 30 seconds for the cache to catch up with the new objects created.
4. Demonstrate that the application user can now retrieve data with masked values.
5. Demonstrate that the application user can now retrieve unmasked values with a GET requests on the sample_examples collection to return multiple records when using the UnMaskedData optional parameter.
6. Demonstrate that the application user can now retrieve unmasked values with a GET request to retrieve a single record when using the UnMaskedData optional parameter.

Export solution

Use an exported solution to test the functionality of the sample configurations outside of this sample.

1. Export the solution created with all the configurations as an unmanaged solution.
2. Export the solution created with all the configurations as a managed solution

Clean up

The static Cleanup function in this sample does the following operations:

When the $DELETE_CREATED_OBJECTS setting in Helpers.ps1 is true, the Cleanup function tries to delete all components created during Setup or Run. The goal is to return the environment to the original state. If you don't want the items to be deleted, you can change the setting to false.

Related samples

Sample: Column-level security using Dataverse SDK for .NET