Dears,
In this blog we will see how we can configure oracle keystore or wallet (used to store encryption keys)
Pictures speaks better:
Keystore/Wallet - A place where encryption (table/tablespace) keys are stored by oracle.
We lock the wallet with one more key (software based - password/hardward key as well).
Oracle TDE setup overview:
1. Sqlnet.ora update
2. create the keystore (software file system based)
3. open the keystore
4. Set master encryption key
5. Encrypt data (which is tablespace creation + data loading)
6. Make the key store to auto-login or local auto-login (oracle asks this to be done @ 3rd step, but this creates ORA-28354, we better do it last)
1) sqlnet.ora update:
[oracle@vcentos79-oracle-ggsrc admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=${ORACLE_BASE}/admin/${ORACLE_SID}/wallet)))
[oracle@vcentos79-oracle-ggsrc admin]$
[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
total 8
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
drwxr-xr-x. 2 oracle oinstall 64 Jul 22 10:41 .
[oracle@vcentos79-oracle-ggsrc wallet]$
No wallet exists for now.
Check in sqlplus:
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,NOT_AVAILABLE ,UNKNOWN ,SINGLE ,UNDEFINED, 0
SQL>
2. Create Key Store:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/GGSRC01T/wallet' IDENTIFIED BY "oracle";
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/GGSRC01T/wallet' IDENTIFIED BY "oracle";
keystore altered.
set lines 300
set pages 3000
set colsep ,
SELECT * FROM v$encryption_wallet;
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,CLOSED ,UNKNOWN ,SINGLE ,UNDEFINED, 0 <<< Look at the status, wallet_type - unknown and status is closed. Also no backup.
SQL>
Output:
[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
total 12
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 10:45 ewallet.p12 <<<< Wallet created now.
drwxr-xr-x. 2 oracle oinstall 83 Jul 22 10:45 .
[oracle@vcentos79-oracle-ggsrc wallet]$
SELECT con_id, key_id FROM v$encryption_keys;
SQL> SELECT con_id, key_id FROM v$encryption_keys;
no rows selected <<< We didnt put any keys yet inside the wallet or keystore. So it is OK.
SQL>
3. Open the keystore:
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
keystore altered.
check status again:
SQL> SELECT con_id, key_id FROM v$encryption_keys;
no rows selected
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN_NO_MASTER_KEY ,PASSWORD ,SINGLE ,UNDEFINED, 0
SQL>
notes: if you wish to close the keystore or wallet...
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by "oracle";
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by "oracle";
keystore altered.
check status:
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,CLOSED ,UNKNOWN ,SINGLE ,UNDEFINED, 0
SQL> SELECT con_id, key_id FROM v$encryption_keys;
no rows selected
SQL>
[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
total 12
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 10:45 ewallet.p12 <<< Even timestamp didnt change. So the wallet or keystore isnt touched yet.
drwxr-xr-x. 2 oracle oinstall 83 Jul 22 10:45 .
[oracle@vcentos79-oracle-ggsrc wallet]$ date
Sat Jul 22 10:51:20 BST 2023
[oracle@vcentos79-oracle-ggsrc wallet]$
Now let us open it, so we can move on with next step:
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN_NO_MASTER_KEY ,PASSWORD ,SINGLE ,UNDEFINED, 0 <<< Look at the status, wallet_type - PASSWORD and status ,OPEN_NO_MASTER_KEY closed. Also backup status is undefined yet.
SQL> SELECT con_id, key_id FROM v$encryption_keys;
no rows selected
SQL>
[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
total 12
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 10:45 ewallet.p12
drwxr-xr-x. 2 oracle oinstall 83 Jul 22 10:45 .
[oracle@vcentos79-oracle-ggsrc wallet]$ date
Sat Jul 22 10:53:30 BST 2023
[oracle@vcentos79-oracle-ggsrc wallet]$
Note: This OPEN/CLOSE is only possible with password based wallet. For now with auto-login the open wont work, since there is a bug in 12.1 (ORA-28354/ORA-28417/ORA-28389)
In such errors, suggested work around is, if your wallet isnt used yet. Please rename the .sso file & check v$encryption_wallet status - you should see CLOSED, then try the close and open command as highlighted in this step.
4. Set master encryption key
The master key is generated by the keystore which later is used for encryption table columns and tablespaces.
Command:
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup';
Output:
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "oracle" WITH BACKUP USING 'master_key_addition';
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN ,PASSWORD ,SINGLE ,NO , 0 <<< Backup status turned from UNKNOWN to NO now, Status turned to just OPEN now. Since we have a master key.
SQL> SELECT con_id, key_id FROM v$encryption_keys;
CON_ID,KEY_ID
----------,------------------------------------------------------------------------------
0,AX6DCNJ6Rk+pvx8d5c8amZoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA <<< this is the master key generated by key wallet
SQL>
[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
total 16
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 11:04 ewallet_2023072210044311_master_key_addition.p12 <<< look at the backup with the using identifier we added "master_key_addition". This backup file size is same as the wallet file we had in last step.
drwxr-xr-x. 2 oracle oinstall 139 Jul 22 11:04 .
-rw-r--r--. 1 oracle oinstall 3848 Jul 22 11:04 ewallet.p12 <<<< now the file size changed and updated as well.
[oracle@vcentos79-oracle-ggsrc wallet]$ date
Sat Jul 22 11:05:14 BST 2023
[oracle@vcentos79-oracle-ggsrc wallet]$
Let us try to close and open the wallet at this stage and see how it works:
Close step:
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by "oracle";
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,CLOSED ,UNKNOWN ,SINGLE ,UNDEFINED, 0 <<<< The wallet type, status and backup all went back to pre-wallet open status. So this means that wallet when in closed state cant tell you weather there is a master key generated or not and any info.
SQL> SELECT con_id, key_id FROM v$encryption_keys;
no rows selected <<<< as soon as keystore is closed, there is no key visible here.
SQL>
[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet;date
total 16
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 11:04 ewallet_2023072210044311_master_key_addition.p12
drwxr-xr-x. 2 oracle oinstall 139 Jul 22 11:04 .
-rw-r--r--. 1 oracle oinstall 3848 Jul 22 11:04 ewallet.p12
Sat Jul 22 11:08:33 BST 2023
[oracle@vcentos79-oracle-ggsrc wallet]$
Open step:
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN ,PASSWORD ,SINGLE ,NO , 0 <<< Now wallet shows it has a master key, and is password type, backup - no!
SQL> SELECT con_id, key_id FROM v$encryption_keys;
CON_ID,KEY_ID
----------,------------------------------------------------------------------------------
0,AX6DCNJ6Rk+pvx8d5c8amZoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA <<< keys are visible
SQL>
[oracle@vcentos79-oracle-ggsrc wallet]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet;date
total 16
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 11:04 ewallet_2023072210044311_master_key_addition.p12
drwxr-xr-x. 2 oracle oinstall 139 Jul 22 11:04 .
-rw-r--r--. 1 oracle oinstall 3848 Jul 22 11:04 ewallet.p12 <<< no updates to the wallet file though
Sat Jul 22 11:10:41 BST 2023
[oracle@vcentos79-oracle-ggsrc wallet]$
alert log:
Creating new database key for new master key and wallet
Creating new database key with the new master key
Retiring: ena 2 flag 6 mkloc 0
encrypted key 401f3292413de573a9fbe43a59f853cb00000000000000000000000000000000
mkid cb022fda780b4fa4bf157975594d56b3
Creating: ena 2 flag e mkloc 1
encrypted key edd88743de731f6f2c88dc4b4958ab9700000000000000000000000000000000
mkid 7e8308d27a464fa9bf1f1de5cf1a999a
Switching out all online logs for the new master key
Thread 1 advanced to log sequence 14 (LGWR switch)
Current log# 2 seq# 14 mem# 0: /oradata/GGSRC01T/redo02.log
Logfile switch for new master key complete
New database key and new master key created successfully
2023-07-22 12:02:52.885000 +01:00
5. Encrypt data (which is tablespace creation + data loading)
Examine if there are any encrypted tablespace already...
SQL> select * from V$ENCRYPTED_TABLESPACES;
no rows selected
SQL> select * from DBA_TABLESPACE_USAGE_METRICS;
TABLESPACE_NAME ,USED_SPACE,TABLESPACE_SIZE,USED_PERCENT
------------------------------,----------,---------------,------------
SYSAUX , 84384, 1160215, 7.27313472
SYSTEM , 101176, 1173015, 8.62529465
TEMP , 0, 1095831, 0
UNDOTBS1 , 160, 1087255, .014715959
USERS , 216, 1071255, .020163266
SQL> select file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_Data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------
BYTES/1024/1024,MAXBYTES/1024/1024
---------------,------------------
/oradata/GGSRC01T/system01.dbf
800, 32767.9844
SQL> !df -m /oradata/GGSRC01T/system01.dbf
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/appdata--vg-oradata--lv 10230 1866 8365 19% /oradata
SQL> select * from V$CLIENT_SECRETS;
no rows selected
SQL> select * from DBA_ENCRYPTED_COLUMNS;
no rows selected
SQL>
SQL> select * from V$WALLET;
no rows selected
SQL>
>>> so no new information to understand.
Create the tablespace using AES256 algorithm.
CREATE TABLESPACE encrypt_ts1
DATAFILE '/oradata/GGSRC01T/encrypt_ts1_01.dbf' SIZE 1M autoextend on next 1m
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);
SQL> CREATE TABLESPACE encrypt_ts1
DATAFILE '/oradata/GGSRC01T/encrypt_ts1_01.dbf' SIZE 1M autoextend on next 1m
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT); 2 3 4
Tablespace created.
alert log:
Completed: CREATE TABLESPACE encrypt_ts1
DATAFILE '/oradata/GGSRC01T/encrypt_ts1_01.dbf' SIZE 1M autoextend on next 1m
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT)
2023-07-22 12:25:57.908000 +01:00
SQL> select * from DBA_TABLESPACE_USAGE_METRICS;
TABLESPACE_NAME ,USED_SPACE,TABLESPACE_SIZE,USED_PERCENT
------------------------------,----------,---------------,------------
ENCRYPT_TS1 , 8, 1070614, .000747235
SYSAUX , 84480, 1160086, 7.28221873
SYSTEM , 101176, 1172886, 8.6262433
TEMP , 0, 1095831, 0
UNDOTBS1 , 160, 1087255, .014715959
USERS , 216, 1071126, .020165695
6 rows selected.
SQL> select * from V$CLIENT_SECRETS;
no rows selected
SQL> select * from DBA_ENCRYPTED_COLUMNS;
no rows selected
SQL> select * from V$WALLET;
no rows selected
SQL>
SQL> select tablespace_name,ENCRYPTED from dba_tablespaces order by 1;
TABLESPACE_NAME ,ENC
--------------------,---
ENCRYPT_TS1 ,YES <<<< ENCRYPTED tablespace which we created now.
SYSAUX ,NO
SYSTEM ,NO
TEMP ,NO
UNDOTBS1 ,NO
USERS ,NO
6 rows selected.
SQL>
optional:
SQL> select * from dba_tablespaces order by 1;
TABLESPACE_NAME ,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS, MAX_SIZE,PCT_INCREASE,MIN_EXTLEN,STATUS ,CONTENTS ,LOGGING ,FOR,EXTENT_MAN,ALLOCATIO,PLU,SEGMEN,DEF_TAB_,RETENTION ,BIG,PREDICA,ENC,COMPRESS_FOR ,DEF_INME,DEF_INME,DEF_INMEMORY_DI,DEF_INMEMORY_COMP,DEF_INMEMORY_
--------------------,----------,--------------,-----------,-----------,-----------,----------,------------,----------,--------------------,---------,---------,---,----------,---------,---,------,--------,-----------,---,-------,---,------------------------------,--------,--------,---------------,-----------------,-------------
ENCRYPT_TS1 , 8192, 65536, , 1, 2147483645,2147483645, , 65536,ONLINE ,PERMANENT,LOGGING ,NO ,LOCAL ,SYSTEM ,NO ,AUTO ,DISABLED,NOT APPLY ,NO ,HOST ,YES, ,DISABLED, , , ,
SYSAUX , 8192, 65536, , 1, 2147483645,2147483645, , 65536,ONLINE ,PERMANENT,LOGGING ,YES,LOCAL ,SYSTEM ,NO ,AUTO ,DISABLED,NOT APPLY ,NO ,HOST ,NO , ,DISABLED, , , ,
SYSTEM , 8192, 65536, , 1, 2147483645,2147483645, , 65536,ONLINE ,PERMANENT,LOGGING ,YES,LOCAL ,SYSTEM ,NO ,MANUAL,DISABLED,NOT APPLY ,NO ,HOST ,NO , ,DISABLED, , , ,
TEMP , 8192, 1048576, 1048576, 1, ,2147483645, 0, 1048576,ONLINE ,TEMPORARY,NOLOGGING,NO ,LOCAL ,UNIFORM ,NO ,MANUAL,DISABLED,NOT APPLY ,NO ,HOST ,NO , ,DISABLED, , , ,
UNDOTBS1 , 8192, 65536, , 1, 2147483645,2147483645, , 65536,ONLINE ,UNDO ,LOGGING ,NO ,LOCAL ,SYSTEM ,NO ,MANUAL,DISABLED,NOGUARANTEE,NO ,HOST ,NO , ,DISABLED, , , ,
USERS , 8192, 65536, , 1, 2147483645,2147483645, , 65536,ONLINE ,PERMANENT,LOGGING ,NO ,LOCAL ,SYSTEM ,NO ,AUTO ,DISABLED,NOT APPLY ,NO ,HOST ,NO , ,DISABLED, , , ,
6 rows selected.
SQL>
let us create a table and load some data into it:
SQL> create user encvault_test identified by "encvault_test" default tablespace ENCRYPT_TS1;
User created.
SQL> alter user encvault_test quota unlimited on ENCRYPT_TS1;
User altered.
SQL> grant connect,resource to encvault_test;
Grant succeeded.
SQL> grant create table to encvault_test;
Grant succeeded.
SQL> grant select_catalog_role to encvault_test;
Grant succeeded.
SQL> grant select on dba_objects to encvault_test;
Grant succeeded.
SQL>
sqlplus encvault_test/encvault_test
...
SQL> create table encrypt_tab1 as select * from sys.dba_objects order by object_id fetch first 10 rows only;
Table created.
SQL>
SQL> select table_name,tablespace_name from user_Tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
ENCRYPT_TAB1 ENCRYPT_TS1 <<< tablespace we setup in encrypted mode is used here.
SQL>
even now dba_encrypted_columns wont show any info, since we really didnt encrypt a column.
SQL> select * from DBA_ENCRYPTED_COLUMNS;
no rows selected
SQL>
SQL> select * from DBA_TABLESPACE_USAGE_METRICS;
TABLESPACE_NAME ,USED_SPACE,TABLESPACE_SIZE,USED_PERCENT
--------------------,----------,---------------,------------
ENCRYPT_TS1 , 16, 1070614, .00149447 <<< space usage increased by 8MB more.
SYSAUX , 84480, 1160086, 7.28221873
SYSTEM , 101176, 1172886, 8.6262433
TEMP , 0, 1095702, 0
UNDOTBS1 , 160, 1087126, .014717705
USERS , 216, 1071126, .020165695
6 rows selected.
SQL>
6. Make the key store to auto-login or local auto-login
Before we try this, let us close the keystore and try access the data and see what happens:
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN ,PASSWORD ,SINGLE ,NO , 0 <<< Wallet in open state
SQL>
as db user..
SQL> select count(1) from ENCRYPT_TAB1;
COUNT(1)
----------
10
SQL>
SQL> select object_id from ENCRYPT_TAB1 order by 1 fetch first 1 rows only;
OBJECT_ID
----------
2
SQL>
Now let us close the wallet:
As sysdba..
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by "oracle";
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,CLOSED ,UNKNOWN ,SINGLE ,UNDEFINED, 0
SQL>
as db user:
SQL> select count(1) from ENCRYPT_TAB1;
select count(1) from ENCRYPT_TAB1
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> select object_id from ENCRYPT_TAB1 order by 1 fetch first 1 rows only;
select object_id from ENCRYPT_TAB1 order by 1 fetch first 1 rows only
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL>
So unless wallet is open, we will get the error we indicated above.
Let us open it now..
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "oracle";
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN ,PASSWORD ,SINGLE ,NO , 0
SQL>
SQL> select count(1) from ENCRYPT_TAB1;
COUNT(1)
----------
10
SQL> select object_id from ENCRYPT_TAB1 order by 1 fetch first 1 rows only;
OBJECT_ID
----------
2
SQL>
Looks fine now.
Let us proceed to make the wallet auto-login:
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/GGSRC01T/wallet' IDENTIFIED BY "oracle";
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/GGSRC01T/wallet' IDENTIFIED BY "oracle";
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN ,PASSWORD ,SINGLE ,NO , 0 <<< no change in status. Since it is already in open state.
SQL>
SQL> select * from V$WALLET;
no rows selected
SQL> select * from V$CLIENT_SECRETS;
no rows selected
SQL> SELECT con_id, key_id FROM v$encryption_keys;
CON_ID,KEY_ID
----------,------------------------------------------------------------------------------
0,AX6DCNJ6Rk+pvx8d5c8amZoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL>
[oracle@vcentos79-oracle-ggsrc ~]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet;date
total 20
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 11:04 ewallet_2023072210044311_master_key_addition.p12
-rw-r--r--. 1 oracle oinstall 3848 Jul 22 11:04 ewallet.p12
-rw-r--r--. 1 oracle oinstall 3893 Jul 22 12:22 cwallet.sso <<<< sso file for autologin created
drwxr-xr-x. 2 oracle oinstall 158 Jul 22 12:22 .
Sat Jul 22 12:26:12 BST 2023
[oracle@vcentos79-oracle-ggsrc ~]$
DB Restart:
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area,1442840576,bytes
Fixed Size , 2924448,bytes
Variable Size , 956301408,bytes
Database Buffers , 469762048,bytes
Redo Buffers , 13852672,bytes
Database mounted.
Database opened.
SQL>
alert log indicating about the encrypted tablespace:
SMON: enabling cache recovery
[18129] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:7258314 end:7258404 diff:90 ms (0.1 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN ,AUTOLOGIN ,SINGLE ,NO , 0 <<< The wallet is autologin now and is in open state
SQL> SELECT con_id, key_id FROM v$encryption_keys;
CON_ID,KEY_ID
----------,------------------------------------------------------------------------------
0,AX6DCNJ6Rk+pvx8d5c8amZoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL>
[oracle@vcentos79-oracle-ggsrc ~]$ ls -altr ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet;date
total 20
drwxr-x---. 7 oracle oinstall 78 Jul 16 19:24 ..
-rw-r--r--. 1 oracle oinstall 2408 Jul 16 19:43 ewallet.p12_22jul23
-rw-r--r--. 1 oracle oinstall 2453 Jul 16 19:53 cwallet.sso.bkp_17jul23
-rw-r--r--. 1 oracle oinstall 2400 Jul 22 11:04 ewallet_2023072210044311_master_key_addition.p12
-rw-r--r--. 1 oracle oinstall 3848 Jul 22 11:04 ewallet.p12
-rw-r--r--. 1 oracle oinstall 3893 Jul 22 12:22 cwallet.sso <<< no change to the file
drwxr-xr-x. 2 oracle oinstall 158 Jul 22 12:22 .
Sat Jul 22 12:50:18 BST 2023
[oracle@vcentos79-oracle-ggsrc ~]$
trying to close a autologin wallet---!!!!!!!!!
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;
keystore altered.
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE ,WRL_PARAMETER ,STATUS ,WALLET_TYPE ,WALLET_OR,FULLY_BAC, CON_ID
--------------------,----------------------------------------,--------------------,--------------------,---------,---------,----------
FILE ,/u01/app/oracle/admin/GGSRC01T/wallet/ ,OPEN ,AUTOLOGIN ,SINGLE ,NO , 0 <<<< nothing changed
SQL>
Anyway no change happened:
SQL> select count(1) from ENCRYPT_TAB1;
COUNT(1)
----------
10
SQL> select object_id from ENCRYPT_TAB1 order by 1 fetch first 1 rows only;
OBJECT_ID
----------
2
SQL>
>>>>>>>> Looks good.
This closes the blog.
Thanks
No comments:
Post a Comment