ORA-01031: Insufficient privileges
-
You are trying to do a select, update, insert, ...
You do not have the sufficient privileges to complete the requested operation.
You can check following data dictionary views to check your privileges:
- session_privs: the privileges that are active for the current session.
- all_tab_privs: the object privileges (table, view, procedure, ...) you have
- session_roles: the roles currently active for the session. This can differ from user_role_privs (default is FALSE will not be enabled by default)
Sometimes it can be that you have the privileges, but you still get this error.
This might occur if you try to use a privilege granted via a role inside a pl/sql program.
Whenever you try to compile a pl/sql program (procedure, function, package, ...), roles are disabled, and thus any privilege you were granted via a role are also disabled.
You will have to grant the privilege directly to the user instead of via a role.
-
You are trying to start the database
If you are on Unix, check if the SQLNET.AUTHENTICATION_SERVICES is set to NONE in your sqlnet.ora
If you are on Windows, check that the user belongs to the ORA_DBA group, SQLNET.AUTHENTICATION_SERVICES is set to NTS in sqlnet.ora
Also check if you are using a password file, but for some reason it's gone.
The password file can be found in your %oracle_home%/dbs directory -
You are trying to make a remote connection AS SYSDBA
Even you have set following parameters correctly:remote_login_passwordfile string EXCLUSIVE remote_os_authent boolean TRUE
You still get the ORA-01031 error when trying to make a remote connection AS SYSDBA.
The most likely cause is that you have no password file. To check this, you issue following select:select * from v$pwfile_users;
If this returns no rows, you do not have a password file, and you will have to make one using the orapwd command.
Welcome to our forum for Oracle error: ORA-01031 Add your own message
my toad command is:::
ALTER TABLESPACE PRPC_UBS_1 ADD DATAFILE 'D:\oracle\product\10.2.0\db_1\oradata\sample\tablespace.dbf' AUTOEXTEND ON NEXT 100m MAXSIZE 2000m;
You can check if you have the necessary privileges with following statement:
SQL> select * from session_privs where privilege = 'ALTER TABLESPACE';
PRIVILEGE
----------------------------------------
ALTER TABLESPACE
I was trying to run this query and i got error:ORA-01031: insufficient privileges
CREATE SYNONYM NCTRO.FACT_NP_CLAIMED_ERP_SC FOR NCTADM.FACT_NP_CLAIMED_ERP_SC;
As a dba, issue following statement:
granr create synonym to yourusername
ora-01031 error message is displayed.
can anyone have solution.
conn /as sysdba
ora-01031 : insufficien privileges.
On Windows, your account need to belong to the ORA_DBA group in order to connect to the database.
The privilege was probably granted through a role, but these are disabled when executing/compiling packages/procedures/functions
See following example. A procedure is created in the SYSTEM schema, which is a dba and has create table privileges:
create or replace procedure system.t is
begin
execute immediate 'create table test(n number)';
end;
/
Procedure created.
SQL> exec t
PL/SQL procedure successfully completed.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
N NUMBER
SQL> drop table test;
Table dropped.
Now, new user is created, with just create session privileges:
SQL> grant create session to normal identified by normal;
gran
Grant succeeded.
SQL> t execute on system.t to normal;
Grant succeeded.
SQL> conn normal/normal
Connected.
SQL> exec system.t
PL/SQL procedure successfully completed.
SQL> show user
USER is "NORMAL"
As you can see, the NORMAL user (without create table privileges) can create the table.
But if we change the procedure as being invokers rights, the NORMAL user will not be able to do that anymore:
SQL> conn system
Enter password:
Connected.
SQL> create or replace procedure t
authid current_user
is
begin
execute immediate 'create table test(n number)';
end;
/
Procedure created.
SQL> drop table test;
Table dropped.
User NORMAL fails to create the table, it has no CREATE TABLE privileges:
SQL> conn normal/normal
Connected.
SQL> exec system.t
BEGIN system.t; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.T", line 5
ORA-06512: at line 1
but i have this message
ora-01031 insufficient privileges
If you are on Unix, check if the SQLNET.AUTHENTICATION_SERVICES is set to NONE in your sqlnet.ora
If you are on Windows, check that the user belongs to the ORA_DBA group, SQLNET.AUTHENTICATION_SERVICES is set to NTS in sqlnet.ora
Also check if you are using a password file, but for some reason it's gone.
The password file can be found in your %oracle_home%/dbs directory
I am getting this error ora-01031 when I try to shutdown the database.I am connected to the database as sysdba and my operating system is windows and the parameter in my sqlnet.ora file is SQLNET.AUTHENTICATION_SERVICES =(NTS)
and my user is also a member of DBA_GRP.
Can U please help me to solve this issue.
What is the value of parameter remote_login_passwordfile?
What happens if you issue startup, will it also complain?
Have you tried to shutdown abort or startup force?
i get the same ORA-01031 error
If for instance you are doing a create table, you need to have this privilege directly granted to the user.
Privileges granted via roles are disabled when executing a procedure/function/package, so any privileges granted via a role need to be granted directly in order to let this work.
i want to create function i have error ora-01031
thanks
When i try to create a function am getting ora-01031 error.
I given 'create view' and 'create any procedure' to user.
Thanks,
select * from session_privs
Please post the output of the command
Hi All,
I am using Oracle 10g database on Windows 2k3 Machine. I was trying to execute the
shutdown abort
command where I got the ORA-01031 Insufficient Privileges Error. I am logged in Windows Machine as Administrator and this user is also part of ORA_DBA Group. In sql, I logged in as Sysdba user. I also trying create a user "Administrator" in oracle, granting him dba permission and then logged in sql prompt as Administration. Even then, i got the same issue.
Don't know what I am missing, kindly help.
Thanks
Sunny
is the value of SQLNET.AUTHENTICATION_SERVICE set to NTS in you sqlnet.ora file?
while executing a package got a Insufficient Privileges Error with create table , and i have grant to create table .. kindly suggest me.
Did you grant the 'create table' privilege to a role?
If so, you need to grant the privilege directly to the user when the create table is executed from within a package.
how to create directory in oracle 9i
how to create the privilege to create directory in oracle 9i
create directory yourname as 'c:\path';
grant read, write on directory yourname to a_user;
when i am trying to execute a procedure It shows the below error in my SQL * plus
Here is an simple example:
1 create or replace procedure p1
2 (n1 in number, n2 in number,tot out number)
3 is
4 Begin
5 TOT:=n1+n2;
6 dbms_output.put_line(tot);
7* end p1;
SQL> /
create or replace procedure p1
*
ERROR at line 1:
ORA-01031: insufficient privileges
can anyone rectify it ASAP.Should i need to connect as "SYS".Plz clarify
As a dba (eg. system user), you need to grant the create procedure privileges to the user you are trying to create this procedure with
when i try to create user while log in with scott then error generates (insufficient privileges) kindly help me immediately.?
Scott is a normal end-users and does not have privileges to create a new account by default.
If you want to enable this privilege for user scott you need to connect to the database as a database administrator (SYS AS SYSDBA or SYSTEM) and execute the following:
grant create user to scott;
I created the password file using orapwd cmd, when i try to connect to database using /Sys as sysdba without pwd (only sys as sysdba) it doesnt let me login and generate error
Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges
for this reason my Database startup schedule on Crontab is not working and i have to start the database manually.. so i want to go back to my original /sys as sysdba form so that my schedule in crontab starts the database as per the schedule.
kindly help its urgent
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.