// Oracle error reference

ORA-01031: insufficient privileges

Your user lacks a system or object privilege the statement needs, or holds it only through a role, which doesn't count inside definer-rights PL/SQL or views.

Error code
ORA-01031
Category
Names and objects
Error message
insufficient privileges

What it means

ORA-01031 means the statement needs a privilege the current user doesn't have: a system privilege such as CREATE TABLE or CREATE VIEW, or an object privilege such as UPDATE on another user's table. A classic trap: privileges granted through roles are not used when compiling definer-rights PL/SQL or creating views on other users' tables, so a statement that works interactively fails inside a procedure.

Common causes

  • Missing system privilege, for example CREATE VIEW, CREATE PROCEDURE or CREATE SEQUENCE.
  • Missing object privilege on another schema's table for the operation (SELECT is not enough for UPDATE).
  • The privilege comes from a role, and the statement runs in definer-rights PL/SQL or is a CREATE VIEW on another schema's objects.
  • Connecting / as sysdba with an OS user who isn't in the DBA group.

How to fix it

  1. See what the user has, directly and through roles.

    SELECT * FROM session_privs ORDER BY privilege;
    SELECT * FROM user_tab_privs WHERE table_name = 'EMPLOYEES';
  2. Grant the missing privilege directly to the user (an administrator or the object's owner runs this).

    GRANT CREATE VIEW TO app_user;
    GRANT SELECT, UPDATE ON hr.employees TO app_user;
  3. For a view over another schema's table that you want to grant on, the owner must grant with the grant option.

    GRANT SELECT ON hr.employees TO app_user WITH GRANT OPTION;

Related errors

More names and objects errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.