Tim,
I'm not trying to knock tokenization, I've seen it used in a very
helpful way in the past and more than once.
Example: worked on a life insurance system where we processed
payments through a payment processor. To allow the payment we initially
sent the card information to the processor in full and fully encrypted.
They in turn returned a token as part of their reply which we stored
locally unencrypted. Next time we needed to process a payment we simply
sent the token and they would reply. Result we did not have to store the
private information locally.
But as I said using the token was a external call which is some
cases took a few seconds to execute. In our case that was not a
problem as it was handled in a batched manner. Whether or not that
would be acceptable in this case is unknown and could be a concern.
On 9/20/2023 15:17, Tim Gorman wrote:
Richard,
While the tokenex page is very useful in many ways, they are incorrect depicting that the tokenized value is always decrypted inline in the application flow. That is the probable application flow when the original value is almost always viewed but isn't stored, which did not sound like what the OP wanted? I read the OP request to mean that the original value is only infrequently viewed, though I could have been mistaken?
When decrypting is infrequent, then simply display the token value itself. If a user wishes to access the original data value, then they submit a request for it separately. At that point, the requesting user can be verified for their privilege to see the decrypted value. That way, users normally see the anonymized (tokenized) value, and when someone infrequently wants the original value, they must request it.
Hope this helps,
-Tim
On 9/20/2023 11:29 AM, richard goulet wrote:
Tim,
While I would have no issues asking for help from an outside vendor it may create issues of it's own by having to make another external system call which may take more time than allowed. Tokenism may make the value not totally understandable, but it isn't encryption which is the requirement stated (https://www.tokenex.com/blog/tokenization-vs-encryption/) and appears to need a external lookup. This in turn may make the entire process dependent of an external server/web service which may increase the complexity of the system and reduce reliability, never mind speed of execution.
I would recommend a real discussion of what IS required from the development/business and move from there.
On 9/19/2023 21:32, Tim Gorman wrote:
Lok,
I think what you want to do is called "tokenization" in the Delphix masking engine documentation <https://maskingdocs.delphix.com/>. That's where the value used to replace or mask the original data value is actually a key (or token) that can be used to retrieve the original data value from another data store. In essence, it is reversible data masking, because the original value is not irreversibly lost.
I'd suggest contacting the folks at Delphix about this; they've been doing data masking and obfuscation for more than 10 years now.
Hope this helps,
-Tim
On 9/19/2023 2:01 PM, Lok P wrote:
Thank you Mark , Lucas and Richard.
Actually the requirement was not just to hide the column value from specific users (for which VPD is a good solution), but to encrypt the whole string itself in the column in the database. But I understand the column encryption/decryption comes with its own performance overhead. So it seems, dbms_encrypt is the only solution we can go with and then perhaps we have to move the only sensitive field out of the JSON string so that we would be able to avoid the burden of encrypting/decrypting the whole clob column.
But even in this case as "richard" also highlighted the datatype of the column has to be blob/raw. I am not able to visualize it clearly. For example the account_numner column will be separately stored in a new column(which would ideally be a VARCHAR2 data type) and will be encrypted using dbms_crypto function. So in that case, do you mean to say, we need to change the datatype of the column to BLOB or RAW to store the encrypted value and it cant be stored in VARCHAR data type?
Additionally as you mentioned the issue with regards to the key management , as because somewhere the key has to be stored , which the application will need access to, for decrypting the column values. Does Oracle have some set standard for this?
On Mon, Sep 18, 2023 at 9:56 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
First, this is significantly MORE than just the column.
Yes, if, for example, the account_number appears in a string
that can be parsed, then it needs to be masked in all such
strings if that is sensitive information in the context of your
access rules.
As for the feasibility of joins, if you use the same encryption
in each such instance that magically works out for itself for
joins and equality but beggars the imagination for ranges.
(Actually you could imagine a lookup table that translates
encryption back to a value that you can range, but it is very
complicated to both make it work and NOT weaken the encryption.)
Your goal is also important. If you just don’t want folks to
casually see an account_number browsing the data that is less
severe than actually making it take considerable computer time
to decrypt such values.
VPD has been mentioned on the thread. That might accomplish
your goal. But probably you need to hire an expert in this,
whose first task should probably be to pin down very carefully
the goals and tolerable side effects of the encryption.
Good luck,
mwf
*From:*oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Lok P
*Sent:* Friday, September 15, 2023 10:00 PM
*To:* Oracle L
*Subject:* Column encryption use-case
Hello Friends, It's Oracle version 19C.
We are using tablespace TDE for "data at rest" encryption. We
have got a security requirement , as part of which TDE is not
sufficient as , although it's encrypted in storage , the data
is visible to clear text to all whoever selects from that
table. The sensitive column(e.g. account_number) should not be
visible to others in clear text and should be stored as
encrypted only. And should also be decrypted as and when
required by the application logic from the app account but not
from the individual user account. And currently the application
is required to query/fetch/join that column as and when
required and we have indexes that exist on the column in few
cases. Also there are cases, the column/attribute which we want
to encrypt is part of a clob column which stores Json format
strings. Would it be okay to encrypt a full clob or should we
extract the sensitive column out from the clob and store it as
a different column and then encrypt that?
While searching over the internet , I found dbms_crypto. But I
have never used it in the past. So I wanted to know from
experts here , if this above use case should be catered by the
dbms_crypto package and then how the key management happens
here? or any other possible easy/more performant methods are
available in latest Oracle versions? Because having indexes
created on the encrypted column may have performance overhead
while decrypting , so I want to be cautious.
And this activity we need to do on the existing data and the
newly coming data to the table. There are 100's of million rows
inserted/queried per day to/from the table along with that column.
Appreciate your guidance.
Regards
Lok
