Oracle Error ORA 01031 Insufficient Privileges

The ORA-01031: “insufficient privileges” error occurs when you attempt to execute a program or function for which you have not been granted the appropriate privileges.

For the DBA, the ORA-01031 can happen if the target OS executables do not have read and execute permissions (e.g. (770) in UNIX/Linux), and ensure that the oracle user is a member of the dba group (e.g. /etc/group). There are similar permission in the Windows registry.

Inside Oracle, the “ORA-01031: insufficient privileges” error can be avoided by signing on “as sysdba” with unlimited database privileges.

You can check the user has which privileges. The below command shows all exist privileges for the existing user.

select * from session_privs;

SQL> select * from session_privs;

PRIVILEGE
---------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

We can see there are only few privileges are there for “SCOTT” user. Now, we will grant USER SCOTT for all privileges.

For this enter in the database as a DBA. Steps to enter into the the database as a DBA,

Step1) Open command prompt.

Step2) In the command prompt logged into the database without providing required credentials.

sqlplus/nolog

C:\Users\KnowProgram> sqlplus/nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 21 06:53:31 2020

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Step3) Authenticated yourself for the underlying O/S and entered in database as DBA. For that, type below command in the command prompt:

connect / as sysdba;

SQL> connect / as sysdba;
Connected.

Step4) Evaluated who is the DBA user in my database if exists. For that, type below command in the command prompt:

select name from V$database;

SQL> select name from V$database;

NAME
---------
KNOWPROG

Step5) Finally this is the most important step to solve our error: ORA-01031 Insufficient privileges.

Now, we have two options,
1) Grant for all Privileges
2) Grant only for particular privileges

Grant for all Previliges

grant all privileges to <dbusername>;

Here <dbusername> should be replaced with the name of the user you want to give access to. Example:-

SQL> grant all privileges to scott;

Grant succeeded.

Our problem is solved. Now, the scott user grant access for all privileges. We can see this in SQL Plus tools. In SQL Plus tool,

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
.....................
.....................
.....................

200 rows selected.

Grant only for Particular Privileges

If you want to give grant only for certain privileges then you can use below command in the command prompt after signing on “as sysdba”,

grant <privileges> to <dbusername>;

In the Oracle doc for Privileges, you can get the list of all System privileges, Object privileges, Privilege hierarchy, the PUBLIC role.

Example:-

I was trying to creating a view but the USER doesn’t have privileges to create the view,

SQL> create or replace view v
  2  as
  3  select * from emp where deptno=10;
create or replace view v
                       *
ERROR at line 1:
ORA-01031: insufficient privileges

Then I have logged in the database as SYSDBA account and issue the command,

GRANT CREATE VIEW TO <dbusername>;

C:\Users\KnowProgram> sqlplus/nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 21 06:53:31 2020

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect / as sysdba;
Connected.

SQL> select name from V$database;

NAME
---------
KNOWPROG

SQL> grant create view to scott;

Grant succeeded.

Now, I am able to create view in Oracle database for SCOTT user.

How to Revoke Privileges:- Revoke

Also see:- How to unlock scott user in SQL Plus

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search