Solved

Ora-01031 Insufficient privileges - SYSTEM USER Oracle 11g

Posted on 2008-10-28
6
4,628 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 74

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

827 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