Character mask output for selection

Question

I am using SQL Server 2008.

I would like the character to mask the output of the request.

This is my data from a table column when making a selection:

column1 384844033434 743423547878 111224678885 

I would like to get this conclusion:

 column1 384xxxxxx434 743xxxxxx878 111xxxxxx885 

How can i do this?

Answer 1

You will need to use the view and deny all users access to the database.

Your opinion will look something like this:

 SELECT SUBSTRING(x.SecurityNumber,1,3) + 'xxxxx' + SUBSTRING(x.SecurityNumber,LEN(x.SecurityNumber) - 2, LEN(x.SecurityNumber)) AS column1 FROM underlyingTable x 

Then you can give SELECT users access to this view only and disable masking in the form in which you described.

If you want your client software to be able to insert or update data in this table, you must use the INSTEAD OF INSERT or INSTEAD OF UPDATE trigger to update the base table.

Answer 2

If you know how long your information field will be, you can use the static version, which will give another answer, but you can always create a function to create it:

 CREATE FUNCTION MixUpCharacters ( @OrigVal varchar(max) ) RETURNS varchar(max) AS BEGIN DECLARE @NewVal varchar(max) DECLARE @OrigLen int DECLARE @LoopCt int DECLARE @Part varchar(max) = '' Declare @PartLength int SET @NewVal = '' SET @OrigLen = DATALENGTH(@OrigVal) SET @LoopCt = 1 SET @Part = SUBSTRING(@OrigVal, 4, len(@OrigVal)-6) set @PartLength = LEN(@Part) WHILE @LoopCt <= @PartLength BEGIN -- Current length of possible characters SET @NewVal = @NewVal + 'X' -- Advance the loop SET @LoopCt = @LoopCt + 1 END Return REPLACE(@OrigVal, @Part, @NewVal) END 

For this function, you pass in the values ​​you want to hide. So your query will look like this:

 declare @temp table ( col1 varchar(50) ) insert into @temp values ('384844033434'), ('743423547878'), ('111224678885') select dbo.MixUpCharacters(col1) col1 from @temp 

See SQL Fiddle with Demo

Result:

 | COL1 | ---------------- | 384XXXXXX434 | | 743XXXXXX878 | | 111XXXXXX885 | 

Or is this a way to do this with a recursive CTE:

 ;with data(col1) as ( select '384844033434' union all select '7434235878' union all select '111224678885' ), s1 (col1, repfull) as ( select col1, SUBSTRING(col1, 4, len(col1)-6) repfull from data ), s2 (col1, item, repfull, r) as ( select col1, cast('x' as varchar(max)), right(repfull, LEN(repfull)-1), repfull from s1 union all select col1, 'x'+ cast(item as varchar(max)), right(repfull, LEN(repfull)-1), r from s2 where len(repfull) >0 ) select REPLACE(col1, r, item) newValue from ( select col1, item, R, ROW_NUMBER() over(partition by col1 order by len(item) desc) rn from s2 ) src where rn = 1 

See SQL Fiddle with Demo

Answer 3

A simple select query only returns that it is on the table, regardless of whether it is encrypted or not.

So, I think you cannot do this at the database level.

For your requirement, you will need a bidirectional encryption algorithm for use in your application, so you can encrypt the data before storing it in encrypted form in the database, and get the encrypted information from the database and decrypt it in your application.

Answer 4

very simple brother

 SELECT CONCAT (SUBSTR('Your string',1,3),LPAD(SUBSTR('Your string',-3),LENGTH('Your string')-1,'*')) RESULT FROM dual 

output:

You ******* ing

if it num converts the type to char

Answer 5

If you want to hide a field that you do not know about the length of the field. You can update the bluefeet code as follows:

 ALTER FUNCTION MixUpCharacters ( @OrigVal varchar(MAX) ) RETURNS varchar(MAX) AS BEGIN DECLARE @NewVal NVARCHAR(MAX) DECLARE @OrigLen INT DECLARE @LoopCt INT DECLARE @Part NVARCHAR(MAX) = '' DECLARE @PartLength INT -- MastLength DECLARE @PartStartIndex INT -- MaskStartIndex SET @NewVal = '' SET @LoopCt = 1 SET @OrigLen = LEN(@OrigVal) IF(@OrigLen = 1) BEGIN RETURN 'X' END IF(@OrigLen < 6) BEGIN SET @PartStartIndex = @OrigLen / 2 SET @PartLength = @OrigLen - @PartStartIndex SET @Part = SUBSTRING(@OrigVal, @PartStartIndex, @PartLength) END ELSE IF(@OrigLen < 8) BEGIN SET @PartStartIndex = 3 SET @PartLength = @OrigLen - @PartStartIndex - 1 SET @Part = SUBSTRING(@OrigVal, @PartStartIndex, @PartLength) END ELSE BEGIN SET @PartStartIndex = 4 SET @PartLength = @OrigLen - @PartStartIndex - 2 SET @Part = SUBSTRING(@OrigVal, @PartStartIndex, @PartLength) END WHILE @LoopCt <= @PartLength BEGIN -- Current length of possible characters SET @NewVal = @NewVal + 'X' -- Advance the loop SET @LoopCt = @LoopCt + 1 END RETURN REPLACE(@OrigVal, @Part, @NewVal) END 

You can test the following:

 SELECT dbo.MixUpCharacters('1') UNION ALL SELECT dbo.MixUpCharacters('12') UNION ALL SELECT dbo.MixUpCharacters('123') UNION ALL SELECT dbo.MixUpCharacters('1234') UNION ALL SELECT dbo.MixUpCharacters('12345') UNION ALL SELECT dbo.MixUpCharacters('123456') UNION ALL SELECT dbo.MixUpCharacters('1234567') UNION ALL SELECT dbo.MixUpCharacters('12345678') UNION ALL SELECT dbo.MixUpCharacters('123456789') UNION ALL select dbo.MixUpCharacters('1234567890') UNION ALL select dbo.MixUpCharacters('12345678910') 

Results:

 X X2 XX3 1XX4 1XXX5 12XX56 12XXX67 123XX678 123XXX789 123XXXX890 123XXXXX910 

Answer 6

From SQL Server 2016+ you can use Dynamic Data Masking .

Masking dynamic data limits data privacy by masking it for non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by allowing customers to determine how much of the sensitive data will be displayed with minimal impact on the application layer. Its data protection function, which hides confidential data in the result set of the request for the specified fields of the database, and the data in the database are not changed. Dynamic data masking is easy to use with existing applications because masking rules apply in query results. Many applications can mask sensitive data without modifying existing queries.

 CREATE TABLE #tab(ID INT IDENTITY(1,1), column1 VARCHAR(12)); INSERT INTO #tab(column1) VALUES('384844033434'),('743423547878'),('111224678885'); SELECT * FROM #tab; 

Output:

 ╔════╦══════════════╗ ║ ID ║ column1 ║ ╠════╬══════════════╣ ║ 1 ║ 384844033434 ║ ║ 2 ║ 743423547878 ║ ║ 3 ║ 111224678885 ║ ╚════╩══════════════╝ ALTER TABLE #tab ALTER COLUMN column1 VARCHAR(12) MASKED WITH (FUNCTION = 'partial(3,"xxxxxx",3)'); SELECT * FROM #tab; 

Output:

 ╔════╦══════════════╗ ║ ID ║ column1 ║ ╠════╬══════════════╣ ║ 1 ║ 384xxxxxx434 ║ ║ 2 ║ 743xxxxxx878 ║ ║ 3 ║ 111xxxxxx885 ║ ╚════╩══════════════╝ 

LiveDemo