Hash numbers in OracleGet list of all tables in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?Oracle: SQL query that returns rows with only numeric valuesAdd fraction constraint to a column in oracleSQL: Concatenate sequential integer valueshash function to generate hash value with only 5 digits in javaOracle numeric string column and indexingRedshift vs Oracle NUMBER datatype differenceFind out columns values associated to more than one columns values in OracleHow to increment the value of the unique constraint column value in ORACLE
What if somebody invests in my application?
What is the opposite of 'gravitas'?
Is exact Kanji stroke length important?
Adding empty element to declared container without declaring type of element
Did US corporations pay demonstrators in the German demonstrations against article 13?
A social experiment. What is the worst that can happen?
How can I successfully establish a nationwide combat training program for a large country?
Greatest common substring
How do ultrasonic sensors differentiate between transmitted and received signals?
Are Warlocks Arcane or Divine?
Organic chemistry Iodoform Reaction
A workplace installs custom certificates on personal devices, can this be used to decrypt HTTPS traffic?
A known event to a history junkie
Hostile work environment after whistle-blowing on coworker and our boss. What do I do?
Lifted its hind leg on or lifted its hind leg towards?
What do you call the infoboxes with text and sometimes images on the side of a page we find in textbooks?
Books on the History of math research at European universities
The One-Electron Universe postulate is true - what simple change can I make to change the whole universe?
Teaching indefinite integrals that require special-casing
What (else) happened July 1st 1858 in London?
Why are on-board computers allowed to change controls without notifying the pilots?
Can a malicious addon access internet history and such in chrome/firefox?
Pronouncing Homer as in modern Greek
Would it be legal for a US State to ban exports of a natural resource?
Hash numbers in Oracle
Get list of all tables in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?Oracle: SQL query that returns rows with only numeric valuesAdd fraction constraint to a column in oracleSQL: Concatenate sequential integer valueshash function to generate hash value with only 5 digits in javaOracle numeric string column and indexingRedshift vs Oracle NUMBER datatype differenceFind out columns values associated to more than one columns values in OracleHow to increment the value of the unique constraint column value in ORACLE
I have a column in table
table1
user_id
123456
123457
123458
123459
123460
I need to generate secure and 1 way hashed values which is 12 digits, non-sequential, no continuous zeros in the number. The hashed value should not overlap (means unique) and should ONLY be a NUMERIC. Something like:
table1
user_id updated_user_id
123456 356456456345
123457 312412012412
123458 340475434545
123459 355344356461
123460 576898973434
I am aware of ORA_HASH, however it only outputs max 10 digits and I would like 12 digits.
Is there a way in Oracle SQL to achieve this?
sql oracle
asked Mar 8 at 8:11
dangdang
59611943
|
show 2 more comments
I have a column in table
table1
user_id
123456
123457
123458
123459
123460
I need to generate secure and 1 way hashed values which is 12 digits, non-sequential, no continuous zeros in the number. The hashed value should not overlap (means unique) and should ONLY be a NUMERIC. Something like:
table1
user_id updated_user_id
123456 356456456345
123457 312412012412
123458 340475434545
123459 355344356461
123460 576898973434
I am aware of ORA_HASH, however it only outputs max 10 digits and I would like 12 digits.
Is there a way in Oracle SQL to achieve this?
sql oracle
asked Mar 8 at 8:11
dangdang
59611943
3
Forget Oracle for a moment - is there any common has function that can match those requirements?It seems very unlikely, not least because they seem arbitrary and unhelpful - what's the benefit of the hash being non-sequential or not having repeated zeros? (Some of your example outputs have sequential runs - the first contains 456 twice and 345 - or is that not what you meant?). If that's really what you want then you'll have to design your own algorithm, and then implement it in SQL or PL/SQL.
– Alex Poole
Mar 8 at 8:52
2
Following on from the excellent points raised by @alexpoole, what is your purpose in hashing these numbers? If it's for any form of security you should really think hard about your requirements. Because they aren't going to deliver strong encryption. 12 digits is too small a space to guarantee no hash collisions. Rolling your own hashing algorithm to handle that and the other constraints is weaker than using Oracle built-in features.
– APC
Mar 8 at 9:26
@APC - thank you for your suggestions. I'm looking at security. What would be the best way to do it? Should I use more digits? What is the best mechanism to ensure the user ID is secured?
– dang
Mar 8 at 9:29
2
@dang: what exactly does "secured" mean with regards to an ID? Why is576898973434"more secure" than 7654398? If you want some random unique IDs to prevent e.g. guessing of IDs then use a UUID. But if "guessing" a valid ID is a problem for your application, then the security in your application that controls access to information connected to that user has serious problems.
– a_horse_with_no_name
Mar 8 at 9:40
2
Well as APC has pointed out 12 digits is to short anyway and doesn't give you any "security" at all. Either do it right, or stick with the IDs you have. It's probably more secure to never expose those IDs outside of your application.
– a_horse_with_no_name
Mar 8 at 9:48
|
show 2 more comments
I have a column in table
table1
user_id
123456
123457
123458
123459
123460
I need to generate secure and 1 way hashed values which is 12 digits, non-sequential, no continuous zeros in the number. The hashed value should not overlap (means unique) and should ONLY be a NUMERIC. Something like:
table1
user_id updated_user_id
123456 356456456345
123457 312412012412
123458 340475434545
123459 355344356461
123460 576898973434
I am aware of ORA_HASH, however it only outputs max 10 digits and I would like 12 digits.
Is there a way in Oracle SQL to achieve this?
sql oracle
asked Mar 8 at 8:11
dangdang
59611943
I have a column in table
table1
user_id
123456
123457
123458
123459
123460
I need to generate secure and 1 way hashed values which is 12 digits, non-sequential, no continuous zeros in the number. The hashed value should not overlap (means unique) and should ONLY be a NUMERIC. Something like:
table1
user_id updated_user_id
123456 356456456345
123457 312412012412
123458 340475434545
123459 355344356461
123460 576898973434
I am aware of ORA_HASH, however it only outputs max 10 digits and I would like 12 digits.
Is there a way in Oracle SQL to achieve this?
sql oracle
sql oracle
asked Mar 8 at 8:11
dangdang
59611943
asked Mar 8 at 8:11
dangdang
59611943
edited Mar 8 at 8:21
dang
asked Mar 8 at 8:11
dangdang
59611943
asked Mar 8 at 8:11
dangdang
59611943
asked Mar 8 at 8:11
dangdang
59611943
59611943
3
Forget Oracle for a moment - is there any common has function that can match those requirements?It seems very unlikely, not least because they seem arbitrary and unhelpful - what's the benefit of the hash being non-sequential or not having repeated zeros? (Some of your example outputs have sequential runs - the first contains 456 twice and 345 - or is that not what you meant?). If that's really what you want then you'll have to design your own algorithm, and then implement it in SQL or PL/SQL.
– Alex Poole
Mar 8 at 8:52
2
Following on from the excellent points raised by @alexpoole, what is your purpose in hashing these numbers? If it's for any form of security you should really think hard about your requirements. Because they aren't going to deliver strong encryption. 12 digits is too small a space to guarantee no hash collisions. Rolling your own hashing algorithm to handle that and the other constraints is weaker than using Oracle built-in features.
– APC
Mar 8 at 9:26
@APC - thank you for your suggestions. I'm looking at security. What would be the best way to do it? Should I use more digits? What is the best mechanism to ensure the user ID is secured?
– dang
Mar 8 at 9:29
2
@dang: what exactly does "secured" mean with regards to an ID? Why is576898973434"more secure" than 7654398? If you want some random unique IDs to prevent e.g. guessing of IDs then use a UUID. But if "guessing" a valid ID is a problem for your application, then the security in your application that controls access to information connected to that user has serious problems.
– a_horse_with_no_name
Mar 8 at 9:40
2
Well as APC has pointed out 12 digits is to short anyway and doesn't give you any "security" at all. Either do it right, or stick with the IDs you have. It's probably more secure to never expose those IDs outside of your application.
– a_horse_with_no_name
Mar 8 at 9:48
|
show 2 more comments
3
Forget Oracle for a moment - is there any common has function that can match those requirements?It seems very unlikely, not least because they seem arbitrary and unhelpful - what's the benefit of the hash being non-sequential or not having repeated zeros? (Some of your example outputs have sequential runs - the first contains 456 twice and 345 - or is that not what you meant?). If that's really what you want then you'll have to design your own algorithm, and then implement it in SQL or PL/SQL.
– Alex Poole
Mar 8 at 8:52
2
Following on from the excellent points raised by @alexpoole, what is your purpose in hashing these numbers? If it's for any form of security you should really think hard about your requirements. Because they aren't going to deliver strong encryption. 12 digits is too small a space to guarantee no hash collisions. Rolling your own hashing algorithm to handle that and the other constraints is weaker than using Oracle built-in features.
– APC
Mar 8 at 9:26
@APC - thank you for your suggestions. I'm looking at security. What would be the best way to do it? Should I use more digits? What is the best mechanism to ensure the user ID is secured?
– dang
Mar 8 at 9:29
2
@dang: what exactly does "secured" mean with regards to an ID? Why is576898973434"more secure" than 7654398? If you want some random unique IDs to prevent e.g. guessing of IDs then use a UUID. But if "guessing" a valid ID is a problem for your application, then the security in your application that controls access to information connected to that user has serious problems.
– a_horse_with_no_name
Mar 8 at 9:40
2
Well as APC has pointed out 12 digits is to short anyway and doesn't give you any "security" at all. Either do it right, or stick with the IDs you have. It's probably more secure to never expose those IDs outside of your application.
– a_horse_with_no_name
Mar 8 at 9:48
3
3
Forget Oracle for a moment - is there any common has function that can match those requirements?It seems very unlikely, not least because they seem arbitrary and unhelpful - what's the benefit of the hash being non-sequential or not having repeated zeros? (Some of your example outputs have sequential runs - the first contains 456 twice and 345 - or is that not what you meant?). If that's really what you want then you'll have to design your own algorithm, and then implement it in SQL or PL/SQL.
– Alex Poole
Mar 8 at 8:52
Forget Oracle for a moment - is there any common has function that can match those requirements?It seems very unlikely, not least because they seem arbitrary and unhelpful - what's the benefit of the hash being non-sequential or not having repeated zeros? (Some of your example outputs have sequential runs - the first contains 456 twice and 345 - or is that not what you meant?). If that's really what you want then you'll have to design your own algorithm, and then implement it in SQL or PL/SQL.
– Alex Poole
Mar 8 at 8:52
2
2
Following on from the excellent points raised by @alexpoole, what is your purpose in hashing these numbers? If it's for any form of security you should really think hard about your requirements. Because they aren't going to deliver strong encryption. 12 digits is too small a space to guarantee no hash collisions. Rolling your own hashing algorithm to handle that and the other constraints is weaker than using Oracle built-in features.
– APC
Mar 8 at 9:26
Following on from the excellent points raised by @alexpoole, what is your purpose in hashing these numbers? If it's for any form of security you should really think hard about your requirements. Because they aren't going to deliver strong encryption. 12 digits is too small a space to guarantee no hash collisions. Rolling your own hashing algorithm to handle that and the other constraints is weaker than using Oracle built-in features.
– APC
Mar 8 at 9:26
@APC - thank you for your suggestions. I'm looking at security. What would be the best way to do it? Should I use more digits? What is the best mechanism to ensure the user ID is secured?
– dang
Mar 8 at 9:29
@APC - thank you for your suggestions. I'm looking at security. What would be the best way to do it? Should I use more digits? What is the best mechanism to ensure the user ID is secured?
– dang
Mar 8 at 9:29
2
2
@dang: what exactly does "secured" mean with regards to an ID? Why is
576898973434 "more secure" than 7654398? If you want some random unique IDs to prevent e.g. guessing of IDs then use a UUID. But if "guessing" a valid ID is a problem for your application, then the security in your application that controls access to information connected to that user has serious problems.– a_horse_with_no_name
Mar 8 at 9:40
@dang: what exactly does "secured" mean with regards to an ID? Why is
576898973434 "more secure" than 7654398? If you want some random unique IDs to prevent e.g. guessing of IDs then use a UUID. But if "guessing" a valid ID is a problem for your application, then the security in your application that controls access to information connected to that user has serious problems.– a_horse_with_no_name
Mar 8 at 9:40
2
2
Well as APC has pointed out 12 digits is to short anyway and doesn't give you any "security" at all. Either do it right, or stick with the IDs you have. It's probably more secure to never expose those IDs outside of your application.
– a_horse_with_no_name
Mar 8 at 9:48
Well as APC has pointed out 12 digits is to short anyway and doesn't give you any "security" at all. Either do it right, or stick with the IDs you have. It's probably more secure to never expose those IDs outside of your application.
– a_horse_with_no_name
Mar 8 at 9:48
|
show 2 more comments
4 Answers
4
active
oldest
votes
I would be careful about adding hash values. This can affect collisions. So:
UPDATE TABLE1
SET updated_user_id = (ORA_HASH(user_id, 1000000000, 0) - 1) * 1000 +
ORA_HASH(user_id, 1000, 1) - 1;
You should realize that hash values can have collisions. So, this is not 100% certain that the results will be unique. If guaranteed uniqueness is important you might want to combine a hash with some manipulation of the user id -- arithmetic or bitwise operations.
edited Mar 11 at 11:30
Peter Paff
454412
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
Uniqueness is very important in generating. How can I use hash but make sure that it is unique?
– dang
Mar 9 at 13:30
@dang . . . You haven't provided enough information in your question to answer this. It depends on what the purpose of the hashing is -- is it mean to be strong encryption or merely obfuscation? How many different user ids are there? Do they change over time? I would suggest that you ask another question with more details.
– Gordon Linoff
Mar 9 at 13:35
The purpose is obfuscation, so that if someone hacks into the database, he should not be able to identify the user ID. We have over 100 million user IDs in the system which are sent by a customer. We want to change the user IDs, so they cannot construct it back. I will ask another question with more details.
– dang
Mar 9 at 13:38
add a comment |
UPDATE TABLE1
SET updated_user_id =
(
ORA_HASH(user_id, 999999999, 0) * 1000 +
ORA_HASH(user_id, 999999999, 1)
);
As both hash functions use a different seed, 0 and 1, this should meet your requirement.
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
add a comment |
You are talking about security. So let's see what we can read from the IDs...
Let's say we know that user IDs 123456 and 123460 exist. Then we can surmise the following:
- User IDs 123457, 123458, and 123459 also exist. We don't know, however, whether these users are still active or not.
- User 123457 was entered after user 123456 and before user 123458.
I think that's about it. We've extracted all the secrets we were so terribly curious about.
If that's it, then you can apply some stupid algorithm. Here are some generated User IDs:
118233146610
414263244517
517233547579
618263344516
919293149578
12-digit numbers. To see the original numbers, look at every other digit: 118233146610 becomes 123460. So in order to generate the numbers get some random digits and use string manipulation to insert them. And unlike with hash numbers, there can be no duplicates.
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
This is excellent. How can this be implemented in SQL?
– dang
Mar 9 at 17:16
I'd google Oracle + random and Oracle + sub string and Oracle and string concatenation :-)
– Thorsten Kettner
Mar 9 at 17:24
add a comment |
Add 110000000000 to each ORA_HASH.
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
ORA_HASH does not guarantee - no continuous zeroes.
– dang
Mar 8 at 8:20
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
);
);
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%2f55059067%2fhash-numbers-in-oracle%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
I would be careful about adding hash values. This can affect collisions. So:
UPDATE TABLE1
SET updated_user_id = (ORA_HASH(user_id, 1000000000, 0) - 1) * 1000 +
ORA_HASH(user_id, 1000, 1) - 1;
You should realize that hash values can have collisions. So, this is not 100% certain that the results will be unique. If guaranteed uniqueness is important you might want to combine a hash with some manipulation of the user id -- arithmetic or bitwise operations.
edited Mar 11 at 11:30
Peter Paff
454412
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
Uniqueness is very important in generating. How can I use hash but make sure that it is unique?
– dang
Mar 9 at 13:30
@dang . . . You haven't provided enough information in your question to answer this. It depends on what the purpose of the hashing is -- is it mean to be strong encryption or merely obfuscation? How many different user ids are there? Do they change over time? I would suggest that you ask another question with more details.
– Gordon Linoff
Mar 9 at 13:35
The purpose is obfuscation, so that if someone hacks into the database, he should not be able to identify the user ID. We have over 100 million user IDs in the system which are sent by a customer. We want to change the user IDs, so they cannot construct it back. I will ask another question with more details.
– dang
Mar 9 at 13:38
add a comment |
I would be careful about adding hash values. This can affect collisions. So:
UPDATE TABLE1
SET updated_user_id = (ORA_HASH(user_id, 1000000000, 0) - 1) * 1000 +
ORA_HASH(user_id, 1000, 1) - 1;
You should realize that hash values can have collisions. So, this is not 100% certain that the results will be unique. If guaranteed uniqueness is important you might want to combine a hash with some manipulation of the user id -- arithmetic or bitwise operations.
edited Mar 11 at 11:30
Peter Paff
454412
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
Uniqueness is very important in generating. How can I use hash but make sure that it is unique?
– dang
Mar 9 at 13:30
@dang . . . You haven't provided enough information in your question to answer this. It depends on what the purpose of the hashing is -- is it mean to be strong encryption or merely obfuscation? How many different user ids are there? Do they change over time? I would suggest that you ask another question with more details.
– Gordon Linoff
Mar 9 at 13:35
The purpose is obfuscation, so that if someone hacks into the database, he should not be able to identify the user ID. We have over 100 million user IDs in the system which are sent by a customer. We want to change the user IDs, so they cannot construct it back. I will ask another question with more details.
– dang
Mar 9 at 13:38
add a comment |
I would be careful about adding hash values. This can affect collisions. So:
UPDATE TABLE1
SET updated_user_id = (ORA_HASH(user_id, 1000000000, 0) - 1) * 1000 +
ORA_HASH(user_id, 1000, 1) - 1;
You should realize that hash values can have collisions. So, this is not 100% certain that the results will be unique. If guaranteed uniqueness is important you might want to combine a hash with some manipulation of the user id -- arithmetic or bitwise operations.
edited Mar 11 at 11:30
Peter Paff
454412
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
I would be careful about adding hash values. This can affect collisions. So:
UPDATE TABLE1
SET updated_user_id = (ORA_HASH(user_id, 1000000000, 0) - 1) * 1000 +
ORA_HASH(user_id, 1000, 1) - 1;
You should realize that hash values can have collisions. So, this is not 100% certain that the results will be unique. If guaranteed uniqueness is important you might want to combine a hash with some manipulation of the user id -- arithmetic or bitwise operations.
edited Mar 11 at 11:30
Peter Paff
454412
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
edited Mar 11 at 11:30
Peter Paff
454412
edited Mar 11 at 11:30
Peter Paff
454412
edited Mar 11 at 11:30
Peter Paff
454412
454412
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
answered Mar 8 at 12:54
Gordon LinoffGordon Linoff
790k35314418
790k35314418
Uniqueness is very important in generating. How can I use hash but make sure that it is unique?
– dang
Mar 9 at 13:30
@dang . . . You haven't provided enough information in your question to answer this. It depends on what the purpose of the hashing is -- is it mean to be strong encryption or merely obfuscation? How many different user ids are there? Do they change over time? I would suggest that you ask another question with more details.
– Gordon Linoff
Mar 9 at 13:35
The purpose is obfuscation, so that if someone hacks into the database, he should not be able to identify the user ID. We have over 100 million user IDs in the system which are sent by a customer. We want to change the user IDs, so they cannot construct it back. I will ask another question with more details.
– dang
Mar 9 at 13:38
add a comment |
Uniqueness is very important in generating. How can I use hash but make sure that it is unique?
– dang
Mar 9 at 13:30
@dang . . . You haven't provided enough information in your question to answer this. It depends on what the purpose of the hashing is -- is it mean to be strong encryption or merely obfuscation? How many different user ids are there? Do they change over time? I would suggest that you ask another question with more details.
– Gordon Linoff
Mar 9 at 13:35
The purpose is obfuscation, so that if someone hacks into the database, he should not be able to identify the user ID. We have over 100 million user IDs in the system which are sent by a customer. We want to change the user IDs, so they cannot construct it back. I will ask another question with more details.
– dang
Mar 9 at 13:38
Uniqueness is very important in generating. How can I use hash but make sure that it is unique?
– dang
Mar 9 at 13:30
Uniqueness is very important in generating. How can I use hash but make sure that it is unique?
– dang
Mar 9 at 13:30
@dang . . . You haven't provided enough information in your question to answer this. It depends on what the purpose of the hashing is -- is it mean to be strong encryption or merely obfuscation? How many different user ids are there? Do they change over time? I would suggest that you ask another question with more details.
– Gordon Linoff
Mar 9 at 13:35
@dang . . . You haven't provided enough information in your question to answer this. It depends on what the purpose of the hashing is -- is it mean to be strong encryption or merely obfuscation? How many different user ids are there? Do they change over time? I would suggest that you ask another question with more details.
– Gordon Linoff
Mar 9 at 13:35
The purpose is obfuscation, so that if someone hacks into the database, he should not be able to identify the user ID. We have over 100 million user IDs in the system which are sent by a customer. We want to change the user IDs, so they cannot construct it back. I will ask another question with more details.
– dang
Mar 9 at 13:38
The purpose is obfuscation, so that if someone hacks into the database, he should not be able to identify the user ID. We have over 100 million user IDs in the system which are sent by a customer. We want to change the user IDs, so they cannot construct it back. I will ask another question with more details.
– dang
Mar 9 at 13:38
add a comment |
UPDATE TABLE1
SET updated_user_id =
(
ORA_HASH(user_id, 999999999, 0) * 1000 +
ORA_HASH(user_id, 999999999, 1)
);
As both hash functions use a different seed, 0 and 1, this should meet your requirement.
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
add a comment |
UPDATE TABLE1
SET updated_user_id =
(
ORA_HASH(user_id, 999999999, 0) * 1000 +
ORA_HASH(user_id, 999999999, 1)
);
As both hash functions use a different seed, 0 and 1, this should meet your requirement.
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
add a comment |
UPDATE TABLE1
SET updated_user_id =
(
ORA_HASH(user_id, 999999999, 0) * 1000 +
ORA_HASH(user_id, 999999999, 1)
);
As both hash functions use a different seed, 0 and 1, this should meet your requirement.
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
UPDATE TABLE1
SET updated_user_id =
(
ORA_HASH(user_id, 999999999, 0) * 1000 +
ORA_HASH(user_id, 999999999, 1)
);
As both hash functions use a different seed, 0 and 1, this should meet your requirement.
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
edited Mar 8 at 9:28
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
answered Mar 8 at 9:00
Peter PaffPeter Paff
454412
454412
add a comment |
add a comment |
You are talking about security. So let's see what we can read from the IDs...
Let's say we know that user IDs 123456 and 123460 exist. Then we can surmise the following:
- User IDs 123457, 123458, and 123459 also exist. We don't know, however, whether these users are still active or not.
- User 123457 was entered after user 123456 and before user 123458.
I think that's about it. We've extracted all the secrets we were so terribly curious about.
If that's it, then you can apply some stupid algorithm. Here are some generated User IDs:
118233146610
414263244517
517233547579
618263344516
919293149578
12-digit numbers. To see the original numbers, look at every other digit: 118233146610 becomes 123460. So in order to generate the numbers get some random digits and use string manipulation to insert them. And unlike with hash numbers, there can be no duplicates.
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
This is excellent. How can this be implemented in SQL?
– dang
Mar 9 at 17:16
I'd google Oracle + random and Oracle + sub string and Oracle and string concatenation :-)
– Thorsten Kettner
Mar 9 at 17:24
add a comment |
You are talking about security. So let's see what we can read from the IDs...
Let's say we know that user IDs 123456 and 123460 exist. Then we can surmise the following:
- User IDs 123457, 123458, and 123459 also exist. We don't know, however, whether these users are still active or not.
- User 123457 was entered after user 123456 and before user 123458.
I think that's about it. We've extracted all the secrets we were so terribly curious about.
If that's it, then you can apply some stupid algorithm. Here are some generated User IDs:
118233146610
414263244517
517233547579
618263344516
919293149578
12-digit numbers. To see the original numbers, look at every other digit: 118233146610 becomes 123460. So in order to generate the numbers get some random digits and use string manipulation to insert them. And unlike with hash numbers, there can be no duplicates.
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
This is excellent. How can this be implemented in SQL?
– dang
Mar 9 at 17:16
I'd google Oracle + random and Oracle + sub string and Oracle and string concatenation :-)
– Thorsten Kettner
Mar 9 at 17:24
add a comment |
You are talking about security. So let's see what we can read from the IDs...
Let's say we know that user IDs 123456 and 123460 exist. Then we can surmise the following:
- User IDs 123457, 123458, and 123459 also exist. We don't know, however, whether these users are still active or not.
- User 123457 was entered after user 123456 and before user 123458.
I think that's about it. We've extracted all the secrets we were so terribly curious about.
If that's it, then you can apply some stupid algorithm. Here are some generated User IDs:
118233146610
414263244517
517233547579
618263344516
919293149578
12-digit numbers. To see the original numbers, look at every other digit: 118233146610 becomes 123460. So in order to generate the numbers get some random digits and use string manipulation to insert them. And unlike with hash numbers, there can be no duplicates.
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
You are talking about security. So let's see what we can read from the IDs...
Let's say we know that user IDs 123456 and 123460 exist. Then we can surmise the following:
- User IDs 123457, 123458, and 123459 also exist. We don't know, however, whether these users are still active or not.
- User 123457 was entered after user 123456 and before user 123458.
I think that's about it. We've extracted all the secrets we were so terribly curious about.
If that's it, then you can apply some stupid algorithm. Here are some generated User IDs:
118233146610
414263244517
517233547579
618263344516
919293149578
12-digit numbers. To see the original numbers, look at every other digit: 118233146610 becomes 123460. So in order to generate the numbers get some random digits and use string manipulation to insert them. And unlike with hash numbers, there can be no duplicates.
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
answered Mar 8 at 13:37
Thorsten KettnerThorsten Kettner
52.8k32643
52.8k32643
This is excellent. How can this be implemented in SQL?
– dang
Mar 9 at 17:16
I'd google Oracle + random and Oracle + sub string and Oracle and string concatenation :-)
– Thorsten Kettner
Mar 9 at 17:24
add a comment |
This is excellent. How can this be implemented in SQL?
– dang
Mar 9 at 17:16
I'd google Oracle + random and Oracle + sub string and Oracle and string concatenation :-)
– Thorsten Kettner
Mar 9 at 17:24
This is excellent. How can this be implemented in SQL?
– dang
Mar 9 at 17:16
This is excellent. How can this be implemented in SQL?
– dang
Mar 9 at 17:16
I'd google Oracle + random and Oracle + sub string and Oracle and string concatenation :-)
– Thorsten Kettner
Mar 9 at 17:24
I'd google Oracle + random and Oracle + sub string and Oracle and string concatenation :-)
– Thorsten Kettner
Mar 9 at 17:24
add a comment |
Add 110000000000 to each ORA_HASH.
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
ORA_HASH does not guarantee - no continuous zeroes.
– dang
Mar 8 at 8:20
add a comment |
Add 110000000000 to each ORA_HASH.
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
ORA_HASH does not guarantee - no continuous zeroes.
– dang
Mar 8 at 8:20
add a comment |
Add 110000000000 to each ORA_HASH.
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
Add 110000000000 to each ORA_HASH.
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
answered Mar 8 at 8:19
Vladimir.V.BvnVladimir.V.Bvn
171125
171125
ORA_HASH does not guarantee - no continuous zeroes.
– dang
Mar 8 at 8:20
add a comment |
ORA_HASH does not guarantee - no continuous zeroes.
– dang
Mar 8 at 8:20
ORA_HASH does not guarantee - no continuous zeroes.
– dang
Mar 8 at 8:20
ORA_HASH does not guarantee - no continuous zeroes.
– dang
Mar 8 at 8:20
add a comment |
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.
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%2f55059067%2fhash-numbers-in-oracle%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
3
Forget Oracle for a moment - is there any common has function that can match those requirements?It seems very unlikely, not least because they seem arbitrary and unhelpful - what's the benefit of the hash being non-sequential or not having repeated zeros? (Some of your example outputs have sequential runs - the first contains 456 twice and 345 - or is that not what you meant?). If that's really what you want then you'll have to design your own algorithm, and then implement it in SQL or PL/SQL.
– Alex Poole
Mar 8 at 8:52
2
Following on from the excellent points raised by @alexpoole, what is your purpose in hashing these numbers? If it's for any form of security you should really think hard about your requirements. Because they aren't going to deliver strong encryption. 12 digits is too small a space to guarantee no hash collisions. Rolling your own hashing algorithm to handle that and the other constraints is weaker than using Oracle built-in features.
– APC
Mar 8 at 9:26
@APC - thank you for your suggestions. I'm looking at security. What would be the best way to do it? Should I use more digits? What is the best mechanism to ensure the user ID is secured?
– dang
Mar 8 at 9:29
2
@dang: what exactly does "secured" mean with regards to an ID? Why is
576898973434"more secure" than 7654398? If you want some random unique IDs to prevent e.g. guessing of IDs then use a UUID. But if "guessing" a valid ID is a problem for your application, then the security in your application that controls access to information connected to that user has serious problems.– a_horse_with_no_name
Mar 8 at 9:40
2
Well as APC has pointed out 12 digits is to short anyway and doesn't give you any "security" at all. Either do it right, or stick with the IDs you have. It's probably more secure to never expose those IDs outside of your application.
– a_horse_with_no_name
Mar 8 at 9:48