In Summary;
ORA-28368: cannot auto-create wallet
Solution: Create the wallet directory (in this case default wallet directory which is $ORACLE_BASE/admin/$ORACLE_SID/wallet)
ORA-28336: cannot encrypt SYS owned objects
Solution: Do not test under SYS schema :)
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Solution: Use encryption_password parameter to store encrypted data safe in the export dump. Else it will be plain text.
ORA-31693: Table data object "ERGEMP"."T_ENCRYPTED" failed to load/unload and is being skipped due to error:
ORA-28336: cannot encrypt SYS owned objects
Solution: Use your own user for exporting instead of SYS as you cannot encrypt SYS obejcts even in the export dumpfile.
Error: ORA-39087: directory name DATA_PUMP_DIR is invalid
Solution: Dont forget to give write permission to your user which you are exporting data with :)
The rest is as follows.
[oracle@defbora01]:/oracle/admin/CORET > sql
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 14 12:18:08 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set encryption key identified by "pass1234";
alter system set encryption key identified by "pass1234"
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@defbora01]:/oracle/admin/CORET > mkdir wallet
[oracle@defbora01]:/oracle/admin/CORET > sql
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 14 12:20:03 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set encryption key identified by "pass1234";
System altered.
SQL>
/*** ***/
SQL> create table t_encrypted(col1 varchar2(50) encrypt);
create table t_encrypted(col1 varchar2(50) encrypt)
*
ERROR at line 1:
ORA-28336: cannot encrypt SYS owned objects
SQL> create user ergemp identified by "pass1234" default tablespace users;
User created.
SQL>
SQL>
SQL> grant connect, resource to ergemp;
Grant succeeded.
SQL> create table ergemp.t_encrypted(col1 varchar2(50) encrypt);
Table created.
SQL> insert into ergemp.t_encrypted values ('this text should be encrypted');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dba_encrypted_columns;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
--------------- ------------------------------ ------------------------- ----------------------------- ---
ERGEMP T_ENCRYPTED COL1 AES 192 bits key YES
SQL> select * from ERGEMP.T_ENCRYPTED;
COL1
--------------------------------------------------
this text should be encrypted
SQL> I will test the datapump export. First i will not use the "encryption_password" parameter and search for the string of the column value if it is somewhere in the dumpfile.
And then i will run the same datapump export by using the "encryption_password" parameter to be sure the column value is not readable in the dumpfile with "string" utility.
[oracle@defbora01]:/oracle/orahome1/rdbms> expdp '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED' Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 11:53:18 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=ERGEMP.T_ENCRYPTED Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "ERGEMP"."T_ENCRYPTED" 4.953 KB 1 rows ORA-39173: Encrypted data has been stored unencrypted in dump file set. Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /oracle/orahome1/rdbms/log/expdp.dmp Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 11:56:53 [oracle@defbora01]:/oracle/orahome1/rdbms/log > strings expdp.dmp | grep "this text should be encrypted" this text should be encrypted [oracle@defbora01]:/oracle/orahome1/rdbms/log >
Without using the "encryption_password", data in the export files are not safe because the they are held as in plain text as it can be seen from the previous example.
[oracle@defbora01]:/oracle/orahome1/rdbms> expdp '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED' encryption_password="pass1234" Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 13:53:56 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=ERGEMP.T_ENCRYPTED encryption_password=******** Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ORA-31693: Table data object "ERGEMP"."T_ENCRYPTED" failed to load/unload and is being skipped due to error: ORA-28336: cannot encrypt SYS owned objects Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /oracle/orahome1/rdbms/log/expdp.dmp Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 13:55:55 [oracle@defbora01]:/oracle/orahome1/rdbms> expdp 'ergemp/********' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED' encryption_password="pass1234" Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 13:59:17 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name DATA_PUMP_DIR is invalid SQL> grant read,write on directory DATA_PUMP_DIR to ERGEMP; Grant succeeded. [oracle@defbora01]:/oracle/orahome1/rdbms> expdp 'ergemp/********' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables='ERGEMP.T_ENCRYPTED' encryption_password="pass1234" Export: Release 10.2.0.4.0 - 64bit Production on Friday, 15 July, 2011 14:08:59 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ERGEMP"."SYS_EXPORT_TABLE_01": 'ergemp/********' directory=DATA_PUMP_DIR dumpfile=expdp.dmp logfile=expdp.log tables=ERGEMP.T_ENCRYPTED encryption_password=******** Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "ERGEMP"."T_ENCRYPTED" 5 KB 1 rows Master table "ERGEMP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ERGEMP.SYS_EXPORT_TABLE_01 is: /oracle/orahome1/rdbms/log/expdp.dmp Job "ERGEMP"."SYS_EXPORT_TABLE_01" successfully completed at 14:10:19 [oracle@defbora01]:/oracle/orahome1/rdbms/log > strings expdp.dmp | grep "this text should be encrypted" [oracle@defbora01]:/oracle/orahome1/rdbms/log >
By using the "encryption_password" while exporting the table the data in the column cannot be distinguished by digging the plain strings in the export file.
resources:
Oracle Advanced Security Admin Guide
No comments:
Post a Comment