This content has been marked as final. Show 7 replies
use dbms_crypto if you would like to see salary column as encrypted in select cmd.
and then alter table to encrypted salary column . everything is OK.i believe you've use Transparent Data Encryption which perform encruption and decrytion automatically at select cmd that why you're not able to see encrypted value.
TDE assurance that the data on disk is encrypted.
Transparent data encryption enables simple and easy encryption for sensitive data in columns without requiring users or applications to manage the encryption key. This freedom can be extremely important when addressing, for example, regulatory compliance issues. No need to use views to decrypt data, because the data is transparently decrypted once a user has passed necessary access control checks. Security administrators have the assurance that the data on disk is encrypted, yet handling encrypted data becomes transparent to applications.
Thanks for your help.
my question is that I still can see encrypted column data (salary) by sql select statement.
per select *from dba_encrypted_columns;
I see that encryption_alg as 3 Key Triple DES 168 bits key and salt is yes.
are there some wrong in my configuration?
Yes, there is a problem with your configuration. The configuration you have used will not encrypt the data when using SQL statements as it will be decrypted automatically by the Oracle RDBMS. The kind of encryption you have used will only protect against viewing the data OUTSIDE of the database. E.g. by looking directly at the DBF files at a file system level. You need to use the DBMS_CRYPTO package to encrypt data at the SQL statement level.
Hope that helps,
Ategrity Solutions Ltd.
Thanks for your help.
In the way, we need to create a procedure to insert/update data into table . DO we have any other simple way that just make some column (such password, salary.. et al) is invisible for select SQL.
You can create a VPD policy on the table that includes column-masking behavior. That will restrict who can see the data for a particular column depending on whatever criteria you want to implement in your policy function. This will not encrypt the data at rest and it will not prevent a DBA from viewing the data.
we configured both VPD and encry at database.
Do we need to grant exec DBMS_CRYPTO package to end user or developer ? so that codes can encryted data.
Why would you do both? It doesn't make a lot of sense to me to both manually encrypt data using DBMS_CRYPTO (particularly instead of TDE) and to use VPD to control access to the data. Both are controlled in the database so it seems like you're just doubling the amount of configuration required and increasing the number of ways that something can go wrong.
If you want developers and/or end users to call DBMS_CRYPTO directly, yes, you'd need to grant them that privilege. If you want them to call an API that you provide that in turn calls DBMS_CRYPTO, you'd need to grant them access to that API instead.