20170712-Oracle Encrypted Tablespaces

最新推荐文章于 2025-11-24 14:42:36 发布

原创最新推荐文章于 2025-11-24 14:42:36 发布 · 331 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

在这里插入图片描述

说明:

本文整理自我的ITPUB博客，链接如下:

https://blog.itpub.net/29785807/viewspace-2141957/

实验：创建加密表空间，并插入测试数据

#一：查看现有wallet

SQL>  select * from v$encryption_wallet;
WRL_TYPE             WRL_PARAMETER                       STATUS
-------------------- ----------------------------------- ------------------
file                 /u01/app/oracle/admin/orcl/wallet   CLOSED

二：创建目录

SQL> ho mkdir /u01/app/oracle/admin/orcl/wallet

三：创建加密KEY

SQL> alter system set encryption key identified by oracle;

四：查看现有wallet状态

SQL> select * from v$encryption_wallet;
WRL_TYPE             WRL_PARAMETER                       STATUS
-------------------- ----------------------------------- ------------------
file                 /u01/app/oracle/admin/orcl/wallet   OPEN

五：创建加密表空间

SQL> create tablespace test_encrypt datafile '/u01/app/oracle/oradata/orcl/test_encrypt01.dbf' size 10M encryption default storage(encrypt);
Tablespace created.

SQL> SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
TEST_ENCRYPT                   YES

6 rows selected.

SQL> SELECT NAME, ENCRYPTIONALG ENCRYPTEDTS
FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE
WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;  

NAME                           ENCRYPT
------------------------------ -------
TEST_ENCRYPT                   AES128

六：创建测试数据

SQL> create table t1(id number,name varchar2(20)) tablespace test_encrypt;
SQL> create table t2(id number,name varchar2(20)) tablespace users;

SQL> insert into t1 values(1,'a');
SQL> commit;
SQL> insert into t2 values(2,'b');
SQL> commit;

七：重启数据库后，钱包自动关闭，并且无法查询加密表空间里的数据

SQL> shutdown immediate
SQL> startup

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

—数据库重启后，钱包会自动关闭

SQL> select * from v$encryption_wallet;
WRL_TYPE             WRL_PARAMETER                       STATUS
-------------------- ----------------------------------- ------------------
file                 /u01/app/oracle/admin/orcl/wallet   CLOSED

八：打开钱包

SQL> alter system set wallet open identified by oracle;
System altered.
---SQL> alter system set wallet close identified by oracle;(关闭钱包)

SQL> select * from t1;
        ID NAME
---------- --------------------
         1 a

九：将t2表移动到加密表空间中

SQL> alter table t2 move tablespace test_encrypt;
System altered.

欢迎关注我的微信公众号"IT小Chen"