ORA-01031 insufficient privileges Solution

Are you getting the “ORA-01031 insufficient privileges” error? Learn what causes this error and how to resolve it in this article.

ORA-01031 Cause

The cause of the ORA-01031 error is that you’re trying to run a program or function and you don’t have the privileges to run it.

This could happen in many situations, such as:

1. You attempted to change a username or password, without having the appropriate privileges.
2. You attempted to query a table where you have at least one privilege but not the required privilege to run the query (e.g. you have SELECT but not UPDATE).
3. You attempted to start an Oracle database using the CONNECT INTERNAL command.
4. You attempted to install an Oracle database without having the appropriate privileges on the operating system.

 

ORA-01031 Solution

There are many solutions to this error message.

First, you could ask the Oracle database administrator to grant you the privileges you are missing. Often this can be an oversight (a missing privilege in a script or a change that wasn’t implemented correctly), so they may grant it to you.

If they won’t grant you the required privileges, you can ask the DBA to execute the code or statement for you.

This is common if you have some privileges but not the required privileges. Let’s take a look at an example.

 

Some Privileges But Not All Required Privileges

You might get the ORA-01031 error if you have SELECT privileges on a table, but you’re trying to run another statement (such as UPDATE or DELETE) and don’t have permission to do that.

Let’s see an example.

CREATE USER userA IDENTIFIED BY password;
CREATE USER userB IDENTIFIED BY password;
CREATE USER testUser IDENTIFIED BY password;
GRANT CONNECT, CREATE SESSION TO testUser;
CREATE TABLE userA.table1(someValue VARCHAR2(1));
CREATE TABLE userB.table2(someValue VARCHAR2(1));
GRANT DELETE ON userB.table2 to testUser;

The above statements show that:

• Two users have been created (userA and userB)
• They each have one table in their schema (userA.table1, and userB.table2)
• We’ll use testUser to test our SELECT queries.
• The only privilege granted to testUser is DELETE on userB.table2. Nothing has been granted on userA.table1.

Now, let’s connect as the testUser and run some queries.

SELECT *
FROM userA.table1;

 

ORA-00942: table or view does not exist

More information on the ORA-00942 error can be found here.

SELECT *
FROM userB.table2;

 

ORA-01031: insufficient privileges

 

We get the first error because, according to testUser, the table doesn’t exist. It actually does exist, but testUser has no privileges on it, so this is expected behaviour.

Now, we get the ORA-01031 on the second table because we have the DELETE privilege, but not the SELECT privilege. We can see the table because we have at least one privilege on it, but we get this error because we don’t have the privilege to do what we want on it.

ORA-01031 On Create View

If you’re getting this error when creating a view, then it’s likely due to not having the correct privileges on the underlying table, or tables.

A CREATE VIEW statement contains a SELECT statement. So, check that you can run the SELECT statement successfully.

If you can’t, then check your privileges against this table. You might need to ask for the privileges from the DBA, or ask the DBA to create the view (as mentioned above).

If you can see the data from the SELECT query, but can’t create the view, it could be you don’t have the appropriate privileges to create the view.

To create a view in the database, you need the CREATE VIEW privilege, as well as the SELECT privilege on the underlying tables. If you don’t have this, then ask the DBA to grant it to you, or ask them to create the view for you.

 

ORA-01031 Create Table

You could get this error as you’re creating a table.

If so, then it means you don’t have the right privileges to create the table.

This might seem obvious, but I thought I’d clarify.

You need the CREATE TABLE privilege to create a table on the database.

Just like with the earlier scenarios of this error message, you can ask the DBA to grant you the privilege, or get the DBA to run the statement and create the table for you.