➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
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!
Great post! I’ve encountered the ORA-01031 error before, and your explanations helped me understand it better. I appreciate the tips on resolving permission issues. It’s easy to overlook user privileges!
Great post! I encountered the ORA-01031 error while trying to execute a stored procedure, and your troubleshooting steps were super helpful. Thanks for breaking down the privileges needed – it really clarified things for me!