OraFAQ Forum: Security » Passwordfile authentication

Home » RDBMS Server » Security » Passwordfile authentication (DB 12.1.0.1, Windows 8.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passwordfile authentication [message #611820]

Wed, 09 April 2014 04:16

John Watson
Messages: 8976
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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: Passwordfile authentication [message #611832 is a reply to message #611820]

Wed, 09 April 2014 05:28

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Passwordfile authentication [message #611837 is a reply to message #611832]

Wed, 09 April 2014 05:51

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Thank you for the idea, but it is the same result:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

$ 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.

Report message to a moderator

Previous Topic:	how to implement audit purge ?
Next Topic:	Oracle password

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 05 01:21:15 CDT 2025