Solved

Ora-01031 Insufficient privileges - SYSTEM USER Oracle 11g

Posted on 2008-10-28
6
4,618 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
ID: 22826505
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
ID: 22826598
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
ID: 22827871
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
ID: 22827923
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
ID: 22827927
Leaving thread open for couple of days.

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22851806
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 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