Kamran Agayev's Oracle Blog

Oracle Certified Master

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

Posted by Kamran Agayev A. on November 16th, 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

8 Responses to “Connecting with a user which has SYSDBA privilege, you act like SYS user”

  1. Mohamed Azar Says:

    Yes, IT’s usefull for me.

    Regards

    Azar
    DBA

  2. Surachart Opun Says:

    Excellence 😉

  3. rasheed Says:

    i am using oracle 8i window 2003 svr.
    i am maintaing 4 svrs. but every day logical backup exported.
    but one day svrs crasheed after that yesterday bkup imported
    but recently trns. no saved.
    how to cr8 rman and catalog database
    step by step explain
    from
    shaik rasheed

  4. Kamran Agayev A. Says:

    Dear Rasheed

    My first question is : Why you’re still using unsupported version of Oracle? Even the version which comes after it (9i) is unsupported! I’d suggest you to upgrade your database to 10g/11g
    If you want to create catalog database, follow this doc:
    http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76990/recocat.htm#435634

    Good Luck

  5. Arulraj Says:

    1. How to use grant command in Oracle SQLPlus?
    2. I dont have a permission to create table command in OracleSQLPlus? it gives an error as insufficient privilieges Error: ORA-01031 :insufficient privilieges
    3. even though I did not have a permission to use the grant command also? it also throws same error as
    ORA-01031 :insufficient privilieges

    Could you please provide me a solution..

  6. Kamran Agayev A. Says:

    You should grant CREATE TABLE privilege from sys user (or any user who has admin option to grant it)

  7. Mahath Says:

    can you please help me with following error

    ORA-27271: szingroup: group lookup failure
    HPUX-ia64 Error: 13: Permission denied
    Additional information: 869

  8. Kamran Agayev A. Says:

    Check the owner of ASM disks

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>