Hiding Email with a Dynamic Data Masking Function–#SQLNewBlogger
source link: https://voiceofthedba.com/2021/11/29/hiding-email-with-a-dynamic-data-masking-function-sqlnewblogger/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Hiding Email with a Dynamic Data Masking Function–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Dynamic Data Masking is a feature that provides some pseudo-security features. This lets you return a portion of data while hiding other portions for unauthorized users. The classic example is preventing someone from seeing PII data if they are a customer service rep or other non-privileged user.
Note: THIS IS NOT A SECURITY function, though it is somewhat marketed and talked about it this way. I say pseudo-security, but be careful here. I have a larger article on why.
A Scenario
I have a simple table here. I’ll give you some DDL and DML.
create table DDMEmailTest
( MyID int not null identity(1,1) constraint DDMEmailTestPK primary Key
, MyName varchar(100)
, Email varchar(100)
, Salary int)
go
insert DDMEmailTest select 'Steve Jones', '[email protected]', 200
insert DDMEmailTest select 'Bob Jones', '[email protected]', 300
Now, if I query this as a normal user, I see something like the image below. Note I can read the email address.
Limiting Access
I can prevent this from occurring by adding Dynamic Data Masking to the column. This is a column level feature, which doesn’t need activating. It’s in SQL Server 2016+ databases. You add masking with an ALTER COLUMN like shown below. The email() function is built into SQL Server.
alter table DDMEmailTest ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
When I now query the data, I see this:
Users that are not admins, or have been granted the UNMASK permission will get masked data. This mask specifically is the first character and then the [email protected] value.
Use it if this fits your scenario.
SQLNewBlogger
I was working with DDM to show something to another person and decided to throw this post together. I’d set up the scenario, so I just had to write. This was about 15 minutes of my day.
You could do the same thing, but explain how you might use this in your organization.
Recommend
-
42
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBlogger s...
-
61
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBlogger s...
-
6
#sqlnewblogger - Twitter Search / TwitterDon’t miss what’s happeningPeople on Twitter are the first to know.
-
7
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Recently I...
-
9
Basic Cursors in T-SQL–#SQLNewBlogger Another post for me that is simple and hopefully serves as an example for peop...
-
8
Creating an HTML URL from a PowerShell String–#SQLNewBlogger Another po...
-
9
Basic Fetch and Offset Experiments–#SQLNewBlogger I’ve never used the FETCH or OFFSET commands for paginatio...
-
10
Description In this episode of Data Exposed with David Trigano and David Brookler, learn how you can leverage new capabilities in Dynamic Data Masking, help your organization prevent unauthorized access to sensitiv...
-
1
Why you need to go beyond dynamic data maskingData masking is defined as replacing sensitive data with a realistic fictional equivalent while ensuring that it still looks like the real data, and retains its referential integrity, and dis...
-
8
Zoom updates iOS app with Dynamic Island support, incoming call hiding, and dark mode for Calendar
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK