7 Replies Latest reply: Jul 30, 2012 12:29 PM by JustinCave RSS

    encrytion table column data

    592815
      Hi Friends,
      I created a test table with 4 columns(id, first_name,last_name, salary-number ) and then alter table to encrypted salary column . everything is OK.

      I insert values into test table. However, I still can see salary value by select SQL.

      What is wrong?

      my db is oracle 11.2.01 in 2008 SP window

      newdba
        • 1. Re: encrytion table column data
          415289
          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.
          • 2. Re: encrytion table column data
            592815
            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?

            newdba
            • 3. Re: encrytion table column data
              IBarr
              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,

              Iain Barr
              Ategrity Solutions Ltd.
              • 4. Re: encrytion table column data
                592815
                Iain,

                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.

                newdba.
                • 5. Re: encrytion table column data
                  JustinCave
                  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.

                  Justin
                  • 6. Re: encrytion table column data
                    592815
                    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.

                    Thanks
                    newdba
                    • 7. Re: encrytion table column data
                      JustinCave
                      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.

                      Justin