Character mask output data on select
Solution 1
You would have to use a view, and deny all users SELECT access to the underlying table.
Your view would look something like
SELECT
SUBSTRING(x.SecurityNumber,1,3) +
'xxxxx' +
SUBSTRING(x.SecurityNumber,LEN(x.SecurityNumber) - 2, LEN(x.SecurityNumber))
AS column1
FROM underlyingTable x
You could then grant your users SELECT access to just this view and have the out masked in the way you described.
If you wanted your client software to be able to insert or update data in this table, you would use an INSTEAD OF INSERT or INSTEAD OF UPDATE trigger to update the base table.
Solution 2
From SQL Server 2016+
you could use Dynamic Data Masking feature.
Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the 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 ║
╚════╩══════════════╝
Solution 3
If you know how long your field of data will be, then you can use the static version that the other answer will produce, but you can always create a function to generate this:
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 will pass in the values that you want to mask. So your query would be:
declare @temp table
(
col1 varchar(50)
)
insert into @temp
values ('384844033434'), ('743423547878'), ('111224678885')
select dbo.MixUpCharacters(col1) col1
from @temp
The result would be:
| COL1 |
----------------
| 384XXXXXX434 |
| 743XXXXXX878 |
| 111XXXXXX885 |
Or here is a way to do it with 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
Solution 4
very simple bro
SELECT CONCAT (SUBSTR('Your string',1,3),LPAD(SUBSTR('Your string',-3),LENGTH('Your string')-1,'*')) RESULT FROM dual
output :
You*******ing
if it is num
type convert to char
Solution 5
A simple select query would return just what it's on the table, no matter if it's encrypted or not.
So, I think you can not do this on the database level.
For your requirement, you would need a bidirectional encryption algorithm to use in your application, so you can encrypt data before saving it encrypted on the database, and get the encrypted information from the database and decrypt it on your applicaition.
pyram
Updated on July 05, 2022Comments
-
pyram almost 2 years
I'm using SQL Server 2008.
I would like to character mask the output data of a query.
This is my data from a column on a table when doing a select:
column1 384844033434 743423547878 111224678885
I would like an output like this:
column1 384xxxxxx434 743xxxxxx878 111xxxxxx885
How can I do this?
-
David about 11 yearsI am using sqlcmd for SQL Server 2008 and found that the offsets are different. For correct output, I used
SUBSTRING(x.SecurityNumber,1,3) + 'xxxxx' + SUBSTRING(x.SecurityNumber,LEN(x.SecurityNumber) - 2, LEN(x.SecurityNumber))
-
Dave Markle about 11 yearsThanks @David. Edited accordingly.
-
Jeson Martajaya almost 6 yearsIs this Oracle SQL ? Author is asking for T-SQL solution.