Hi All,
Need help with questions regarding DB encryption.
1. Is it possible to encrypt down to a specific record in a table or is a table level the lowest?
2. Will ODBC access still be allowed?
We have a client who needs to go done this security path and this is all pretty new to us.
Thanks, Tony.
You can encrypt entire Type I storage areas, or at the storage object level (tables, indexes, or LOBs) in Type II storage areas. You can't encrypt at the level of individual records.
Database clients can access the data if they successfully authenticate against the TDE keystore. That applies to SQL or ABL clients.
The TDE documentation is pretty good so I suggest you start there. Also, if you search Progress Community for Progress Exchange conference sessions you'll find several sessions from past years, like 2009 - 2011, on TDE. It's worth looking at.
You can encrypt entire Type I storage areas, or at the storage object level (tables, indexes, or LOBs) in Type II storage areas. You can't encrypt at the level of individual records.
Database clients can access the data if they successfully authenticate against the TDE keystore. That applies to SQL or ABL clients.
The TDE documentation is pretty good so I suggest you start there. Also, if you search Progress Community for Progress Exchange conference sessions you'll find several sessions from past years, like 2009 - 2011, on TDE. It's worth looking at.
Seconding the recommendation for looking at the TDE documentation... it is pretty clear on how TDE works and covers the limited number of "gotchas" you need to worry about.
In the real world I have never seen a need to use TDE on a single row or subset of rows. Either you need to encrypt the table because you store sensitive data or you have some special data that needs to be hidden from general view via security settings.
BTW - Performance for OE encryption is very much in line with the TDE offerings from other databases (Oracle, SQL Server, etc.). Meaning less than 5% difference for heavy updates of encrypted vs non encrypted data.
Re performance, encryption is block-based and you pay the encryption/decryption cost for application data in two places: when a block is read from disk into a buffer pool and when a modified block is written out to disk. (Note that this isn't the only computational cost of TDE. By default, AI, BI, and probkup data are also encrypted.)
Data within the buffer pools is in plain text. So the more efficiently you can cache that data, the less decryption overhead you will incur. If your objects to be encrypted are relatively static, you can leverage the Alternate Buffer Pool to keep them memory-resident. But that may not be enough of a performance bump to matter.
I strongly encourage you to benchmark your application once you have developed your plan of what to encrypt and how. I agree with Keith about the performance hit. For us, encrypting one table and two indexes, the difference was in the measurement noise; not statistically significant. Don't encrypt more data than you need to.
I probably should have expanded on the definition of "heavy updates" a bit. Those numbers were based on a worst case scenario for one of the first large databases to implement TDE... loading about 10 million rows (several GB of data) into an encrypted table.
As Rob says, in the real world it will depend on how often your encrypted tables/indexes are written or read from disk.
But unless you are running close to the edge on your current hardware or just decide to encrypt everything... you aren't likely to see a noticeable performance difference. Certainly not compared to the value that TDE provides.
Thank you all for your input. Much appreciated.
"The TDE documentation is pretty good"
Whaahahahahahahaa *sarcasm*
> On Apr 11, 2020, at 5:07 AM, Lieven Cardoen wrote:
>
> "The TDE documentation is pretty good"
>
> Whaahahahahahahaa *sarcasm*
>
Point taken. Still, the information in the quick start and the FAQ papers are quite sufficient.