Home » RDBMS Server » Security » Passwordfile authentication (DB 12.1.0.1, Windows 8.1)
Passwordfile authentication [message #611820] Wed, 09 April 2014 04:16 Go to next message
John Watson
Messages: 4368
Registered: January 2010
Location: Global Village
Senior Member
I have an issue with SYS authentication. I'm trying to demonstrate the way password file authentication and operating system authentication work, and I cannot make Oracle use the password file. See this:
c:\users\john>
c:\users\john>sqlplus sys/oracle@127.0.0.1:1521/orclz as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 10:04:16 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

127.0.0.1:1521/orclz> select sys_context('userenv','authentication_method') from dual;

SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
----------------------------------------------------------------------------------------
OS

127.0.0.1:1521/orclz> conn sys/rubbish@127.0.0.1:1521/orclz as sysdba
Connected.
127.0.0.1:1521/orclz> select sys_context('userenv','authentication_method') from dual;

SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
----------------------------------------------------------------------------------------
OS

127.0.0.1:1521/orclz>
I am running SQL*Plus from a different Oracle home than the database; I am connecting via a listener; I am using TCP, not IPC; I do not have ORACLE_SID set to the instance name. So even though client and server are on the same machine, I do not see how OS authentication is being used rather than password file authentication. What is going on?

Any insight will be gratefully received.

Re: Passwordfile authentication [message #611832 is a reply to message #611820] Wed, 09 April 2014 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think (but can't prove it having not the environment) Oracle recognizes your are connecting from the database server itself (127.0.0.1) and bypasses the network layers. Try to use the real IP address instead.

Re: Passwordfile authentication [message #611837 is a reply to message #611832] Wed, 09 April 2014 05:51 Go to previous message
John Watson
Messages: 4368
Registered: January 2010
Location: Global Village
Senior Member
Thank you for the idea, but it is the same result:
c:\users\john>
c:\users\john>sqlplus sys/oracle@192.168.1.65:1521/orclz as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 11:37:42 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

192.168.1.65:1521/orclz> select sys_context('userenv','authentication_method') from dual;

SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
---------------------------------------------------------------------------
OS

192.168.1.65:1521/orclz>
I am wondering if this is a Windows thing. I have tested (on Windows) with both 11.2.0.3 and 12.1.0.1, and get this same result. On an 11.2.0.3 Linux system (client and server off the same home, loopback IP address, ORACLE_SID exported) I get this:
$ sqlplus sys/rubbish@127.0.0.1:1521/apexres1 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 06:45:20 2014

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: sys/oracle@127.0.0.1:1521/apexres1 as sysdba

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


Session altered.

127.0.0.1:1521/apexres1> set pages 10000
127.0.0.1:1521/apexres1> set lines 120
127.0.0.1:1521/apexres1> select sys_context('userenv','authentication_method') from dual;

SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
--------------------------------------------------------------------------------------------
PASSWORD

127.0.0.1:1521/apexres1>

which is what I expected. I am still no nearer to forcing Oracle on Windows to use the passwordfile. If I can work out how to create another Windows user who is not in the OSDBA group, perhaps then I'll get somewhere.
Previous Topic: how to implement audit purge ?
Next Topic: Oracle password
Goto Forum:
  


Current Time: Tue Jul 22 07:22:37 CDT 2014

Total time taken to generate the page: 0.09314 seconds