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
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