ORA-01031: insufficient privileges

ORA-01031: insufficient privileges

The current user lacks the system privilege the statement needs, or holds it only through a role where roles don't count.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14

ORA-01031 means Oracle knows what you want to do, but the current user isn't allowed to do it. Most of the time it's a missing system privilege such as CREATE MATERIALIZED VIEW, or a privilege that came through a role and isn't active inside a stored procedure.

Check what the session actually has with SELECT privilege FROM session_privs. Granting privileges needs a DBA (here system).

Note: on 23ai, missing object privileges (e.g. UPDATE on a table you can only SELECT) raise ORA-41900/ORA-41904 instead of ORA-01031.

Causes and fixes

  1. Missing system privilege (CREATE MATERIALIZED VIEW)
  2. Privilege granted through a role, used inside a stored procedure
  3. Creating a table in another user's schema

1Missing system privilege (CREATE MATERIALIZED VIEW)

The user could create tables and views, but CREATE MATERIALIZED VIEW sales_by_region ... failed with ORA-01031. session_privs listed 8 privileges (CREATE TABLE, CREATE VIEW, ...) and no CREATE MATERIALIZED VIEW. Having CREATE TABLE is not enough.

After GRANT CREATE MATERIALIZED VIEW TO lab_a (run as system), the same statement printed Materialized view created. and returned EAST 100 / WEST 200.

Setup
Setuprun as lab_a
CREATE TABLE sales (sale_id NUMBER PRIMARY KEY, region VARCHAR2(10), amount NUMBER);
INSERT INTO sales VALUES (1, 'EAST', 100);
INSERT INTO sales VALUES (2, 'WEST', 200);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
CREATE MATERIALIZED VIEW sales_by_region AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;
OUTPUT
CREATE MATERIALIZED VIEW sales_by_region AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region
                                                                                     *
ERROR at line 1:
ORA-01031: insufficient privileges
Help: https://docs.oracle.com/error-help/db/ora-01031/
Checkrun as lab_a
COLUMN privilege FORMAT A20
SELECT privilege FROM session_privs ORDER BY privilege;
OUTPUT
PRIVILEGE
--------------------
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW

8 rows selected.
Fixrun as system
GRANT CREATE MATERIALIZED VIEW TO lab_a;
OUTPUT
Grant succeeded.

Each object type has its own CREATE privilege. Ask your DBA for the exact one the failing statement needs.

After the fixrun as lab_a
CREATE MATERIALIZED VIEW sales_by_region AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;
SELECT region, total FROM sales_by_region ORDER BY region;
OUTPUT
Materialized view created.


REGION          TOTAL
---------- ----------
EAST              100
WEST              200

2 rows selected.

2Privilege granted through a role, used inside a stored procedure

CREATE TABLE was granted only through the role LAB_DEV. Creating a table directly in SQL*Plus worked (TEST_DIRECT), but a procedure running EXECUTE IMMEDIATE 'CREATE TABLE work_tmp ...' failed with ORA-01031 at LAB_A.MAKE_WORK_TABLE, line 3. Roles are not active inside definer-rights procedures.

user_sys_privs had no CREATE TABLE (no rows selected); it was only in role_sys_privs for LAB_DEV. After granting CREATE TABLE directly to the user, the procedure completed and WORK_TMP was created.

Setup
Setuprun as system
REVOKE CREATE TABLE FROM lab_a;
DROP ROLE IF EXISTS lab_dev;
CREATE ROLE lab_dev;
GRANT CREATE TABLE TO lab_dev;
GRANT lab_dev TO lab_a;
OUTPUT
Revoke succeeded.


Role dropped.


Role created.


Grant succeeded.


Grant succeeded.
Setuprun as lab_a
CREATE TABLE test_direct (x NUMBER);
CREATE OR REPLACE PROCEDURE make_work_table AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE work_tmp (id NUMBER, val VARCHAR2(20))';
END;
/
OUTPUT
Table created.


Procedure created.
Reproducerun as lab_a
EXEC make_work_table
OUTPUT
BEGIN make_work_table; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "LAB_A.MAKE_WORK_TABLE", line 3
ORA-06512: at line 1
Help: https://docs.oracle.com/error-help/db/ora-01031/
Checkrun as lab_a
COLUMN privilege FORMAT A20
COLUMN granted_role FORMAT A12
SELECT privilege FROM user_sys_privs WHERE privilege = 'CREATE TABLE';
SELECT granted_role FROM user_role_privs;
SELECT privilege FROM role_sys_privs WHERE role = 'LAB_DEV';
OUTPUT
no rows selected


GRANTED_ROLE
------------
LAB_DEV

1 row selected.


PRIVILEGE
--------------------
CREATE TABLE

1 row selected.
Fixrun as system
GRANT CREATE TABLE TO lab_a;
OUTPUT
Grant succeeded.

Typical when SQL works in a tool but the same SQL fails inside a package or job. Grant the privilege directly to the procedure owner.

After the fixrun as lab_a
EXEC make_work_table
SELECT table_name FROM user_tables ORDER BY table_name;
OUTPUT
PL/SQL procedure successfully completed.


TABLE_NAME
------------------------------
TEST_DIRECT
WORK_TMP

2 rows selected.

3Creating a table in another user's schema

lab_a ran CREATE TABLE lab_b.price_hist ... and got ORA-01031. Creating in another schema needs CREATE ANY TABLE; your own CREATE TABLE only covers your schema. SELECT USER FROM dual confirmed the session was LAB_A.

The table was created by the owner lab_b instead, which then ran GRANT SELECT, INSERT ON price_hist TO lab_a.

Setup
Setuprun as lab_b
CREATE TABLE price (item_id NUMBER PRIMARY KEY, price NUMBER);
OUTPUT
Table created.
Reproducerun as lab_a
CREATE TABLE lab_b.price_hist (item_id NUMBER, price NUMBER, changed DATE);
OUTPUT
CREATE TABLE lab_b.price_hist (item_id NUMBER, price NUMBER, changed DATE)
*
ERROR at line 1:
ORA-01031: insufficient privileges
Help: https://docs.oracle.com/error-help/db/ora-01031/
Checkrun as lab_a
COLUMN current_user FORMAT A12
SELECT USER AS current_user FROM dual;
OUTPUT
CURRENT_USER
------------
LAB_A

1 row selected.

Creating objects as the owning schema is safer than asking for a broad ANY privilege.

After the fixrun as lab_b
CREATE TABLE price_hist (item_id NUMBER, price NUMBER, changed DATE);
GRANT SELECT, INSERT ON price_hist TO lab_a;
OUTPUT
Table created.


Grant succeeded.

FAQ

How do I check which privileges I have in Oracle?

SELECT privilege FROM session_privs shows what the current session can use, including privileges from enabled roles. user_sys_privs shows only those granted directly to you, user_role_privs shows your roles.

Why does CREATE TABLE work in SQL*Plus but fail with ORA-01031 in a procedure?

The privilege comes from a role. Roles are not active inside definer-rights procedures. Grant the privilege directly to the procedure owner.

I have CREATE TABLE. Why does CREATE MATERIALIZED VIEW give ORA-01031?

It is a separate system privilege. GRANT CREATE MATERIALIZED VIEW TO <user> fixed it in this lab.

Can I create a table in another schema?

Only with CREATE ANY TABLE. Usually it's better to create it as the owner and grant access to the other user.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors