Understanding Data Control Language (DCL)

While DML lets you make changes to the data in your database, data control language (DCL) protects your data from harm. If you correctly use the tools that DCL provides, you can keep unauthorized users from viewing or changing your data, and prevent many of the problems that can corrupt your database. There are four DCL commands:

• COMMIT. Tells the DBMS to make permanent changes made to temporary copies of the data by updating the permanent database tables to match the updated, temporary copies. The COMMIT statement is discussed in Tip 129, "Understanding When to Use the COMMIT Statement."

• ROLLBACK. Tells the DBMS to undo any changes made to the DBMS after the most recent commit. The ROLLBACK statement is discussed in Tip 130, "Using the ROLLBACK Statement to UNDO Changes Made to Database Objects."

• GRANT. Gives specific SQL statement access on individual database objects to a user or group of users. The GRANT statement is discussed in Tip 145, "Using the GRANT Statement WITH GRANT OPTION to Allow Users to Give Database Object Access to Other Users."

• REVOKE. Removes specific SQL statement access previously granted on individual database objects from a user or group of users. The REVOKE statement is discussed in Tip 147, "Using the REVOKE Statement with the CASCADE Option to Remove Privileges."

A database is most vulnerable to damage while someone is changing it. If the software or hardware fails in the middle of making a change, the data will be left in an indeterminate state-part of what you wanted done will be completed, and part will not. Suppose for example that you told SQL to move money from one bank account to another. If the computer locks up while it is doing the transfer, you won't know if the DBMS debited the one account or if it got around to crediting the second account.

By encapsulating the debit and credit UPDATE statements within a transaction, you can make sure that the DBMS executes both statements successfully before executing the COMMIT command to write the updated balances permanently to the database.

If the DBMS does not successfully complete all of the statements in a transaction, you issue a ROLLBACK command. The DBMS will back out any changes made to the database since the last COMMIT command was executed. In the case of our failed money transfer example, the DBMS would back out any and all updates so that the balances in the accounts would be as they were before the DBMS attempted to move money from one account to another-it would be as if the transaction never happened.

Aside from data corruption caused by hardware or software failures, you also have to protect your data from the users themselves. Some people should have no access to the database. Others should be able to see some but not all of the data, while not being able to update any of it. Still others should have access to see and update a portion of the data. Thus, you must be able to approach database security on a user-by-user and group-by-group basis. DCL gives you the GRANT and REVOKE commands to use in assigning access privileges to individual users and groups of users. The DCL commands used to control security are:

• GRANT SELECT. Lets the user or group see the data in a table or view. Tip 149 discusses the GRANT and REVOKE SELECT statements.

• REVOKE SELECT. Prevents the user or group from seeing data in a table or view. Tip 149 discusses the GRANT and REVOKE SELECT statements.

• GRANT INSERT. Lets the user or group to add row(s) to a table or view. Tip 151 discusses the GRANT INSERT statement

• REVOKE INSERT. Prevents users or groups from adding row(s) to a table or view. Tip 151 discusses the REVOKE INSERT statement.

• GRANT UPDATE. Lets the user or group of users change the values in the columns of a table or view. Tip 152 discusses the GRANT UPDATE statement.

• REVOKE UPDATE. Prevents the user or group of users from changing the values in the columns of a table or view. Tip 152 discusses the REVOKE UPDATE statement.

• GRANT DELETE. Allows a user or group of users to delete row(s) in table or view

• REVOKE DELETE. Prevents a user or group of users from deleting row(s) in a table or view.

• GRANT REFERENCES. Lets a user or group of users to define a FOREIGN KEY reference to the table. Tip 153 discusses the GRANT REFERENCES statement.

• REVOKE REFERENCES REVOKE REFERENCES. Prevents the user or group of users from defining a FOREIGN KEY reference to the table. Tip 153 discusses the REVOKE REFERENCES statement.

Thus, DCL contains commands you can use to control who can access your database and what those users can do once they log in. Moreover, the DCL gives you control over when the DBMS makes permanent (COMMITs) changes to your database and lets you undo (ROLLBACK) changes not yet committed.