Validate and test

4 min

after setting up and opening the encryption wallet, it’s important to verify that transparent data encryption (tde) is functioning correctly validation ensures that the wallet is active and properly associated with your database, while testing confirms that encryption works as expected when applied to database objects in this section, we will confirm the wallet status from both the {{cdb}} and {{pdb}} verify that the wallet key is available for encryption operations test tde functionality by closing to wallet and try to attain the data these steps assure that your wallet configuration is successful and that tde is ready for secure use in your oracle environment validate wallet is open to validate that the wallet is open, run the following command sql> select wrl type, wrl parameter, wallet type, status from v$encryption wallet; if the wallet is configured properly, status of hsm will show as open as shown below wrl type | wrl parameter | wallet type | status \ + + + file | /etc/oracle/wallets/tde/ | autologin | open no master key hsm | | hsm | open validate if tde is active connect to the oracle database, and make sure that the database instance is running $ sqlplus / as sysdbasql> startup; switch to a preferred pdb container sql sql> alter session set container = \<pdb container>; check if the pdb container has a hsm keystore set up sql> select wrl type, status, wallet type from v$encryption wallet; important each pdb must have its own keystore opened and a tde master key generated this process should be performed only after the cdb’s keystore has been opened and its tde master key created repeat the steps from the previous page for every pdb requiring tde encryption create a test user sql sql> create user test user identified by "\<user password>"; grant basic privileges to the user sql sql> grant connect, resource to test user; grant quota unlimited to the user sql> alter user test user quota unlimited on users; in a separate terminal, connect to the newly created user sqlplus test user/\<user password>@\<hostname> \<port>/\<pdb name> important to check which hostname and port the pdb is connected to, check the listener ora or tnsnames ora files cat $oracle home/network/admin/listener ora cat $oracle home/network/admin/tnsnames ora create a table sql> create table customers (id number(5), name varchar2(42), credit limit number(10)); insert test data into the table sql> insert into customers values (1, 'test user', 1000); sql> insert into customers values (2, 'test user 2', 2000); encrypt a column using the tde key on cryptohub sql> alter table customers modify (credit limit encrypt); check if the column is encrypted sql> select table name, column name, encryption alg from user encrypted columns where table name = 'customers'; a successful output will look similar to the output below table name column name encryption alg \ customers credit limit aes 192 bits key test if tde is active in the terminal that is logged in as the newly created user, run the following command to see the data in the credit limit column in the table sql> select credit limit from customers; output should look similar to credit limit \ 	1000 	2000 in another terminal that is logged in as sys, close the pdb wallet sql> administer key management set keystore close identified by "cryptohub identity password"; in the terminal that is logged in as the newly created user, try to view the data in the credit limit column sql> select credit limit from customers; a successful tde output will look similar to below error at line 1 ora 28365 wallet is not open