Re: Column encryption use-case

 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