I have been given access to a certain database at Oracle level, but no
UNIX access.
Is it possible to determine whether a connection (Oracle session) is
Bequeath connection or connection through listener by looking at V$
views?
Thanks

On Jan 27, 9:18=A0pm, vsevolod afanassiev
<vsevolod.afanass...@gmail.com> wrote:
> I have been given access to a certain database at Oracle level, but no
> UNIX access.
> Is it possible to determine whether a connection (Oracle session) is
> Bequeath connection or connection through listener by looking at V$
> views?
> Thanks

Possibly:

select username, osuser, process, program
from v$session
where username is not null
and machine =3D (select host_name from v$instance);

You may see some emagent sessions which won't be listed as BEQ
connections (at least not by ps -ef) but it will show you all of the
local connections bearing actual user names.


David Fitzjarrell

Hi David,

Thanks for responding

The query that you provided will show connections coming from the same
server
where the database is running. However this is not the same as
Bequeath
connection.

On 28.01.2010 03:18, vsevolod afanassiev wrote:
> I have been given access to a certain database at Oracle level, but no
> UNIX access.
> Is it possible to determine whether a connection (Oracle session) is
> Bequeath connection or connection through listener by looking at V$
> views?
> Thanks

You can look at v$session_connect_info:

SQL> connect system
Enter password: ******
Connected.
SQL> select network_service_banner from v$session_connect_info where 
sid=sys_context('userenv','sid');

NETWORK_SERVICE_BANNER
----------------------------------------------------------------------------------------------------
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - 
Production
Oracle Advanced Security: authentication service for Linux: Version 
10.2.0.4.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 
10.2.0.4.0 - Production
Oracle Advanced Security: crypto-checksumming service for Linux: Version 
10.2.0.4.0 - Production

SQL> connect system@emrep
Enter password: ******
Connected.
SQL> select network_service_banner from v$session_connect_info where 
sid=sys_context('userenv','sid');

NETWORK_SERVICE_BANNER
----------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 
10.2.0.4.0 - Production
Oracle Advanced Security: crypto-checksumming service for Linux: Version 
10.2.0.4.0 - Production
Oracle Advanced Security: crypto-checksumming service for Linux: Version 
10.2.0.4.0 - Production


Best regards

Maxim

On Jan 28, 2:48=A0am, Maxim Demenko <mdeme...@gmail.com> wrote:
> On 28.01.2010 03:18, vsevolod afanassiev wrote:
>
> > I have been given access to a certain database at Oracle level, but no
> > UNIX access.
> > Is it possible to determine whether a connection (Oracle session) is
> > Bequeath connection or connection through listener by looking at V$
> > views?
> > Thanks
>
> You can look at v$session_connect_info:
>
> SQL> connect system
> Enter password: ******
> Connected.
> SQL> select network_service_banner from v$session_connect_info where
> sid=3Dsys_context('userenv','sid');
>
> NETWORK_SERVICE_BANNER
> -------------------------------------------------------------------------=
--=AD-------------------------
> Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 -
> Production
> Oracle Advanced Security: authentication service for Linux: Version
> 10.2.0.4.0 - Production
> Oracle Advanced Security: encryption service for Linux: Version
> 10.2.0.4.0 - Production
> Oracle Advanced Security: crypto-checksumming service for Linux: Version
> 10.2.0.4.0 - Production
>
> SQL> connect system@emrep
> Enter password: ******
> Connected.
> SQL> select network_service_banner from v$session_connect_info where
> sid=3Dsys_context('userenv','sid');
>
> NETWORK_SERVICE_BANNER
> -------------------------------------------------------------------------=
--=AD-------------------------
> TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
> Oracle Advanced Security: encryption service for Linux: Version
> 10.2.0.4.0 - Production
> Oracle Advanced Security: crypto-checksumming service for Linux: Version
> 10.2.0.4.0 - Production
> Oracle Advanced Security: crypto-checksumming service for Linux: Version
> 10.2.0.4.0 - Production
>
> Best regards
>
> Maxim

I had forgotten all about this view.  It is pretty easy to modify the
where clause using a like to find all connections using TCP/IP or
Bequeath.

HTH -- Mark D Powell --

Thanks for this information. It seems that there are 4 lines in this
view for each session.
If session connected through listener then first line has
network_service_banner = 'TCP/IP NT Protocal Adapter'
If session connected through Bequeath then first line has
network_service_banner = 'Oracle Bequeath Network Protocol Adapter'

What's about next 3 lines? They start from 'Oracle Advanced
Security...' - I thought that
Advanced Security is separately licensed option - and we don't even
install it!

Op 29-1-2010 3:30, vsevolod afanassiev schreef:
> Thanks for this information. It seems that there are 4 lines in this
> view for each session.
> If session connected through listener then first line has
> network_service_banner = 'TCP/IP NT Protocal Adapter'
> If session connected through Bequeath then first line has
> network_service_banner = 'Oracle Bequeath Network Protocol Adapter'
>
> What's about next 3 lines? They start from 'Oracle Advanced
> Security...' - I thought that
> Advanced Security is separately licensed option - and we don't even
> install it!
>
>
>
Then maybe your DBA forgot to deactivate it. If you don't do so, the 
License Police may require you to pay the license.... even if you NEVER 
used it.

Shakespeare

On Jan 29, 1:47=A0am, Shakespeare <what...@xs4all.nl> wrote:
> Op 29-1-2010 3:30, vsevolod afanassiev schreef:> Thanks for this informat=
ion. It seems that there are 4 lines in this
> > view for each session.
> > If session connected through listener then first line has
> > network_service_banner =3D 'TCP/IP NT Protocal Adapter'
> > If session connected through Bequeath then first line has
> > network_service_banner =3D 'Oracle Bequeath Network Protocol Adapter'
>
> > What's about next 3 lines? They start from 'Oracle Advanced
> > Security...' - I thought that
> > Advanced Security is separately licensed option - and we don't even
> > install it!
>
> Then maybe your DBA forgot to deactivate it. If you don't do so, the
> License Police may require you to pay the license.... even if you NEVER
> used it.
>
> Shakespeare

Hmmm, I see the four entries per connection, but I don't see anything
about Advanced Security in DBA_FEATURE_USAGE_STATISTICS or V_$OPTION
(10.2.0.4).

Maybe this is evidence of one of those things that are there for
Oracle internal usage but not generally seen unless you install it?  I
don't have an Advanced Security to check.  Given the high visibility
of security issues, I would hope the base product would be secure...

jg
--
@home.com is bogus.
The perfectability of spam
http://www.newscientist.com/article/mg20527446.000-to-beat-spam-turn-its-ow=
n-weapons-against-it.html?haasFormId=3D46aa9eea-8158-4b87-8d70-3994c2a1bd64=
&haasPage=3D0

On 29.01.2010 18:17, joel garry wrote:
> On Jan 29, 1:47 am, Shakespeare<what...@xs4all.nl>  wrote:
>> Op 29-1-2010 3:30, vsevolod afanassiev schreef:>  Thanks for this information. It seems that there are 4 lines in this
>>> view for each session.
>>> If session connected through listener then first line has
>>> network_service_banner = 'TCP/IP NT Protocal Adapter'
>>> If session connected through Bequeath then first line has
>>> network_service_banner = 'Oracle Bequeath Network Protocol Adapter'
>>
>>> What's about next 3 lines? They start from 'Oracle Advanced
>>> Security...' - I thought that
>>> Advanced Security is separately licensed option - and we don't even
>>> install it!
>>
>> Then maybe your DBA forgot to deactivate it. If you don't do so, the
>> License Police may require you to pay the license.... even if you NEVER
>> used it.
>>
>> Shakespeare
>
> Hmmm, I see the four entries per connection, but I don't see anything
> about Advanced Security in DBA_FEATURE_USAGE_STATISTICS or V_$OPTION
> (10.2.0.4).
>
> Maybe this is evidence of one of those things that are there for
> Oracle internal usage but not generally seen unless you install it?  I
> don't have an Advanced Security to check.  Given the high visibility
> of security issues, I would hope the base product would be secure...
>
> jg
> --
> @home.com is bogus.
> The perfectability of spam
> http://www.newscientist.com/article/mg20527446.000-to-beat-spam-turn-its-own-weapons-against-it.html?haasFormId=46aa9eea-8158-4b87-8d70-3994c2a1bd64&haasPage=0

Afaik, in the base product secured is only password transfer, nothing 
more, if you want your data to be transferred secure, you'll need either 
use secure channel (ipsec,tunnel over ssh,etc) or enable advanced 
security option. To my knowledge, ASO is installed, if you select 
enterprise edition, you can only deselect it if you choose custom 
install. The fact, that it is not reflected in 
dba_feature_usage_statistics is explained in the Note 843077.1
<quote>
The code changes in 10.2.0.4 has isolated Oracle Advanced Security 
options as a set of adapters rather than feature in the database , due 
to which ASO should not be seen in DBA_FEATURE_USAGE_STATISTICS itself .
</quote>
However, due to bug 5475037 it still may be shown there.

Whether the ASO is installed, can be verified with the shell script 
adapters, which yield output similar to

oracle$ adapters

Installed Oracle Net transport protocols are:

     IPC
     BEQ
     TCP/IP
     SSL
     RAW
     SDP/IB

Installed Oracle Net naming methods are:

     Local Naming (tnsnames.ora)
     Oracle Directory Naming
     Oracle Host Naming
    Error!!!   Oracle Names Server Naming is not completely installed!

Installed Oracle Advanced Security options are:

     RC4 40-bit encryption
     RC4 56-bit encryption
     RC4 128-bit encryption
     RC4 256-bit encryption
     DES40 40-bit encryption
     DES 56-bit encryption
     3DES 112-bit encryption
     3DES 168-bit encryption
     AES 128-bit encryption
     AES 192-bit encryption
     AES 256-bit encryption
     MD5 crypto-checksumming
     SHA-1 crypto-checksumming
     Kerberos v5 authentication
     RADIUS authentication


I think, to install ASO is not a violation against license terms, if it 
is not used, however i'm not sure, how oracle track the usage of it (as 
opposite to options in dba_feature_usage_statistics).

To deinstall ASO the most straightforward way would be via launch 
runInstaller.
There is a Note 1020065.102 which describes, how to remove all external 
references to authentication adapters for Oracle 7.3, which basically 
consists of regeneration of nautab.o (the script genautab produces the 
code) and recompilation all oracle components (rdbms, network, etc), 
but, it seems, genautab is no more included since oracle 9i (in Oracle 
8i it is still here), and, maybe the process doesn't fully apply to 
recent Oracle versions, so the Oracle Installer would be the right choice.

Best regards

Maxim

Op 29-1-2010 20:10, Maxim Demenko schreef:
> On 29.01.2010 18:17, joel garry wrote:
>> On Jan 29, 1:47 am, Shakespeare<what...@xs4all.nl> wrote:
>>> Op 29-1-2010 3:30, vsevolod afanassiev schreef:> Thanks for this
>>> information. It seems that there are 4 lines in this
>>>> view for each session.
>>>> If session connected through listener then first line has
>>>> network_service_banner = 'TCP/IP NT Protocal Adapter'
>>>> If session connected through Bequeath then first line has
>>>> network_service_banner = 'Oracle Bequeath Network Protocol Adapter'
>>>
>>>> What's about next 3 lines? They start from 'Oracle Advanced
>>>> Security...' - I thought that
>>>> Advanced Security is separately licensed option - and we don't even
>>>> install it!
>>>
>>> Then maybe your DBA forgot to deactivate it. If you don't do so, the
>>> License Police may require you to pay the license.... even if you NEVER
>>> used it.
>>>
>>> Shakespeare
>>
>> Hmmm, I see the four entries per connection, but I don't see anything
>> about Advanced Security in DBA_FEATURE_USAGE_STATISTICS or V_$OPTION
>> (10.2.0.4).
>>
>> Maybe this is evidence of one of those things that are there for
>> Oracle internal usage but not generally seen unless you install it? I
>> don't have an Advanced Security to check. Given the high visibility
>> of security issues, I would hope the base product would be secure...
>>
>> jg
>> --
>> @home.com is bogus.
>> The perfectability of spam
>> http://www.newscientist.com/article/mg20527446.000-to-beat-spam-turn-its-own-weapons-against-it.html?haasFormId=46aa9eea-8158-4b87-8d70-3994c2a1bd64&haasPage=0
>>
>
> Afaik, in the base product secured is only password transfer, nothing
> more, if you want your data to be transferred secure, you'll need either
> use secure channel (ipsec,tunnel over ssh,etc) or enable advanced
> security option. To my knowledge, ASO is installed, if you select
> enterprise edition, you can only deselect it if you choose custom
> install. The fact, that it is not reflected in
> dba_feature_usage_statistics is explained in the Note 843077.1
> <quote>
> The code changes in 10.2.0.4 has isolated Oracle Advanced Security
> options as a set of adapters rather than feature in the database , due
> to which ASO should not be seen in DBA_FEATURE_USAGE_STATISTICS itself .
> </quote>
> However, due to bug 5475037 it still may be shown there.
>
> Whether the ASO is installed, can be verified with the shell script
> adapters, which yield output similar to
>
> oracle$ adapters
>
> Installed Oracle Net transport protocols are:
>
> IPC
> BEQ
> TCP/IP
> SSL
> RAW
> SDP/IB
>
> Installed Oracle Net naming methods are:
>
> Local Naming (tnsnames.ora)
> Oracle Directory Naming
> Oracle Host Naming
> Error!!! Oracle Names Server Naming is not completely installed!
>
> Installed Oracle Advanced Security options are:
>
> RC4 40-bit encryption
> RC4 56-bit encryption
> RC4 128-bit encryption
> RC4 256-bit encryption
> DES40 40-bit encryption
> DES 56-bit encryption
> 3DES 112-bit encryption
> 3DES 168-bit encryption
> AES 128-bit encryption
> AES 192-bit encryption
> AES 256-bit encryption
> MD5 crypto-checksumming
> SHA-1 crypto-checksumming
> Kerberos v5 authentication
> RADIUS authentication
>
>
> I think, to install ASO is not a violation against license terms, if it
> is not used, however i'm not sure, how oracle track the usage of it (as
> opposite to options in dba_feature_usage_statistics).
>
> To deinstall ASO the most straightforward way would be via launch
> runInstaller.
> There is a Note 1020065.102 which describes, how to remove all external
> references to authentication adapters for Oracle 7.3, which basically
> consists of regeneration of nautab.o (the script genautab produces the
> code) and recompilation all oracle components (rdbms, network, etc),
> but, it seems, genautab is no more included since oracle 9i (in Oracle
> 8i it is still here), and, maybe the process doesn't fully apply to
> recent Oracle versions, so the Oracle Installer would be the right choice.
>
> Best regards
>
> Maxim

To my experience, Oracle LMS (License Management Services) checks if the 
option is unchecked in Enterprise Manager. I am not sure though, if this 
option still shows up there, have no installation available at the 
moment. For other options/packs: a check means installed means used 
means have to pay (according to Oracle).

Shakespeare

On Jan 29, 11:17=A0am, Shakespeare <what...@xs4all.nl> wrote:
> Op 29-1-2010 20:10, Maxim Demenko schreef:
>

[Excellent descriptions snipped]

Thanks guys!

jg
--
@home.com is bogus.
What's in a name?  http://www.ktla.com/news/landing/ktla-concrete-lotto,0,6=
757304.story