Tom,

I tried to do following in XE edition on Windows but it is not working. Can you help me?

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>echo %username%
administrator

C:\Documents and Settings\Administrator>

SYSTEM@XE> show parameter os_auth

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE

SYSTEM@XE>

SYSTEM@XE> create user ops$administrator identified by foobar;

User created.

SYSTEM@XE> grant connect,resource to ops$administrator;

Grant succeeded.

SYSTEM@XE>

SYSTEM@XE> conn ops$administrator/foobar
Connected.

OPS$ADMINISTRATOR@XE>

OPS$ADMINISTRATOR@XE> conn /
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
OPS$ADMINISTRATOR@XE>

What did I do wrong here?

<b>
C:\>echo %USERDOMAIN%
XP10GR1
</b>
C:\>sqlplus /

SQL*Plus: Release 10.1.0.4.0 - Production on Fri Jun 12 0

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "OPS$<b>XP10GR1\</b>TKYTE"
SQL> connect "OPS$<b>XP10GR1\</b>TKYTE"/foobar
Connected.
SQL> connect "OPS$XP10GR1\TKYTE"/foo
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

You created a user which will be authenticated by password ('foobar'). You will need to change the CREATE USER statement to

CREATE USER ops$administrator identified externally

This will instruct oracle to authenticate the user by looking into OS authentication. Here is the link to documentation (including example)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#i2065278

Narendra please read the original post and the follow up.

Tom should how to create a user with a password and still connect it thru without giving password provided you are local on the server

Reader tested on Xe and it did not work.

PLEASE READ

Tom,
Thanx for taking my Question.

You mentioned "ou do not want to do os authentication over the network - if you have to set remote_os_authent=true, you do not want to do that. "

1) Can you please explain why. what are the risks involved.

2) Method you suggested will still need useranme/password on a remote client ? Please correct me

3) Is there a way to connect remotely thru client both ways .
I was looking for that.
So I can connect using / as well as giving username/password.


4) In one of the follow up you mentioned about windoze domain.
In your example you mean the server is on windoze ?
What username did you create "initially" in the database
is it ops$tkyte or "OPS$XP10GR1\TKYTE"

Tom,

Thanks for all the information you provide on this site, it's a great resource.

Could I get your opinion on Oracle's Secure External Password Store ?

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm

I'm looking to use this in place passwords stored in "dot" files with the permissions locked down to just the owner.

Thanks!

Hi Tom,

I've been playing with OS authentication a bit, just to learn. And having issues with the domain bit:

SQL> begin
  2      dbms_stats.gather_table_stats
  3      ( ownname    => user,
  4        tabname    => 'BIG_TABLE',
  5        method_opt => 'for all indexed columns',
  6        cascade    => TRUE );
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-20001: OPS$CSOPERA10G\ADMINISTRATOR is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 13198
ORA-06512: at "SYS.DBMS_STATS", line 13218
ORA-06512: at line 2

Is there some way around this?

Thanks!

Hi Tom,

10.2.0.3 on Windoze 2003 server. Adding the quotes works beautifully, thanks.

Tom,
What is the significance/purpose of using prefix ops$ or any prefix to that matter ?

What does it do ?

But can this not be achieved even without using any os_prefix ? What if os_prefix is not set

Not sure why you are stressing so hard on the os prefix? As the name says, its just a prefix which has nothing technical to do with teh authentication. The benefit of this I guess was clearly mentioned by Tom by saying this,
it makes it clear to all of you that I'm probably "connect /" - just identified externally.

Which boils down to that if you use os prefix, its easier for you and anyone else to identify that they are dealing with an o/s authenticated user which otherwise wont be possible to dig out.

You should concentrate on the big picture that whether you want to set up o/s authentication or not? If yes than how? Prefix doesn't contribute much in the process.

Sorry Tom & "A reader".
I was wrong and I should not have jumped at conclusion.

Let me add my 2 cent.

Check your sqlnet.ora file when you are using Windows (file used by your client)

#SQLNET.AUTHENTICATION_SERVICES= (NTS)

OS authentification doesn't work


SQLNET.AUTHENTICATION_SERVICES= (NTS)

OS authentification works

Hi Tom, tough the date seems a lot older, but we should point out that today(11g) you can set the parameter to an empty string (""), so that externally dientified users do not contain prefix as explained in note: 60634.1. More important you can set to any string, xxx, ppp, ops$ or "".
Quoting from the note:
"SQL> SELECT value FROM v$parameter WHERE name = 'os_authent_prefix'; 

   VALUE 
   ------------ 
   OPS$ 

   OPS$ is the default value.
   But it can be changed by setting os_authent_prefix = "XYZ" or
   os_authent_prefix = ""  in the init<SID>.ora file and restart the database.
  
   This parameter gives the difference in name between the os-user and the
   database user: 

   os-user os_authent_prefix database user 
   ------- ----------------- --------------------------- 
   FRANK  "OPS$"             OPS$FRANK
   FRANK  ""                 FRANK
   FRANK  "XYZ"              XYZFRANK
   See Note:30785.1
   It is recommended to set this value to "", especially if you want to use any
   of the ANO Authentication Adapters. 
"

I had to issue alter user identified externally for OS authentication to work

lishai@tas:~$ sqlplus sys as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 25 20:44:49 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> show parameter os_auth

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
SQL>
SQL> alter user ops$lishai identified by NotMyRealPassword
2 /

User altered.

SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> conn /
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user ops$lishai identified externally;

User altered.

SQL> conn /
Connected.
SQL>