Select from table while masking a column
1
I'd like to export a table while masking a specific column, something like:
SELECT randomMask(userId), name, location FROM travel_location;
Requirements:
- Masked column must be random but consistent for that query. Eg 2345 will always be randomized as abwr for that query.
- Masked column must not be able to be decoded. abwr should not be able to be decoded into 2345 by me after running the query.
- Every query should randomly mask the id differently, 2345 may be masked as abwr this time, but it should be something else next time.
example result:
sql
sql-server sql-server-2014 asked Nov 11 at 21:34
Chun Yin
233110
add a comment |
1
I'd like to export a table while masking a specific column, something like:
SELECT randomMask(userId), name, location FROM travel_location;
Requirements:
- Masked column must be random but consistent for that query. Eg 2345 will always be randomized as abwr for that query.
- Masked column must not be able to be decoded. abwr should not be able to be decoded into 2345 by me after running the query.
- Every query should randomly mask the id differently, 2345 may be masked as abwr this time, but it should be something else next time.
example result:
sql
sql-server sql-server-2014 asked Nov 11 at 21:34
Chun Yin
233110
1
So, you want a "random" value every time, that can't be repeated but should be consistent for every instance of that value, and can't be traced back to its original value (by anyone?).
– Larnu
Nov 11 at 21:39
Also, you've tagged 3 different versions of SQL Server, all of which are out of support, and 2008 is basically out of extended support too. What version are you really using? Tag that one, and that one only. I've removed the tags for the moment; add the relevant one back please. If you are using so an old version of SQL Server though you should be looking at upgrading (to be honest, you should have been looking into that years ago).
– Larnu
Nov 11 at 21:42
Sorry, didn't understand how the tags worked, fixed now. And yes, your assumption above is correct.
– Chun Yin
Nov 11 at 22:08
Given all of these requirements you will probably be best off just creating a temp table with all the distinct userid first and another column with a randomly generated string, then join on that rather than expecting a function.
– Martin Smith
Nov 11 at 22:14
What about if I'm allowed to use a mask key? Meaning it can only be decoded if I have the key
– Chun Yin
Nov 11 at 23:22
add a comment |
1
1
1
1
I'd like to export a table while masking a specific column, something like:
SELECT randomMask(userId), name, location FROM travel_location;
Requirements:
- Masked column must be random but consistent for that query. Eg 2345 will always be randomized as abwr for that query.
- Masked column must not be able to be decoded. abwr should not be able to be decoded into 2345 by me after running the query.
- Every query should randomly mask the id differently, 2345 may be masked as abwr this time, but it should be something else next time.
example result:
sql
sql-server sql-server-2014 asked Nov 11 at 21:34
Chun Yin
233110
I'd like to export a table while masking a specific column, something like:
SELECT randomMask(userId), name, location FROM travel_location;
Requirements:
- Masked column must be random but consistent for that query. Eg 2345 will always be randomized as abwr for that query.
- Masked column must not be able to be decoded. abwr should not be able to be decoded into 2345 by me after running the query.
- Every query should randomly mask the id differently, 2345 may be masked as abwr this time, but it should be something else next time.
example result:
sql
sql-server sql-server-2014 sql
sql-server sql-server-2014 asked Nov 11 at 21:34
Chun Yin
233110
asked Nov 11 at 21:34
Chun Yin
233110
edited Nov 11 at 22:07
asked Nov 11 at 21:34
Chun Yin
233110
asked Nov 11 at 21:34
Chun Yin
233110
asked Nov 11 at 21:34
Chun Yin
233110
233110
1
So, you want a "random" value every time, that can't be repeated but should be consistent for every instance of that value, and can't be traced back to its original value (by anyone?).
– Larnu
Nov 11 at 21:39
Also, you've tagged 3 different versions of SQL Server, all of which are out of support, and 2008 is basically out of extended support too. What version are you really using? Tag that one, and that one only. I've removed the tags for the moment; add the relevant one back please. If you are using so an old version of SQL Server though you should be looking at upgrading (to be honest, you should have been looking into that years ago).
– Larnu
Nov 11 at 21:42
Sorry, didn't understand how the tags worked, fixed now. And yes, your assumption above is correct.
– Chun Yin
Nov 11 at 22:08
Given all of these requirements you will probably be best off just creating a temp table with all the distinct userid first and another column with a randomly generated string, then join on that rather than expecting a function.
– Martin Smith
Nov 11 at 22:14
What about if I'm allowed to use a mask key? Meaning it can only be decoded if I have the key
– Chun Yin
Nov 11 at 23:22
add a comment |
1
So, you want a "random" value every time, that can't be repeated but should be consistent for every instance of that value, and can't be traced back to its original value (by anyone?).
– Larnu
Nov 11 at 21:39
Also, you've tagged 3 different versions of SQL Server, all of which are out of support, and 2008 is basically out of extended support too. What version are you really using? Tag that one, and that one only. I've removed the tags for the moment; add the relevant one back please. If you are using so an old version of SQL Server though you should be looking at upgrading (to be honest, you should have been looking into that years ago).
– Larnu
Nov 11 at 21:42
Sorry, didn't understand how the tags worked, fixed now. And yes, your assumption above is correct.
– Chun Yin
Nov 11 at 22:08
Given all of these requirements you will probably be best off just creating a temp table with all the distinct userid first and another column with a randomly generated string, then join on that rather than expecting a function.
– Martin Smith
Nov 11 at 22:14
What about if I'm allowed to use a mask key? Meaning it can only be decoded if I have the key
– Chun Yin
Nov 11 at 23:22
1
1
So, you want a "random" value every time, that can't be repeated but should be consistent for every instance of that value, and can't be traced back to its original value (by anyone?).
– Larnu
Nov 11 at 21:39
So, you want a "random" value every time, that can't be repeated but should be consistent for every instance of that value, and can't be traced back to its original value (by anyone?).
– Larnu
Nov 11 at 21:39
Also, you've tagged 3 different versions of SQL Server, all of which are out of support, and 2008 is basically out of extended support too. What version are you really using? Tag that one, and that one only. I've removed the tags for the moment; add the relevant one back please. If you are using so an old version of SQL Server though you should be looking at upgrading (to be honest, you should have been looking into that years ago).
– Larnu
Nov 11 at 21:42
Also, you've tagged 3 different versions of SQL Server, all of which are out of support, and 2008 is basically out of extended support too. What version are you really using? Tag that one, and that one only. I've removed the tags for the moment; add the relevant one back please. If you are using so an old version of SQL Server though you should be looking at upgrading (to be honest, you should have been looking into that years ago).
– Larnu
Nov 11 at 21:42
Sorry, didn't understand how the tags worked, fixed now. And yes, your assumption above is correct.
– Chun Yin
Nov 11 at 22:08
Sorry, didn't understand how the tags worked, fixed now. And yes, your assumption above is correct.
– Chun Yin
Nov 11 at 22:08
Given all of these requirements you will probably be best off just creating a temp table with all the distinct userid first and another column with a randomly generated string, then join on that rather than expecting a function.
– Martin Smith
Nov 11 at 22:14
Given all of these requirements you will probably be best off just creating a temp table with all the distinct userid first and another column with a randomly generated string, then join on that rather than expecting a function.
– Martin Smith
Nov 11 at 22:14
What about if I'm allowed to use a mask key? Meaning it can only be decoded if I have the key
– Chun Yin
Nov 11 at 23:22
What about if I'm allowed to use a mask key? Meaning it can only be decoded if I have the key
– Chun Yin
Nov 11 at 23:22
add a comment |
2 Answers
2
active
oldest
votes
1
You can use checksum() to get a reasonable random value:
select checksum(userId)
Of course, there are collisions, but that is probably acceptable.
Alas, this will be the same each time you run the query. To fix that, you could add a random number or use the time:
select checksum(userid + rand())
or:
select checksum(convert(varchar(255), userid) + convert(varchar(255), getdate())
Both rand() and getdate() are guaranteed to be the same on all rows for for each expression they occur in. However, they will vary from query to query.
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
Thanks, it worked perfectly. I used the rand() and had to convert it btw. Not sure why getdate() was returning me the same value every time tho.
– Chun Yin
Nov 12 at 1:22
add a comment |
0
You can try (works for SQL Server 2016 or more):
ALTER Table travel_location
ALTER COLUMN userId ADD MASKED WITH (FUNCTION='Random(1,100000)')
The users who were not granted to unmask like below wouldn't see the real value after this change.
GRANT UNMASK TO TestUser;
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
I may be recalling wrong, but wasn't Dynamic Data Masking introduced with SQL Server 2016? Although I've just removed them, the OP had tagged SQL Server 2000-2008 when this answer was made.
– Larnu
Nov 11 at 21:44
Does this meet all three of the requirements in the Q?
– Martin Smith
Nov 11 at 22:18
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253460%2fselect-from-table-while-masking-a-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can use checksum() to get a reasonable random value:
select checksum(userId)
Of course, there are collisions, but that is probably acceptable.
Alas, this will be the same each time you run the query. To fix that, you could add a random number or use the time:
select checksum(userid + rand())
or:
select checksum(convert(varchar(255), userid) + convert(varchar(255), getdate())
Both rand() and getdate() are guaranteed to be the same on all rows for for each expression they occur in. However, they will vary from query to query.
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
Thanks, it worked perfectly. I used the rand() and had to convert it btw. Not sure why getdate() was returning me the same value every time tho.
– Chun Yin
Nov 12 at 1:22
add a comment |
1
You can use checksum() to get a reasonable random value:
select checksum(userId)
Of course, there are collisions, but that is probably acceptable.
Alas, this will be the same each time you run the query. To fix that, you could add a random number or use the time:
select checksum(userid + rand())
or:
select checksum(convert(varchar(255), userid) + convert(varchar(255), getdate())
Both rand() and getdate() are guaranteed to be the same on all rows for for each expression they occur in. However, they will vary from query to query.
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
Thanks, it worked perfectly. I used the rand() and had to convert it btw. Not sure why getdate() was returning me the same value every time tho.
– Chun Yin
Nov 12 at 1:22
add a comment |
1
1
1
You can use checksum() to get a reasonable random value:
select checksum(userId)
Of course, there are collisions, but that is probably acceptable.
Alas, this will be the same each time you run the query. To fix that, you could add a random number or use the time:
select checksum(userid + rand())
or:
select checksum(convert(varchar(255), userid) + convert(varchar(255), getdate())
Both rand() and getdate() are guaranteed to be the same on all rows for for each expression they occur in. However, they will vary from query to query.
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
You can use checksum() to get a reasonable random value:
select checksum(userId)
Of course, there are collisions, but that is probably acceptable.
Alas, this will be the same each time you run the query. To fix that, you could add a random number or use the time:
select checksum(userid + rand())
or:
select checksum(convert(varchar(255), userid) + convert(varchar(255), getdate())
Both rand() and getdate() are guaranteed to be the same on all rows for for each expression they occur in. However, they will vary from query to query.
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
answered Nov 11 at 23:22
Gordon Linoff
757k35291399
757k35291399
Thanks, it worked perfectly. I used the rand() and had to convert it btw. Not sure why getdate() was returning me the same value every time tho.
– Chun Yin
Nov 12 at 1:22
add a comment |
Thanks, it worked perfectly. I used the rand() and had to convert it btw. Not sure why getdate() was returning me the same value every time tho.
– Chun Yin
Nov 12 at 1:22
Thanks, it worked perfectly. I used the rand() and had to convert it btw. Not sure why getdate() was returning me the same value every time tho.
– Chun Yin
Nov 12 at 1:22
Thanks, it worked perfectly. I used the rand() and had to convert it btw. Not sure why getdate() was returning me the same value every time tho.
– Chun Yin
Nov 12 at 1:22
add a comment |
0
You can try (works for SQL Server 2016 or more):
ALTER Table travel_location
ALTER COLUMN userId ADD MASKED WITH (FUNCTION='Random(1,100000)')
The users who were not granted to unmask like below wouldn't see the real value after this change.
GRANT UNMASK TO TestUser;
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
I may be recalling wrong, but wasn't Dynamic Data Masking introduced with SQL Server 2016? Although I've just removed them, the OP had tagged SQL Server 2000-2008 when this answer was made.
– Larnu
Nov 11 at 21:44
Does this meet all three of the requirements in the Q?
– Martin Smith
Nov 11 at 22:18
add a comment |
0
You can try (works for SQL Server 2016 or more):
ALTER Table travel_location
ALTER COLUMN userId ADD MASKED WITH (FUNCTION='Random(1,100000)')
The users who were not granted to unmask like below wouldn't see the real value after this change.
GRANT UNMASK TO TestUser;
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
I may be recalling wrong, but wasn't Dynamic Data Masking introduced with SQL Server 2016? Although I've just removed them, the OP had tagged SQL Server 2000-2008 when this answer was made.
– Larnu
Nov 11 at 21:44
Does this meet all three of the requirements in the Q?
– Martin Smith
Nov 11 at 22:18
add a comment |
0
0
0
You can try (works for SQL Server 2016 or more):
ALTER Table travel_location
ALTER COLUMN userId ADD MASKED WITH (FUNCTION='Random(1,100000)')
The users who were not granted to unmask like below wouldn't see the real value after this change.
GRANT UNMASK TO TestUser;
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
You can try (works for SQL Server 2016 or more):
ALTER Table travel_location
ALTER COLUMN userId ADD MASKED WITH (FUNCTION='Random(1,100000)')
The users who were not granted to unmask like below wouldn't see the real value after this change.
GRANT UNMASK TO TestUser;
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
edited Nov 11 at 21:45
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
answered Nov 11 at 21:40
Eray Balkanli
3,89241943
3,89241943
I may be recalling wrong, but wasn't Dynamic Data Masking introduced with SQL Server 2016? Although I've just removed them, the OP had tagged SQL Server 2000-2008 when this answer was made.
– Larnu
Nov 11 at 21:44
Does this meet all three of the requirements in the Q?
– Martin Smith
Nov 11 at 22:18
add a comment |
I may be recalling wrong, but wasn't Dynamic Data Masking introduced with SQL Server 2016? Although I've just removed them, the OP had tagged SQL Server 2000-2008 when this answer was made.
– Larnu
Nov 11 at 21:44
Does this meet all three of the requirements in the Q?
– Martin Smith
Nov 11 at 22:18
I may be recalling wrong, but wasn't Dynamic Data Masking introduced with SQL Server 2016? Although I've just removed them, the OP had tagged SQL Server 2000-2008 when this answer was made.
– Larnu
Nov 11 at 21:44
I may be recalling wrong, but wasn't Dynamic Data Masking introduced with SQL Server 2016? Although I've just removed them, the OP had tagged SQL Server 2000-2008 when this answer was made.
– Larnu
Nov 11 at 21:44
Does this meet all three of the requirements in the Q?
– Martin Smith
Nov 11 at 22:18
Does this meet all three of the requirements in the Q?
– Martin Smith
Nov 11 at 22:18
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253460%2fselect-from-table-while-masking-a-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
So, you want a "random" value every time, that can't be repeated but should be consistent for every instance of that value, and can't be traced back to its original value (by anyone?).
– Larnu
Nov 11 at 21:39
Also, you've tagged 3 different versions of SQL Server, all of which are out of support, and 2008 is basically out of extended support too. What version are you really using? Tag that one, and that one only. I've removed the tags for the moment; add the relevant one back please. If you are using so an old version of SQL Server though you should be looking at upgrading (to be honest, you should have been looking into that years ago).
– Larnu
Nov 11 at 21:42
Sorry, didn't understand how the tags worked, fixed now. And yes, your assumption above is correct.
– Chun Yin
Nov 11 at 22:08
Given all of these requirements you will probably be best off just creating a temp table with all the distinct userid first and another column with a randomly generated string, then join on that rather than expecting a function.
– Martin Smith
Nov 11 at 22:14
What about if I'm allowed to use a mask key? Meaning it can only be decoded if I have the key
– Chun Yin
Nov 11 at 23:22