Adding an encryption using Oracle TDE worked, but would not allow a search on an indexed field. It looks like Toad also offers an Oracle encryption with the differences between TOAD encryption and Oracle TDE are as follows:
1). TDE creates a transaction in the redo-log for every row it encrypts. This caused the encryption process to fail because the log file filled up. TOAD encryption did not have this issue for some reason. Why?
2). TDE does not allow for encrypted columns to be indexed for searches. TOAD can encrypt a column, put an index on it and it returned a record very fast. Why?
3). TDE requires a wallet be stored in a safe place. TOAD makes no mention of a wallet. What key does Toad use to encrypt?
Toad does not do any of the encryption. In the create/alter table window | encrypt a column, Toad issues the commands to use Transparent Data Encryption (TDE). The wallet must set up already in the database for this to work or else Toad will display an ORA error (from Oracle).
Toad does not have any “Toad Encryption”. Toad is not performing any encryption or decryption. Oracle is the one that does that, and this would be the reason Toad does not need a wallet. When you go to Toad’s create/alter table window and encrypt a column, Toad just sends Oracle the command to do that. If a wallet is already open in the database, Oracle will encrypt the column using that wallet. If no wallet is open, an error will be thrown. Toad only acts as a GUI for Oracle, and Oracle is still the one that’s doing the transaction in the background.
When TDE is used (or when Toad tells Oracle to use TDE – it’s the same thing), the data in the datafile is encrypted. When you select the data back, the client program (Toad, SQL Plus, etc.) is not even aware of the encryption because Oracle decrypts it before even sending the result out via SQL Net. This is why it is called “Transparent” Data Encryption. The purpose of this type of encryption is that if someone steals a backup copy of your database, when they try to recover it, they won’t be able to read the encrypted columns unless they have the wallet.
To see further the statements that Toad sends to the database, go to the Database Menu | Monitor | SQL Tracker | the SQL Tracker window will open | click “Play” for the “toad.exe”. From here on, any action in Toad, the SQL Tracker will report all the SQL statements Toad sends to the database. Try performing an encryption on a column and then check out the output in SQL Tracker to see what Toad sends to the database.
Alternatively (this would be faster), you can use the Show SQL button in the create/alter window to see exactly what Toad is sending to the database.
As for the questions, please see below.
1) Unable to answer this question because it is not known exactly what what was done in SQL Plus (or the app used to apply TDE). Maybe in one case a table had the ‘logging’ property enabled, and in the other case it did not.
2) Unable to answer this question for the same reason, but Toad just sends commands to Oracle, so Toad is subject to whatever rules are in place / used by Oracle. Toad only acts as a GUI app for Oracle.
3) See the first few paragraphs above.
