ORA-01031: insufficient privileges
ORA-01031: insufficient privilegesThe 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
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
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;Table created.
1 row created.
1 row created.
Commit complete.CREATE MATERIALIZED VIEW sales_by_region AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;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/COLUMN privilege FORMAT A20
SELECT privilege FROM session_privs ORDER BY privilege;PRIVILEGE
--------------------
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
8 rows selected.GRANT CREATE MATERIALIZED VIEW TO lab_a;Grant succeeded.Each object type has its own CREATE privilege. Ask your DBA for the exact one the failing statement needs.
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;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
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;Revoke succeeded.
Role dropped.
Role created.
Grant succeeded.
Grant succeeded.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;
/Table created.
Procedure created.EXEC make_work_tableBEGIN 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/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';no rows selected
GRANTED_ROLE
------------
LAB_DEV
1 row selected.
PRIVILEGE
--------------------
CREATE TABLE
1 row selected.GRANT CREATE TABLE TO lab_a;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.
EXEC make_work_table
SELECT table_name FROM user_tables ORDER BY table_name;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
CREATE TABLE price (item_id NUMBER PRIMARY KEY, price NUMBER);Table created.CREATE TABLE lab_b.price_hist (item_id NUMBER, price NUMBER, changed DATE);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/COLUMN current_user FORMAT A12
SELECT USER AS current_user FROM dual;CURRENT_USER
------------
LAB_A
1 row selected.Creating objects as the owning schema is safer than asking for a broad ANY privilege.
CREATE TABLE price_hist (item_id NUMBER, price NUMBER, changed DATE);
GRANT SELECT, INSERT ON price_hist TO lab_a;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