Set Constraint Returns Intermittent Ora-01031 Errors (Doc ID 861532.1)

Last updated on NOVEMBER 10, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1
Information in this document applies to any platform.

Symptoms


Deferring a constraint errors out intermittently with ora-1031. It can happen that the statement will fail when it is run for the second time in the same session. The error occurs when the user running the set constraint command is granted select access to the table via a role. When the select privilege is granted directly to the user everything is OK:



SQL> create user aaa identified by aaa
           default tablespace users
           quota unlimited on users;

User created.

SQL> grant connect to aaa;

Grant succeeded.


SQL> create user bbb identified by bbb
          default tablespace users
          quota unlimited on users;

User created.

SQL> grant connect to bbb;

Grant succeeded.

SQL> connect aaa/aaa
Connected.

SQL> create table test_pk(n1 number, n2 number);
Table created.

SQL>create table test_fk(n1 number, n2 number);
Table created.

SQL>alter table test_pk add constraint pk_test primary key (n1);
Table altered.

SQL>alter table test_fk add constraint fk_test foreign key (n1) referencing test_pk deferrable;
Table altered.

SQL>grant select on test_pk to bbb;
Grant succeeded.

SQL>grant select on test_fk to bbb;
Grant succeeded.

SQL> connect bbb/bbb
Connected

SQL> set constraint aaa.fk_test deferred;
Constraint set.

SQL> set constraint aaa.fk_test deferred;
Constraint set.

#################################################
## Grant the privilege via a role
#################################################

SQL> connect / as sysdba
Connected

SQL> create role ccc;
Role created

SQL> grant ccc to bbb;
Grant succeeded.

SQL>connect aaa/aaa
Connected

SQL> revoke select on test_pk from bbb;

SQL> revoke select on test_fk from bbb;

SQL> grant select on test_fk to ccc;

SQL> grant select on test_pk to ccc;

SQL> connect bbb/bbb

Connected

SQL> set constraint aaa.fk_test deferred;
Constraint set.

SQL> SET constraint aaa.fk_test deferred
*
ERROR at line 1:
ORA-1031: insufficient privileges


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms