Home » RDBMS Server » Security » Connect as sysdba works, regular credentials don't (Oracle Database 10g Release 10.2.0.5.0, Client on Windows Server 2008)
Connect as sysdba works, regular credentials don't [message #579251] Sun, 10 March 2013 10:55 Go to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 #579253 is a reply to message #579252] Sun, 10 March 2013 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is only one thing to do: set remote_os_authent to FALSE.

Regards
Michel

[Edit: missing word]

[Updated on: Sun, 10 March 2013 13:47]

Report message to a moderator

Re: Connect as sysdba works, regular credentials don't [message #579254 is a reply to message #579253] Sun, 10 March 2013 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
>C:\Users\my_shop>sqlplus No_Such_User/No_Such_Password@shop1 as sysdba

to which DB did you actually connect?
Re: Connect as sysdba works, regular credentials don't [message #579255 is a reply to message #579251] Sun, 10 March 2013 12:36 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 #579257 is a reply to message #579255] Sun, 10 March 2013 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
>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.

>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
Re: Connect as sysdba works, regular credentials don't [message #579259 is a reply to message #579257] Sun, 10 March 2013 13:04 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 #579260 is a reply to message #579259] Sun, 10 March 2013 13:13 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
>Well, it was not on a local Database server, that's correct.
Thank you for NOT clarifying what you have.
I don't know to what "it" refers.
Local is a relative term & you are remote from me.

I'll stop wasting my time on this tail chasing activity.
Re: Connect as sysdba works, regular credentials don't [message #579267 is a reply to message #579260] Sun, 10 March 2013 15:37 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 #579268 is a reply to message #579267] Sun, 10 March 2013 17:04 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is why REMOTE_OS_AUTHENT MUST always be set to FALSE otherwise anyone with his laptop can connect as a privileged user.

Regards
Michel
Re: Connect as sysdba works, regular credentials don't [message #579312 is a reply to message #579268] Mon, 11 March 2013 05:04 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
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 #579315 is a reply to message #579314] Mon, 11 March 2013 05:14 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi Michel,
What did you want to say with this laconic quote?

What does "misuse of user SYS" have to do with success of authentication with OS credentials, and the configuration of OS_AUTHENT_PREFIX???

Regards,
Andrey
Re: Connect as sysdba works, regular credentials don't [message #579317 is a reply to message #579315] Mon, 11 March 2013 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It means that OS_AUTHENT_PREFIX is irrelevant to SYS and cannot be applied to SYS, it belongs only to normal accounts.

Regards
Michel
Re: Connect as sysdba works, regular credentials don't [message #579319 is a reply to message #579317] Mon, 11 March 2013 05:57 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 #579321 is a reply to message #579319] Mon, 11 March 2013 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What parameters to look in?


I already told it twice: REMOTE_OS_AUTHENT.

Regards
Michel
Re: Connect as sysdba works, regular credentials don't [message #579334 is a reply to message #579321] Mon, 11 March 2013 07:39 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 #579339 is a reply to message #579334] Mon, 11 March 2013 08:07 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
Senior Member

And yes, I understand it is *not normal* , however, what can cause this behavior?
How can the reason be checked, and what approach taken to solve this situation?

Regards,
Andrey
Re: Connect as sysdba works, regular credentials don't [message #579349 is a reply to message #579339] Mon, 11 March 2013 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is your account in ORA_DBA group?

Regards
Michel
Re: Connect as sysdba works, regular credentials don't [message #579719 is a reply to message #579349] Thu, 14 March 2013 04:05 Go to previous messageGo to next message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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 Go to previous message
Andrey_R
Messages: 187
Registered: January 2012
Location: Euro-Asia
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
Previous Topic: Given Grants doesn't reflect on Oracle forms 6i
Next Topic: How to decrypt DBMS_CRYPTO.HASH encrypted value
Goto Forum:
  


Current Time: Mon Apr 21 03:44:32 CDT 2014

Total time taken to generate the page: 0.11383 seconds