| Problem with connect / as sysdba [message #130917] |
Wed, 03 August 2005 03:20  |
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  |
Frank Naude
Messages: 4581
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 |
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 #132667 is a reply to message #131341] |
Mon, 15 August 2005 10:21 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
