Can this work on XE also?
Reader, June 12, 2009 - 1:40 am UTC
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?
June 12, 2009 - 10:04 am UTC
you are on windoze - which has this "domain thingy"
<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.
for "Reader from IN"
Narendra, June 12, 2009 - 4:12 am UTC
June 12, 2009 - 10:06 am UTC
no, you don't....
as I wrote above:
I achieved that by create user ops$tkyte identified by foobar;
the ops$ is sufficient - unless you are windoze - then you might need the domain name too.
Narendra Thats not True
A reader, June 12, 2009 - 9:24 am UTC
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
June 12, 2009 - 11:28 am UTC
it wasn't XE, it is "windows" that is causing the issue - see above, the domain is part of the name
Questions :
A reader, June 12, 2009 - 10:41 am UTC
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"
June 12, 2009 - 11:39 am UTC
1) set remote os authent true and I'll drop my laptop into your network, boot up linux and create an OS account with a name that matches your username and log in remotely using that.
2) yes, as I said, over the network you use user/pass, on the server, you just use "/"
3) if you have to set remote os authent, you do not want to do that - as stated. If you have windows - you might be able to remotely connect via sqlnet without having to use remote os authent. See the windows admin guides for information.
4) the username is ops$<domain>\tkyte - show user showed you that...
Other alternatives?
THG, June 12, 2009 - 12:53 pm UTC
June 12, 2009 - 1:16 pm UTC
yes, this would be better than simple, unencrypted OS files
Windoze
Stephan, June 12, 2009 - 2:05 pm UTC
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!
June 12, 2009 - 2:55 pm UTC
what release, I cannot reproduce that issue.
try a quoted identifier '"' || user || '"'
Thanks!
Stephan, June 12, 2009 - 3:05 pm UTC
Hi Tom,
10.2.0.3 on Windoze 2003 server. Adding the quotes works beautifully, thanks.
Question
A reader, June 12, 2009 - 3:19 pm UTC
Tom,
What is the significance/purpose of using prefix ops$ or any prefix to that matter ?
What does it do ?
June 12, 2009 - 4:23 pm UTC
with os_authent_prefix set to ops$, it allows you to
a) connect /
b) connect ops$user/pw
c) connect ops$user/pw@database
you can be both "identified externally" and "identified by a password"
and for me, it makes it clear to all of you that I'm probably "connect /" - just identified externally.
Question
A reader, June 12, 2009 - 4:51 pm UTC
But can this not be achieved even without using any os_prefix ? What if os_prefix is not set
June 15, 2009 - 11:35 am UTC
then no, it won't work - you are either identified externally OR via password (or globally or whatever) - but just one thing
(ops$ is what you get when it is not set - it is the default)
the os_authent_prefix is the "magic" that makes/lets this work. The ability to be external AND password authenticated.
If you don't have a prefix, it doesn't know what users should be "externally" authenticated
to "a reader"
Aman...., June 12, 2009 - 10:33 pm UTC
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.
Apologies, my mistake
Narendra, June 15, 2009 - 3:27 am UTC
Sorry Tom & "A reader".
I was wrong and I should not have jumped at conclusion.
Windows stuff
dmitryR, July 29, 2009 - 12:57 pm UTC
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
os_authent_prefix can be set to null string
Nazir, June 22, 2011 - 7:53 am UTC
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.
"
Does not work for me on oracle 12.2
Lishai, June 25, 2018 - 5:47 pm UTC
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>
June 26, 2018 - 3:47 am UTC
Yes you're right - this was changed in recent releases for added security. I'll update the question to bring it up to date.