| Connect as sysdba works, regular credentials don't [message #579251] |
Sun, 10 March 2013 10:55  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I'm trying to figure out why when I go to CMD and type
C:\Users\my_shop>sqlplus No_Such_User/No_Such_Password@shop1
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Mar 10 23:49:41 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
However, when I try the same with "as sysdba" - I get connected to SYS user...
C:\Users\my_shop>sqlplus No_Such_User/No_Such_Password@shop1 as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Mar 10 23:51:48 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.5.0 - Production
SQL> show user
USER is "SYS"
SQL>
How could that be?
Thanks in advance:)
Regards,
Andrey
[Updated on: Sun, 10 March 2013 10:56] Report message to a moderator |
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579252 is a reply to message #579251] |
Sun, 10 March 2013 11:30  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
After reading & trying some more I got to the conclusion that authentication is successful with a garbage user/password
because my OS user belongs to a group Oracle sees as eligible of logging in and managing it without Oracle SYS user credentials.
I will look for this groups name and settings in the DC's lusrmgr.msc menu, or the local one.
Thanks.
Regards,
Andrey
|
|
|
|
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579255 is a reply to message #579251] |
Sun, 10 March 2013 12:36 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Quote:There is only thing to do: set remote_os_authent to FALSE.
Thanks Michel, It all makes sense now.
Quote:>C:\Users\my_shop>sqlplus No_Such_User/No_Such_Password@shop1 as sysdba
to which DB did you actually connect?
Well, there was only one - the one shop1 is attached to in tnsnames.ora
Or did I misunderstood something?
|
|
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579259 is a reply to message #579257] |
Sun, 10 March 2013 13:04 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Quote:
>Well, there was only one - the one shop1 is attached to in tnsnames.ora
>Or did I misunderstood something?
sqlplus scott/tiger
# above can only connect to a DB on the local server from which the sqlplus command was initiated
sqlplus scott/tiger@TNS_ALIAS
# above connects to whatever instance the TNS_ALIAS points to
#If you are connecting to to DB on the local server, it make NO sense use SQL*Net (@TNS_ALIAS)
I am just trying to understand what you have in the way of systems & databases.
I see. Well, it was not on a local Database server, that's correct.
Quote:
>SQL*Plus: Release 10.2.0.3.0 - Production on Sun Mar 10 23:51:48 2013
please explain why the version above does not match the version below.
>Oracle Database 10g Release 10.2.0.5.0 - Production
I noticed it too.
All I can say is "it wasn't me..." - It's a customer's env - he's the king of his castle..
Regards,
Andrey
|
|
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579267 is a reply to message #579260] |
Sun, 10 March 2013 15:37 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
BlackSwan, I was on a Windows Server 2008 which is *not* the database server.
I tried to connect with SQL*Plus to a distant Oracle database server.
I didn't understand why I can give gibberish credentials and still be connected,
But then it turned out that the OS user I worked with must be in the OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT was set to TRUE,
(Thanks Michel) ,so I could connect as SYS with every credential set I would give, for that database(attached to "shop1" connection string).
So, I figured that my question is pretty much answered..
Are anymore details missing in your opinion?
[Updated on: Sun, 10 March 2013 15:39] Report message to a moderator |
|
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579312 is a reply to message #579268] |
Mon, 11 March 2013 05:04 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel, BlackSwan,
I've read some more about the parameter, and I understand that if I can login as SYS with my OS credentials,
and the value of OS_AUTHENT_PREFIX is $OPS - user SYS must have been created(at the time of install) with a OPS$ prefix?
I don't see him like that in DBA_USERS...I see just "SYS" with the regular password, not EXTERNALL like I would expect...
So I don't understand how this authentication still works...
C:\Users\my_shop>sqlplus blabla/blabla@shop1 as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Mar 11 17:58:24 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.5.0 - Production
SQL> show user
USER is "SYS"
SQL> set lines 200
SQL> set pages 100
SQL> col USERNAME for a30
SQL> col PASSWORD for a25
SQL> col EXTERNAL_NAME for a20
SQL> select USERNAME ,PASSWORD , EXTERNAL_NAME from dba_users
2 where username='SYS';
USERNAME PASSWORD EXTERNAL_NAME
------------------------------ ------------------------- --------------------
SYS 7A0F2B316C212D67
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579314 is a reply to message #579312] |
Mon, 11 March 2013 05:08 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
As I often say:
- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.
NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.
Regards
Michel
[Updated on: Mon, 11 March 2013 05:08] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579319 is a reply to message #579317] |
Mon, 11 March 2013 05:57 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Quote:It means that OS_AUTHENT_PREFIX is irrelevant to SYS and cannot be applied to SYS, it belongs only to normal accounts.
I understand. However, I don't understand why I can login with "SQLPLUS whatever/blabla@shop1 AS SYSDBA" from my OS...
Can you explain it, or suggest what can I do to check why? What parameters to look in? Even what documentation to read...
Thanks & Regards,
Andrey
|
|
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579334 is a reply to message #579321] |
Mon, 11 March 2013 07:39 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel,
I have logged in to a testing Oracle server, and changed the parameter to TRUE by:
alter system set remote_os_authent=TRUE scope=SPFILE;
Then, restarted my database.
I still *cannot* login the same way I saw in the "shop1" environment:
C:\Users\Administrator>sqlplus blabla/bla@db11g as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 5 10:19:25 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
So, how can I understand the possibility in "shop1" to connect with any credential as SYSDBA successfully?
What can explain it?
Regards,
Andrey
[Updated on: Mon, 11 March 2013 07:39] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579719 is a reply to message #579349] |
Thu, 14 March 2013 04:05 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi Michel,
Sorry for the late reply.
1. Yes, it is in ORA_DBA group *on the database server itself*.
2. When I look at Local Users And Groups of the application server(the remote server)
(from which I access the database as described in previous posts),
The OS user *is not* in ORA_DBA group(I look at Local Users and Groups menu).
|
|
|
|
| Re: Connect as sysdba works, regular credentials don't [message #579996 is a reply to message #579719] |
Mon, 18 March 2013 15:57 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
However, This kind of authentication works even from a remote server, in which's Local Users And Groups Management (Run > lusrmgr.msc) my user *is not* in ORA_DBA group - I still manage to connect with "sqlplus WHATEVER/BLA_BLA@shop1 as sysdba",
And I don't understand why. Does anybody know what allows this behavior?
What should I check furthermore?
Thanks in advance.
Regards,
Andrey
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
