How to Make a Column Unmodifiable After Row Creation in Oracle

constraintddloracle

What is the most idiomatic way to make a column unmodifiable/non-updatable after its initial creation via INSERT in Oracle?

CREATE TABLE my_table (
  id           NUMBER       NOT NULL CONSTRAINT my_table_pk PRIMARY KEY,
  -- lots of other columns that are updatable
  crt_ts       TIMESTAMP(6) NOT NULL default CURRENT_TIMESTAMP, -- immutable?
  upd_ts       TIMESTAMP(6) NOT NULL
)

Is there a constraint or technique that can easily be applied to the crt_ts column that does not allow it to be modified on an UPDATE for a single user account.

I know I could hack the permissions to restrict writes to that table and that column and/or do some hacky triggers, updatable views, etc. Those solutions would not be maintainable because I have dozens of tables with columns that have this business logic requirement to be immutable and the database is already in production. I am not sure I might not be missing some low impact solution that is named something strange, because you know, Oracle.

Ideally I would like the crt_ts not to be INSERTable either and just force the default but that might be a stretch.

Best Answer

GRANTS

Oracle has column level GRANT capabilities.

Therefore, the answer you seek: Don't GRANT the update privilege on that column of that table to that user.

grant insert,select,delete on my_table to some_role;
grant update (updatable_column, upd_ts) on my_table to some_role;
grant some_role to single_user;

VPD/FGAC

Note: Virtual Private Database (VPD) and Fine Grain Access Control (FGAC) require Enterprise Edition (EE); 18c XE also supports VPD/FGAC

VPD allows you to add a WHERE clause based specific conditions. In this example, the function always_false generates the appropriate conditions.

create function always_false return varchar2 deterministic
as
begin
  if sys_context('USERENV', 'SESSION_USER' ) = 'SINGLE_USER'
  then
    return '1=0'
  end if;

  return null; -- does not apply a rule
end;
/

begin
  dbms_rls.add_policy (
     object_name       => 'MY_TABLE'
    ,policy_name       => 'NO_CRT_TS'
    ,policy_function   => 'ALWAYS_FALSE'
    ,statement_types   => 'UPDATE'
    ,enable            => 'TRUE'
    ,static_policy     => 'TRUE'
    ,sec_relevent_cols => 'CRT_TS'
  );
end;
/

note: I haven't tested this code

TRIGGER

I wouldn't use TRIGGER to enforce this because triggers can be by-passed.

Related Question