Re: Column encryption use-case

I don't have any architecture diagram or implementation step details handy but below is the Oracle doc which states the detailed implementation for same.

https://docs.oracle.com/cd/E65319_01/OKVHM/toc.htm

On Sun, 26 Nov, 2023, 8:02 am Lok P, <loknath.73_at_gmail.com> wrote:

> That is a lot of useful information. Thanks a lot for clarifying the
> concept and the differences between these for me.
>
> It would be great if you have a implementation guide to share OR an
> architecture diagram which you have implemented for the same?
>
> On Sun, Nov 26, 2023 at 12:37 AM yudhi s <learnerdatabase99_at_gmail.com>
> wrote:
>
>> Though I don't have much expertise around the database security stuff,
>> one of our clients is also going through similar PCI(payment card industry)
>> standard evaluation stuff. Got some details around the implementation of
>> TDE with HSM. Below are the details, Open for thoughts..
>>
>> These databases are hosted on Exadata with TDE Encryption leveraging
>> integrated Oracle Wallet. But I think the key thing here is that by default
>> TDE uses a software keystore within the Oracle Database to manage
>> encryption keys. However PCI requires the keys to be managed by separate
>> servers with HSM integration.
>>
>> The integration with an HSM for TDE is facilitated through the Oracle Key
>> Vault (OKV). Oracle Key Vault is a separate product designed to provide
>> centralized key management for various Oracle security features, including
>> TDE. With OKV, you can store and manage TDE master encryption keys securely
>> in an external HSM.
>>
>> TDE uses a two-tiered key architecture. The Master Encryption Key (MEK)
>> encrypts the Data Encryption Key (DEK). The DEK is the key used to encrypt
>> and decrypt the actual data.The MEK is stored securely within the HSM. The
>> HSM manages the encryption and decryption operations related to the MEK,
>> ensuring the key's security.
>>
>> When the Oracle database needs to access encrypted data, it retrieves the
>> DEK from the HSM, using the MEK stored in the HSM. This process ensures
>> that the DEK is never exposed in plaintext outside the HSM.With the DEK
>> retrieved from the HSM, the database can then perform the necessary
>> encryption or decryption operations on the data.
>>
>> Thus HSM mostly addresses the key management part only. And it is in no
>> way comparable with the tokenization piece.
>>
>> Basically TDE encrypts the data at the storage layer without any
>> complexity being added to the application layer logic. Tokenization
>> Provides protection by substituting sensitive data with tokens. The actual
>> sensitive data is stored in a secure vault, reducing the risk of exposure
>> in the event of a breach. It introduces complexity in the application
>> logic, especially if you need to retain the association between tokens and
>> actual PAN data for specific use cases. Managing tokens and ensuring proper
>> mapping between tokens and PAN data can be challenging, especially in
>> scenarios where you need to search, retrieve, or correlate data.
>>
>> It may have performance issues while tokenization needs to be applied to
>> large datasets or across multiple databases, whereas TDE is less
>> performance intensive.
>>
>> When actual data is needed, the tokenization system is used to map tokens
>> back to the original data and thus it has to hit the tokenization service
>> and it may cause bottleneck if this is a frequent need, whereas with TDE
>> just needs appropriate keys to decrypt the encrypted data stored in the
>> database.
>>
>> On Fri, Nov 3, 2023 at 1:58 AM Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Few of the third party team members suggested using Oracle TDE with HSM
>>> to cater to this PCI requirement. We are already using Oracle
>>> TDE(Tablespace encryption). But hearing this(Oracle TDE with HSM) for the
>>> first time, I want to check here if anybody has experience using this in
>>> the past and this will really suffice the PCI standard security needs.
>>>
>>>
>>> https://www.dwt.com/blogs/financial-services-law-advisor/2022/05/payment-card-industry-data-security-standards
>>>
>>> On Sun, Sep 24, 2023 at 11:04 PM richard goulet <rjgoulet_at_comcast.net>
>>> wrote:
>>>
>>>> Lok,
>>>>
>>>> In that system we sent the card info in encrypted format using a
>>>> shared and changed every 30 days key. The next time we only sent the token
>>>> along with our key. Now if the customer needed to change the card info we
>>>> had a separate method for that that sent the card info via encryption. The
>>>> payment processor did not change the token, but replied with a
>>>> confirmation. All we stored was the token & had no local copy of the card
>>>> info. At the time (2009) that was sufficient in that we never retained a
>>>> copy of the card information for more than a few seconds, but reading the
>>>> link you referenced that seems no longer the case.
>>>>
>>>> Now I think, if you can get buy in on this you could store the PAN
>>>> number using VPD as a separate column in the table and create the JSON
>>>> string at run time through a view that assembles it: IE "JSON := Part_1 ||
>>>> PAN ||Part_2". VPD should return the PAN as a blank for those not
>>>> authorized to see it.
>>>> On 9/24/2023 03:26, Lok P wrote:
>>>>
>>>> Thank you so much.
>>>>
>>>> Consider a system which is operating as an intermediary between
>>>> merchants and "card holder banks" and deals with transaction approval and
>>>> also chargebacks. And the new requirement as part of PCI 4.0(in below URL)
>>>> has some stricter rules.This system was previously storing cleartext PAN
>>>> numbers, but looks like it has to be encrypted anyway now as per this
>>>> standard.
>>>>
>>>>
>>>> https://www.dwt.com/blogs/financial-services-law-advisor/2022/05/payment-card-industry-data-security-standards
>>>>
>>>> Apology if these are silly ones, but I am a bit confused on the part of
>>>> tokenization vs encryption OR do we have to have both of them for the PAN?
>>>> As because both are reversible to original value. Encryption(dbms_encrypt)
>>>> relies on a Key(to decrypt it back) and if that key is compromised that
>>>> will be an issue, So how should we store that key and give access to
>>>> applications as per industry standard? And if tokenization is also similar
>>>> to encryption only , then why can't we do/implement that within our system
>>>> but relying on a third party system.
>>>>
>>>> As you mentioned in the below example , you send the card
>>>> information to the processor in full and encrypted , was it both clear text
>>>> and encrypted version both? And I understand implementing and hitting
>>>> encryption and decryption or hitting third party tokenization service for
>>>> every arrival/fetch of the PAN data will be a performance overhead to the
>>>> system. Correct me if wrong.
>>>>
>>>> *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.*
>>>>
>>>> *Snippet of PCI 4.0 rule:*
>>>> *Encryption and Compliance Scoping*
>>>>
>>>> *The update also addresses the issue of whether entities that only
>>>> process encrypted cardholder data fall within the scope of PCI DSS. In
>>>> short, "encryption alone is generally insufficient to render the cardholder
>>>> data out of scope for PCI DSS and does not remove the need for PCI DSS in
>>>> that environment. The entity's environment is still in scope for PCI DSS
>>>> due to the presence of cardholder data."*
>>>>
>>>> *The standard provides the following situations in which encrypted
>>>> cardholder data will still be in scope of PCI DSS:*
>>>>
>>>> - *Systems performing encryption and/or decryption of cardholder
>>>> data, and systems performing key management functions;*
>>>> - *Encrypted cardholder data that is not isolated from the
>>>> encryption and decryption and key management processes;*
>>>> - *Encrypted cardholder data that is present on a system or media
>>>> that also contains the decryption key;*
>>>> - *Encrypted cardholder data that is present in the same
>>>> environment as the decryption key; and*
>>>> - *Encrypted cardholder data that is accessible to an entity that
>>>> also has access to the decryption key.*
>>>>
>>>> *Nevertheless, if a service provider (or other entity) merely receives
>>>> and/or stores encrypted data and does not have the ability to decrypt it,
>>>> the data can largely be considered out of scope of the PCI DSS. Version 4.0
>>>> explains that in a case where a service provider stores encrypted
>>>> cardholder data on behalf of a customer, does not have access to the
>>>> decryption key, and does not perform key management for its customer, the
>>>> data can be excluded when the service provider determines its PCI DSS
>>>> scope. Similarly, if a service provider only receives encrypted cardholder
>>>> data for the purpose of routing the data to other entities and does not
>>>> have access to the decryption key, the service provider may be considered
>>>> the same as a public network and would not have any PCI DSS responsibility
>>>> for the encrypted data*
>>>>
>>>>
>>>>
>>>> On Thu, Sep 21, 2023 at 10:17 PM richard goulet <rjgoulet_at_comcast.net>
>>>> wrote:
>>>>
>>>>> 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