Skip to Main Content
  • Questions
  • Unix Strings command and data not encrypted

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: November 27, 2018 - 6:18 pm UTC

Last updated: November 29, 2018 - 12:30 am UTC

Version: 12.01

Viewed 1000+ times

You Asked

Hello TOM,

I have a problem with the Unix command named STRINGS. If I do not use encryption on my datas, I can read my string datas via Unix, even if these datas were truncated under Oracle.

And dtranger, I have their history too...


First, I create a tablespace with one datafile.
SQL> CREATE TABLESPACE TEST_STRINGS DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/test_strings.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
     SQL> alter user HR quota unlimited on TEST_STRINGS;


I create a table in this tablespace.
SQL> CREATE TABLE HR.ZZTEST_STRINGS(ID VARCHAR2(20 CHAR)) tablespace test_strings;
     SQL> select tablespace_name from dba_tables where table_name = 'ZZTEST_STRINGS';
     TABLESPACE_NAME
     ------------------------------
     TEST_STRINGS



We go in the directory with the datafile of the table.
 [oracle@vbgeneric orcl]$ cd /u01/app/oracle/oradata/orcl12c/orcl
   
     [oracle@vbgeneric orcl]$ ls -l
     total 2156408
     -rw-r----- 1 oracle oinstall    7938048 Sep 16 06:02 APEX_1991375173370654.dbf
     -rw-r----- 1 oracle oinstall    2695168 Sep 16 06:02 APEX_1993195660370985.dbf
     -rw-r----- 1 oracle oinstall 1247813632 Sep 16 06:02 sysaux01.dbf
     -rw-r----- 1 oracle oinstall  367009792 Sep 16 06:02 system01.dbf
     -rw-r----- 1 oracle oinstall   67117056 Sep 16 05:57 temp01.dbf
     -rw-r----- 1 oracle oinstall   10493952 Sep 16 06:02 test_strings.dbf
     -rw-r----- 1 oracle oinstall  482353152 Sep 16 06:02 undotbs01.dbf
     -rw-r----- 1 oracle oinstall   81272832 Sep 16 06:02 users01.dbf


I create data and I can see them with STRINGS if I force DBWR to write it with a flush of the buffer cache.
     SQL> insert into ZZTEST_STRINGS values('TESTZZ01');


Nothing about my inserted data...
     [oracle@vbgeneric orcl]$ strings test_strings.dbf
     }|{z
     -ORCL12C
     TEST_STRINGS


If I flush the buffer cache, it's OK (but I don't know what is AAAAAAAA).
How can I avoid this problem : must I encrypt my datas?
   SQL> alter system flush BUFFER_CACHE;
 
     [oracle@vbgeneric orcl]$ strings test_strings.dbf
     }|{z
     -ORCL12C
     TEST_STRINGS
     AAAAAAAA
     TESTZZ01



If I truncate my table, I can read again the datas. Is it normal?
     SQL> truncate table HR.ZZTEST_STRINGS;
     SQL> alter system flush BUFFER_CACHE;
              
     [oracle@vbgeneric orcl]$ strings test_strings.dbf
     }|{z
     -ORCL12C
     TEST_STRINGS
     AAAAAAAA
     TESTZZ01




Stranger, I have access of the history of the datas after updates :-)
     SQL> connect HR/HR@orcl; 



I drop my tablespace and its datafile.
SQL> drop tablespace TEST_STRINGS INCLUDING CONTENTS;
SQL> ! rm /u01/app/oracle/oradata/orcl12c/orcl/test_strings.dbf


I start with a new table.
     SQL> CREATE TABLESPACE TEST_STRINGS DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/test_strings.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
     SQL> CREATE TABLE ZZTEST_STRINGS(ID NUMBER, COMMENT_TEST VARCHAR2(20 CHAR)) tablespace test_strings;   
    
     SQL> insert into ZZTEST_STRINGS values(1, 'TEST1');
     SQL> insert into ZZTEST_STRINGS values(2, 'TEST2');
     SQL> insert into ZZTEST_STRINGS values(3, 'TEST3');
     SQL> commit;
   
     SQL> alter system flush BUFFER_CACHE;
              
     [oracle@vbgeneric orcl]$ strings test_strings.dbf
     }|{z
     -ORCL12C
     TEST_STRINGS
     AAAAAAAA
     TEST3,
     TEST2,
     TEST1



SQL> UPDATE ZZTEST_STRINGS set COMMENT_TEST = 'TEST1_UPDATE' where id = 1;
     SQL> commit;
     SQL> alter system flush BUFFER_CACHE;


We have the old and the new value.
     [oracle@vbgeneric orcl]$ strings test_strings.dbf
     }|{z
     -ORCL12C
     TEST_STRINGS
     AAAAAAAA
     TEST1_UPDATE,
     TEST3,
     TEST2,
     TEST1<q


Have a nice day,

David DUBOIS



and Connor said...

This is a common misconception - that the data stored by Oracle is undecipherable in some way. By default, as you've discovered, the data is just more or less plain text just on the file system.

This is why we recommend (and on our cloud offerings its mandatory) that people encrypt their data using our transparent data encryption.

Here's a video demo of that


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you very much

David DUBOIS, November 28, 2018 - 9:24 am UTC

Thank you for the answer.
Have a nice day.

David
Connor McDonald
November 29, 2018 - 12:30 am UTC

glad we could help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database