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
November 16th, 2009 at 12:51 pm
Yes, IT’s usefull for me.
Regards
Azar
DBA
November 16th, 2009 at 4:45 pm
Excellence 😉
January 8th, 2010 at 6:44 pm
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
January 8th, 2010 at 6:59 pm
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
February 6th, 2013 at 5:43 am
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..
June 13th, 2013 at 5:55 am
You should grant CREATE TABLE privilege from sys user (or any user who has admin option to grant it)
June 19th, 2015 at 7:22 am
can you please help me with following error
ORA-27271: szingroup: group lookup failure
HPUX-ia64 Error: 13: Permission denied
Additional information: 869
July 4th, 2015 at 7:55 pm
Check the owner of ASM disks