SQL Write protection of tables

Write protection of tables

No votes yet.
Please wait...
Werbung / Advertisements

Write protection can be applied to tables. This prevents DDL (Data Definition Language) and DML (Data Manipulation Language) changes during maintenance mode. The statement:

ALTER TABLE tablename READ ONLY; -- nur mehr lesbar ALTER TABLE tablename READ WRITE; -- Read and write possible

VIEWs can be equipped with a WITH READ ONLY option. This ensures that no DML operations can be performed.

Werbung / Advertisements
CREATE VIEW empView AS SELECT empno, empname, deptid FROM emp WHERE deptid = 10 WITH READ ONLY;

Alternatively, a CHECK option can be added to keep DML operations in the VIEW domain.

Werbung / Advertisements
CREATE VIEW empView AS SELECT empno, empname, deptid FROM emp WHERE deptid = 10 WITH CHECK OPTION CONSTRAINT empView_ck;

This ensures that only values relating to Department 10 can be inserted, changed or deleted. Inserting an employee with another deptId is not possible.

No votes yet.
Please wait...
Werbung / Advertisements