Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for November 16th, 2009

Connecting with a user which has SYSDBA privilege, you act like SYS user

Posted by Kamran Agayev A. on 16th November 2009

According to the Oracle documentation, when you connect with SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your username. See the following example:

[sourcecode language="css"]
SQL> create user usr1 identified by test;

User created.

SQL> grant sysdba to usr1;

Grant succeeded.

SQL> conn usr1/test
ERROR:
ORA-01045: user USR1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant dba to usr1;

Grant succeeded.

SQL> conn usr1/test
Connected.
SQL> create table table1 (id number);

Table created.

SQL> conn usr1/test as sysdba
Connected.

SQL> create table table2 (id number);

Table created.

SQL> col owner format a35
SQL> col object_name format a35
SQL> select owner, object_name from dba_objects where object_name like 'TABLE_';

OWNER OBJECT_NAME
----------------------------------- -----------------------------------
SYS TABLE2
USR1 TABLE1

SQL> show user
USER is "SYS"
SQL>

SQL> drop user usr1 cascade;

User dropped.

SQL>

[/sourcecode]

As you see, although you've granted SYSDBA privilege, the user can't connect to the database and as you connected with SYSDBA privilege, the table you've created is not created under the schema of the connected user, but is created under the SYS schema, because you're acting as SYS user by connecting with SYSDBA privilege

Posted in Administration | 8 Comments »