Table of Contents
RND & TESTING to read data / Contents from oracle datafiles which are not encrypted and Prove, why encryption is important for oracle databases.
To achieve the above task, I have installed oracle 21c rpm on Linux oracle 8.
Database Version: Oracle Database 21c Express Edition Release 21.0.0.0.0
Database name : XE
Microsoft Windows [Version 10.0.19044.2251][oracle@localhost ~]$ . oraenv
ORACLE_SID = [oracle] ? XE
The Oracle base has been set to /opt/oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 7 08:58:51 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> COLUMN NAME FORMAT A8
SQL> set colsep | pagesize 2000 trims on
SQL> set linesize 200
SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;
NAME |CDB| CON_ID
--------|---|----------
XE |YES| 0
SQL> col cname format a10
SQL> select name cname,dbid, OPEN_MODE,OPEN_TIME from V$CONTAINERS C
CNAME | DBID|OPEN_MODE |OPEN_TIME
----------|----------|----------|---------------------------------------------------------------------------
CDB$ROOT |3006541124|READ WRITE|07-JAN-23 04.10.04.739 AM +00:00
PDB$SEED |3937184287|READ ONLY |07-JAN-23 04.10.04.789 AM +00:00
XEPDB1 | 671033675|READ WRITE|07-JAN-23 04.10.09.346 AM +00:00
SQL> col WRL_PARAMETER format a10
SQL> col WRL_TYPE format a10
SQL> col wallet_type format a10
SQL> col STATUS format a20
SQL> col KEYSTORE_MODE format a50
SQL> set linesize 300 trims on colsep |
SQL> select con_id,WRL_TYPE,wallet_type,STATUS,KEYSTORE_MODE from V$encryption_wallet;
CON_ID|WRL_TYPE |WALLET_TYP|STATUS |KEYSTORE_MODE
----------|----------|----------|--------------------|--------------------------------------------------
1|FILE |UNKNOWN |NOT_AVAILABLE |NONE
2|FILE |UNKNOWN |NOT_AVAILABLE |UNITED
3|FILE |UNKNOWN |NOT_AVAILABLE |UNITED
SQL> select name cname,dbid,E.con_id,wrl_type,wallet_type,status,keystore_mode
from V$ENCRYPTION_WALLET E , V$CONTAINERS C
where E.con_id=C.con_id ORDER BY CON_ID;
CNAME | DBID| CON_ID|WRL_TYPE |WALLET_TYP|STATUS |KEYSTORE_MODE
----------|----------|----------|----------|----------|--------------------|--------------------------------
CDB$ROOT |3006541124| 1|FILE |UNKNOWN |NOT_AVAILABLE |NONE
PDB$SEED |3937184287| 2|FILE |UNKNOWN |NOT_AVAILABLE |UNITED
XEPDB1 | 671033675| 3|FILE |UNKNOWN |NOT_AVAILABLE |UNITED
Steps to prove, We can read contents from oracle datafiles if it is not encrypted: 1.) Connect to container XEPDB1 2.) Now, I will create a TABLESPACE (HACK_TEST) with single DATAFILE ('hack_test_datafile_01.dbf') in container XEPDB1 3.) Create a user PREY_USER with table EMP with few records. 4.) Execute command with sys "ALTER SYSTEM CHECKPOINT;" and "ALTER SYSTEM SWITCH LOGFILE;" to write data from buffer/cache to datafiles. Note: Repeat commands from privileged account 3 to 4 time. 5.) Read datafile with LINUX command and will Prove data is readable of unencrypted file (.DBF) with OS command.
SQL> select name cname,dbid,E.con_id,wrl_type,wallet_type,status,keystore_mode from V$ENCRYPTION_WALLET E , V$CONTAINERS C where E.con_id=C.con_id ORDER BY CON_ID; CNAME | DBID| CON_ID|WRL_TYPE |WALLET_TYP|STATUS |KEYSTORE_MODE ----------|----------|----------|----------|----------|--------------------|-------------------------------- CDB$ROOT |3006541124| 1|FILE |UNKNOWN |NOT_AVAILABLE |NONE PDB$SEED |3937184287| 2|FILE |UNKNOWN |NOT_AVAILABLE |UNITED XEPDB1 | 671033675| 3|FILE |UNKNOWN |NOT_AVAILABLE |UNITED
1.) Connect to container XEPDB1 SQL> alter session set container =XEPDB1; SQL> alter session set container =XEPDB1; Session altered.
SQL> col FILE_NAME format a70 select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED from dba_tablespaces T , dba_data_files d where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1; TABLESPACE_NAME |FILE_NAME |STATUS |LOGGING |FOR|ENC ----------------|-----------------------------------------------|---------|---------|---|--- SYSAUX |/opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf |ONLINE |LOGGING |YES|NO SYSTEM |/opt/oracle/oradata/XE/XEPDB1/system01.dbf |ONLINE |LOGGING |YES|NO UNDOTBS1 |/opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf |ONLINE |LOGGING |NO |NO USERS |/opt/oracle/oradata/XE/XEPDB1/users01.dbf |ONLINE |LOGGING |NO |NO
2.)Now, I will create a TABLESPACE (HACK_TEST) with single DATAFILE in container XEPDB1 SQL>CREATE TABLESPACE "HACK_TEST" DATAFILE '/opt/oracle/oradata/XE/XEPDB1/hack_test_datafile_01.dbf' SIZE 5242880 AUTOEXTEND ON MAXSIZE 1G; Tablespace created. SQL> select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED from dba_tablespaces T , dba_data_files d where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1; 2 3 4 TABLESPACE_NAME |FILE_NAME |STATUS |LOGGING |FOR|ENC ------------------|----------------------------------------------------------|--------------------|---------|---|--- HACK_TEST |/opt/oracle/oradata/XE/XEPDB1/hack_test_datafile_01.dbf |ONLINE |LOGGING |NO |NO <<<<<<<<<<<<<<<<<<<< Newly created data file SYSAUX |/opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf |ONLINE |LOGGING |YES|NO SYSTEM |/opt/oracle/oradata/XE/XEPDB1/system01.dbf |ONLINE |LOGGING |YES|NO UNDOTBS1 |/opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf |ONLINE |LOGGING |NO |NO USERS |/opt/oracle/oradata/XE/XEPDB1/users01.dbf |ONLINE |LOGGING |NO |NO 3.) Create a user PREY_USER with table EMP with few records. SQL> CREATE USER PREY_USER IDENTIFIED BY prey123 DEFAULT TABLESPACE HACK_TEST TEMPORARY TABLESPACE TEMP; User created. SQL> GRANT CONNECT, RESOURCE TO PREY_USER; Grant succeeded. SQL> ALTER USER PREY_USER QUOTA UNLIMITED ON HACK_TEST; User altered.
Note :
SQL>connect PREY_USER/prey123@XEPDB1 CREATE TABLE EMP (EMPNO NUMBER(4), ENAME VARCHAR2(10), MOBILE_NUMBER VARCHAR2(20), DEPTNO NUMBER(2)); INSERT INTO EMP VALUES (7566, 'JONES','+91-7448236762', 10); INSERT INTO EMP VALUES (7156, 'RSAINI','+91-7448236444', 20);
SQL> commit; Commit complete. SQL> exit
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0
Most Important Step:
4.) Execute command with sys "ALTER SYSTEM CHECKPOINT;" and "ALTER SYSTEM SWITCH LOGFILE;" to write data from buffer/cache to datafiles. Note: repeat commands from privileged account 3 to 4 time. [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 7 09:44:19 2023 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 ALTER SYSTEM CHECKPOINT; ALTER SYSTEM SWITCH LOGFILE;
SQL> System altered. SQL> System altered. [oracle@localhost ~]$ strings /opt/oracle/oradata/XE/XEPDB1/hack_test_datafile_01.dbf }|{z HACK_TEST RL,w AAAAAAAA & : N b v Qplsql_compiler_flagsINTERPRETED,NON_DEBUG Qplscope_settingsIDENTIFIERS:NONE Qplsql_ccflagsLUG Qplsql_warnings DISABLE:ALL Qnls_length_semanticsBYTE Qplsql_debug_FALSE Qplsql_code_typeUINTERPRETED Qplsql_optimize_level2 package prvt_compress wrapped a000000 abcd abcd abcd ........ ........ abcd abcd abcd abcd abcd abcd abcd 361 1ba ZX2/u+vZD4rXLycMsI/HBGH27ygwg+3xAK5qfC9AWHPVSBoJlal1T) ' ; O c w timeExja RSAINI <<<<<<<<<<<<< You can see ENAME +91-7448236444 <<<<<<<<<<<< You can see MOBILE_NUMBER JONES <<<<<<<<<<<<< You can see ENAME +91-7448236762 <<<<<<<<<<<<< You can see MOBILE_NUMBER [oracle@localhost ~]$
Hence it is proved, We can read data from oracle data files if files are not encrypted.