This will add a random number of days to 1st of January, 1900:
SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');
According to the Microsoft Docs, CRYPT_GEN_RANDOM "returns a cryptographic random number generated by the Crypto API (CAPI). The output is a hexadecimal number of the specified number of bytes."
select DATEADD(DAY, -(ABS(CHECKSUM(NEWID()) % 36500 )), getdate());
To summarize, the following code generates a random number between 0 and 36500. (36500 days roughly equals to 100 years; you can use 36525 to ...
Data masking occurs just before the data is sent to the client (your program, SSMS, etc). In that sense, it is "replaced and gone" by the time it gets to a client that is requesting the data to view it (assuming the user accessing the data doesn't have the UNMASK privilege). Thus, you can perform logic on it in your queries (apply where clauses ...
Id addition to Josh's reply:
In some cases, it makes sense to store the data as masked. This is in contrast to using the "Dynamic Data Masking" functionality in SQL Server (as described by Josh).
For instance, you export data from a production database into a development database. Some information shouldn't be available to the developers. So we ...
As far as I know, this is not supported, and you can only use tables in this case.
As found in following article which is using Row level security which is not the same as Dynamic Data masking, but it might be linked to each other.
https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ by ...
No, I am not aware of any built-in function that does exactly this. But, you can still accomplish this without doing anything too complicated.
You could use the built-in CRYPT_GEN_RANDOM function (introduced in SQL Server 2008 R2) which generates random values based on a supplied length. The output is in hex/binary values so each byte returned is ...
CRYPT_GEN_RANDOM is your friend for this kind of thing. It creates a simple number of the length you desire, and is about as lightweight as you can get for a cryptographic strength random number. RAND(), in contrast will always generate the same sequence of numbers when called with the same seed value, and as such is definitely not cryptographically secure....
Scrubbing sensitive data is a vast topic. You basically need to define as per your company's data protection policy - what is considered PII (Personally Identifiable Information) or what is sensitive that you don't want other people to see ?
SQL Server 2012* does not have any native tools to mask or scrub sensitive data.
In my company, we have developed in-...
Here's one approach (demo).
Create a view as CRYPT_GEN_RANDOM won't be allowable in a function
CREATE VIEW dbo.LongRandom
SELECT CONVERT(VARCHAR(500), CRYPT_GEN_RANDOM(500), 2) AS random_hex_string
And then call it in a function and Replace all A-F with empty strings. The below uses TRANSLATE to map all B-F to A and a final REPLACE to remove all the ...
Documentation is shamefully silent in regard to what is the behavior when masked column is the part of an expression.
This is how masked column is represented in execution plan:
here (3) denotes masking function type and correspond to partial masking.
And this is how LastName + ', ' + ...
While the docs are light on this, I suspect Stijn is right, that the limitations on Dynamic Data Masking are the same as Row-Level Security (and probably Always Encrypted, too). Sounds like they were applied like a blanket, and I'd have to think more on why certain combinations of features are not supported.
In the meantime, a couple of potential ...
The following code creates a view to provide access to the side-effecting function, CRYPT_GEN_RANDOM. The function calls the view numerous times getting a single byte from CYRPT_GEN_RANDOM for each call.
DROP FUNCTION IF EXISTS dbo.gen_ran_tvf;
DROP VIEW IF EXISTS dbo.gen_ran_view;
CREATE VIEW dbo.gen_ran_view
SELECT cgr = CONVERT(...
A solution that will keep the existing distribution of birth dates is the following: create a new birth date by concatenating year, month and day from three other different existing birth days in the database. Generate three different random numbers i, j, k (that are less than the total number of records), pick year from row i, month from row j, day from row ...
Using the examples in Generating Random Numbers in SQL Server Without Collisions as a starting place, here is something that may work for you.
--set up demo data
--using a recursive CTE, load a table with 1 million pre-allocated random number string
--this took about 10 seconds on my laptop
drop table if exists #temp
Declare @start int, @end int
It was removed from the final version of SSMS 18. Interestingly, no explanation deemed necessary to be given as why it was taken out or will this feature be coming soon.
From SQL Server Management Studio (SSMS) 18.0 released for general availability by Brad Groux:
Removed static data masking (preview) feature – While this feature was available in ...
From what I understand, DMA looks at the column names and tables for keywords that would hold PII, Sensitive Data (such as logins, passwords, etc.), or other unique data attributes. If it finds anything then it should mark it for both dynamic data masking and always encrypted.
If you change the name of the column to not have easily identifiable keywords, ...
As a test, I created a custom user function that does nothing except pass the string back to the caller; it also exhibits this behavior.
Be aware, this code will drop users and objects, so only run it in a test database.
First, we create the required objects, users, and insert some data:
IF OBJECT_ID(N'dbo.EmployeeDataMasking', N'U') IS NOT NULL
Since dynamic data masking is performed right before returning the query result to the user, applying functions such as AVG will not have the masked data as a resultset.
You can see this in the execution plan, the masking function appears right before the select (in other words, returning the data to the user):
While the actual aggreggate happens in the ...
You will not be able to do this using built-in replication. There are no hooks in the replication chain that allow you to manipulate the data as it passes by.
What has worked for me in the past is a copy, mask, publish approach. Take a backup of the production DB and restore it as a new DB. Since this still contains sensitive data I would keep it in a ...
Is it a reasonable approach?
Yes. That's what the (removed) "Static Data Masking" component in SSMS did. A simple way to implement this is to copy data from production using an identity for which Dynamic Data Masking is enforced on the production database, using the Copy Database Wizard or a custom script.
Thanks to @mustaccio's question got me thinking about datatype issues.
NVarchar2 seems to be the issue in this problem.
Creating a copy of the column in varchar2 fixed the issue. Data redaction on varchar2 is working as expected.
Column Masking in Oracle is done via Virtual Private Database (VPD) that is an included ($0) feature of the Oracle Enterprise Edition (EE) Database.
If you want to mask data but still store it unmasked, and you don't want to use the Oracle provided tools (chargeable extra!) then you need to develop an API that does the masking for you.
You store the data as normal in the database.
Clients don't get any access to the tables directly. When they request data, use a "GET" call, which masks out the data to ...
Got it. Well.. I've got one way. Might be kinda slow on a big set:
since you can't use rand() in a function, I stole a view from @Pரதீப் in this post:
and modified it a little bit to look like this:
create VIEW random_val_view
You can also do it using a date table and ordering it by newid().
I've used this technique to scramble lots and lots of data in the past. One advantage is that you can scramble any field by joining the table to itself on rank() over (order by newid())
Note: if your person table is bigger than your date table, in this example, loop the date table insert a ...
You probably need to be more detailed in what you mean by "data scrubbing". I'm assuming that you mean taking a production data and randomising sensitive information (anything that identifies people or organisations such as names, codes, addresses, and so forth).
It is highly unlikely that you will find much by way of automated solutions for that, at least ...