jcob_l
asked on
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:
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:
ASKER
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>
create user OPS$SCOTT identified externally
*
ERROR at line 1:
ORA-01920: user name 'OPS$SCOTT' conflicts with another user or role name
SQL>
>>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;
You already created him... you need to drop him first:
drop user ops$scott;
ASKER
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>
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>
>>[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.
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.
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.
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.
ASKER
ok..
Then what is the advantage then....
ops$scott does not have any data right...
Then what is the advantage then....
ops$scott does not have any data right...
>>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.
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.
ASKER
Can you explain this..
The main advantage is that you don't need to hard-code any usernames or passwords in scripts ------------------------
The main advantage is that you don't need to hard-code any usernames or passwords in scripts ------------------------
>>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:
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:
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.
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;
EOFAll 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;
EOFThere 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.
ASKER
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,,
My environment is linux..
I saw all matierials in google as windows.
Just to make sure this will work in linux os,,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes fine..
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 /