➤ 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.
C:\Users\KnowProgram> sqlplus/nolog SQL*Plus: Release 220.127.116.11.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>;
<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.
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 18.104.22.168.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!