Create wallet using GUI utilities: $ORACLE_HOME/bin/owm
Start the Oracle Wallet Manager from $ORACLE_HOME/bin/owm
Select NO for now.
When we close the wallet manger, it would ask whether we want to store the wallet.
MANUALLY Setup the Wallet location
The default location of the wallet is at $ORACLE_BASE/admin/$ORACLE_SID/wallet . If the folder does not exist, we would get the ORA-28368: cannot auto-create wallet while create the wallet in next step.
We can also use sqlnet.ora to set up the location for the wallet. $ORALE_HOME/admin/network/sqlnet.ora
The ewallet.p12 file would be created under wallet directory .
Below command would create and open the wallet .
alter system set encryption key IDENTIFIED by "XXXXX";
alter system set encryption wallet open IDENTIFIED BY "XXXX";
alter system set encryption wallet close IDENTIFIED BY "XXXX";
Many document indicate can close the wallet by “ALTER SYSTEM SET WALLET CLOSE;” or “ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;” However, I always get the error ORA-28390: auto login wallet not open but encryption wallet may be open
ENCRYPTION the table column
- SYS object can not be encrypted . If we try to encrypt the sys object , we would get ORA-28336: cannot encrypt SYS owned objects error.
- To encrypt the column, the wallet must be open first .otherwise we would get ORA-28365: wallet is not open error.
CREATE TABLE test_employee (
ALTER TABLE test_employee MODIFY (salary ENCRYPT);
Test the TDE
Create the new table space TEST_TDE and create the new user
CREATE TABLESPACE TEST_TDE
DATAFILE '/u01/app/oracle/oradata/oradb0/TEST_TDE01.dbf' SIZE 256K
AUTOEXTEND ON NEXT 64K;
CREATE USER tde IDENTIFIED BY tde DEFAULT TABLESPACE TEST_TDE;
ALTER USER tde QUOTA UNLIMITED ON TEST_TDE;
GRANT CONNECT TO tde;
GRANT CREATE TABLE TO tde;
Use the new user tde create 2 tables, test_employee and test_employee2. In the test_employee table, encrypt the first column.
CREATE TABLE test_employee (
ALTER TABLE test_employee MODIFY (first_name VARCHAR2(10) ENCRYPT);
INSERT INTO test_employee VALUES ('SUMMER', 'PANDA_SUMMER',1000);
select * from test_employee;
CREATE TABLE test_employee2 (
INSERT INTO test_employee2 VALUES ('SUNNY', 'PANDA_SUNNY',3000);
select * from test_employee2;
From query perspective, they looks identical. Data in both table are retrievable.
Let issue the ALTER SYSTEM FLUSH BUFFER_CACHE; to make sure the data is written to the data file.
Use the ghex to open the data file and search the PANDA
As you can see, the first row data “SUMMER” in the table test_employee is not visible in the data file
However, the 2nd table test_employee2 does not encrypted, therefore all data are visible in the hex editor.
We can use strings to convert the binary data to the text file to conclude the same result as well.
select * from USER_ENCRYPTED_COLUMNS;
DECRYPT the data
ALTER TABLE test_employee MODIFY (first_name VARCHAR2(10) DECRYPT);
Since data is decrypt, it is visible from hex editor or strings