반응형
일반적인 On-Premise환경에서 Oracle DB Standard Edition은 Transparent Data Encryption(TDE)를 지원 하지 않는다.
그러나 OCI DBCS환경에서는 Oracle DB Standard Edition에도 TDE를 지원 한다.
TDE 설정 확인
[oracle@one19c admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@one19c admin]$ ls
samples shrept.lst sqlnet.ora tnsnames.ora
[oracle@one19c admin]$ more sqlnet.ora
# ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/$ORACLE_UNQNAME/tde)))
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)
SQLNET.EXPIRE_TIME=10
[oracle@one19c admin]$
[oracle@one19c ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 22 16:15:52 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.16.0.0.0
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
SQL> select PARAMETER, VALUE from v$option where parameter='Transparent Data Encryption';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Transparent Data Encryption FALSE
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ONE19C_PDB1 READ WRITE NO
4 SEPDB1 READ WRITE NO
SQL>
SQL>
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> set linesize 200
SQL> col WRL_TYPE for a10
SQL> col WRL_PARAMETER for a60
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ------------------------------------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE /opt/oracle/dcs/commonstore/wallets/one19c_icn1s3/tde/ OPEN AUTOLOGIN SINGLE NONE NO 1
FILE OPEN AUTOLOGIN SINGLE UNITED NO 2
FILE OPEN AUTOLOGIN SINGLE UNITED NO 3
FILE OPEN AUTOLOGIN SINGLE UNITED NO 4
SQL>
SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS YES
SQL>
암호화 Tablespace 생성
[oracle@one19c ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 22 16:15:52 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.16.0.0.0
SQL> alter session set container=SEPDB1;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 SEPDB1 READ WRITE NO
SQL>
SQL> show con_name
CON_NAME
------------------------------
SEPDB1
SQL>
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
SQL> select PARAMETER, VALUE from v$option where parameter='Transparent Data Encryption';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Transparent Data Encryption FALSE
SQL>
SQL> set linesize 200
SQL> col WRL_TYPE for a10
SQL> col WRL_PARAMETER for a60
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ------------------------------------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE OPEN AUTOLOGIN SINGLE UNITED NO 4
SQL>
SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
TEMP YES
USERS YES
SQL>
SQL> SELECT VT.NAME, VET.ENCRYPTIONALG FROM V$TABLESPACE VT, V$ENCRYPTED_TABLESPACES VET WHERE VT.TS#=VET.TS#;
NAME ENCRYPT
------------------------------ -------
SYSTEM AES128
SYSAUX AES128
UNDOTBS1 AES128
TEMP AES128
USERS AES128
SQL>
SQL> CREATE TABLESPACE ENC_TS_AES256 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_DEFAULT DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_3DES168 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_AES192 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'AES192' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_ARIA192 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'ARIA128' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_ARIA256 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'ARIA192' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_SEED128 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'SEED128' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_GOST256 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'GOST256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL>
SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
TEMP YES
USERS YES
ENC_TS_AES256 YES
ENC_TS_DEFAULT YES
ENC_TS_3DES168 YES
ENC_TS_AES192 YES
ENC_TS_ARIA192 YES
ENC_TS_ARIA256 YES
ENC_TS_SEED128 YES
ENC_TS_GOST256 YES
13 rows selected.
SQL>
SQL> SELECT VT.NAME, VET.ENCRYPTIONALG FROM V$TABLESPACE VT, V$ENCRYPTED_TABLESPACES VET WHERE VT.TS#=VET.TS#;
NAME ENCRYPT
------------------------------ -------
SYSTEM AES128
SYSAUX AES128
UNDOTBS1 AES128
TEMP AES128
USERS AES128
ENC_TS_AES256 AES256
ENC_TS_DEFAULT AES128
ENC_TS_3DES168 3DES168
ENC_TS_AES192 AES192
ENC_TS_ARIA192 ARIA128
ENC_TS_ARIA256 ARIA192
ENC_TS_SEED128 SEED128
ENC_TS_GOST256 GOST256
SQL> exit
[oracle@one19c ~]$
반응형