SQL Series...9

Data Control Language

The Data Control Language (DCL) has four commands: COMMIT, ROLLBACK, GRANT, and REVOKE. These commands protect the database from harm, either accidental or intentional.

Transactions

Your database is most vulnerable to damage while you or someone else is changing it. Even in a single-user system, making a change can be dangerous to a database. If a software or hardware failure occurs while the change is in progress, a database may be left in an indeterminate state between where it was before the change started and where it would be if it were able to finish.

SQL protects your database by restricting operations that can change the database so that these operations occur only within transactions. During a transaction, SQL records every operation on the data in a log file. If anything interrupts the transaction before the COMMIT statement ends the transaction, you can restore the system to its original state by issuing a ROLLBACK state-ment. The ROLLBACK processes the transaction log in reverse, undoing all the actions that took place in the transaction. After you roll back the database to its state before the transaction began, you can clear up whatever caused the problem and then attempt the transaction again.

As long as a hardware or software problem can possibly occur, your database is susceptible to damage. To minimize the chance of damage, today’s DBMSs close the window of vulnerability as much as possible by performing all oper-ations that affect the database within a transaction and then committing all these operations at one time. Modern database management systems use log-ging in conjunction with transactions to guarantee that hardware, software, or operational problems will not damage data. After a transaction has been committed, it’s safe from all but the most catastrophic of system failures. Prior to commitment, incomplete transactions can be rolled back to their starting point and applied again, after the problem is corrected.

In a multi-user system, database corruption or incorrect results are possible even if no hardware or software failures occur. Interactions between two or more users who access the same table at the same time can cause serious problems. By restricting changes so that they occur only within transactions, SQL addresses these problems as well.

By putting all operations that affect the database into transactions, you can isolate the actions of one user from those of another user. Such isolation is critical if you want to make sure that the results you obtain from the data-base are accurate.

You may wonder how the interaction of two users can produce inaccurate results. For example, say that Donna reads a record in a database table. An instant later (more or less) David changes the value of a numeric field in that record. Now Donna writes a value back into that field, based on the value that she read initially. Because Donna is unaware of David’s change, the value after Donna’s write operation is incorrect.

Another problem can result if Donna writes to a record and then David reads that record. If Donna rolls back her transaction, David is unaware of the roll-back and bases his actions on the value that he read, which doesn’t reflect the value that’s in the database after the rollback. It makes for good comedy, but lousy data management.

Users and privileges

Another major threat to data integrity is the users themselves. Some people should have no access to the data. Others should have only restricted access to some of the data but no access to the rest. Some should have unlimited access to everything. You need a system for classifying users and for assign-ing access privileges to the users in different categories.

The creator of a schema specifies who is considered its owner. As the owner of a schema, you can grant access privileges to the users you specify. Any privileges that you don’t explicitly grant are withheld. You can also revoke privileges that you’ve already granted. A user must pass an authentication procedure to prove his identity before he can access the files you authorize him to use. That procedure is implementation-dependent.

SQL gives you the capability to protect the following database objects:

Tables Columns Views Domains

Character sets Collations

Translations

SQL:2003 supports several different kinds of protection: seeing, adding, modi-fying, deleting, referencing, and using databases, as well as protections associ-ated with the execution of external routines.

You permit access by using the GRANT statement and remove access by using the REVOKE statement. By controlling the use of the SELECT command, the DCL controls who can see a database object such as a table, column, or view. Controlling the INSERT command determines who can add new rows in a table. Restricting the use of the UPDATE command to authorized users con-trols who can modify table rows, and restricting the DELETE command con-trols who can delete table rows.

If one table in a database contains as a foreign key a column that is a primary key in another table in the database, you can add a constraint to the first table so that it references the second table. When one table references another, the owner of the first table may be able to deduce information about the contents of the second. As the owner of the second table, you may want to prevent such

snooping. The GRANT REFERENCES statement gives you that power. The fol-lowing section discusses the problem of a renegade reference and how the GRANT REFERENCES statement prevents it. By using the GRANT USAGE state-ment, you can control who can use or even see the contents of a domain, character set, collation, or translation.

Table summarizes the SQL statements that you use to grant and revoke privileges.

Table	Types of Protection
Protection Operation	Statement
Enable to see a table	GRANT SELECT
Prevent from seeing a table	REVOKE SELECT
Enable to add rows to a table	GRANT INSERT
Prevent from adding rows to a table	REVOKE INSERT
Enable to change data in table rows	GRANT UPDATE
Prevent from changing data	REVOKE UPDATE
in table rows
Enable to delete table rows	GRANT DELETE
Prevent from deleting table rows	REVOKE DELETE
Enable to reference a table	GRANT REFERENCES
Prevent from referencing a table	REVOKE REFERENCES
Enable to use a domain,	GRANT USAGE ON DOMAIN, GRANT
character translation,	USAGE ON CHARACTER SET, GRANT
or set collation	USAGE ON COLLATION, GRANT USAGE
	ON TRANSLATION
Prevent the use of a domain,	REVOKE USAGE ON DOMAIN, REVOKE
character set, collation,	USAGE ON CHARACTER SET, REVOKE
or translation	USAGE ON COLLATION, REVOKE
	USAGE ON TRANSLATION

You can give different levels of access to different people, depending on their needs. The following commands offer a few examples of this capability:

GRANT SELECT

ON CUSTOMER

TO SALES_MANAGER;

The preceding example enables one person, the sales manager, to see the CUSTOMER table.

The following example enables anyone with access to the system to see the retail price list:

GRANT SELECT

ON RETAIL_PRICE_LIST

TO PUBLIC;

The following example enables the sales manager to modify the retail price list.

She can change the contents of existing rows, but she can’t add or delete rows:

GRANT UPDATE

ON RETAIL_PRICE_LIST

TO SALES_MANAGER;

This following example enables the sales manager to add new rows to the retail price list:

GRANT INSERT

ON RETAIL_PRICE_LIST

TO SALES_MANAGER;

Now, thanks to this last example, the sales manager can delete unwanted rows from the table, too:

GRANT DELETE

ON RETAIL_PRICE_LIST

TO SALES MANAGER;

Referential integrity constraints can jeopardize your data

You may think that if you can control the seeing, creating, modifying, and deleting functions on a table, you’re well protected. Against most threats, you are. A knowledgeable hacker, however, can still ransack the house by using an indirect method.

A correctly designed relational database has referential integrity, which means that the data in one table in the database is consistent with the data in all the other tables. To ensure referential integrity, database designers apply con-straints to tables that restrict what someone can enter into the tables. If you have a database with referential integrity constraints, a user can possibly create a new table that uses a column in a confidential table as a foreign key. That column then serves as a link through which someone can possibly steal confidential information.

Say, for example, that you’re a famous Wall Street stock analyst. Many people believe in the accuracy of your stock picks, so whenever you recommend a stock to your subscribers, many people buy that stock, and its value increases. You keep your analysis in a database, which contains a table named FOUR_ STAR. Your top recommendations for your next newsletter are in that table. Naturally, you restrict access to FOUR_STAR so that word doesn’t leak out to the investing public before your paying subscribers receive the newsletter.

You’re still vulnerable, however, if anyone other than yourself can create a new table that uses the stock name field of FOUR_STAR as a foreign key, as shown in the following command example:

CREATE TABLE HOT_STOCKS (

Stock CHARACTER (30) REFERENCES FOUR_STAR );

The hacker can now try to insert the name of every stock on the New York Stock Exchange, American Stock Exchange, and NASDAQ into the table. Those inserts that succeed tell the hacker which stocks match the stocks that you name in your confidential table. It doesn’t take long for the hacker to extract your entire list of stocks.

You can protect yourself from hacks such as the one in the preceding example by being very careful about entering statements similar to the following:

GRANT REFERENCES (Stock)

ON FOUR_STAR

TO SECRET_HACKER;

Avoid granting privileges to people who may abuse them. True, people don’t come with guarantees printed on their foreheads. But if you wouldn’t lend your new car to a person for a long trip, you probably shouldn’t grant him the REFERENCES privilege on an important table either.

The preceding example offers one good reason for maintaining careful con-trol of the REFERENCES privilege. The following list describes two other rea-sons for careful control of REFERENCES:

If the other person specifies a constraint in HOT STOCKS by using a RESTRICT option and you try to delete a row from your table, the DBMS tells you that you can’t, because doing so would violate a referential constraint.

If you want to use the DROP command to destroy your table, you find that you must get the other person to first drop his constraint (or his table).

The bottom line is that enabling another person to specify integrity con-straints on your table not only introduces a potential security breach, but also means that the other user sometimes gets in your way.

Delegating responsibility for security

To keep your system secure, you must severely restrict the access privileges you grant and the people to whom you grant these privileges. But people who can’t do their work because they lack access are likely to hassle you constantly. To preserve your sanity, you’ll probably need to delegate some of the responsibility for maintaining database security. SQL provides for such delegation through the WITH GRANT OPTION clause. Consider the following example:

GRANT UPDATE

ON RETAIL_PRICE_LIST

TO SALES_MANAGER WITH GRANT OPTION

This statement is similar to the previous GRANT UPDATE example in that the statement enables the sales manager to update the retail price list. The state-ment also gives her the right to grant the update privilege to anyone she wants. If you use this form of the GRANT statement, you must not only trust the grantee to use the privilege wisely, but also trust her to choose wisely in granting the privilege to others.

The ultimate in trust, and therefore the ultimate in vulnerability, is to execute a statement such as the following:

GRANT ALL PRIVILEGES

ON FOUR_STAR

TO BENEDICT_ARNOLD WITH GRANT OPTION;

Be extremely careful about using statements such as this one.