Re: verifying network encryption on 11gR2?

From: Adric Norris <landstander668_at_gmail.com>
Date: Wed, 22 Jan 2014 15:07:21 -0600
Message-ID: <CAJueESouv+uKPjF+WmqfoNqo4WkOgNLNZ=0Pc5XbonFCG6KOiw_at_mail.gmail.com>

So far, it looks like [g]v$session_connect_info is the only view which exposes this information. For the time being, I'm going with the following query which seems to be doing the job reliably.

[ADRIC_at_mydb ] SQL> with sessions as (

  2     select /*+ MATERIALIZE */ inst_id, username, sid, serial#
  3        from gv$session
  4        where not ( type = 'BACKGROUND' or username is NULL )
  5     ),
  6     session_connect_info as (
  7        select /*+ MATERIALIZE */ inst_id, sid, serial#,
  8               regexp_replace( network_service_banner,
  9                               '^Oracle Advanced Security:
([[:alnum:]]+) encryption service adapter.+$',
 10                               '\1'
 11                             ) encryption_type
 12           from gv$session_connect_info
 13           where network_service_banner like 'Oracle Advanced Security:
%encryption service adapter%'
 14     )
 15  select s.inst_id, s.sid, s.serial#, s.username, sci.encryption_type
 16     from sessions s
 17        join session_connect_info sci on ( sci.inst_id = s.inst_id and
 18                                           sci.sid     = s.sid     and
 19                                           sci.serial# = s.serial#
 20                                         )
 21     order by s.sid;

   INST_ID        SID    SERIAL# USERNAME                       ENCRYPTION
---------- ---------- ---------- ------------------------------ ----------
         2        517      23965 ADRIC                          AES256

If I learn of a better option, I'll certainly let you know. Thanx all!!!

On Fri, Jan 17, 2014 at 8:41 PM, Adric Norris <landstander668_at_gmail.com>wrote:

> Is there a good way to check, from within the database, whether or not
> database sessions are utilizing network encryption? I know you can look at
> the *network_service_banner* column of *v$session_connect_info*, but the
> text format makes it difficult to parse effectively... not to mention that
> I'm not certain that it's always populated (thinking of JDBC thin clients
> here). The databases in question are all 11.2.0.3/11.2.0.4, running
> under a combination of Linux X86-64 and Solaris SPARC 64-bit.
>
> We're thinking of enabling opportunistic network encryption in the near
> future, with the goal of it eventually becoming mandatory. I'd therefore
> like to be able to identify plaintext sessions from within the database, so
> that we have an idea of which applications / groups will need to make
> configuration changes.
>
> Thanx!
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 22 2014 - 22:07:21 CET