Re: Column encryption use-case

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