Home » RDBMS Server » Security » Password file authentication (11.2.0.3, Windows x86 (haven;t tested any other))
Password file authentication [message #574028] Sat, 05 January 2013 12:34 Go to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
Can someone explain what is happening here:
c:\users\john\home>
c:\users\john\home>orapwd file=%ORACLE_HOME%\database\PWDorcl.ora password=oracle

c:\users\john\home>sqlplus sys/garbage@orcl as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 5 18:25:06 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

orcl> sho user
USER is "SYS"
orcl> select sys_context('userenv','ip_address') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
---------------------------------------------------------------------------------
------------------
127.0.0.1

orcl>
Why can I get a sys login, when I am connecting through the listener and giving an incorrect password? The listening address is a loopback address, is Oracle clever enough to realize that I am in fact logged on to the server as a member of the OSDBA group? I didn't think that information was passed through SQL*Net.

Of course, this is of no real significance - just interesting. At least, interesting to a dweeb like me.

Re: Password file authentication [message #574029 is a reply to message #574028] Sat, 05 January 2013 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is Oracle clever enough to realize that I am in fact logged on to the server as a member of the OSDBA group?


Yes.
If you put the actual IP address of your server instead of 127.0.0.1, you will not be able to connect (unless Oracle is smarter in 11.2 than in 10.2, I didn't test it in 11g).
(You can see the same thing happens with database link pointing to the same database, Oracle sees it is not a remote connection and does not start a distributive transaction.)

Regards
Michel
Re: Password file authentication [message #574060 is a reply to message #574029] Sun, 06 January 2013 07:22 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
Sussed. I had noticed anomalies with loop back database links, nice to have them cleared up too. It seems to work with any ip address, as long as the client is on the machine as the listener:
c:\users\john\home>lsnrctl status vlist

LSNRCTL for 32-bit Windows: Version 11.2.0.3.0 - Production on 06-JAN-2013 13:18:11

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.1)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     vlist
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.3.0 - Production
Start Date                05-JAN-2013 17:22:40
Uptime                    0 days 19 hr. 55 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\john\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\john\diag\tnslsnr\jwdell\vlist\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

c:\users\john\home>sqlplus sys/oracle@192.168.56.1:1522/orcl as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 6 13:18:30 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options


Session altered.

192.168.56.1:1522/orcl> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

c:\users\john\home>sqlplus sys/garbage@192.168.56.1:1522/orcl as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 6 13:18:40 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options


Session altered.

192.168.56.1:1522/orcl> sho user
USER is "SYS"
192.168.56.1:1522/orcl> select sys_context('userenv','ip_address') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
------------------------------------------------------------------------------------------
------------------
192.168.56.1

192.168.56.1:1522/orcl> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

c:\users\john\home>ipconfig

Windows IP Configuration


Ethernet adapter Local Area Connection 3:

   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : fe80::9919:1231:50c6:b815%41
   IPv4 Address. . . . . . . . . . . : 192.168.56.1
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . :


Re: Password file authentication [message #574063 is a reply to message #574060] Sun, 06 January 2013 08:12 Go to previous message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is also the case in 10.2, I thought I have tested it in this version but I was wrong, it was in a lower version. There was a big update in sqlnet and tns layers in 10g.
Oracle becomes smarter at each version... hopefully.

Regards
Michel
Previous Topic: OS authentication
Next Topic: Oracle Auditing DBA_STMT_AUDIT_OPTS where user_name is null
Goto Forum:
  


Current Time: Thu Dec 18 20:19:58 CST 2014

Total time taken to generate the page: 0.14013 seconds