authenti

why external authentication fails here

SQL> create user OPS$SCOTT identified by TIGER;


User created.

SQL> SQL> create user "OPS$CLAIMDOMAIN\SCOTT"
identified externally;    
  3  ;
identified external
           *
ERROR at line 2:
ORA-00924: missing BY keyword


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@newmac dbs]$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 26 14:25:44 2013

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

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


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


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


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@newmac dbs]$ sqlplus ops$scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 26 14:26:25 2013

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

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


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


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


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@newmac dbs]$
[oracle@newmac dbs]$
[oracle@newmac dbs]$ sqlplus ops$scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 26 14:26:33 2013

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

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


Enter user-name:
jcob_lAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
OPS$ and identified externally is for OS authentication.

For this to work, you need to be logged in to the unix box as 'scott'.

Create a scott unix user then:
create user OPS$SCOTT identified externally;

then log into unix as scott and try:
sqlplus /
0
jcob_lAuthor Commented:
SQL> create user OPS$SCOTT identified externally;
create user OPS$SCOTT identified externally
            *
ERROR at line 1:
ORA-01920: user name 'OPS$SCOTT' conflicts with another user or role name


SQL>
0
slightwv (䄆 Netminder) Commented:
>>ORA-01920: user name 'OPS$SCOTT' conflicts with another user or role name

You already created him...  you need to drop him first:
drop user ops$scott;
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jcob_lAuthor Commented:
Is this the expected output.


SQL> drop user ops$scott;

User dropped.

SQL> create user OPS$SCOTT identified externally;

User created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@newmac dbs]$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 26 15:16:32 2013

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

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


Enter user-name: scott
Enter password:

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

SQL> username
SP2-0042: unknown command "username" - rest of line ignored.
SQL> show user;
USER is "SCOTT"
SQL>
0
slightwv (䄆 Netminder) Commented:
>>[oracle@newmac dbs]$ sqlplus /

Are you logged into to Unix as the user scott?

From an OS command prompt:
id

>>Enter user-name: scott

The regular database user scott is different from the ops$scott user.
0
slightwv (䄆 Netminder) Commented:
What identified externally does is tell Oracle that if you are logged in to the operating system, you can log into the database without a password.  Oracle uses the operating systems username as the database username but the database username must start with the prefix specified by:

SQL> show parameter os_authent_prefix
os_authent_prefix                    string      OPS$


OPS$ is the default so when you create: OPS$SCOTT, this means the operating system user scott.
0
jcob_lAuthor Commented:
ok..

Then what is the advantage then....

ops$scott does not have any data right...
0
slightwv (䄆 Netminder) Commented:
>>Then what is the advantage then....

The main advantage is that you don't need to hard-code any usernames or passwords in scripts.

There are disadvantages:  Mainly from a security standpoint.  If I compromise the OS scott user, I have access to the database without a password.

>>ops$scott does not have any data right...

The user is no different than any other user:  They only have whatever rights you grant them.
0
jcob_lAuthor Commented:
Can you explain this..
The main advantage is that you don't need to hard-code any usernames or passwords in scripts ------------------------
0
slightwv (䄆 Netminder) Commented:
>>The main advantage is that you don't need to hard-code any usernames or passwords in scripts

When you need to script something for automation/scheduling of just for normal everyday things these are typically SHELL scripts in Unix (BAT in Windows).

If any of these scripts need to access sqlplus, the common practice is to hard-code the username and password in the Shell script file.

Something like:
#/bin/ksh
sqlplus scott/tiger <<!EOF
select sysdate from dual;
EOF

Open in new window


All I have to do is look at the script and I have a database username and password.

If the scott user is the one that is running the script and you have OS authentication, there is no need to use a username:
#/bin/ksh
sqlplus / <<!EOF
select sysdate from dual;
EOF

Open in new window


There is a TON of information on the Internet about the pros and cons of OS authentication and Oracle.  Google around.

What it all boils down to is:  There is no right answer to using OS authentication or not.  It works for some, not for others.
0
jcob_lAuthor Commented:
I really want to make sure one thing..
My environment is linux..

I saw all matierials in google as windows.
Just to make sure this will work in linux os,,
0
slightwv (䄆 Netminder) Commented:
When talking about the pros and cons of OS authentication, OS really doesn't matter.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jcob_lAuthor Commented:
yes fine..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.