Check SSL Status

1-- Check SSL/TLS and encryption status for the current session
2-- Run this after connecting to RDS Oracle to verify encryption is active
3-- Usage: @check_ssl_status.sql (from sqlplus/SQLcl)
4
5SET LINESIZE 200
6SET PAGESIZE 50
7
8PROMPT === Network Protocol ===
9SELECT SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') AS network_protocol FROM dual;
10-- 'tcps' = SSL/TLS active, 'tcp' = unencrypted (check NNE below)
11
12PROMPT === Encryption Banners ===
13SELECT network_service_banner
14FROM v$session_connect_info
15WHERE sid = SYS_CONTEXT('USERENV', 'SID')
16AND network_service_banner IS NOT NULL;
17
18PROMPT === Authentication Info ===
19SELECT
20    SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD') AS auth_method,
21    SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') AS identity,
22    SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
23    SYS_CONTEXT('USERENV', 'HOST') AS client_host
24FROM dual;
25
26PROMPT === Session Details ===
27SELECT
28    s.sid,
29    s.serial#,
30    s.username,
31    s.program,
32    s.machine,
33    s.status,
34    s.logon_time
35FROM v$session s
36WHERE s.sid = SYS_CONTEXT('USERENV', 'SID');
37
38PROMPT === Database Version ===
39SELECT banner_full FROM v$version WHERE ROWNUM = 1;