ORA-00942: table or view does not exist on inserting rows with a user other than the table owner

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

As of Oracle 12.1 it became possible to create tables with IDENTITY columns, that is a numeric column where the value is automatically generated using a system created sequence.

In Oracle 12.2, and perhaps also in 12.1, one could also define a numeric column with a default value based on some sequence.nextval.

The result is similar but there is a subtle difference:
the behavior of an INSERT statement executed by a user that is not the owner of the table.

In the case of an identity column, if the table owner grants insert on the table to a user or role X,  X would be able to insert a row into the table and the same holds if one creates a before insert trigger in the old fashion.

The surprise comes when using the sequence as a DEFAULT value.

SQL Error: ORA-00942: table or view does not exist

The error message is misleading, it refers to the fact that X hasn't got the GRANT SELECT on the sequence, not on the table.

 

CREATE TABLE TEST_GRANT_ID(
  ID    INTEGER GENERATED ALWAYS AS IDENTITY,
  TEXT  VARCHAR2(10)
);

CREATE SEQUENCE TEST_GRANT_SEQ_DFT;

CREATE TABLE TEST_GRANT_DFT(
  ID    INTEGER DEFAULT TEST_GRANT_SEQ_DFT.NEXTVAL,
  TEXT  VARCHAR2(10)
);

CREATE SEQUENCE TEST_GRANT_SEQ_TRG;

CREATE TABLE TEST_GRANT_TRG(
  ID    INTEGER,
  TEXT  VARCHAR2(10)
);

CREATE OR REPLACE TRIGGER BI_TEST_GRANT_TRG  
   before insert on TEST_GRANT_TRG 
   for each row 
begin  
   if inserting then 
      if :NEW."ID" is null then 
         select TEST_GRANT_SEQ_TRG.nextval into :NEW."ID" from dual; 
      end if; 
   end if; 
end;
/

GRANT INSERT ON TEST_GRANT_ID TO &&USER_X.;
GRANT INSERT ON TEST_GRANT_DFT TO &USER_X.;
GRANT INSERT ON TEST_GRANT_TRG TO &USER_X.;

Now you can connect as USER_X and see the different behavior when inserting the rows.

See message translations for ORA-00942 and search additional resources.