Why can my new user login as sysdba in oracle?

3
votes

I just installed Oracle 11g R2 on windows 7 machine. I am able to login using my sys as sysdba account.

Using the sys account I created a user as follows

create user testuser identified by testuser;

When I try to connect to database with testuser as Normal user it says that 'user testuser lacks CREATE SESSION privilege; logon denied' which is understandable since I have not granted any permissions so far to testuser.

But when I try to login to database using testuser as sysdba, it allows me to connect to database and the selected schema seems to me as SYS schema.

Is there something I am missing because I think user testuser should not be able to even login let alone login to SYS schema. I have also found that I can connect as sysdba with any username regardless of if it is present in the database.

How can I create a regular oracle user which does not have access to any other schema?

2
oracleschemasyssysdba

2 Answers

3
votes

Your windows user is part of the dba group (oradba). It uses Windows authentification to connect as sysdba. See this thread on AskTom:

"as sysdba" is extremely powerful, it uses OS AUTHENTICATION, it does not require database authentication (it is used to login before there is a "database" or even an "instance"!)

As a member of the dba group, you can login as sysdba with any database user. You can even login without a user: sqlplus / as sysdba.

0
votes

This is how I would normally create a new user:

create user &1 identified by &2 default tablespace &3 temporary tablespace &4;

grant connect, resource to &1; grant alter session to &1;

** get the picture; also include other default privileges like "create ... " : sequence, synonym, table, view, database link select_catalog_role etc.