1

This is the first time I use oracle db. I have an integer column in oracle db, and I want to hash them into some other integer. What is the fastest way to do that? The contains millions of row.

Gregorius Edwadr
  • 399
  • 1
  • 3
  • 14
  • 1
    It depends which hash you want to use; and if you don't already know that, it will depend on why you're hashing it and what the hash will be used for. And how far you need/want to go to avoid collisions. – Alex Poole Sep 19 '18 at 09:10
  • Fastest would only be confirmed with testing, but for a built in hash function, there is `ora_hash` – Andrew Sep 19 '18 at 09:11
  • I don't know yet what hash to use. I want to hash it so that when hand the data to other people they won't be able to reverse that column. @AlexPoole – Gregorius Edwadr Sep 19 '18 at 09:15
  • So you suggest I do the ora_hash @Andrew? – Gregorius Edwadr Sep 19 '18 at 09:15
  • 1
    If you just want to hide the real value, why not either not send it at all, or if something has to be provided, maybe replace it with a random number - which would give you more control over, say, matching the magnitude of the original to make it more representative? – Alex Poole Sep 19 '18 at 10:28
  • @GregoriusEdwadr - if you are wanting to absolutely one way hash it then go with MTO's answer, only caveat I would add is don't use MD5, there are enough MD5 lookup's in existence that I would not consider it secure. – Andrew Sep 19 '18 at 10:32

1 Answers1

1

I want to hash it so that when hand the data to other people they won't be able to reverse that column.

You want a one-way (cryptographic) hash function.

Oracle has a DBMS_CRYPTO package that in Oracle 12.1 supports:

Cryptographic hash algorithms: MD5, SHA-1, SHA-2 (SHA-256, SHA-384, SHA-512), MD4

Keyed hash (MAC) algorithms: HMAC_MD5, HMAC_SH1, HMAC_SH256, HMAC_SH384, HMAC_SH512

(Earlier Oracle versions support fewer algorithms - but you can always add support for more secure algorithms via a custom Java function stored in the database.)

Use one of them that has the features (security, performance, etc) that you require.

What is the fastest way to do that?

You can have faster or more secure and you should pick the most secure algorithm that you can that meets your performance requirements (rather than just going for the fastest and least secure algorithm).

You also asked:

So you suggest I do the ora_hash

No, it is difficult to know exactly what hash algorithm ora_hash uses as it is not in the official documentation but there is no mention of it being a cryptographic/one-way hash function.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Two problems: a) if the input number was the same for two rows, the output number will also be the same for these two rows. Maybe you need to hide that relationship as well. b) If the input numbers are easily guessable (and integer numbers probably are, at least mechanically, unless they are huge) you need a secret salt to prevent brute-forcing – Thilo Sep 19 '18 at 10:55
  • If possible, just don't give out the column at all. – Thilo Sep 19 '18 at 10:55