Database Reference
In-Depth Information
Creating multiple-field indexes in Access involves a slightly different process than creating single-field
indexes. To create multiple-field indexes, click the Indexes button in the Show/Hide group on the Table Tools
Design tab, enter a name for the index, and then select the fields that make up the index key. If data for any
of the fields is to appear in descending order, change the corresponding entry in the Sort Order column to
Descending, as shown in Figure 4-14.
130
First index key
Descending sort
order selected
Name of
multiple-field
index
Second index key
FIGURE 4-14
Creating a multiple-field index in Access
The SQL command used to drop (delete) an index that is no longer necessary is DROP INDEX, which
consists of the words DROP INDEX followed by the name of the index to drop. To drop the RepBal index, for
example, the command is as follows:
DROP INDEX RepBal
;
In MySQL, the DROP INDEX command must include an ON clause that specifies the name of the table.
The corresponding command in MySQL is as follows:
DROP INDEX RepBal ON CUSTOMER
;
To delete an index in Access, select the index in the Indexes dialog box (see Figure 4-14), right-click it,
and then click Delete Rows on the shortcut menu.
SECURITY
Security is the prevention of unauthorized access to the database. Within an organization, the database
administrator determines the types of access various users can have to the database. Some users may be able
to retrieve and update anything in the database. Other users may be able to retrieve any data from the data-
base but not make any changes to it. Still other users may be able to access only a portion of the database.
For example, Bill Kaiser may be able to retrieve and update sales rep and customer data, but not be permit-
ted to retrieve data about parts and orders. Mary Smith may be able to retrieve part data and nothing else.
Kyung Park may be able to retrieve and update data on parts in the HW class but not in other classes.
After the database administrator has determined the access different users of the database will have, it is up
to the DBMS to enforce it. In particular, it is up to whatever security mechanism the DBMS provides. In SQL sys-
tems, there are two security mechanisms. You have already seen that views furnish a certain amount of security.
(When users are accessing the database through a view, they cannot access any data that is not included in the
view.) The main mechanism for providing access to a database, however, is the GRANT statement.
The basic idea of the GRANT statement is that different types of privileges can be granted to users and, if
necessary, later revoked. These privileges include such things as the right to select, insert, update, and delete
table data. You can revoke user privileges using the REVOKE statement. Following are examples of these two
statements.
