لطفا منتظر باشید ...
Using Constraints for Security
Constraints often are used to maintainintegrity, 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.
| Note | 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.
| Note | 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
| Note | 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.
