Ошибка oracle 01031

ORA-01031

ORA-01031: не достаточно привилегий

Причина:

Вы предприняли попытку изменить текущее имя пользователя или пароль без привилегии АБД. Эта ошибка часто происходит, если вы пытаетесь изменить (UPDATE) таблицу, для которой вы имеете только SELECT привилегии, или если вы пытаетесь связаться внутренне без необходимых привилегий операционной системы.

Действие:

Попросите своего АБД выполнить операцию или дать вам необходимую привилегию.

ORA-01031: Insufficient Privileges means that the current user did not use the right privilege to process the SQL statement.

Since this error can be seen almost in every kind of SQL statement, sometimes you would never know what privilege you lack. So I do my best to collect cases for you.

There’re several error patterns of ORA-01031 in this post. You may click whichever situation you encountered.

1. Select (Query)
2. Create Table
3. Create Index
4. Create View
5. Create Synonym
6. Insert, Update and Delete
7. Alter Table (Add Constraint)
8. Alter Table (Other’s Table)
9. Alter User
10. Analyze Table
11. Password Change
12. EXECUTE IMMEDIATE
13. DGMGRL
14. Alter Pluggable Database Close
15. PDB Clone
16. GRANT System Privilege

Select (Query)

Tried to select other’s table, we got ORA-01031: insufficient privileges.

SQL> show user
USER is "HR"
SQL> select distinct gender from oe.customers;
select distinct gender from oe.customers
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

Theoretically, if we can’t see other’s table, we got ORA-00942: table or view does not exist. But the error message indicates us that we don’t use the right privilege to do it. Why? We’d better do some tests.

The first test is that, can we describe the table’s definition?

SQL> desc oe.customers;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 GENDER                                             VARCHAR2(1)
...

Yes, we can see its metadata, but not data.

So what object privileges we have now? Let’s check them by a privileged user.

SQL> show user
USER is "SYSTEM"
SQL> select privilege from dba_tab_privs where owner = 'OE' and table_name = 'CUSTOMERS' and grantee = 'HR' order by 1;

PRIVILEGE
----------------------------------------
DELETE
INSERT
UPDATE

OK, we can INSERT, UPDATE and DELETE, but no SELECT privilege. This is really weird.

Solution to ORA-01031

To solve insufficient privilege in querying, we should grant SELECT privilege to the user.

SQL> grant select on oe.customers to hr;

Grant succeeded.

Then we query the table again.

SQL> select distinct gender from oe.customers;

G
-
M
F

OK, the problem is solved.

Create Table

Let’s see what will happen if the new user wants to create a table.

C:UsersAdministrator>sqlplus thomas/thomas@orcl
...
SQL> create table test1 (id number, e_id number);
create table test1 (id number, e_id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Immediately, ORA-01031: insufficient privileges shows up, which tells the user who doesn’t have the right privilege to do that.

Solution to ORA-01031

The solution is simple, just grant CREATE TABLE to user, a schema-based privilege or CREATE ANY TABLE, a system-wide privilege.

SQL> conn / as sysdba
Connected.
SQL> grant create table to thomas;

Grant succeeded.

Then tell him to try it again.

SQL> create table test1 (id number, e_id number);

Table created.

If you use EXECUTE IMMEDIATE to run CREATE TABLE in a stored procedure, you may check ORA-01031 in EXECUTE IMMEDIATE section in this post.

Create Index

In the above section, we have granted CREATE TABLE to the new user, which naturally enables it to CREATE INDEX in his own schema.

Please note that, CREATE INDEX is not a valid privilege, but CREATE ANY INDEX is.

Let’s see an example and then we guess what privilege we need.

Suppose the new user wants to create an index for SH.CUSTOMERS in his own schema, so we grant SELECT on that table (object privilege) by instinct.

SQL> grant select on sh.customers to thomas;

Grant succeeded.

Then it tries to create an index on that table.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);
create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender)
                                          *
ERROR at line 1:
ORA-01031: insufficient privileges

Solution to ORA-01031

This is because SELECT on that table is not enough, you should additionally grant INDEX on that table to user, which is an object privilege.

SQL> grant index on sh.customers to thomas;

Grant succeeded.

Try again.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);

Index created.

Even though the case is possible in reality, we seldom create index for other user’s table in our schema.

Create View

If you have read the above section, then you have known that you have to grant CREATE VIEW to the user in order to solve ORA-01031.

SQL> grant create view to thomas;

Grant succeeded.

If the user still got ORA-01031 after granting CREATE VIEW to him, it must be a deeper problem. That’s why this section is little longer.

Inherit Privilege from Role

Some privileges inherit from role do not work in some situation, especially when accessing intermediate kinds of object, like views or store procedures.

Here is a case that can reproduce the error.

We grant role RESOURCE to the user. Then we grant the system privilege SELECT ANY TABLE to the role RESOURCE.

SQL> grant resource to thomas;

Grant succeeded.

SQL> grant select any table to resource;

Grant succeeded.

So we can expect that the user inherits the system privilege from RESOURCE. That is, it can select any other’s table.

Let’s do the first test. Use it to select other user’s table SH.SALE.

SQL> select count(*) from sh.sales;

  COUNT(*)
----------
    918843

Good, it acts as we expect, although the user has not any object privilege on SH.SALE.

Let’s do the second test. We use it to create a view which is based on other user’s table SH.SALE.

SQL> create view sh_sales_v as select * from sh.sales;
create view sh_sales_v as select * from sh.sales
                                           *
ERROR at line 1:
ORA-01031: insufficient privileges

What happened? the user has CREATE VIEW and inherit SELECT ANY TABLE from RESOURCE, it should have no problem.

The result implies that the role’s privileges does not reach underlying objects through intermediate objects like views.

Solution to ORA-01031

The solution to this problem is to grant SELECT on the table to user directly.

First, grant the object privilege explicitly to resolve the problem.

SQL> grant select on sh.sales to thomas;

Grant succeeded.

Then tell Thomas to create view again.

SQL> create view sh_sales_v as select * from sh.sales;

View created.

Now, it’s no problem.

Please notice that, if you create a synonym on SH.SALES, it will succeed whether the explicit object privilege is granted directly or not.

Create Synonym

Tried to create a private synonym, but it failed with ORA-01031.

SQL> show user
USER is "HR"
SQL> create synonym customers for oe.customers;
create synonym customers for oe.customers;
*
ERROR at line 1:
ORA-01031: insufficient privileges

To solve the problem, just simply grant CREATE SYNONYM to the user.

SQL> grant create synonym to hr;

Grant succeeded.

Then create it again.

SQL> create synonym customers for oe.customers;

Synonym created.

Problem solved.

Insert, Update and Delete

You may have the right to select other’s table.

SQL> conn sh/sh
Connected.
SQL> select * from hr.t1;

        ID
----------
         1
         2
         3

But you may not have the right to perform some Data Manipulation Language (DML) operations on the table. For example, INSERT INTO some data like this:

SQL> insert into hr.t1 values (4);
insert into hr.t1 values (4)
               *
ERROR at line 1:
ORA-01031: insufficient privileges

This is because you lack INSERT, UPDATE or DELETE privilege to modify on that table which is usually owned by others.

Solution to ORA-01031

Clearly, the right privilege is INSERT, UPDATE or DELETE at object-level. You may ask for DBA or the object owner to grant the privilege to you.

Grant DML

We may grant individual privileges to the user.

SQL> conn hr/hr
Connected.
SQL> grant insert,update,delete on hr.t1 to sh;

Grant succeeded.

As we can see, the grantor grants 3 object privileges INSERT, UPDATE and DELETE on the table to the grantee at a time.

Grant All

In some cases, you may consider to grant all possible object privileges to user, for example:

SQL> grant all on hr.t1 to sh;

Grant succeeded.

In the statement, ALL is a keyword which means all possible privileges on specified object. For a table, it naturally includes not only SELECT, but also INSERT, UPDATE and DELETE.

As a result, we can insert some rows.

SQL> conn sh/sh
Connected.
SQL> insert into hr.t1 values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.t1;

        ID
----------
         1
         2
         3
         4

That is to say, not only SELECT, but also INSERT, UPDATE or DELETE privilege you should have to manipulate tables owned by other users.

Alter Table (ADD CONSTRAINT)

In the above section, we have granted CREATE TABLE to the user. Implicitly, he also has the right to ALTER TABLE on schema-level. So the cause of ORA-01031 in ALTER TABLE is not so obvious as we thought.

Please note that, ALTER TABLE is not a privilege, but ALTER ANY TABLE is. That’s why there’s no such GRANT ALTER TABLE TO statement.

Let’s see an example. User Thomas wants to add a constraint so as to make a reference to another user’s data, so we grant SELECT on that table to the user by instinct.

SQL> grant select on hr.employees to thomas;

Then we make the reference.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);
alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id)
                                                                         *
ERROR at line 1:
ORA-01031: insufficient privileges

We got ORA-01031.

Solution to ORA-01031

The right privilege to reference other’s data is not SELECT, it’s REFERENCES.

We should grant REFERENCES on the table to user either by HR or privileged users.

SQL> grant references on hr.employees to thomas;

Grant succeeded.

Now Thomas can finish his job.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);

Table altered.

Alter Table (Other’s Table)

If you were trying to alter other user’s table without a proper privilege, you may get ORA-01031 like this:

SQL> show user
USER is "HR"
SQL> alter table sh.customers add (col varchar2(10));
alter table sh.customers add (col varchar2(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

Although you have some other object privilege like SELECT or UPDATE on the table, you still cannot add a column for other user’s table.

Solution

The right privilege to alter other’s table is not UPDATE, it’s ALTER. The single word, the single word can be an object privilege.

SQL> grant alter on sh.customers to hr;

Grant succeeded.

Then do it again.

SQL> alter table sh.customers add (col varchar2(10));

Table altered.

System privilege ALTER ANY TABLE shall also work, but it’s unnecessary for most cases.

Alter User

Tried to add some quota on tablespace to itself, but it lacks of some privileges.

SQL> show user
USER is "HR"
SQL> alter user hr quota unlimited on users;
alter user hr quota unlimited on users
*
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

The right privilege in this case is ALTER USER.

SQL> show user
USER is "SYSTEM"
SQL> grant alter user to hr;

Grant succeeded.

Then we do it again.

SQL> show user
USER is "HR"
SQL> alter user hr quota unlimited on users;

User altered.

SQL> alter user oe account lock;

User altered.

As you can see, with ALTER USER privilege, the user not only can grant some quota to itself, but also can change other’s status.

Analyze Table

We saw ORA-01031 when we tried to analyze other user’s table.

SQL> show user
USER is "HR"
SQL> analyze table sh.customers validate structure;
analyze table sh.customers validate structure
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

To enable an user to analyze and gather statistics, the correct privilege is ANALYZE ANY.

SQL> show user
USER is "SYS"
SQL> grant analyze any to hr;

Grant succeeded.

Then analyze the table again.

SQL> analyze table sh.customers validate structure;

Table analyzed.

We solved it.

Password Change

Tried to change other’s password by SQL*Plus command password, but the user has inadequate privileges.

SQL> show user
USER is "HR"
SQL> password oe
Changing password for oe
New password:
Retype new password:
ERROR:
ORA-01031: insufficient privileges

Password unchanged

Since password command is actually an ALTER USER statement, the correct privilege to change other’s password is ALTER USER.

SQL> grant alter user to hr;

Grant succeeded.

Then do it again.

Let’s see how I reproduce ORA-01031 for statements using EXECUTE IMMEDIATE by the following example.

Inherit Privilege from Role

In the above section, I have granted role RESOURCE to THOMAS. Now I grant CREATE ANY DIRECTORY and DROP ANY DIRECTORY to the role RESOURCE.

SQL> grant create any directory, drop any directory to resource;

Grant succeeded.

So we can expect that the user can also do such operations by inheriting all privileges from RESOURCE.

Things look fine when we use the user to create or drop directories.

SQL> create directory tmp_path as '/u02/tmp';

Directory created.

SQL> drop directory tmp_path;

Directory dropped.

SQL> create directory tmp_path as '/u02/tmp';

Directory created

Now, Thomas would like to create directories in stored procedures which is also called named PL/SQL blocks or programming units.

First of all, DBA have to grant CREATE PROCEDURE to him before Thomas doing anything.

SQL> grant create procedure to thomas;

Grant succeeded.

Then Thomas create a procedure like this:

SQL> create or replace procedure drop_create_tmp_dir is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

It seems no problem. But when we execute the stored procedure (named PL/SQL), we got ORA-01031 at line 3.

SQL> exec drop_create_tmp_dir;
BEGIN drop_create_tmp_dir; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "THOMAS.DROP_CREATE_TMP_DIR", line 3
ORA-06512: at line 1

Rationale

This is because the system privileges inherit from role cannot be used in named stored procedures with definer’s right.

Solutions to ORA-01031

Now we have several options, the first one is to grant all necessary privilege to the user directly, the second one is to use invoker’s right, and the last one is to use anonymous PL/SQL blocks.

1. Directly Granting to User

The user should directly get the system privilege from DBA, not inherit from role.

SQL> grant create any directory, drop any directory to thomas;

Grant succeeded.

Back to THOMAS, we can execute it again.

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

The better thing is that we don’t have to recompile the procedure.

2. Use Invoker’s Right

Another way to solve ORA-01031 for statements in EXECUTE IMMEDIATE is to use invoker’s right to define the procedure.

Let’s revert the granting by SYS.

SQL> revoke create any directory, drop any directory from thomas;

Revoke succeeded.

Then we created the procedure with AUTHID CURRENT_USER clause.

SQL> create or replace procedure drop_create_tmp_dir authid current_user is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

Try to execute the procedure by the user.

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

By invoker’s right, we can use role’s privileges.

3. Anonymous PL/SQL Block

What we mean in the above is that role privileges cannot penetrate NAMED stored procedures. That is to say, you can use role privileges in anonymous PL/SQL blocks. For instance, we can rewrite the stored procedure to an anonymous PL/SQL as this:

begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end;
/

You can save and use it as a normal SQL script file.

For the same reason, CREATE TABLE in EXECUTE IMMEDIATE can also throw ORA-01031.

DGMGRL

DGMGRL allows user to query the status of all nodes involved through the local authentication without problem, but it might fail to switchover to a standby database or convert to a snapshot standby.

DGMGRL Switchover

Let’s see a switchover in 11g, it will fail when you connect DGMGRL with local authentication.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> switchover to standb
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primdb2" on database "primdb"
Shutting down instance "primdb2"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the SWITCHOVER command:
        shut down instance "primdb2" of database "primdb"

DGMGRL>

But if you connect DGMGRL with the database password, the switchover will succeed.

[oracle@primary01 ~]$ dgmgrl sys/password@primdb1
...
DGMGRL> switchover to standb
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primdb2" on database "primdb"
Shutting down instance "primdb2"...
Database closed.
Database dismounted.
ORACLE instance shut down.
New primary database "standb" is opening...
Operation requires shutdown of instance "primdb1" on database "primdb"
Shutting down instance "primdb1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "primdb1" on database "primdb"
Starting instance "primdb1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "standb"
DGMGRL>

DGMGRL Convert

Same error happened in a conversion.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> CONVERT DATABASE standb TO SNAPSHOT STANDBY;
Converting database "standb" to a Snapshot Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        shut down instance "standb2" of database "standb"

Solution to ORA-01031

You must use the database authentication to convert a standby database.

[oracle@primary01 ~]$ dgmgrl sys/password@primdb1
...
DGMGRL> CONVERT DATABASE standb TO SNAPSHOT STANDBY;
Converting database "standb" to a Snapshot Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Continuing to convert database "standb" ...
Database "standb" converted successfully
...

For the same reason, the broker is unable to startup the new standby database during a switchover and throws ORA-01017 due to OS authentication.

Alter Pluggable Database Close

We saw an error when we tried to close a pluggable database (PDB) by a normal user.

SQL> conn hr/password@orclpdb
Connected.
SQL> show user
USER is "HR"
SQL> alter pluggable database close;
alter pluggable database close
*
ERROR at line 1:
ORA-01031: insufficient privileges

To solve ORA-01031, we take two steps to make the user be able to close a PDB.

1. Grant SYSDBA to the User

Please make sure that you login as SYS and are in the right container.

SQL> show user
USER is "SYS"
SQL> show con_namev
CON_NAME
------------------------------
ORCLPDB

Then we grant SYSDBA privilege to the user.

SQL> grant sysdba to hr;

Grant succeeded.

2. Connect as SYSDBA

The user should use SYSDBA privilege to connect to the PDB.

SQL> conn hr/password@orclpdb as sysdba
Connected.
SQL> alter pluggable database close;

Pluggable database altered.

Actually, the normal user has become a SYS which of course has the ability to maintain database.

PDB Clone

When you try to clone a remote PDB via a database link, you may see ORA-17628 and ORA-01031 at that moment. I have talk about it in that post.

GRANT System Privilege

It failed with ORA-01031 when we tried to grant a system privilege to another user.

SQL> show user
USER is "HR"
SQL> grant create table to oe;
grant create table to oe
*
ERROR at line 1:
ORA-01031: insufficient privileges

To be able to grant any system privilege to any grantee, the grantor requires GRANT ANY PRIVILEGE privilege.

SQL> show user
USER is "SYSTEM"
SQL> grant grant any privilege to hr;

Grant succeeded.

That is to say, the insufficient privilege in this case is GRANT ANY PRIVILEGE.

Posted by Patrick Hamou on 2016:04:19 20:15:14

Purpose

This document is intended to supply some commonly used steps for diagnosing error ORA-1031 encountered while connecting as sysdba.

RENAPS Offers Oracle Database & Middleware Managed Services. Learn More about how we can help your business

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

[oracle@seclin4 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba) context=user_u:system_r:unconfined_t

[oracle@seclin4 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

/* IMPORTANT: this file needs to be in sync with
rdbms/src/server/osds/config.c, specifically regarding the
number of elements in the ss_dba_grp array.
*/

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

[celcaix3]/grdbms/64bit/app/oracle/product/1120/rdbms/lib> cat config.s
# SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.
# Refer to the Installation and User's Guide for further information.

.rename H.10.NO_SYMBOL{TC},""
.rename H.11.NO_SYMBOL{TC},""
.rename H.12.NO_SYMBOL{TC},""
.rename H.13.NO_SYMBOL{RO},""
.rename H.14.NO_SYMBOL{RO},""
.rename H.15.NO_SYMBOL{RO},""
.rename H.16.ss_dba_grp{TC},"ss_dba_grp"

.lglobl H.13.NO_SYMBOL{RO}
.lglobl H.14.NO_SYMBOL{RO}
.lglobl H.15.NO_SYMBOL{RO}
.globl ss_dba_grp{RW}

# .text section

# .data section

.toc
T.16.ss_dba_grp:
.tc H.16.ss_dba_grp{TC},ss_dba_grp{RW}
T.10.NO_SYMBOL:
.tc H.10.NO_SYMBOL{TC},H.13.NO_SYMBOL{RO}
T.11.NO_SYMBOL:
.tc H.11.NO_SYMBOL{TC},H.14.NO_SYMBOL{RO}
T.12.NO_SYMBOL:
.tc H.12.NO_SYMBOL{TC},H.15.NO_SYMBOL{RO}

.csect ss_dba_grp{RW}, 3
.llong H.13.NO_SYMBOL{RO}
.llong H.14.NO_SYMBOL{RO}
.llong H.15.NO_SYMBOL{RO}
# End csect ss_dba_grp{RW}

.csect H.13.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.13.NO_SYMBOL{RO}

.csect H.14.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.14.NO_SYMBOL{RO}

.csect H.15.NO_SYMBOL{RO}, 3
.string ""
# End csect H.15.NO_SYMBOL{RO}
.llong 0x00000000

# .bss section

        .section        ".text",#alloc,#execinstr
/* 0x0000          7 */         .file   "x.c"

        .section        ".data",#alloc,#write
/* 0x0000          9 */         .global ss_dba_grp
/* 0x0000         10 */         .align  8

                        .global ss_dba_grp
                       ss_dba_grp:
/* 0x0000         17 */         .align  8
/* 0x0000         18 */         .xword  (.L12+0)
/* 0x0004         24 */         .align  8
/* 0x0004         25 */         .xword  (.L13+0)
/* 0x0008         26 */         .type   ss_dba_grp,#object
/* 0x0008         27 */         .size   ss_dba_grp,16

        .section        ".rodata1",#alloc
/* 0x0008         13 */         .align  8


                       .L12:
/* 0x0008         15 */         .ascii  "dba"
/* 0x0014         20 */         .align  8


                       .L13:
/* 0x0014         22 */         .ascii  "dba"

D:oracleproduct10.2.0db_1NETWORKADMIN>echo %username%
dbadmin

D:oracleproduct10.2.0db_1NETWORKADMIN>NET LOCALGROUP ORA_DBA
Alias name ORA_DBA
Comment Members can connect to the Oracle database as a DBA without a password
Members

-------------------------------------------------------------------------------
dbadmin
NT AUTHORITYSYSTEM
The command completed successfully.
D:oracleproduct10.2.0db_1NETWORKADMIN>set oracle_sid=d1v10204

D:oracleproduct10.2.0db_1NETWORKADMIN>NET LOCALGROUP ORA_%ORACLE_SID%_DBA
Alias name ORA_d1v10204_DBA
Comment Members can connect to instance d1v10204 as a DBA without a password

Members
-------------------------------------------------------------------------------
dbadmin
The command completed successfully.

SQLNET.AUTHENTICATION_SERVICES = (ALL)

SQLNET.AUTHENTICATION_SERVICES = (BEQ,)

SQLNET.AUTHENTICATION_SERVICES = (NTS)

SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)

trace_level_client = 16
trace_directory_client = c:tempnewsqlnet

[02-OCT-2011 09:21:02:076] naun5authent: SSPI: 0x8009030c error in InitializeSecurityContext
[02-OCT-2011 09:21:02:076] naun5authent: exit
[02-OCT-2011 09:21:02:076] naunauthent: exit
[02-OCT-2011 09:21:02:076] nau_ccn: get credentials function failed
[02-OCT-2011 09:21:02:076] nau_ccn: failed with error 12638
[02-OCT-2011 09:21:02:076] nacomsd: entry
[02-OCT-2011 09:21:02:076] nacomfsd: entry
[02-OCT-2011 09:21:02:076] nacomfsd: exit
[02-OCT-2011 09:21:02:076] nacomsd: exit
[02-OCT-2011 09:21:02:076] nau_ccn: exit
[02-OCT-2011 09:21:02:076] na_csrd: failed with error 12638
[02-OCT-2011 09:21:02:076] na_csrd: exit
[02-OCT-2011 09:21:02:076] nacomer: error 12638 received from authentication service 

Linux: 
strace -o /tmp/strace_sysdba.output -cfT sqlplus / as sysdba
AIX, Solaris: 
truss -fea -o /tmp/truss_sysdba.output sqlplus / as sysdba

HP-UX: 
tusc -afpo /tmp/tusc_sysdba.output -aef sqlplus / as sysdba

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

$ > orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=<password> force=y ignorecase=n

-rw-r----- 1 oracle oinstall 1536 Jul 21 13:50 orapwdv11201

D:> cd %ORACLE_HOME/database 
D:> orapwd file=pwd<sid>.ora password=<password> force=y nosysdba=n

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

grant SYSDBA to myadminuser; 

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
    (GLOBAL_DBNAME = d1v11203)
     (ORACLE_HOME = /oracle/product/11203)
    (SID_NAME = d1v11203)
   )
  )

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = d1v11203)
      (ORACLE_HOME = /oracle/product/11203)
     (SID_NAME = d1v11203)
   )
  )

CLIENT_load_balance= 
 (DESCRIPTION = 
  (LOAD_BALANCE = yes)
 (ADDRESS_LIST = 
   (ADDRESS = (PROTOCOL = TCP)(HOST = ro-rac1.ro.oracle.com)(PORT = 1521)) 
   (ADDRESS = (PROTOCOL = TCP)(HOST = ro-rac2.ro.oracle.com)(PORT = 1521)) 
  ) 
  (CONNECT_DATA = 
  (SERVICE_NAME = service.idc.oracle.com) 
  ) 
 )

RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

Caused by: oracle.sysman.emdrep.config.ConfigurationException:
Cannot Create Connection:(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testemconfig.us.oracle.com)(PORT=1553)))(CONNECT_DATA=(SERVICE_NAME=TESTDB)))
SYS
sysdba
ORA-01031: insufficient privileges

[oracle@test dbs]$ sqlplus sys/oracle@r01 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 7 16:02:39 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

When using Oracle, it is common to encounter the ORA-01031 error. This error indicates that the user has insufficient privileges to access a particular database or perform a specific action. The ORA-01031 error is a persistent problem, but it can be fixed.

This article will discuss the causes of this error, how to troubleshoot and fix it, and how to avoid it in the future. By following the tips and tricks outlined here, users can quickly and efficiently resolve the ORA-01031 error in Oracle.

Causes of the ORA-01031 Error:

Oracle database users may encounter the ORA-01031 error when they attempt to access a database without sufficient privileges. This error occurs when a user attempts to access a database in Oracle without the proper privileges. The error is usually raised when the user does not have the ‘CONNECT’ privilege assigned to them. Without this privilege, the user cannot connect to the database.

In addition to not having the proper privileges, the ORA-01031 error can be caused by syntax errors in the SQL statement that are being run. If the statement contains any invalid syntax, the database will not be able to process the statement and will end with the ORA-01031 error. This error can also be caused by insufficient space in the tablespace for the database. If the tablespace is entire, the statement cannot execute and will produce the ORA-01031 error.

In some cases, the ORA-01031 error can be caused by insufficient resources in the database. It can include a need for more memory, disk space, and CPU resources. If the system does not have enough resources to process the query, it may return the ORA-01031 error.

The ORA-01031 error can also occur when the database is not open. The database must be open for users to access the data. If a user attempts to access the data and the database is not open, the ORA-01031 error will be returned.

Finally, the ORA-01031 error can be caused by a missing initialization parameter. This parameter is used to store specific settings, such as the size of the database and the path to the data files. If this parameter is missing, the database cannot be opened, and the ORA-01031 error will be returned.

These are the most common causes of the ORA-01031 error in Oracle. To resolve this error, it is essential to understand what may be causing the issue so that the proper steps can be taken to fix it.

Troubleshooting Tips:

When looking to resolve the ORA-01031 error, it is crucial to understand why it occurred in the first place. This article section will provide troubleshooting tips on identifying and fixing the error.

The first step in troubleshooting an ORA-01031 error is to check the user privileges. Users working with databases must have sufficient privileges to access and manipulate data. If the privileges are insufficient, the user will be denied access, and the ORA-01031 error will be thrown.

The second step is to review the SQL statement for any syntax errors. SQL statements are a type of computer language that is used to interact with a database. Syntax errors in an SQL statement can cause the ORA-01031 error to be thrown.

The third step is to check the amount of space allocated to the tablespace. If the tablespace is complete, the user will be denied access, and the ORA-01031 error will be thrown. Increasing the space available in the tablespace helps resolve the error.

These steps can help to identify the cause of the ORA-01031 error. Once the cause is identified, steps can be taken to resolve the error and prevent it from occurring. Understanding the cause of the error will inform the steps to take to fix and prevent it from happening again.

Steps to Resolve ORA-01031 Error:

When confronted with an ORA-01031 error in Oracle, taking the proper steps to troubleshoot and resolve the issue is essential.

• The first step should be to check for any insufficient privileges the user might have. If so, the user can be granted additional privileges to access the required resources.
• The second step is to review the SQL statement for syntax errors. Reviewing the statement and ensuring it is correctly formatted thoroughly is vital. If any syntax errors are found, they can be corrected to prevent the ORA-01031 error.
• The third step is to check the amount of space allocated to the tablespace. If the tablespace does not have enough space, the error will occur. To prevent this, additional space can be allocated to the tablespace to provide enough space for the operation.
• The fourth step is to confirm that the database exists and is open. If the database is not open, it cannot be accessed, and the error will occur. To prevent this, the database must be open before attempting to access it.
• The fifth step is to check for any missing initialization parameters. Initialization parameters must be set on the database for it to function correctly. If any of these parameters are missing or set incorrectly, the ORA-01031 error can occur.

Once all of these steps have been taken, the ORA-01031 error should be resolved. If the error persists, the user can consider the standard solutions, such as granting additional privileges, finding and correcting syntax errors, and allocating more space in the tablespace.

The user can also take steps to avoid the error in the future, such as monitoring database privileges, updating software regularly, and performing database maintenance. By following these steps, the user should be able to successfully and safely fix and avoid the ORA-01031 error in Oracle.

Common Solutions:

A few standard solutions can be used when resolving the ORA-01031 error in Oracle. One of the most common solutions is to grant the user the necessary privileges. It can be done either manually by granting privileges to the user or using a role-based approach. That will ensure the user has the necessary privileges to execute the query.

Another solution is to check and correct any syntax errors in the SQL statement. It is crucial as the ORA-01031 error can be caused by incorrect syntax. Checking and correcting any syntax errors can help to resolve the error.

Finally, allocating more space in the tablespace can be an excellent solution to the ORA-01031 error. It can be done manually by adding additional files to the tablespace or using an automated process. Increasing the space in the tablespace can allow for more data and help resolve the ORA-01031 error.

It is also important to note that the ORA-01031 error can be caused by combining the above solutions. For instance, an insufficient amount of space in the tablespace can be caused by a lack of privileges and an incorrect SQL statement. Therefore, it is essential to consider all possible solutions when attempting to resolve the ORA-01031 error.

Furthermore, if the ORA-01031 error is caused by a bug or an issue with the Oracle software, it is essential to update the software to the latest version. It can help to resolve the error, as older versions of the software may contain bugs or other issues that can cause the ORA-01031 error.

Finally, it is essential to ensure that regular database maintenance is performed. That can help to prevent the ORA-01031 error from occurring in the future, as maintenance can help to identify and resolve any issues with the database before they become a problem.

Safely Avoiding ORA-01031 Error:

While it is possible to fix the ORA-01031 error in Oracle, it is much easier to avoid it altogether. To do so, there are a few steps that should be taken.

First and foremost, it is crucial to monitor the database privileges that are granted to users. If the privilege causing the ORA-01031 error is known, removing it from the user or adjusting the user’s role is best. That helps ensure the user is not granted access to objects unless explicitly allowed.

It is also essential to keep the software up to date. It ensures that the software runs the most recent database version and that all the privileges, objects, and functions are compatible. If the software is not updated, the ORA-01031 error may likely occur due to incompatibilities.

Finally, it is crucial to perform database maintenance regularly. This includes checking the database for any errors or inconsistencies. If any are found, they should be addressed as soon as possible. It helps to ensure that the database does not encounter any unexpected errors that could lead to the ORA-01031 error.

Users can avoid the ORA-01031 error in Oracle more efficiently by taking these steps. It helps to ensure that the database is running smoothly and that any encountered errors can be quickly addressed.

Preventing the Error in the Future:

The best way to avoid the ORA-01031 error in Oracle is to be proactive. Taking steps to prevent this error from occurring in the first place can save you time and money. Here are some tips to help prevent the ORA-01031 error in Oracle.

The first step is to assign the appropriate privileges to the user. You must ensure the user has the correct system privileges to do this. Privileges can be assigned by using the GRANT statement in the command line. With this statement, you can assign specific privileges to a user or group of users. It is essential to be aware of the privileges being assigned and not to assign unnecessary privileges.

The second step is to ensure the SQL statement is correctly formatted. It means checking all the syntax and making sure it is correct. Any typos or syntax errors can cause the ORA-01031 error to be thrown. Paying attention to the syntax and formatting can save a lot of time in the long run.

The third step is to monitor the amount of space in the tablespace. Knowing the amount of space available can help prevent errors from occurring. If you expect extensive data to be stored in a particular tablespace, you may need to increase the space allocated. Doing this in advance can save you from receiving the ORA-01031 error in the future.

Finally, monitoring the database privileges and updating software regularly is vital. It will ensure the user has the correct privileges and that the software is up-to-date. Updating the software can help prevent errors from occurring due to outdated software.

You can save time and money by taking these steps to prevent the ORA-01031 error in Oracle. Being proactive and taking the time to ensure all the privileges have been assigned correctly, the SQL statement is correctly formatted, and the correct amount of space is allocated to the tablespace can go a long way in preventing the ORA-01031 error in Oracle.

Conclusion:

Fixing the ORA-01031 error in Oracle is a time-consuming process that requires patience and attention to detail. The key is to understand why the error occurs and troubleshoot it correctly. The most common causes of this error are lack of user privileges, syntax errors in the SQL statement, and insufficient table space.

First, to resolve the error, check user privileges, review the SQL statement for errors, and increase the space allocated to the tablespace. Then, standard solutions include:

• Granting user privileges.
• Finding and correcting syntax errors.
• Allocating more space in the tablespace.

To avoid errors in the future, it is recommended to monitor database privileges, update software regularly, and perform database maintenance. Finally, assigning the appropriate privileges to the user is essential, ensuring the SQL statement is correctly optimized and keeping an eye on the amount of space in the tablespace.

Following the tips and tricks outlined in this article, you can quickly fix and prevent the ORA-01031 error in Oracle.

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.