| Connect as sysdba works, regular credentials don't [message #579251] |
Sun, 10 March 2013 10:55  |
Andrey_R
Messages: 345
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 #579267 is a reply to message #579260] |
Sun, 10 March 2013 15:37  |
Andrey_R
Messages: 345
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: 345
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: 67667
Registered: March 2007
Location: Nanterre, France, http://...
|
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 #579334 is a reply to message #579321] |
Mon, 11 March 2013 07:39 |
Andrey_R
Messages: 345
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 #579996 is a reply to message #579719] |
Mon, 18 March 2013 15:57 |
Andrey_R
Messages: 345
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
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
