ANBOB™

Oracle has many security features available within the database, but until now there has been no “out-of-the-box” method for protecting the data at the operating system level. The Transparent Data Encryption (TDE) feature introduced in Oracle 10g Database Release 2 allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system. This article presents some basic examples of its use.

for example: by anbob.com

configuration

[oracle@aix ~]$ cd $ORACLE_HOME
[oracle@aix db_1]$ ls
aix_anbob    dbs               jdk      nls          owm          sqlj
assistants   demo              jlib     oc4j         perl         sqlnet.log
bin          diagnostics       jre      odbc         plsql        sqlplus
cdata        has               ldap     olap         precomp      srvm
cfgtoollogs  hs                lib      OPatch       racg         sysman
clone        install           log      opmn         rdbms        uix
config       install.platform  md       oracore      relnotes     wwg
crs          inventory         mesg     oraInst.loc  root.sh      xdk
css          javavm            mgw      ord          root.sh.old
ctx          jdbc              network  oui          slax
[oracle@aix db_1]$ cd network/admin/
[oracle@aix admin]$ ls
listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora
[oracle@aix admin]$ vi sqlnet.ora


# sqlnet.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/adm
in/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/oracle/admin/anbob/encryption_wallet/))
  )

~                                                                               
                                                                           
~                                                                               
"sqlnet.ora" 10L, 309C written      

                       
[oracle@aix admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on  22 22:16:34 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system set encryption key identified by anbob;
alter system set encryption key identified by anbob
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet


SQL> !oerr ora 28368
28368, 0000, "cannot auto-create wallet"
// *Cause:  The database failed to auto create an Oracle wallet. The Oracle
//          process may not have proper file permissions or a wallet may
//          already exist.
// *Action: Confirm that proper directory permissions are granted to the Oracle
//          user and that neither an encrypted or obfuscated wallet exists in
//          the specified wallet location and try again.

原因：目录不存在
[oracle@aix anbob]$ pwd
/oracle/admin/anbob
[oracle@aix anbob]$ mkdir encryption_wallet

  
SQL> alter system set encryption key identified by "anbob";

System altered.

SQL> create table testenc (id int,name varchar2(11),card varchar2(20) encrypt)
  2  ;
create table testenc (id int,name varchar2(11),card varchar2(20) encrypt)
                                               *
ERROR at line 1:
ORA-28336: cannot encrypt SYS owned objects


SQL> conn anbob/anbob
Connected.
SQL> create table testenc (id int,name varchar2(11),card varchar2(20) encrypt);

Table created.

SQL> insert into testenc values(1,'anbob.com','1102232123123123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testenc;

        ID NAME        CARD
---------- ----------- --------------------
         1 anbob.com   1102232123123123

note:
cannot encrypt SYS owned objects

SQL> conn system/oracle
Connected.
SQL> select * from anbob.testenc;

        ID NAME        CARD
---------- ----------- --------------------
         1 anbob.com   1102232123123123

SQL> alter system set encryption wallet close;

System altered.

SQL> conn anbob/anbob
Connected.

SQL> select * from testenc;
select * from testenc
              *
ERROR at line 1:
ORA-28365: wallet is not open


SQL> select count(*) from testenc;

  COUNT(*)
----------
         1

SQL> select id,name from testenc;

        ID NAME
---------- -----------
         1 anbob.com

SQL> select id,name,card from testenc;
select id,name,card from testenc
                         *
ERROR at line 1:
ORA-28365: wallet is not open


SQL> desc testenc;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(11)
 CARD                                               VARCHAR2(20) ENCRYPT

note: 关闭了钱夹，只能访问非encrypt列

  
SQL> create index idx_testenc on testenc (card);
create index idx_testenc on testenc (card)
                                     *
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt


SQL> alter table testenc modify card varchar2(20) encrypt no salt;
alter table testenc modify card varchar2(20) encrypt no salt
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> conn system/oracle
Connected.

SQL> alter system set encryption wallet open identified by "anbob.com";
alter system set encryption wallet open identified by "anbob.com"
*
ERROR at line 1:
ORA-28353: failed to open wallet


SQL> alter system set encryption wallet open identified by "anbob";

System altered.

SQL> conn anbob/anbob
Connected.
SQL> alter table testenc modify card varchar2(20) encrypt no salt;

Table altered.

SQL> create index idx_testenc on testenc (card);

Index created.

note: encrypt salt column can’t create index, but no salt it work!and open password must same the before set password

  
SQL> conn system/oracle
Connected.

SQL> set autot on
SQL> select * from anbob.testenc where card like '11%';

        ID NAME        CARD
---------- ----------- --------------------
         1 anbob.com   1102232123123123


Execution Plan
----------------------------------------------------------
Plan hash value: 2305070768

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    66 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTENC |     1 |    66 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(INTERNAL_FUNCTION("CARD") LIKE '11%')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         36  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        540  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

note: query encrypt column “INTERNAL_FUNCTION” in Explan

[oracle@aix encryption_wallet]$ pwd
/oracle/admin/anbob/encryption_wallet
[oracle@aix encryption_wallet]$ ls
ewallet.p12

[oracle@aix ~]$ exp anbob/anbob tables=testenc file=encrypt

Export: Release 10.2.0.1.0 - Production on  22 22:39:50 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00107: Feature (COLUMN ENCRYPTION) of column CARD in table ANBOB.TESTENC is not supported. The table will not be exported.
Export terminated successfully with warnings.

[oracle@aix ~]$ oerr exp 107
00107, 00000, "Feature (%s) of column %s in table %s.%s is not supported. The table will not be exported."
// *Cause:  Export does not contain the required support for this feature.
//          The table will not be exported.
// *Action: Use the Data Pump version of Export to export this table.

SQL> show user
USER is "SYSTEM"

SQL> create directory dir_anbob as '/oracle';

Directory created.

SQL> grant read,write on directory dir_anbob to anbob;

Grant succeeded.

[oracle@aix admin]$ expdp anbob/anbob dumpfile=encrypt.dump directory=dir_anbob tables=testenc

Export: Release 10.2.0.1.0 - Production on ????????‰, 22 6???, 2011 23:00:59

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ANBOB"."SYS_EXPORT_TABLE_01":  anbob/******** dumpfile=encrypt.dump directory=dir_anbob tables=testenc 
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/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "ANBOB"."TESTENC"                           5.562 KB       1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ANBOB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ANBOB.SYS_EXPORT_TABLE_01 is:
  /oracle/encrypt.dump
Job "ANBOB"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 23:01:52

note: it not export data but can use expdp

打赏

微信扫一扫，打赏作者吧～