To determine whether an SSL/TLS connection is being used in an Oracle database session, you can query the V$SESSION_CONNECT_INFO and V$SESSION views. These views contain information about the connection type, including whether SSL is being used.

Here’s a SQL query to check if SSL/TLS is being used:

SELECT s.sid, s.serial#, s.username, s.osuser, ci.network_service_banner FROM v$session s JOIN v$session_connect_info ci ON s.sid = ci.sid WHERE ci.network_service_banner LIKE '%TLS%' OR ci.network_service_banner LIKE '%SSL%';

Explanation:

• V$SESSION_CONNECT_INFO: This view contains connection-specific information for each session, including the network service banner which can indicate if SSL/TLS is being used.
• V$SESSION: Provides session-specific information such as the session ID (SID), serial number, and the user details.
• network_service_banner: This column in the V$SESSION_CONNECT_INFO view shows the type of connection, including whether SSL or TLS is used.

Steps:

1. Join the Views: The query joins V$SESSION with V$SESSION_CONNECT_INFO on the SID to combine session information with connection details.
2. Filter by SSL/TLS: The query filters sessions where the network_service_banner contains ‘TLS’ or ‘SSL’, indicating that these sessions are using SSL/TLS for secure communication.

Result:

• If the query returns rows, it means those sessions are using SSL/TLS.
• The output will include the session ID (SID), serial number, Oracle username, OS user, and the network_service_banner confirming the use of SSL/TLS.

If the query does not return any rows, it suggests that there are no active sessions currently using SSL/TLS.