-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathssenc_info.sql
150 lines (139 loc) · 5.98 KB
/
ssenc_info.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
--------------------------------------------------------------------------------
-- OraDBA - Oracle Database Infrastructure and Security, 5630 Muri, Switzerland
--------------------------------------------------------------------------------
-- Name......: ssenc_info.sql
-- Author....: Stefan Oehrli (oes) [email protected]
-- Editor....: Stefan Oehrli
-- Date......: 2023.08.30
-- Revision..:
-- Purpose...: Show information about the TDE Configuration
-- Notes.....:
-- Reference.: Requires SYS, SYSDBA or SYSKM privilege
-- License...: Apache License Version 2.0, January 2004 as shown
-- at http://www.apache.org/licenses/
--------------------------------------------------------------------------------
-- format SQLPlus output and behavior
SET LINESIZE 180 PAGESIZE 66
SET HEADING ON
SET VERIFY ON
SET FEEDBACK ON
COLUMN wrl_type FORMAT A8
COLUMN wrl_parameter FORMAT A75
COLUMN status FORMAT A18
COLUMN wallet_type FORMAT A15
COLUMN con_id FORMAT 99999
COLUMN name FORMAT A42
COLUMN value FORMAT A60
COLUMN memory FORMAT A50
COLUMN spfile FORMAT A50
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN encryption_alg FORMAT A5
COLUMN salt FORMAT A5
COLUMN integrity_alg FORMAT A5
COLUMN Parameter FORMAT A42
COLUMN Container FORMAT A12
COLUMN Session FORMAT A9
COLUMN Instance FORMAT A35
COLUMN S FORMAT A1
COLUMN I FORMAT A1
COLUMN D FORMAT A1
COLUMN Description FORMAT A65
COLUMN key_id FORMAT A52
COLUMN creation_time FORMAT A20
COLUMN creator FORMAT A10
COLUMN creator_pdbname FORMAT A10
COLUMN activation_time FORMAT A20
COLUMN activating_pdbname FORMAT A10
ALTER SESSION SET nls_timestamp_tz_format='DD.MM.YYYY HH24:MI:SS';
SPOOL ssenc_info.log
-- list pending parameter init.ora parameter for TDE information i.e., parameter
-- which are set in SPFILE but not yet active in memory
PROMPT == Pending parameter for the TDE configuration in SPFILE ================
SELECT vsp.name,nvl(vp.value,'undef') memory,nvl(vsp.value,'undef') spfile,vsp.ISSPECIFIED FROM v$parameter vp, v$spparameter vsp
WHERE vsp.name IN ('wallet_root','tde_configuration','tablespace_encryption','encrypt_new_tablespaces')
AND vsp.name = vp.name
AND nvl(vsp.value,'undef') != nvl(vp.value,'undef')
AND vsp.isspecified ='TRUE'
ORDER BY vsp.name;
PROMPT == Current regular parameter for TDE configuration ======================
SELECT
lower(a.ksppinm) "Parameter",
decode(c.con_id,0,'CDB',(SELECT pdb.name||' ('||pdb.con_id||')' FROM v$pdbs pdb WHERE pdb.con_id=c.con_id)) "Container",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND upper(a.ksppinm) IN (
'WALLET_ROOT',
'TDE_CONFIGURATION',
'TABLESPACE_ENCRYPTION',
'ENCRYPT_NEW_TABLESPACES',
'EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION'
)
ORDER BY a.ksppinm,c.con_id;
PROMPT == Current hidden parameter for TDE configuration =======================
SELECT
lower(a.ksppinm) "Parameter",
decode(c.con_id,0,'CDB',(SELECT pdb.name||' ('||pdb.con_id||')' FROM v$pdbs pdb WHERE pdb.con_id=c.con_id)) "Container",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND upper(a.ksppinm) IN (
'_DB_DISCARD_LOST_MASTERKEY',
'_REMOVE_INACTIVE_STANDBY_TDE_MASTER_KEY',
'_REMOVE_STDBY_OLD_KEY_AFTER_CHECKPOINT_SCN',
'_ASSERT_ENCRYPTED_TABLESPACE_BLOCKS',
'_BACKUP_ENCRYPT_OPT_MODE',
'_OVERRIDE_DATAFILE_ENCRYPT_CHECK',
'_TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM',
'_USE_HYBRID_ENCRYPTION_MODE',
'_USE_PLATFORM_ENCRYPTION_LIB',
'_VERIFY_ENCRYPTED_TABLESPACE_KEYS',
'_AUTO_REKEY_DURING_MRCV',
'_DB_GENERATE_DUMMY_MASTERKEY',
'_TSENC_OBFUSCATE_KEY'
)
ORDER BY a.ksppinm,c.con_id;
-- list encryption wallet information
PROMPT == Encryption wallet information from v$encryption_wallet ===============
SELECT * FROM v$encryption_wallet;
-- list encryption key information
PROMPT == Encryption key information from v$encryption_keys ===============
SELECT
key_id,key_use, keystore_type, backed_up,creation_time,creator,
creator_pdbname,activation_time,activating_pdbname,con_id
FROM v$encryption_keys ORDER BY creation_time;
-- list wallet information
PROMPT == Wallet information from v$wallet =====================================
SELECT * FROM v$wallet;
-- list information about TDE TS
PROMPT == List of encrypted tablespaces ========================================
SELECT tablespace_name, encrypted FROM dba_tablespaces WHERE encrypted ='YES';
PROMPT == Details about encrypted tablespaces ==================================
SELECT
t.ts#,t.name,
et.encryptionalg,
et.encryptedts,
et.blocks_encrypted,
et.blocks_decrypted,
et.con_id
FROM v$tablespace t, v$encrypted_tablespaces et
WHERE t.ts# = et.ts#;
-- list information about TDE column
PROMPT == Details about encrypted table columns ================================
SELECT * FROM dba_encrypted_columns;
SPOOL OFF
-- EOF -------------------------------------------------------------------------