Constraints often are used to maintain integrity, be it referential, data integrity (also called entity integrity), or domain integrity (discussed in greater detail in Chapter 4). Here we will discuss the use of constraints from a security point of view only.
While all CONSTRAINT examples are given as ALTER TABLE, they also may be created in the CREATE TABLE syntax.
Domain integrity constraints, like the CHECK constraint or the DEFAULT constraint, validate data for correct format and content. For example, in the ACME database the CHK_ADDR_TYPE constraint that follows validates an address as being either a 'SHIPPING' or 'BILLING' type of address:
ALTER TABLE address ADD CONSTRAINT chk_addr_type CHECK (addr_type_s = 'SHIPPING' OR addr_type_s = 'BILLING')
Now any attempt — legitimate or otherwise — to enter invalid data, for example, 'HOME' address type, would generate an error; your data is protected against inconsistency. Validating data before it is committed to the database table is a very efficient security layer.
If the constraint you've trying to add is already in the database, the above example would generate an error. To run the example you might need to drop the constraint.
Another mechanism for enforcing domain integrity is the DEFAULT constraint. When specified, this constraint guarantees that if any data was omitted from the query, a default value will be used instead of blank space or NULL. In the following example (valid for Microsoft SQL Server only, as Oracle 9i and IBM DB2 UDB do not consider it a constraint; see Chapter 4 for more information on constraints), the DEFAULT constraint DF_CUSTOMER_CUST_S assigns the default value of 'Y' in every insert statement that does not supply this value:
ALTER TABLE customer ADD CONSTRAINT df_customer_cust_s DEFAULT ('Y') FOR cust_status_s
It is open for discussion whether DEFAULT represents a security breach or a security enforcement mechanism. On the one hand, it prevents data inconsistency, which is a good thing; on the other, it requires less precision on the data entry end — by preventing omission/sloppiness errors — and less effort for a malicious intruder to insert data.
Entity integrity, which essentially refers to a row of data, is maintained with indices and constraints like the PRIMARY KEY constraint or the UNIQUE constraint. It effectively prevents users from entering duplicate values. Putting these constraints on the Social Security Number (SSN) column would prevent miscreants from applying for a job using stolen SSN cards.
Referential integrity maintains healthy relationships between the tables for which it is declared. It mandates that there cannot be a record in the child table if a corresponding record in the parent table is missing, or that a record in the parent table cannot be deleted as long as it has a corresponding record in the child table. Here is an example using the table ADDRESS from the ACME database. The FOREIGN KEY constraint mandates that there will be no record in the ADDRESS database unless it refers to a valid customer in the CUSTOMER table; the field ADDR_CUSTID_FN from the ADDRESS table is referencing the primary key field CUST_ID_N of the CUSTOMER table:
ALTER TABLE address ADD CONSTRAINT fk_addr_cust FOREIGN KEY(addr_custid_fn) REFERENCES customer(cust_id_n)
Constraints by themselves cater to a very narrow segment of database security and should be considered supplemental to the more robust mechanisms provided by the overall RDBMS security.