Fix ORA-01031: insufficient privileges in Oracle

I know that ORA-01031 message pops up right when you're trying to run a query or connect to a database, and it's annoying. The good news: it's almost always a simple permission problem, not a broken database.

Quick fix for ORA-01031

Here's what almost always works:

1. Check which user you're logged in as. Open a SQL*Plus session and run:

SHOW USER;

You'll see something like USER is "SCOTT". If it's not the owner of the schema or a DBA, you'll get ORA-01031.

2. Grant the missing privilege. If you are a DBA (or know the DBA password), run this as SYS or SYSTEM:

GRANT CREATE SESSION TO scott;
GRANT CONNECT TO scott;
GRANT RESOURCE TO scott;

If you need to read from another user's table, do this:

GRANT SELECT ON hr.employees TO scott;

After running the grant, reconnect as scott and try again. If that doesn't work, move to step 3.

3. Reconnect with the right user. Sometimes you're logged in as a user that doesn't own the objects. Exit SQL*Plus and reconnect as the schema owner:

CONNECT schema_owner/password@database;

After doing that, run your query. You should see results now.

Why this error happens

Oracle is strict about permissions. It's not like some other databases where you can read anything. Every privilege has to be given explicitly. The ORA-01031 error means Oracle checked your user's privileges and said "nope, you don't have the right one."

The most common reasons:

• Your user only has CREATE SESSION but not SELECT ANY TABLE or specific object grants.
• You're trying to run ALTER SYSTEM or CREATE VIEW without the DBA role.
• You're connected to the wrong database instance (like connecting to a test database when you meant production).

Giving the minimal privilege needed fixes it without opening too much access.

Less common variations of ORA-01031

Not every ORA-01031 is the same. Here are three specific cases I've seen in the field:

1. ORA-01031 when creating a stored procedure

You write a simple procedure, try to compile it, and get ORA-01031. The fix: you need the CREATE PROCEDURE system privilege. Grant it:

GRANT CREATE PROCEDURE TO your_user;

After that, recompile. It should work.

2. ORA-01031 on Oracle Cloud or AWS RDS

In a managed Oracle database, you might not have SYSDBA access. The error shows up when you try to run ALTER SYSTEM SET .... Instead, use the cloud console or parameter groups. For AWS RDS, check the parameter group in the console — you can't run those commands directly.

3. ORA-01031 from a Java application

Your app connects fine but throws this error on a specific query. The app user probably doesn't have SELECT on that table. Check the connection pool settings — make sure it's using a user with the right grants. I've seen cases where the developer used scott for everything but forgot to grant SELECT on a new table.

How to prevent ORA-01031

You can avoid this error with two habits:

1. Document your grants. When you create a new user, write down what privileges you gave. When you create a new table, immediately grant access to the users that need it.
2. Use a script for new schemas. Instead of granting privileges one by one, run a single script like this:

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO new_user;
GRANT SELECT ON schema.table1 TO new_user;
GRANT SELECT ON schema.table2 TO new_user;

Test the connection right after creating the user. That way you catch the error before your users do.

One more thing: if you're using Oracle 12c or later, remember that CONNECT and RESOURCE roles might not include everything you expect. In a pluggable database (PDB), you might need to grant privileges at the PDB level, not the container database level.

That's it. ORA-01031 is a permission problem, not a hardware failure. Grant the right privilege or reconnect as the right user, and you're done.