a female police officer standing guard

Enforcing DEFAULT values

Posted by

Handling default values on columns has been an age old problem that we solved in 12c with extensions to the default clause. Originally the issue with a DEFAULT setting was that is was only ever considered if the column was not specified at all in the INSERT statement. For example, here’s a subset of the EMP table and we’ll add a CREATED column with a default of SYSDATE


SQL> create table t as select empno, ename from scott.emp;

Table created.

SQL>
SQL> alter table t add created date default sysdate;

Table altered.

By default (see what I did there 🙂), if I omit the CREATED column from an INSERT, we’ll pick up SYSDATE (or whatever we had nominated for our default clause)


SQL> insert into t ( empno, ename) values (123,'Connor');

1 row created.

SQL> select * from t;

     EMPNO ENAME      CREATED
---------- ---------- ---------
      7369 SMITH      30-MAR-22
      7499 ALLEN      30-MAR-22
      7521 WARD       30-MAR-22
      7566 JONES      30-MAR-22
      7654 MARTIN     30-MAR-22
      7698 BLAKE      30-MAR-22
      7782 CLARK      30-MAR-22
      7788 SCOTT      30-MAR-22
      7839 KING       30-MAR-22
      7844 TURNER     30-MAR-22
      7876 ADAMS      30-MAR-22
      7900 JAMES      30-MAR-22
      7902 FORD       30-MAR-22
      7934 MILLER     30-MAR-22
       123 Connor     30-MAR-22

15 rows selected.

SQL> roll;
Rollback complete.

But the strategy breaks down because the standard DEFAULT clause is ignored if we explicitly nominate a value, even if that value is NULL


SQL> insert into t ( empno, ename, created) values (123,'Connor',null);

1 row created.

SQL> select * from t;

     EMPNO ENAME      CREATED
---------- ---------- ---------
      7369 SMITH      30-MAR-22
      7499 ALLEN      30-MAR-22
      7521 WARD       30-MAR-22
      7566 JONES      30-MAR-22
      7654 MARTIN     30-MAR-22
      7698 BLAKE      30-MAR-22
      7782 CLARK      30-MAR-22
      7788 SCOTT      30-MAR-22
      7839 KING       30-MAR-22
      7844 TURNER     30-MAR-22
      7876 ADAMS      30-MAR-22
      7900 JAMES      30-MAR-22
      7902 FORD       30-MAR-22
      7934 MILLER     30-MAR-22
       123 Connor

15 rows selected.

SQL> roll;
Rollback complete.

As I mentioned, in 12c we improved on this by introducing the DEFAULT ON NULL extension. I’ll modify the CREATED column to force the default value to be taken if the INSERT tries to override that definition with a NULL.


SQL> alter table t modify created default on null sysdate;

Table altered.

SQL> insert into t ( empno, ename, created) values (123,'Connor',null);

1 row created.

SQL> select * from t;

     EMPNO ENAME      CREATED
---------- ---------- ---------
      7369 SMITH      30-MAR-22
      7499 ALLEN      30-MAR-22
      7521 WARD       30-MAR-22
      7566 JONES      30-MAR-22
      7654 MARTIN     30-MAR-22
      7698 BLAKE      30-MAR-22
      7782 CLARK      30-MAR-22
      7788 SCOTT      30-MAR-22
      7839 KING       30-MAR-22
      7844 TURNER     30-MAR-22
      7876 ADAMS      30-MAR-22
      7900 JAMES      30-MAR-22
      7902 FORD       30-MAR-22
      7934 MILLER     30-MAR-22
       123 Connor     30-MAR-22

15 rows selected.

SQL> roll;
Rollback complete.

So far so good, and I’ve regularly presented and blogged that this is now a solved problem. But what if you wanted even more rigorous enforcement of a default? What if the value nominated in the DEFAULT clause must be used in all cases. For example, if I want my CREATED column to always be SYSDATE, then DEFAULT ON NULL is not a strong enough declaration for me. Here’s an example where I can just slam in a future date


SQL> insert into t ( empno, ename, created) values (123,'Connor',sysdate+12);

1 row created.

SQL> select * from t;

     EMPNO ENAME      CREATED
---------- ---------- ---------
      7369 SMITH      30-MAR-22
      7499 ALLEN      30-MAR-22
      7521 WARD       30-MAR-22
      7566 JONES      30-MAR-22
      7654 MARTIN     30-MAR-22
      7698 BLAKE      30-MAR-22
      7782 CLARK      30-MAR-22
      7788 SCOTT      30-MAR-22
      7839 KING       30-MAR-22
      7844 TURNER     30-MAR-22
      7876 ADAMS      30-MAR-22
      7900 JAMES      30-MAR-22
      7902 FORD       30-MAR-22
      7934 MILLER     30-MAR-22
       123 Connor     11-APR-22

15 rows selected.

SQL> roll;
Rollback complete.

So what are my options here? One avenue of attack is a row level trigger. I can simply define a trigger that will force the value I want into the CREATED column no matter what an application may try to do


SQL> create or replace
  2  trigger trg before insert on t
  3  for each row
  4  begin
  5    :new.created := sysdate;
  6  end;
  7  /

Trigger created.

SQL> insert into t ( empno, ename, created) values (123,'Connor',sysdate+12);

1 row created.

SQL> select * from t;

     EMPNO ENAME      created
---------- ---------- ---------
      7369 SMITH      30-MAR-22
      7499 ALLEN      30-MAR-22
      7521 WARD       30-MAR-22
      7566 JONES      30-MAR-22
      7654 MARTIN     30-MAR-22
      7698 BLAKE      30-MAR-22
      7782 CLARK      30-MAR-22
      7788 SCOTT      30-MAR-22
      7839 KING       30-MAR-22
      7844 TURNER     30-MAR-22
      7876 ADAMS      30-MAR-22
      7900 JAMES      30-MAR-22
      7902 FORD       30-MAR-22
      7934 MILLER     30-MAR-22
       123 Connor     30-MAR-22

15 rows selected.

SQL> roll;
Rollback complete.

Whilst this works for probably 99% of applications out there, introducing a trigger now means I might run into scalability issues at the extreme end of performance. Also it would be nice to have something declarative or at least close to being declarative.

Perhaps making the column invisible will help? I’ll drop the trigger and make the column invisible to the application.


SQL> drop trigger trg;

Trigger dropped.

SQL>
SQL> alter table t modify created invisible;

Table altered.

Unfortunately the visibility of a column does not really change its accessibility. If anyone knows that the CREATED column is present, then they can still override the default value. A “SELECT *” won’t show the column, but I can still do whatever INSERT I want if I know the column name.


SQL> insert into t ( empno, ename, created) values (123,'Connor',sysdate+12);

1 row created.

SQL> select * from t;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
       123 Connor

15 rows selected.

SQL> select empno, ename, created from t;

     EMPNO ENAME      CREATED
---------- ---------- ---------
      7369 SMITH      30-MAR-22
      7499 ALLEN      30-MAR-22
      7521 WARD       30-MAR-22
      7566 JONES      30-MAR-22
      7654 MARTIN     30-MAR-22
      7698 BLAKE      30-MAR-22
      7782 CLARK      30-MAR-22
      7788 SCOTT      30-MAR-22
      7839 KING       30-MAR-22
      7844 TURNER     30-MAR-22
      7876 ADAMS      30-MAR-22
      7900 JAMES      30-MAR-22
      7902 FORD       30-MAR-22
      7934 MILLER     30-MAR-22
       123 Connor     11-APR-22

15 rows selected.

SQL> roll;
Rollback complete.

But as long as the column is never “publicised” then this might be a potential strategy, but that strikes me as a risky option because you’d probably never know if anyone had touched it with an illegal value.

Perhaps a CHECK constraint.can be used? I’ll set my column back to visible and see if I can add a constraint to make sure that column is always SYSDATE.


SQL> alter table t modify created visible;

Table altered.

SQL>
SQL> alter table t add constraint chk check (  created = sysdate);
alter table t add constraint chk check (  created = sysdate)
                                                *
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint

Unfortunately CHECK constraints do not work this way. Often people think the constraint is in effect a piece of stored SQL that will be applied only at INSERT and UPDATE time, but constraints also have to be able to check the existing data, and thus SYSDATE does not make sense in a constraint definition because it always changes. Your data might be valid at the point of entry, but of course, just a moment later its impossible to say whether it is valid because SYSDATE has moved on. However, if it was the case that your DEFAULT clause was for a static value, a CHECK constraint is probably an option for you.

After a bit of a think about how this could be done, here’s a (nearly) declarative option for you to explore. I’ll use some of the facilities in Virtual Private Database to block people from referencing the CREATED column in their INSERT statements. We can take advantage of the SEC_RELEVANT_COLS setting which lets us nominate which columns need to be secured from others. I’ll create a policy that blocks all rows from referencing the CREATED column in an INSERT statement.


SQL> grant insert,update,delete,select on t to scott;

Grant succeeded.

SQL> create or replace
  2  function no_rows_allowed (own in varchar2, obj in varchar2)
  3  return varchar2 as
  4  begin
  5    return '1=0';
  6  end;
  7  /

Function created.

SQL> grant execute on no_rows_allowed to public;

Grant succeeded.

SQL>
SQL> begin
  2    dbms_rls.add_policy (object_schema     => user,
  3                         object_name       => 'T',
  4                         policy_name       => 'hard_default',
  5                         function_schema   => user,
  6                         policy_function   => 'no_rows_allowed',
  7                         sec_relevant_cols => 'created',
  8                         statement_types   => 'INSERT',
  9                         update_check      => true
 10                         );
 11  end;
 12  /

PL/SQL procedure successfully completed.

Now when I connect as SCOTT, I can still see the CREATED column with no problems


SQL> conn  scott/tiger@db19_pdb1
Connected.
SQL> select * from mcdonac.t;

     EMPNO ENAME      CREATED
---------- ---------- ---------
      7369 SMITH      30-MAR-22
      7499 ALLEN      30-MAR-22
      7521 WARD       30-MAR-22
      7566 JONES      30-MAR-22
      7654 MARTIN     30-MAR-22
      7698 BLAKE      30-MAR-22
      7782 CLARK      30-MAR-22
      7788 SCOTT      30-MAR-22
      7839 KING       30-MAR-22
      7844 TURNER     30-MAR-22
      7876 ADAMS      30-MAR-22
      7900 JAMES      30-MAR-22
      7902 FORD       30-MAR-22
      7934 MILLER     30-MAR-22

14 rows selected.

And similarly, I’m allowed to run an INSERT statement as long as it does not refernce the secured column


SQL> insert into mcdonac.t (empno, ename) values (123,'Connor');

1 row created.

But if I try to mess with CREATED then I’m blocked and cannot do it.


SQL> insert into mcdonac.t (empno, ename,created) values (123,'Connor',sysdate+1);
insert into mcdonac.t (empno, ename,created) values (123,'Connor',sysdate+1)
                    *
ERROR at line 1:
ORA-28115: policy with check option violation

The downside here is that if you do the “right” thing and specify SYSDATE, then you’ll still get an error. If you’re wondering why I didn’t include an UPDATE in the policy definition (and you certainly could), recall that the UPDATE privilege has column level granularity, so rather than a policy I can control updates to the CREATED column by only grant UPDATE privileges to the other columns in the table.

Got any other ways you would implement this? Let me know in the comments.

TL;DR. If you’re looking to force a default value in all cases, perhaps Virtual Private Database is something you might explore.

4 comments

  1. alter table t add created_hidden_from_view date default on null sysdate;
    alter table t add created_virtual date generated always as ( created_hidden_from_view + 0 ) virtual; — +0 is intentional

    create view view_t
    as
    select empno, ename, created_virtual as created from t;

    — grant select, insert, update and delete on view_t ( disallow dml on t )
    — possibly create synonym t to view_t

    — granted, doing the ‘good thing’ still returns an error, now along the lines of “INSERT operation disallowed on virtual columns”

  2. Hi Connor,

    What happens if you define the policy function as follows:

    create or replace
    function no_rows_allowed (own in varchar2, obj in varchar2)
    return varchar2 as
    begin
    return ‘ trunc(created) = trunc(sysdate) ‘ ;
    end;
    /

    As by the Oracle documentation, the meaning of UPDATE_CHECK = TRUE is to ensure that an INSERTED
    or UPDATED row does satisfy the policy predicate ( aka, similar to WITH CHECK OPTION for a view ).

    This way the user will be able to insert the correct (default) value into the column,
    or, more precisely, any value in the same day as the current day.

    Please correct me if my understanding is not right, as, unfortunately, I can only use LiveSQL for my tests
    and DBMS_RLS is not available in that environment.

    Cheers & Best Regards,
    Iudith

  3. (This from Jonathan Lewis via email)

    Assuming all you want is the sysdate when the row is created to be visible but unchangeable – how about this: less “processing” overhead, probably, at a cost of a little extra space and redo.

    drop table t1;

    create table t1 (
    id number,
    created date default on null sysdate,
    hidden_created date default on null sysdate,
    constraint t1_dont_try_messing_with_the_date check (created = hidden_created)
    )
    /

    create or replace view v1
    as
    select id, created
    from t1
    /

    grant select, insert, update, delete on v1 to u1;
    /

    connect u1/u1
    create synonym v1 for test_user.v1;

    insert into v1 values(1,sysdate);
    update v1 set created = sysdate -1;

    ————————-

    1 row created.

    SQL> update v1 set created = sysdate -1
    *
    ERROR at line 1:
    ORA-02290: check constraint (TEST_USER.T1_DONT_TRY_MESSING_WITH_THE_DATE) violated

    It’s just made me wonder if there’s a short-circuit path in the code that means a constraint is not evaluated if the column doesn’t change. Could test it with a deterministic expensive function, I suppose.

    Regards
    Jonathan Lewis

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.