Oracle 1Z0-071: WITH CHECK OPTION Violation and Sequence Gaps Under CACHE

WITH CHECK OPTION Violation and Sequence Gaps Under CACHE

SALES_APP owns CUSTOMERS(customer_id primary key, name, status VARCHAR2(8) with CHECK status IN ('ACTIVE','INACTIVE','HOLD'), credit_limit NUMBER) and the view:

CREATE OR REPLACE VIEW v_active_customers AS SELECT customer_id, name, status, credit_limit FROM customers WHERE status = 'ACTIVE' WITH CHECK OPTION CONSTRAINT v_active_chk;

A support developer runs UPDATE v_active_customers SET status='INACTIVE' WHERE customer_id=1042 and receives ORA-01402. Separately, order loading uses SEQ_ORDER_ID, created with CACHE 20 ORDER. The instance bounced three times during month-end, and order IDs jumped from 884,120 to 884,500. An auditor flags the gap as evidence of deleted orders. The team also wants to expose only v_active_customers to a new read-only reporting account.

1. Explain ORA-01402, show how customer 1042 can legitimately be deactivated, and state whether WITH CHECK OPTION is a security control.

Read the worked answer

What ORA-01402 means. WITH CHECK OPTION tells Oracle that any row inserted or updated through the view must still satisfy the view's WHERE clause. The UPDATE tries to set status to 'INACTIVE'; the resulting row would have status <> 'ACTIVE' and would be invisible through the view, so Oracle rejects the statement with ORA-01402 (view WITH CHECK OPTION where-clause violation). Nothing is wrong with the data or the privileges - the view is enforcing its own predicate.

Legitimate deactivation.

UPDATE sales_app.customers
SET    status = 'INACTIVE'
WHERE  customer_id = 1042;

If support staff must not hold base-table DML, expose a procedure owned by SALES_APP and grant only EXECUTE on it:

CREATE OR REPLACE PROCEDURE sales_app.deactivate_customer (p_id NUMBER)
AUTHID DEFINER IS
BEGIN
  UPDATE customers SET status = 'INACTIVE' WHERE customer_id = p_id;
END;
/
GRANT EXECUTE ON sales_app.deactivate_customer TO support_role;

Is it a security control? No. It is a data-integrity constraint on one access path. Anyone with UPDATE on CUSTOMERS can bypass it, and it does not apply to DELETE through the view at all. Also note the practical consequence: updates that keep status as 'ACTIVE' (for example credit_limit) succeed through the view, and inserts through the view must supply status 'ACTIVE' or they fail.

2. Explain the sequence gap, evaluate whether CACHE 1 or NOORDER would fix it, and advise the auditor.

Read the worked answer

Where the gaps come from. CACHE 20 makes each instance pre-allocate 20 values in memory and hand them out without touching the data dictionary. On instance shutdown, the unused values in that cached block are discarded permanently. Three bounces can therefore waste up to 60 values, and more if several sessions had populated cache blocks. Rollbacks and failed transactions also consume NEXTVALs: sequence numbers are generated outside transaction control and are never rolled back.

What would not fix it.

CACHE 1 narrows the window but still leaves gaps; a crash between NEXTVAL and COMMIT still loses numbers, and it adds dictionary contention.
NOORDER is the default and is unrelated to gaps. It only says values need not be generated in request order across RAC instances.
ORDER guarantees increasing values in request order (useful when a sequence supplies a timestamp-like ordering) at a synchronisation cost. It does not guarantee contiguity.
A trigger assigning MAX(id)+1 is worse: it serialises inserts and breaks under concurrent sessions.

If gapless numbering is truly required by regulation, the number cannot come from a sequence. Use a one-row control table updated in the same transaction as the order insert (SELECT ... FOR UPDATE, increment, commit). That serialises the allocating transaction, which is a deliberate throughput trade-off.

Answer to the auditor. Gaps are not evidence of deleted orders. Prove deletions with an audit trail (AUDIT DELETE ON, or a deleted_at/history table) and timestamps, not with sequence continuity. Sequence values show allocation order, not survival of rows.

3. Describe how to expose the view to the reporting account safely and list the privilege pitfalls to check.

Read the worked answer

Grant on the view, not on the table.

GRANT SELECT ON sales_app.v_active_customers TO rpt_user;

Because the view is definer's rights by default, REPORT_USER needs no privilege on CUSTOMERS at all; the owner's privileges resolve the base table. The reporting user sees only ACTIVE customers and cannot read INACTIVE or HOLD rows.

Pitfalls to check.

If SELECT on CUSTOMERS is also granted, the view's filter is irrelevant because the user can query the base table directly. Check for PUBLIC grants and for SELECT ANY TABLE before claiming the data is protected.
The view as written is key-preserving and updatable, so grant only SELECT. If read-only intent should be enforced by the database rather than by the grant, add WITH READ ONLY to the view definition.
WITH CHECK OPTION and WITH READ ONLY are orthogonal: the first constrains which rows may be written, the second blocks writes entirely. They can be combined.
Object privileges granted through a role work for interactive SQL but are not enabled inside definer's-rights PL/SQL owned by another schema, so do not rely on a role to satisfy a procedure that reads the view.
For long-running extracts, remember read consistency: the report sees one snapshot as of query start, and a very long report on a busy table can hit ORA-01555. That is a tuning concern, not a privilege one.