0

I have a requirement to encrypt NUMBER datatype in Oracle 19c. The requirement is to encrypt the data, and update the same to the column. After a while, if needed, the data has to be decrypted and provided to the customers.

I found the DBMS_CRYPTO package, which provides the encryption. I found examples for VARCHAR2 datatype, but none for numeric.

On trying to encrypt the NUMBER column, the encryption results in an alphanumeric, which cannot be updated in the column.

How can my objective of encrypting the numeric column and updating the same be achieved? Is DBMS_CRYPTO the right way to proceed for this?

Anjana
  • 1
  • 4
  • 2
    For encrypted data you should use even `RAW` data type (or `BLOB` if they can get bigger). NUMBER or VARCHAR2 data type will fail. Of course, you could use the HEX values from RAW data and convert to decimal numbers but it will be cumbersome and error prone. And most likely you will exceed the max. range of numbers. – Wernfried Domscheit Jan 11 '21 at 16:54
  • I would look into Oracle Transparent Data Encryption. Your problem here is that most random combinations of bits (ie, an encrypted value) are not valid Oracle NUMBERs, and the key sizes are larger than 32-bit BINARY_INTEGERs - so you can't store an arbitrary encrypted value in a NUMBER field very easily. – kfinity Jan 11 '21 at 17:04
  • 1
    @kfinity Oracle TDE only encrypts data at rest in files on disk. It does not encrypt column values in the tables, which seems to be the intent of the OP. As Wernfried pointed out, they will have to use different columns to store the encrypted vs. unencrypted data if they are going to use DBMS_CRYPTO. – pmdba Jan 11 '21 at 21:32

0 Answers0