Can only login on Oracle as sysdba

Question

I just installed 11g2 on Redhat 5. I logged in using sqlplus as follows:

$sqlplus sys/abcabc as sysdba

I created a user as follows:

sqlplus> create user "xyz" 
           profile "DEFAULT" 
           identified by "abcabc" 
           default tablespace "MYTAB" 
           temporary tablespace "TEMP" 
           account unlock;

sqlplus> grant dba to "xyz"

sqlplus> commit

sqlplus reported user created and dba granted. No errors were repored by sqlplus.

Now when I try to login as follows:

$sqlplus xyz/abcabc

I get a login prompt again and after 3 retries, I'm kicked out.

BUT, if I login as follows:

$sqlplus xyz/abcabc as sysdba

I get logged in.

What could be the problem? I've followed the exact same procedure for creating a user many times before and it always worked. What is different this time that I need to login as sysdba?

Thanks

You might get better results posting this on http://dba.stackexchange.com/. — Bob Jarvis - Слава Україні, Oct 27 '12 at 20:49

score 2 · Answer 1 · edited May 23 '17 at 11:48

Are you sure that you want to create a case-sensitive username? That would be very, very unusual.

When you surround an Oracle identifier with double-quotes, you are asking Oracle to treat it as a case-sensitive identifier. So when you

create user "xyz"

you're asking for a case-sensitive username. Lots of tools are not going to have trouble passing in case-sensitive identifiers and you have to worry about getting the double-quotes escaped properly in your shell.

Most likely, you really don't want a case-sensitive identifier. You simply want to use a standard case-insensitive identifier

 create user xyz
     profile "DEFAULT" 
     identified by "abcabc" 
     default tablespace "MYTAB" 
     temporary tablespace "TEMP" 
     account unlock;

I would prefer that the double-quotes be removed from the tablespace names and the profile names but that isn't causing your issues.

Thanks. Actually, the double quotes were the culprit. I dropped the user and created again without any quotes and things are normal now. It is strange as this used to work previously with the quotes. — Really Studly, Oct 28 '12 at 04:01

score 0 · Answer 2 · answered Oct 27 '12 at 06:40

BUT, if I login as follows:
$sqlplus xyz/abcabc as sysdba
I get logged in.

You will also be able to login like this:

$sqlplus / as sysdba

That's because the / as sysdba causes Oracle to rely on the OS authentication. If your OS user is part of the dba group (on Unix) or has Administrator privilege (on Windows) you're in.

But your're in as SYS, regardless of whether you specified a user. This is dangerous because SYS is the owner of the data dictionary, and so it is quite easy to royally muck up the database if you don't know what you're doing.

Can only login on Oracle as sysdba

2 Answers2