Re: Column encryption use-case

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_at_rsiz.com> 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_at_freelists.org
>>>> [mailto:oracle-l-bounce_at_freelists.org] *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
>>>>
>>>
>

--
http://www.freelists.org/webpage/oracle-l