Oracle NOT NULL Constraint Violation: Column Name Not Shown

errorsoracleoracle-19cpermissions

I'm using an Oracle 19 DB that is structured into multiple layers by using DB users. Here is a simplified overview of the structure:

  • User DATA: Has the table XTEST and the view TEST. The view is created with a CREATE VIEW TEST AS SELECT * FROM XTEST statement, so it contains all columns of the table.
  • User APP: Uses the view DATA.TEST for all SQL and DML.
    The APP user does not have any grants on the table DATA.XTEST, but does have SELECT,INSERT,UPDATE,DELETE grants on the view DATA.TEST.

Now to my problem:
If a NOT NULL constraint on the table DATA.XTEST is violated, the error message doesn't show the column name. The error message is literally:

ORA-01400: cannot insert NULL into (???)

The DB used Oracle 11 some time ago and back then we had the same problem, but could fix it by granting the REFERENCES privilege on the table DATA.XTEST to the APP user (see related question: Grant privilege to see column names in null constraint violations when inserting in views). But this doesn't work any more in Oracle 19.

Does anyone know how to get the column name in the error message?

Best Answer

Other than the obvious solution (grant select on data.xtest to app;), you can revert to the old behavior by setting SQL92_SECURITY to FALSE. In 11.2, its default value was FALSE, but in 19c, its default value is TRUE.

alter system set sql92_security=false scope=spfile;

Then restart the database, as this parameter can not be changed online.

Demo:

create table bp.test (c1 number not null);
create view bp.xtest as select * from bp.test;
create user u1 identified by u1;
grant create session to u1;
grant select, insert, update, delete on bp.xtest to u1;
grant references on bp.test to u1;

SQL> show parameter sql92

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql92_security                       boolean     TRUE
SQL>

SQL> conn u1/u1
Connected.

SQL> insert into bp.xtest values(null);
insert into bp.xtest values(null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (???)

By changing the parameter:

SQL> conn / as sysdba
Connected.
SQL> alter system set sql92_security=false scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             553648128 bytes
Database Buffers         1577058304 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> show parameter sql92

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql92_security                       boolean     FALSE
SQL> conn u1/u1
Connected.
SQL> insert into bp.xtest values(null);
insert into bp.xtest values(null)
                            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("BP"."TEST"."C1")
Related Question