SQL 选择时的字符掩码输出数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13848872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Character mask output data on select
提问by pyram
I'm using SQL Server 2008.
我正在使用 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?
我怎样才能做到这一点？
采纳答案by Dave Markle
You would have to use a view, and deny all users SELECT access to the underlying table.
您将不得不使用视图，并拒绝所有用户对基础表的 SELECT 访问。
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.
然后，您可以授予您的用户仅此视图的 SELECT 访问权限，并按照您描述的方式屏蔽输出。
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.
如果您希望您的客户端软件能够在该表中插入或更新数据，您将使用 INSTEAD OF INSERT 或 INSTEAD OF UPDATE 触发器来更新基表。
回答by Taryn
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:
或者这里有一种使用递归 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
回答by Lukasz Szozda
From SQL Server 2016+you could use Dynamic Data Maskingfeature.
从SQL Server 2016+您可以使用动态数据屏蔽功能。
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 ║
╚════╩══════════════╝
回答by Nathan
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.
根据您的要求，您需要在您的应用程序中使用双向加密算法，以便您可以在将数据加密保存到数据库之前对其进行加密，并从数据库中获取加密信息并在您的应用程序上对其进行解密。
回答by Srinivas
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 numtype convert to char
如果它是num类型转换为char
回答by Can OTUR
If you want to mask field which you dont know about field's length. You can upgrade bluefeet's code like this:
如果您想屏蔽您不知道字段长度的字段。您可以像这样升级 bluefeet 的代码：
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 like this:
你可以这样测试：
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
