Solved

Ora-01031 Insufficient privileges - SYSTEM USER Oracle 11g

Posted on 2008-10-28
6
4,583 Views
Last Modified: 2013-12-19
Getting error ora-01031 - Insufficient privileges when creating a new user as SYS or SYSTEM.

Checked dba_user_roles and SYS and SYSTEM user have DBA privileges granted.

Is there a new feature in Oracle 11g that require an explicit privilege granted to SYS or SYSTEM to enable create user command?

Thanks.

0
Comment
Question by:mohammadzahid
6 Comments
 

Expert Comment

by:denverskier
Comment Utility
Hi,

Not aware of any changes in 11g for this.  Are you doing this from the command line or from OEM?  If you are going through OEM, I'd try it again from command line and connect / as sysdba to ensure that you're logging in as sys.  

Good luck!
0
 
LVL 11

Author Comment

by:mohammadzahid
Comment Utility
Hi,

I tried both OEM, command line as well as using TOAD. Same error message.

I think problem could be something to do with privileges for SYS or SYSTEM schema. Since it is a new database with hardly any data inside, I will drop entire database and create a fresh one :-).

 I will wait few more hours to see if I can get any suggestions from someone else that might help in resolving this error.

0
 
LVL 23

Expert Comment

by:David
Comment Utility
Not connecting as NORMAL, are you?
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:mohammadzahid
Comment Utility
No.

Hate to erase problem with a quick and easy fix. I dropped and recreated my database since it was local on my laptop. Now I can create a user with SYSTEM and SYS. Hate to throw away a problem without a proper solution.

I will investigate again if occurs again, reopen thread and post solution if found.

Thanks.

0
 
LVL 11

Author Comment

by:mohammadzahid
Comment Utility
Leaving thread open for couple of days.

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
if you can restore a backup of your old database  you can check these...

SELECT *
  FROM dba_sys_privs
 WHERE grantee IN ('SYS', 'SYSTEM', 'DBA') AND privilege = 'CREATE USER';


If SYS and SYSTEM have DBA role (and it's enabled) then make sure DBA has the create user privilege
or check if either has it granted directly.

By default.  SYS and DBA should have it,  SYSTEM should have it through the DBA role.
Both SYS and SYSTEM have DBA assigned and default to enabled.

Also check for DDL or CREATE triggers that fire when you create a user.  While SYS might be able to do it,  a trigger may try to do something illegal.

Those are the only reasons that should be able to generate a 1031 exception
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now