Home » RDBMS Server » Security » Problem with connect / as sysdba
Problem with connect / as sysdba [message #130917] Wed, 03 August 2005 03:20 Go to next message
vietcave
Messages: 2
Registered: August 2005
Junior Member
Hi Tom,

I have two Oracle servers A and B.

I run some statements to connect server B from server A:

SQL> conn scott/tiger@B as sysdba
Connected
SQL> show user
USER IS "SYS"

I can't understand why USER is "SYS". It should have be SCOTT.

In some cases although I input the wrong account (but still use as sysdba), I still connect to server B.

Thanks.



Re: Problem with connect / as sysdba [message #130940 is a reply to message #130917] Wed, 03 August 2005 06:46 Go to previous messageGo to next message
Frank Naude
Messages: 4570
Registered: April 1998
Senior Member
This is expected behaviour:

SQL> connect scott/tiger as sysdba
Connected.
SQL> show user
USER is "SYS"


If you want to connect to the SCOTT user, leave out the "AS SYSDBA" part:

SQL> connect scott/tiger
Connected.
SQL> show user
USER is "SCOTT"


Best regards.

Frank
Re: Problem with connect / as sysdba [message #131015 is a reply to message #130917] Wed, 03 August 2005 19:33 Go to previous messageGo to next message
vietcave
Messages: 2
Registered: August 2005
Junior Member
Oh, no.

I know that I can connect to server B with the account SCOTT by leaving out "AS SYSDBA" but I would like to know why in this case user is "SYS". Remember that I am using the wrong account of SYS.

I thinks it should have had an error, for example "The account is wrong".

I even use other wrong accounts but still connect successfully.

Thanks.
Re: Problem with connect / as sysdba [message #131341 is a reply to message #131015] Fri, 05 August 2005 07:23 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hi
Quote:

I can't understand why USER is "SYS". It should have be SCOTT.

As Frank said, this is expected behaviour. Whenever you log in as sysdba, you are logged in as sys.
Jim
Re: Problem with connect / as sysdba [message #132667 is a reply to message #131341] Mon, 15 August 2005 10:21 Go to previous message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
Quote:

I know that I can connect to server B with the account SCOTT by leaving out "AS SYSDBA" but I would like to know why in this case user is "SYS". Remember that I am using the wrong account of SYS.


Just to clarify, "as sysdba" means log in as the user sys.

I know this seems strange, but if you think about it it makes sense. The main reason for "as sysdba" is to startup the database (I know it does loads of other things as well.)

Your permission to perform actions on the database are stored in the data dictionary tables in the system tablespace. Oracle can't get access to these tables until the database is fully open, therfor how does Oracle decide who is allowed to open the database. He does this using external authentication (permissions outside the database) this can be done in 2 different ways.

1. OS authenticaion - logon to the server is a member of a particular group on the os (usually dba or oradba). I'm logged onto the server as ALISON and ALISON is a member of the DBA group.. ALISON now has rights to use "as sysdba" dosen't matter what username and password I put in (I can even put in NO username and password) I will still get logged onto the database as user SYS because I'm logged onto the server as user ALISON and alison is a member of the DBA group...

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> connect alison/jane as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> connect tom/cruise as sysdba
Connected.
SQL> show user
USER is "SYS"


2. The second option is password file authentiction.. this works on a similar concept, but instead of lyour os user verifying who you are, now you need to have knowledge of the password in the password file.. you still get logged on as SYS but this time we are checking your credentials - you are who you say you are..

SQL> connect sys/oracle@orcl as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> alter user sys identified by secure;

User altered.

SQL> connect sys/secure@orcl as sysdba
Connected.
SQL> show user
USER is "SYS"

changing the sys password changes the password in the password file and the users wanting to log on have to know the new password..

We can also add new users to the password file if we want to

SQL> grant sysdba to hr;

Grant succeeded.

SQL> connect hr/hr@orcl as sysdba
Connected.
SQL> show user
USER is "SYS"

All that Oracle is doing here is making sure that you are in the file (SYS is by default) and you know the password.. When you add a user to the password file (grant sysdba to ...) then their current password is the one that is used to log on as sysdba...

Final word... as sysdba always means SYS, all that Oracle needs to do is check your credentials either because of who you are logged into the OS as or whether the administrator has added you to the password file....

It's confusing to explain so I hope all of that makes sense...

Allie
Previous Topic: How to Allow a user to create a table in another schema
Next Topic: ORA-01031: insufficient privileges when conn sys/password as sysdba
Goto Forum:
  


Current Time: Fri Dec 03 01:40:14 CST 2021