Chapter 21 Security and Authorization (Database Management Systems)

21 SECURITY AND AUTHORIZATION ☛ What are the main security considerations in designing a database application? ☛ What mechanisms does a DBMS provide to control a user’s access to data? ☛ W...

21 SECURITY AND AUTHORIZATION ☛ What are the main security considerations in designing a database application? ☛ What mechanisms does a DBMS provide to control a user’s access to data? ☛ What is discretionary access control and how is it supported in SQL? ☛ What are the weaknesses of discretionary access control? How are these addressed in mandatory access control? ☛ What are covert channels and how do they compromise mandatory access control? ☛ What must the DBA do to ensure security? ☛ What is the added security threat when a database is accessed re- motely? ☛ What is the role of encryption in ensuring secure access? How is it used for certifying servers and creating digital signatures? ➽ Key concepts: security, integrity, availability; discretionary access control, privileges, GRANT, REVOKE; mandatory access control, objects, subjects, security classes, multilevel tables, polyinstantiation; covert channels, DoD security levels; statistical databases, inferring secure information; authentication for remote access, securing servers, digital signatures; encyption, public-key encryption. I know that’s a secret, for it’s whispered everywhere. —William Congreve 692 Security and Authorization 693 The data stored in a DBMS is often vital to the business interests of the or- ganization and is regarded as a corporate asset. In addition to protecting the intrinsic value of the data, corporations must consider ways to ensure privacy and control access to data that must not be revealed to certain groups of users for various reasons. In this chapter, we discuss the concepts underlying access control and secu- rity in a DBMS. After introducing database security issues in Section 21.1, we consider two distinct approaches, called discretionary and mandatory, to spec- ifying and managing access controls. An access control mechanism is a way to control the data accessible by a given user. After introducing access controls in Section 21.2, we cover discretionary access control, which is supported in SQL, in Section 21.3. We briefly cover mandatory access control, which is not supported in SQL, in Section 21.4. In Section 21.6, we discuss some additional aspects of database security, such as security in a statistical database and the role of the database administrator. We then consider some of the unique challenges in supporting secure access to a DBMS over the Internet, which is a central problem in e-commerce and other Internet database applications, in Section 21.5. We conclude this chapter with a discussion of security aspects of the Barns and Nobble case study in Section 21.7. 21.1 INTRODUCTION TO DATABASE SECURITY There are three main objectives when designing a secure database application: 1. Secrecy: Information should not be disclosed to unauthorized users. For example, a student should not be allowed to examine other students’ grades. 2. Integrity: Only authorized users should be allowed to modify data. For example, students may be allowed to see their grades, yet not allowed (obviously) to modify them. 3. Availability: Authorized users should not be denied access. For example, an instructor who wishes to change a grade should be allowed to do so. To achieve these objectives, a clear and consistent security policy should be developed to describe what security measures must be enforced. In particular, we must determine what part of the data is to be protected and which users get access to which portions of the data. Next, the security mechanisms of the underlying DBMS and operating system, as well as external mechanisms, 694 Chapter 21 such as securing access to buildings, must be utilized to enforce the policy. We emphasize that security measures must be taken at several levels. Security leaks in the OS or network connections can circumvent database secu- rity mechanisms. For example, such leaks could allow an intruder to log on as the database administrator, with all the attendant DBMS access rights. Human factors are another source of security leaks. For example, a user may choose a password that is easy to guess, or a user who is authorized to see sensitive data may misuse it. Such errors account for a large percentage of security breaches. We do not discuss these aspects of security despite their importance because they are not specific to database management systems; our main focus is on database access control mechanisms to support a security policy. We observe that views are a valuable tool in enforcing security policies. The view mechanism can be used to create a ‘window’ on a collection of data that is appropriate for some group of users. Views allow us to limit access to sensitive data by providing access to a restricted version (defined through a view) of that data, rather than to the data itself. We use the following schemas in our examples: Sailors(sid: integer, sname: string, rating: integer, age: real) Boats(bid: integer, bname: string, color: string) Reserves(sid: integer, bid: integer, day: dates) Increasingly, as database systems become the backbone of e-commerce appli- cations requests originate over the Internet. This makes it important to be able to authenticate a user to the database system. After all, enforcing a security policy that allows user Sam to read a table and Elmer to write the table is not of much use if Sam can masquerade as Elmer. Conversely, we must be able to assure users that they are communicating with a legitimate system (e.g., the real Amazon.com server, and not a spurious application intended to steal sensitive information such as a credit card number). While the details of authentication are outside the scope of our coverage, we discuss the role of authentication and the basic ideas involved in Section 21.5, after covering database access control mechanisms. 21.2 ACCESS CONTROL A database for an enterprise contains a great deal of information and usually has several groups of users. Most users need to access only a small part of the database to carry out their tasks. Allowing users unrestricted access to all the Security and Authorization 695 data can be undesirable, and a DBMS should provide mechanisms to control access to data. A DBMS offers two main approaches to access control. Discretionary access control is based on the concept of access rights, or privileges, and mecha- nisms for giving users such privileges. A privilege allows a user to access some data object in a certain manner (e.g., to read or modify). A user who creates a database object such as a table or a view automatically gets all applicable privileges on that object. The DBMS subsequently keeps track of how these privileges are granted to other users, and possibly revoked, and ensures that at all times only users with the necessary privileges can access an object. SQL sup- ports discretionary access control through the GRANT and REVOKE commands. The GRANT command gives privileges to users, and the REVOKE command takes away privileges. We discuss discretionary access control in Section 21.3. Discretionary access control mechanisms, while generally effective, have certain weaknesses. In particular, a devious unauthorized user can trick an authorized user into disclosing sensitive data. Mandatory access control is based on systemwide policies that cannot be changed by individual users. In this ap- proach each database object is assigned a security class, each user is assigned clearance for a security class, and rules are imposed on reading and writing of database objects by users. The DBMS determines whether a given user can read or write a given object based on certain rules that involve the security level of the object and the clearance of the user. These rules seek to ensure that sensitive data can never be ‘passed on’ to a user without the necessary clearance. The SQL standard does not include any support for mandatory access control. We discuss mandatory access control in Section 21.4. 21.3 DISCRETIONARY ACCESS CONTROL SQL supports discretionary access control through the GRANT and REVOKE com- mands. The GRANT command gives users privileges to base tables and views. The syntax of this command is as follows: GRANT privileges ON object TO users [ WITH GRANT OPTION ] For our purposes object is either a base table or a view. SQL recognizes certain other kinds of objects, but we do not discuss them. Several privileges can be specified, including these: SELECT: The right to access (read) all columns of the table specified as the object, including columns added later through ALTER TABLE commands. 696 Chapter 21 INSERT(column-name): The right to insert rows with (non-null or non- default) values in the named column of the table named as object. If this right is to be granted with respect to all columns, including columns that might be added later, we can simply use INSERT. The privileges UPDATE(column-name) and UPDATE are similar. DELETE: The right to delete rows from the table named as object. REFERENCES(column-name): The right to define foreign keys (in other ta- bles) that refer to the specified column of the table object. REFERENCES without a column name specified denotes this right with respect to all columns, including any that are added later. If a user has a privilege with the grant option, he or she can pass it to another user (with or without the grant option) by using the GRANT command. A user who creates a base table automatically has all applicable privileges on it, along with the right to grant these privileges to other users. A user who creates a view has precisely those privileges on the view that he or she has on every one of the views or base tables used to define the view. The user creating the view must have the SELECT privilege on each underlying table, of course, and so is always granted the SELECT privilege on the view. The creator of the view has the SELECT privilege with the grant option only if he or she has the SELECT privilege with the grant option on every underlying table. In addition, if the view is updatable and the user holds INSERT, DELETE, or UPDATE privileges (with or without the grant option) on the (single) underlying table, the user automatically gets the same privileges on the view. Only the owner of a schema can execute the data definition statements CREATE, ALTER, and DROP on that schema. The right to execute these statements cannot be granted or revoked. In conjunction with the GRANT and REVOKE commands, views are an important component of the security mechanisms provided by a relational DBMS. By defining views on the base tables, we can present needed information to a user while hiding other information that the user should not be given access to. For example, consider the following view definition: CREATE VIEW ActiveSailors (name, age, day) AS SELECT S.sname, S.age, R.day FROM Sailors S, Reserves R WHERE S.sid = R.sid AND S.rating > 6 A user who can access ActiveSailors but not Sailors or Reserves knows the names of sailors who have reservations but cannot find out the bids of boats reserved by a given sailor. Security and Authorization 697 Role-Based Authorization in SQL: Privileges are assigned to users (authorization IDs, to be precise) in SQL-92. In the real world, privileges are often associated with a user’s job or role within the organization. Many DBMSs have long supported the concept of a role and allowed privileges to be assigned to roles. Roles can then be granted to users and other roles. (Of courses, privileges can also be granted directly to users.) The SQL:1999 standard includes support for roles. Roles can be created and destroyed using the CREATE ROLE and DROP ROLE commands. Users can be granted roles (optionally, with the ability to pass the role on to others). The standard GRANT and REVOKE commands can assign privileges to (and revoke from) roles or authorization IDs. What is the benefit of including a feature that many systems already sup- port? This ensures that, over time, all vendors who comply with the stan- dard support this feature. Thus, users can use the feature without worrying about portability of their application across DBMSs. Privileges are assigned in SQL to authorization IDs, which can denote a sin- gle user or a group of users; a user must specify an authorization ID and, in many systems, a corresponding password before the DBMS accepts any com- mands from him or her. So, technically, Joe, Michael, and so on are authoriza- tion IDs rather than user names in the following examples. Suppose that user Joe has created the tables Boats, Reserves, and Sailors. Some examples of the GRANT command that Joe can now execute follow: GRANT INSERT, DELETE ON Reserves TO Yuppy WITH GRANT OPTION GRANT SELECT ON Reserves TO Michael GRANT SELECT ON Sailors TO Michael WITH GRANT OPTION GRANT UPDATE (rating) ON Sailors TO Leah GRANT REFERENCES (bid) ON Boats TO Bill Yuppy can insert or delete Reserves rows and authorize someone else to do the same. Michael can execute SELECT queries on Sailors and Reserves, and he can pass this privilege to others for Sailors but not for Reserves. With the SELECT privilege, Michael can create a view that accesses the Sailors and Reserves tables (for example, the ActiveSailors view), but he cannot grant SELECT on ActiveSailors to others. On the other hand, suppose that Michael creates the following view: CREATE VIEW YoungSailors (sid, age, rating) AS SELECT S.sid, S.age, S.rating 698 Chapter 21 FROM Sailors S WHERE S.age < 18 The only underlying table is Sailors, for which Michael has SELECT with the grant option. He therefore has SELECT with the grant option on YoungSailors and can pass on the SELECT privilege on YoungSailors to Eric and Guppy: GRANT SELECT ON YoungSailors TO Eric, Guppy Eric and Guppy can now execute SELECT queries on the view YoungSailors— note, however, that Eric and Guppy do not have the right to execute SELECT queries directly on the underlying Sailors table. Michael can also define constraints based on the information in the Sailors and Reserves tables. For example, Michael can define the following table, which has an associated table constraint: CREATE TABLE Sneaky ( maxrating INTEGER, CHECK ( maxrating >= ( SELECT MAX (S.rating ) FROM Sailors S ))) By repeatedly inserting rows with gradually increasing maxrating values into the Sneaky table until an insertion finally succeeds, Michael can find out the highest rating value in the Sailors table. This example illustrates why SQL requires the creator of a table constraint that refers to Sailors to possess the SELECT privilege on Sailors. Returning to the privileges granted by Joe, Leah can update only the rating column of Sailors rows. She can execute the following command, which sets all ratings to 8: UPDATE Sailors S SET S.rating = 8 However, she cannot execute the same command if the SET clause is changed to be SET S.age = 25, because she is not allowed to update the age field. A more subtle point is illustrated by the following command, which decrements the rating of all sailors: UPDATE Sailors S SET S.rating = S.rating−1 Leah cannot execute this command because it requires the SELECT privilege on the S.rating column and Leah does not have this privilege. Security and Authorization 699 Bill can refer to the bid column of Boats as a foreign key in another table. For example, Bill can create the Reserves table through the following command: CREATE TABLE Reserves ( sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (bid, day), FOREIGN KEY (sid) REFERENCES Sailors ), FOREIGN KEY (bid) REFERENCES Boats ) If Bill did not have the REFERENCES privilege on the bid column of Boats, he would not be able to execute this CREATE statement because the FOREIGN KEY clause requires this privilege. (A similar point holds with respect to the foreign key reference to Sailors.) Specifying just the INSERT privilege (similarly, REFERENCES and other privi- leges) in a GRANT command is not the same as specifying SELECT(column-name) for each column currently in the table. Consider the following command over the Sailors table, which has columns sid, sname, rating, and age: GRANT INSERT ON Sailors TO Michael Suppose that this command is executed and then a column is added to the Sailors table (by executing an ALTER TABLE command). Note that Michael has the INSERT privilege with respect to the newly added column. If we had executed the following GRANT command, instead of the previous one, Michael would not have the INSERT privilege on the new column: GRANT INSERT ON Sailors(sid), Sailors(sname), Sailors(rating), Sailors(age), TO Michael There is a complementary command to GRANT that allows the withdrawal of privileges. The syntax of the REVOKE command is as follows: REVOKE [ GRANT OPTION FOR ] privileges ON object FROM users { RESTRICT | CASCADE } The command can be used to revoke either a privilege or just the grant option on a privilege (by using the optional GRANT OPTION FOR clause). One of the two alternatives, RESTRICT or CASCADE, must be specified; we see what this choice means shortly. The intuition behind the GRANT command is clear: The creator of a base table or a view is given all the appropriate privileges with respect to it and is allowed 700 Chapter 21 to pass these privileges—including the right to pass along a privilege—to other users. The REVOKE command is, as expected, intended to achieve the reverse: A user who has granted a privilege to another user may change his or her mind and want to withdraw the granted privilege. The intuition behind exactly what effect a REVOKE command has is complicated by the fact that a user may be granted the same privilege multiple times, possibly by different users. When a user executes a REVOKE command with the CASCADE keyword, the effect is to withdraw the named privileges or grant option from all users who currently hold these privileges solely through a GRANT command that was previously executed by the same user who is now executing the REVOKE command. If these users received the privileges with the grant option and passed it along, those recipients in turn lose their privileges as a consequence of the REVOKE command, unless they received these privileges through an additional GRANT command. We illustrate the REVOKE command through several examples. First, consider what happens after the following sequence of commands, where Joe is the creator of Sailors. GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Art) REVOKE SELECT ON Sailors FROM Art CASCADE (executed by Joe) Art loses the SELECT privilege on Sailors, of course. Then Bob, who received this privilege from Art, and only Art, also loses this privilege. Bob’s privilege is said to be abandoned when the privilege from which it was derived (Art’s SELECT privilege with grant option, in this example) is revoked. When the CASCADE keyword is specified, all abandoned privileges are also revoked (pos- sibly causing privileges held by other users to become abandoned and thereby revoked recursively). If the RESTRICT keyword is specified in the REVOKE com- mand, the command is rejected if revoking the privileges just from the users specified in the command would result in other privileges becoming abandoned. Consider the following sequence, as another example: GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Art) REVOKE SELECT ON Sailors FROM Art CASCADE (executed by Joe) As before, Art loses the SELECT privilege on Sailors. But what about Bob? Bob received this privilege from Art, but he also received it independently Security and Authorization 701 (coincidentally, directly from Joe). So Bob retains this privilege. Consider a third example: GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) REVOKE SELECT ON Sailors FROM Art CASCADE (executed by Joe) Since Joe granted the privilege to Art twice and only revoked it once, does Art get to keep the privilege? As per the SQL standard, no. Even if Joe absentmindedly granted the same privilege to Art several times, he can revoke it with a single REVOKE command. It is possible to revoke just the grant option on a privilege: GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) REVOKE GRANT OPTION FOR SELECT ON Sailors FROM Art CASCADE (executed by Joe) This command would leave Art with the SELECT privilege on Sailors, but Art no longer has the grant option on this privilege and therefore cannot pass it on to other users. These examples bring out the intuition behind the REVOKE command, and they highlight the complex interaction between GRANT and REVOKE commands. When a GRANT is executed, a privilege descriptor is added to a table of such descriptors maintained by the DBMS. The privilege descriptor specifies the fol- lowing: the grantor of the privilege, the grantee who receives the privilege, the granted privilege (including the name of the object involved), and whether the grant option is included. When a user creates a table or view and ‘automati- cally’ gets certain privileges, a privilege descriptor with system as the grantor is entered into this table. The effect of a series of GRANT commands can be described in terms of an authorization graph in which the nodes are users—technically, they are au- thorization IDs—and the arcs indicate how privileges are passed. There is an arc from (the node for) user 1 to user 2 if user 1 executed a GRANT command giving a privilege to user 2; the arc is labeled with the descriptor for the GRANT command. A GRANT command has no effect if the same privileges have already been granted to the same grantee by the same grantor. The following sequence of commands illustrates the semantics of GRANT and REVOKE commands when there is a cycle in the authorization graph: GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Art) 702 Chapter 21 GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Bob) GRANT SELECT ON Sailors TO Cal WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Cal) REVOKE SELECT ON Sailors FROM Art CASCADE (executed by Joe) The authorization graph for this example is shown in Figure 21.1. Note that we indicate how Joe, the creator of Sailors, acquired the SELECT privilege from the DBMS by introducing a System node and drawing an arc from this node to Joe’s node. System (System, Joe, Select on Sailors, Yes) (Joe, Art, Select on Sailors, Yes) Joe Art (Art, Bob, Select on Sailors, Yes) (Joe, Cal, Select on Sailors, Yes) (Bob, Art, Select on Sailors, Yes) Cal Bob (Cal, Bob, Select on Sailors, Yes) Figure 21.1 Example Authorization Graph As the graph clearly indicates, Bob’s grant to Art and Art’s grant to Bob (of the same privilege) creates a cycle. Bob is subsequently given the same privilege by Cal, who received it independently from Joe. At this point Joe decides to revoke the privilege he granted Art. Let us trace the effect of this revocation. The arc from Joe to Art is removed because it corresponds to the granting action that is revoked. All remaining nodes have the following property: If node N has an outgoing arc labeled with a privilege, there is a path from the System node to node N in which each arc label contains the same privilege plus the grant option. That is, any remaining granting action is justified by a privilege received (directly or indirectly) from the System. The execution of Joe’s REVOKE command therefore stops at this point, with everyone continuing to hold the SELECT privilege on Sailors. This result may seem unintuitive because Art continues to have the privilege only because he received it from Bob, and at the time that Bob granted the privilege to Art, he had received it only from Art. Although Bob acquired the privilege through Cal subsequently, should we not undo the effect of his grant Security and Authorization 703 to Art when executing Joe’s REVOKE command? The effect of the grant from Bob to Art is not undone in SQL. In effect, if a user acquires a privilege multiple times from different grantors, SQL treats each of these grants to the user as having occurred before that user passed on the privilege to other users. This implementation of REVOKE is convenient in many real-world situations. For example, if a manager is fired after passing on some privileges to subordinates (who may in turn have passed the privileges to others), we can ensure that only the manager’s privileges are removed by first redoing all of the manager’s granting actions and then revoking his or her privileges. That is, we need not recursively redo the subordinates’ granting actions. To return to the saga of Joe and his friends, let us suppose that Joe decides to revoke Cal’s SELECT privilege as well. Clearly, the arc from Joe to Cal corresponding to the grant of this privilege is removed. The arc from Cal to Bob is removed as well, since there is no longer a path from System to Cal that gives Cal the right to pass the SELECT privilege on Sailors to Bob. The authorization graph at this intermediate point is shown in Figure 21.2. System (System, Joe, Select on Sailors, Yes) Joe Art (Art, Bob, Select on Sailors, Yes) (Bob, Art, Select on Sailors, Yes) Cal Bob Figure 21.2 Example Authorization Graph during Revocation The graph now contains two nodes (Art and Bob) for which there are outgoing arcs with labels containing the SELECT privilege on Sailors; therefore, these users have granted this privilege. However, although each node contains an incoming arc carrying the same privilege, there is no such path from System to either of these nodes; so these users’ right to grant the privilege has been abandoned. We therefore remove the outgoing arcs as well. In general, these nodes might have other arcs incident on them, but in this example, they now have no incident arcs. Joe is left as the only user with the SELECT privilege on Sailors; Art and Bob have lost their privileges. 704 Chapter 21 21.3.1 Grant and Revoke on Views and Integrity Constraints The privileges held by the creator of a view (with respect to the view) change over time as he or she gains or loses privileges on the underlying tables. If the creator loses a privilege held with the grant option, users who were given that privilege on the view lose it as well. There are some subtle aspects to the GRANT and REVOKE commands when they involve views or integrity constraints. We consider some examples that highlight the following important points: 1. A view may be dropped because a SELECT privilege is revoked from the user who created the view. 2. If the creator of a view gains additional privileges on the underlying tables, he or she automatically gains additional privileges on the view. 3. The distinction between the REFERENCES and SELECT privileges is impor- tant. Suppose that Joe created Sailors and gave Michael the SELECT privilege on it with the grant option, and Michael then created the view YoungSailors and gave Eric the SELECT privilege on YoungSailors. Eric now defines a view called FineYoungSailors: CREATE VIEW FineYoungSailors (name, age, rating) AS SELECT S.sname, S.age, S.rating FROM YoungSailors S WHERE S.rating > 6 What happens if Joe revokes the SELECT privilege on Sailors from Michael? Michael no longer has the authority to execute the query used to define Young- Sailors because the definition refers to Sailors. Therefore, the view YoungSailors is dropped (i.e., destroyed). In turn, FineYoungSailors is dropped as well. Both view definitions are removed from the system catalogs; even if a remorseful Joe decides to give back the SELECT privilege on Sailors to Michael, the views are gone and must be created afresh if they are required. On a more happy note, suppose that everything proceeds as just described until Eric defines FineYoungSailors; then, instead of revoking the SELECT privilege on Sailors from Michael, Joe decides to also give Michael the INSERT privilege on Sailors. Michael’s privileges on the view YoungSailors are upgraded to what he would have if he were to create the view now. He therefore acquires the INSERT privilege on YoungSailors as well. (Note that this view is updatable.) What about Eric? His privileges are unchanged. Whether or not Michael has the INSERT privilege on YoungSailors with the grant option depends on whether or not Joe gives him the INSERT privilege on Security and Authorization 705 Sailors with the grant option. To understand this situation, consider Eric again. If Michael has the INSERT privilege on YoungSailors with the grant option, he can pass this privilege to Eric. Eric could then insert rows into the Sailors table because inserts on YoungSailors are effected by modifying the underlying base table, Sailors. Clearly, we do not want Michael to be able to authorize Eric to make such changes unless Michael has the INSERT privilege on Sailors with the grant option. The REFERENCES privilege is very different from the SELECT privilege, as the following example illustrates. Suppose that Joe is the creator of Boats. He can authorize another user, say, Fred, to create Reserves with a foreign key that refers to the bid column of Boats by giving Fred the REFERENCES privilege with respect to this column. On the other hand, if Fred has the SELECT privilege on the bid column of Boats but not the REFERENCES privilege, Fred cannot create Reserves with a foreign key that refers to Boats. If Fred creates Reserves with a foreign key column that refers to bid in Boats and later loses the REFERENCES privilege on the bid column of boats, the foreign key constraint in Reserves is dropped; however, the Reserves table is not dropped. To understand why the SQL standard chose to introduce the REFERENCES priv- ilege rather than to simply allow the SELECT privilege to be used in this sit- uation, consider what happens if the definition of Reserves specified the NO ACTION option with the foreign key—Joe, the owner of Boats, may be pre- vented from deleting a row from Boats because a row in Reserves refers to this Boats row. Giving Fred, the creator of Reserves, the right to constrain updates on Boats in this manner goes beyond simply allowing him to read the values in Boats, which is all that the SELECT privilege authorizes. 21.4 MANDATORY ACCESS CONTROL Discretionary access control mechanisms, while generally effective, have certain weaknesses. In particular they are susceptible to Trojan horse schemes whereby a devious unauthorized user can trick an authorized user into disclosing sensi- tive data. For example, suppose that student Tricky Dick wants to break into the grade tables of instructor Trustin Justin. Dick does the following: He creates a new table called MineAllMine and gives INSERT privileges on this table to Justin (who is blissfully unaware of all this attention, of course). He modifies the code of some DBMS application that Justin uses often to do a couple of additional things: first, read the Grades table, and next, write the result into MineAllMine. 706 Chapter 21 Then he sits back and waits for the grades to be copied into MineAllMine and later undoes the modifications to the application to ensure that Justin does not somehow find out later that he has been cheated. Thus, despite the DBMS enforcing all discretionary access controls—only Justin’s authorized code was allowed to access Grades—sensitive data is disclosed to an intruder. The fact that Dick could surreptitiously modify Justin’s code is outside the scope of the DBMS’s access control mechanism. Mandatory access control mechanisms are aimed at addressing such loopholes in discretionary access control. The popular model for mandatory access control, called the Bell-LaPadula model, is described in terms of objects (e.g., tables, views, rows, columns), subjects (e.g., users, programs), security classes, and clearances. Each database object is assigned a security class, and each subject is assigned clearance for a security class; we denote the class of an object or subject A as class(A). The security classes in a system are organized according to a partial order, with a most secure class and a least secure class. For simplicity, we assume that there are four classes: top secret (TS), secret (S), confidential (C), and unclassified (U). In this system, TS > S > C > U, where A > B means that class A data is more sensitive than class B data. The Bell-LaPadula model imposes two restrictions on all reads and writes of database objects: 1. Simple Security Property: Subject S is allowed to read object O only if class(S) ≥ class(O). For example, a user with TS clearance can read a table with C clearance, but a user with C clearance is not allowed to read a table with TS classification. 2. *-Property: Subject S is allowed to write object O only if class(S) ≤ class(O). For example, a user with S clearance can write only objects with S or TS classification. If discretionary access controls are also specified, these rules represent addi- tional restrictions. Therefore, to read or write a database object, a user must have the necessary privileges (obtained via GRANT commands) and the security classes of the user and the object must satisfy the preceding restrictions. Let us consider how such a mandatory control mechanism might have foiled Tricky Dick. The Grades table could be classified as S, Justin could be given clearance for S, and Tricky Dick could be given a lower clearance (C). Dick can create objects of only C or lower classification; so the table MineAllMine can have at most the classification C. When the application program running on behalf of Justin (and therefore with clearance S) tries to copy Grades into MineAllMine, it is not allowed to do so because class(MineAllMine) < class(application), and the *-Property is violated. Security and Authorization 707 21.4.1 Multilevel Relations and Polyinstantiation To apply mandatory access control policies in a relational DBMS, a security class must be assigned to each database object. The objects can be at the granularity of tables, rows, or even individual column values. Let us assume that each row is assigned a security class. This situation leads to the concept of a multilevel table, which is a table with the surprising property that users with different security clearances see a different collection of rows when they access the same table. Consider the instance of the Boats table shown in Figure 21.3. Users with S and TS clearance get both rows in the answer when they ask to see all rows in Boats. A user with C clearance gets only the second row, and a user with U clearance gets no rows. bid bname color Security Class 101 Salsa Red S 102 Pinto Brown C Figure 21.3 An Instance B1 of Boats The Boats table is defined to have bid as the primary key. Suppose that a user with clearance C wishes to enter the row 101, Picante, Scarlet, C. We have a dilemma: If the insertion is permitted, two distinct rows in the table have key 101. If the insertion is not permitted because the primary key constraint is vio- lated, the user trying to insert the new row, who has clearance C, can infer that there is a boat with bid=101 whose security class is higher than C. This situation compromises the principle that users should not be able to infer any information about objects that have a higher security classification. This dilemma is resolved by effectively treating the security classification as part of the key. Thus, the insertion is allowed to continue, and the table instance is modified as shown in Figure 21.4. bid bname color Security Class 101 Salsa Red S 101 Picante Scarlet C 102 Pinto Brown C Figure 21.4 Instance B1 after Insertion 708 Chapter 21 Users with clearance C or U see just the rows for Picante and Pinto, but users with clearance S or TS see all three rows. The two rows with bid=101 can be interpreted in one of two ways: only the row with the higher classification (Salsa, with classification S) actually exists, or both exist and their presence is revealed to users according to their clearance level. The choice of interpretation is up to application developers and users. The presence of data objects that appear to have different values to users with different clearances (for example, the boat with bid 101) is called polyin- stantiation. If we consider security classifications associated with individual columns, the intuition underlying polyinstantiation can be generalized in a straightforward manner, but some additional details must be addressed. We remark that the main drawback of mandatory access control schemes is their rigidity; policies are set by system administrators, and the classification mecha- nisms are not flexible enough. A satisfactory combination of discretionary and mandatory access controls is yet to be achieved. 21.4.2 Covert Channels, DoD Security Levels Even if a DBMS enforces the mandatory access control scheme just discussed, information can flow from a higher classification level to a lower classification level through indirect means, called covert channels. For example, if a trans- action accesses data at more than one site in a distributed DBMS, the actions at the two sites must be coordinated. The process at one site may have a lower clearance (say, C) than the process at another site (say, S), and both processes have to agree to commit before the transaction can be committed. This requirement can be exploited to pass information with an S classification to the process with a C clearance: The transaction is repeatedly invoked, and the process with the C clearance always agrees to commit, whereas the process with the S clearance agrees to commit if it wants to transmit a 1 bit and does not agree if it wants to transmit a 0 bit. In this (admittedly tortuous) manner, information with an S clearance can be sent to a process with a C clearance as a stream of bits. This covert channel is an indirect violation of the intent behind the *-Property. Additional examples of covert channels can be found readily in statistical databases, which we discuss in Section 21.6.2. DBMS vendors recently started implementing mandatory access control mech- anisms (although they are not part of the SQL standard) because the United States Department of Defense (DoD) requires such support for its systems. The DoD requirements can be described in terms of security levels A, B, C, and D, of which A is the most secure and D is the least secure. Security and Authorization 709 Current Systems: Commercial RDBMSs are available that support dis- cretionary controls at the C2 level and mandatory controls at the B1 level. IBM DB2, Informix, Microsoft SQL Server, Oracle 8, and Sybase ASE all support SQL’s features for discretionary access control. In general, they do not support mandatory access control; Oracle offers a version of their product with support for mandatory access control. Level C requires support for discretionary access control. It is divided into sublevels C1 and C2; C2 also requires some degree of accountability through procedures such as login verification and audit trails. Level B requires sup- port for mandatory access control. It is subdivided into levels B1, B2, and B3. Level B2 additionally requires the identification and elimination of covert channels. Level B3 additionally requires maintenance of audit trails and the designation of a security administrator (usually, but not necessarily, the DBA). Level A, the most secure level, requires a mathematical proof that the security mechanism enforces the security policy! 21.5 SECURITY FOR INTERNET APPLICATIONS When a DBMS is accessed from a secure location, we can rely upon a simple password mechanism for authenticating users. However, suppose our friend Sam wants to place an order for a book over the Internet. This presents some unique challenges: Sam is not even a known user (unless he is a repeat cus- tomer). From Amazon’s point of view, we have an individual asking for a book and offering to pay with a credit card registered to Sam, but is this individual really Sam? From Sam’s point of view, he sees a form asking for credit card information, but is this indeed a legitimate part of Amazon’s site, and not a rogue application designed to trick him into revealing his credit card number? This example illustrates the need for a more sophisticated approach to authen- tication than a simple password mechanism. Encryption techniques provide the foundation for modern authentication. 21.5.1 Encryption The basic idea behind encryption is to apply an encryption algorithm to the data, using a user-specified or DBA-specified encryption key. The output of the algorithm is the encrypted version of the data. There is also a decryp- tion algorithm, which takes the encrypted data and a decryption key as input and then returns the original data. Without the correct decryption key, the decryption algorithm produces gibberish. The encryption and decryption 710 Chapter 21 DES and AES: The DES standard, adopted in 1977, has a 56-bit en- cryption key. Over time, computers have become so fast that, in 1999, a special-purpose chip and a network of PCs were used to crack DES in under a day. The system was testing 245 billion keys per second when the correct key was found! It is estimated that a special-purpose hardware device can be built for under a million dollars that can crack DES in under four hours. Despite growing concerns about its vulnerability, DES is still widely used. In 2000, a successor to DES, called the Advanced Encryp- tion Standard (AES), was adopted as the new (symmetric) encryption standard. AES has three possible key sizes: 128, 192, and 256 bits. With a 128 bit key size, there are over 3 · 1038 possible AES keys, which is on the order of 1024 more than the number of 56-bit DES keys. Assume that we could build a computer fast enough to crack DES in 1 second. This computer would compute for about 149 trillion years to crack a 128-bit AES key. (Experts think the universe is less than 20 billion years old.) algorithms themselves are assumed to be publicly known, but one or both keys are secret (depending upon the encryption scheme). In symmetric encryption, the encryption key is also used as the decryption key. The ANSI Data Encryption Standard (DES), which has been in use since 1977, is a well-known example of symmetric encryption. It uses an en- cryption algorithm that consists of character substitutions and permutations. The main weakness of symmetric encryption is that all authorized users must be told the key, increasing the likelihood of its becoming known to an intruder (e.g., by simple human error). Another approach to encryption, called public-key encryption, has become increasingly popular in recent years. The encryption scheme proposed by Rivest, Shamir, and Adleman, called RSA, is a well-known example of public- key encryption. Each authorized user has a public encryption key, known to everyone, and a private decryption key, known only to him or her. Since the private decryption keys are known only to their owners, the weakness of DES is avoided. A central issue for public-key encryption is how encryption and decryption keys are chosen. Technically, public-key encryption algorithms rely on the existence of one-way functions, whose inverses are computationally very hard to determine. The RSA algorithm, for example, is based on the observation that, although checking whether a given number is prime is easy, determining the prime factors of a nonprime number is extremely hard. (Determining the Security and Authorization 711 Why RSA Works: The essential point of the scheme is that it is easy to compute d given e, p, and q, but very hard to compute d given just e and L. In turn, this difficulty depends on the fact that it is hard to determine the prime factors of L, which happen to be p and q. A caveat: Factoring is widely believed to be hard, but there is no proof that this is so. Nor is there a proof that factoring is the only way to crack RSA; that is, to compute d from e and L. prime factors of a number with over 100 digits can take years of CPU time on the fastest available computers today.) We now sketch the idea behind the RSA algorithm, assuming that the data to be encrypted is an integer I. To choose an encryption key and a decryption key for a given user, we first choose a very large integer L, larger than the largest integer we will ever need to encode.1 We then select a number e as the encryption key and compute the decryption key d based on e and L; how this is done is central to the approach, as we see shortly. Both L and e are made public and used by the encryption algorithm. However, d is kept secret and is necessary for decryption. The encryption function is S = I e mod L. The decryption function is I = S d mod L. We choose L to be the product of two large (e.g., 1024-bit), distinct prime numbers, p ∗ q. The encryption key e is a randomly chosen number between 1 and L that is relatively prime to (p − 1) ∗ (q − 1). The decryption key d is computed such that d ∗ e = 1 mod ((p − 1) ∗ (q − 1)). Given these choices, results in number theory can be used to prove that the decryption function recovers the original message from its encrypted version. A very important property of the encryption and decryption algorithms is that the roles of the encryption and decryption keys can be reversed: decrypt(d, (encrypt(e, I))) = I = decrypt(e, (encrypt(d, I))) Since many protocols rely on this property, we henceforth simply refer to pub- lic and private keys (since both keys can be used for encryption as well as decryption). 1 A message that is to be encrypted is decomposed into blocks such that each block can be treated as an integer less than L. 712 Chapter 21 While we introduced encryption in the context of authentication, we note that it is a fundamental tool for enforcing security. A DBMS can use encryption to protect information in situations where the normal security mechanisms of the DBMS are not adequate. For example, an intruder may steal tapes containing some data or tap a communication line. By storing and transmitting data in an encrypted form, the DBMS ensures that such stolen data is not intelligible to the intruder. 21.5.2 Certifying Servers: The SSL Protocol Suppose we associate a public key and a decryption key with Amazon. Any- one, say, user Sam, can send Amazon an order by encrypting the order using Amazon’s public key. Only Amazon can decrypt this secret order because the decryption algorithm requires Amazon’s private key, known only to Amazon. This hinges on Sam’s ability to reliably find out Amazon’s public key. A num- ber of companies serve as certification authorities, e.g., Verisign. Amazon generates a public encryption key eA (and a private decryption key) and sends the public key to Verisign. Verisign then issues a certificate to Amazon that contains the following information: Verisign, Amazon, https://www.amazon.com, eA The certificate is encrypted using Verisign’s own private key, which is known to (i.e., stored in) Internet Explorer, Netscape Navigator, and other browsers. When Sam comes to the Amazon site and wants to place an order, his browser, running the SSL protocol,2 asks the server for the Verisign certificate. The browser then validates the certificate by decrypting it (using Verisign’s public key) and checking that the result is a certificate with the name Verisign, and that the URL it contains is that of the server it is talking to. (Note that an attempt to forge a certificate will fail because certificates are encrypted using Verisign’s private key, which is known only to Verisign.) Next, the browser generates a random session key, encrypt it using Amazon’s public key (which it obtained from the validated certificate and therefore trusts), and sends it to the Amazon server. From this point on, the Amazon server and the browser can use the session key (which both know and are confident that only they know) and a symmetric encryption protocol like AES or DES to exchange securely encrypted messages: Messages are encrypted by the sender and decrypted by the receiver using the same session key. The encrypted messages travel over the Internet and may be 2A browser uses the SSL protocol if the target URL begins with https. Security and Authorization 713 intercepted, but they cannot be decrypted without the session key. It is useful to consider why we need a session key; after all, the browser could simply have encrypted Sam’s original request using Amazon’s public key and sent it securely to the Amazon server. The reason is that, without the session key, the Amazon server has no way to securely send information back to the browser. A further advantage of session keys is that symmetric encryption is computationally much faster than public key encryption. The session key is discarded at the end of the session. Thus, Sam can be assured that only Amazon can see the information he types into the form shown to him by the Amazon server and the information sent back to him in responses from the server. However, at this point, Amazon has no assurance that the user running the browser is actually Sam, and not someone who has stolen Sam’s credit card. Typically, merchants accept this situation, which also arises when a customer places an order over the phone. If we want to be sure of the user’s identity, this can be accomplished by addi- tionally requiring the user to login. In our example, Sam must first establish an account with Amazon and select a password. (Sam’s identity is originally established by calling him back on the phone to verify the account information or by sending email to an email address; in the latter case, all we establish is that the owner of the account is the individual with the given email address.) Whenever he visits the site and Amazon needs to verify his identity, Amazon redirects him to a login form after using SSL to establish a session key. The password typed in is transmitted securely by encrypting it with the session key. One remaining drawback in this approach is that Amazon now knows Sam’s credit card number, and he must trust Amazon not to misuse it. The Secure Electronic Transaction protocol addresses this limitation. Every customer must now obtain a certificate, with his or her own private and public keys, and every transaction involves the Amazon server, the customer’s browser, and the server of a trusted third party, such as Visa for credit card transactions. The basic idea is that the browser encodes non-credit card information using Amazon’s public key and the credit card information using Visa’s public key and sends these to the Amazon server, which forwards the credit card information (which it cannot decrypt) to the Visa server. If the Visa server approves the information, the transaction goes through. 21.5.3 Digital Signatures Suppose that Elmer, who works for Amazon, and Betsy, who works for McGraw- Hill, need to communicate with each other about inventory. Public key encryp- tion can be used to create digital signatures for messages. That is, messages 714 Chapter 21 can be encoded in such a way that, if Elmer gets a message supposedly from Betsy, he can verify that it is from Betsy (in addition to being able to decrypt the message) and, further, prove that it is from Betsy at McGraw-Hill, even if the message is sent from a Hotmail account when Betsy is traveling. Similarly, Betsy can authenticate the originator of messages from Elmer. If Elmer encrypts messages for Betsy using her public key, and vice-versa, they can exchange information securely but cannot authenticate the sender. Someone who wishes to impersonate Betsy could use her public key to send a message to Elmer, pretending to be Betsy. A clever use of the encryption scheme, however, allows Elmer to verify whether the message was indeed sent by Betsy. Betsy encrypts the message using her private key and then encrypts the result using Elmer’s public key. When Elmer receives such a message, he first decrypts it using his private key and then decrypts the result using Betsy’s public key. This step yields the original un- encrypted message. Furthermore, Elmer can be certain that the message was composed and encrypted by Betsy because a forger could not have known her private key, and without it the final result would have been nonsensical, rather than a legible message. Further, because even Elmer does not know Betsy’s private key, Betsy cannot claim that Elmer forged the message. If authenticating the sender is the objective and hiding the message is not im- portant, we can reduce the cost of encryption by using a message signature. A signature is obtained by applying a one-way function (e.g., a hashing scheme) to the message and is considerably smaller. We encode the signature as in the basic digital signature approach, and send the encoded signature together with the full, unencoded message. The recipient can verify the sender of the signa- ture as just described, and validate the message itself by applying the one-way function and comparing the result with the signature. 21.6 ADDITIONAL ISSUES RELATED TO SECURITY Security is a broad topic, and our coverage is necessarily limited. This section briefly touches on some additional important issues. 21.6.1 Role of the Database Administrator The database administrator (DBA) plays an important role in enforcing the security-related aspects of a database design. In conjunction with the owners of the data, the DBA also contributes to developing a security policy. The DBA has a special account, which we call the system account, and is responsible Security and Authorization 715 for the overall security of the system. In particular, the DBA deals with the following: 1. Creating New Accounts: Each new user or group of users must be assigned an authorization ID and a password. Note that application pro- grams that access the database have the same authorization ID as the user executing the program. 2. Mandatory Control Issues: If the DBMS supports mandatory control— some customized systems for applications with very high security require- ments (for example, military data) provide such support—the DBA must assign security classes to each database object and assign security clear- ances to each authorization ID in accordance with the chosen security pol- icy. The DBA is also responsible for maintaining the audit trail, which is essen- tially the log of updates with the authorization ID (of the user executing the transaction) added to each log entry. This log is just a minor extension of the log mechanism used to recover from crashes. Additionally, the DBA may choose to maintain a log of all actions, including reads, performed by a user. Analyzing such histories of how the DBMS was accessed can help prevent se- curity violations by identifying suspicious patterns before an intruder finally succeeds in breaking in, or it can help track down an intruder after a violation has been detected. 21.6.2 Security in Statistical Databases A statistical database contains specific information on individuals or events but is intended to permit only statistical queries. For example, if we maintained a statistical database of information about sailors, we would allow statistical queries about average ratings, maximum age, and so on, but not queries about individual sailors. Security in such databases poses new problems because it is possible to infer protected information (such as a sailor’s rating) from answers to permitted statistical queries. Such inference opportunities represent covert channels that can compromise the security policy of the database. Suppose that sailor Sneaky Pete wants to know the rating of Admiral Horn- tooter, the esteemed chairman of the sailing club, and happens to know that Horntooter is the oldest sailor in the club. Pete repeatedly asks queries of the form “How many sailors are there whose age is greater than X?” for various values of X, until the answer is 1. Obviously, this sailor is Horntooter, the oldest sailor. Note that each of these queries is a valid statistical query and is permitted. Let the value of X at this point be, say, 65. Pete now asks the query, “What is the maximum rating of all sailors whose age is greater than 716 Chapter 21 65?” Again, this query is permitted because it is a statistical query. However, the answer to this query reveals Horntooter’s rating to Pete, and the security policy of the database is violated. One approach to preventing such violations is to require that each query must involve at least some minimum number, say, N, of rows. With a reasonable choice of N, Pete would not be able to isolate the information about Horntooter, because the query about the maximum rating would fail. This restriction, however, is easy to overcome. By repeatedly asking queries of the form, “How many sailors are there whose age is greater than X?” until the system rejects one such query, Pete identifies a set of N sailors, including Horntooter. Let the value of X at this point be 55. Now, Pete can ask two queries: “What is the sum of the ratings of all sailors whose age is greater than 55?” Since N sailors have age greater than 55, this query is permitted. “What is the sum of the ratings of all sailors, other than Horntooter, whose age is greater than 55, and sailor Pete?” Since the set of sailors whose rat- ings are added up now includes Pete instead of Horntooter, but is otherwise the same, the number of sailors involved is still N, and this query is also permitted. From the answers to these two queries, say, A1 and A2 , Pete, who knows his rating, can easily calculate Horntooter’s rating as A1 − A2 + Pete’s rating. Pete succeeded because he was able to ask two queries that involved many of the same sailors. The number of rows examined in common by two queries is called their intersection. If a limit were to be placed on the amount of intersection permitted between any two queries issued by the same user, Pete could be foiled. Actually, a truly fiendish (and patient) user can generally find out information about specific individuals even if the system places a minimum number of rows bound (N) and a maximum intersection bound (M) on queries, but the number of queries required to do this grows in proportion to N/M. We can try to additionally limit the total number of queries that a user is allowed to ask, but two users could still conspire to breach security. By maintaining a log of all activity (including read-only accesses), such query patterns can be detected, ideally before a security violation occurs. This discussion should make it clear, however, that security in statistical databases is difficult to enforce. 21.7 DESIGN CASE STUDY: THE INTERNET STORE We return to our case study and our friends at DBDudes to consider security issues. There are three groups of users: customers, employees, and the owner of the book shop. (Of course, there is also the database administrator, who Security and Authorization 717 has universal access to all data and is responsible for regular operation of the database system.) The owner of the store has full privileges on all tables. Customers can query the Books table and place orders online, but they should not have access to other customers’ records nor to other customers’ orders. DBDudes restricts access in two ways. First, it designs a simple Web page with several forms similar to the page shown in Figure 7.1 in Chapter 7. This allows customers to submit a small collection of valid requests without giving them the ability to directly access the underlying DBMS through an SQL interface. Second, DBDudes uses the security features of the DBMS to limit access to sensitive data. The webpage allows customers to query the Books relation by ISBN number, name of the author, and title of a book. The webpage also has two buttons. The first button retrieves a list of all of the customer’s orders that are not completely fulfilled yet. The second button displays a list of all completed orders for that customer. Note that customers cannot specify actual SQL queries through the Web but only fill in some parameters in a form to instantiate an automatically generated SQL query. All queries generated through form input have a WHERE clause that includes the cid attribute value of the current customer, and evaluation of the queries generated by the two buttons requires knowledge of the customer identification number. Since all users have to log on to the website before browsing the catalog, the business logic (discussed in Section 7.7) must maintain state information about a customer (i.e., the customer identification number) during the customer’s visit to the website. The second step is to configure the database to limit access according to each user group’s need to know. DBDudes creates a special customer account that has the following privileges: SELECT ON Books, NewOrders, OldOrders, NewOrderlists, OldOrderlists INSERT ON NewOrders, OldOrders, NewOrderlists, OldOrderlists Employees should be able to add new books to the catalog, update the quantity of a book in stock, revise customer orders if necessary, and update all customer information except the credit card information. In fact, employees should not even be able to see a customer’s credit card number. Therefore, DBDudes creates the following view: CREATE VIEW CustomerInfo (cid,cname,address) AS SELECT C.cid, C.cname, C.address FROM Customers C DBDudes gives the employee account the following privileges: 718 Chapter 21 SELECT ON CustomerInfo, Books, NewOrders, OldOrders, NewOrderlists, OldOrderlists INSERT ON CustomerInfo, Books, NewOrders, OldOrders, NewOrderlists, OldOrderlists UPDATE ON CustomerInfo, Books, NewOrders, OldOrders, NewOrderlists, OldOrderlists DELETE ON Books, NewOrders, OldOrders, NewOrderlists, OldOrderlists Observe that employees can modify CustomerInfo and even insert tuples into it. This is possible because they have the necessary privileges, and further, the view is updatable and insertable-into. While it seems reasonable that employees can update a customer’s address, it does seem odd that they can insert a tuple into CustomerInfo even though they cannot see related information about the customer (i.e., credit card number) in the Customers table. The reason for this is that the store wants to be able to take orders from first-time customers over the phone without asking for credit card information over the phone. Employees can insert into CustomerInfo, effectively creating a new Customers record without credit card information, and customers can subsequently provide the credit card number through a Web interface. (Obviously, the order is not shipped until they do this.) In addition, there are security issues when the user first logs on to the website using the customer identification number. Sending the number unencrypted over the Internet is a security hazard, and a secure protocol such as SSL should be used. Companies such as CyberCash and DigiCash offer electronic commerce pay- ment solutions, even including electronic cash. Discussion of how to incorporate such techniques into the website are outside the scope of this book. 21.8 REVIEW QUESTIONS Answers to the review questions can be found in the listed sections. What are the main objectives in designing a secure database application? Explain the terms secrecy, integrity, availability, and authentication. (Sec- tion 21.1) Explain the terms security policy and security mechanism and how they are related. (Section 21.1) What is the main idea behind discretionary access control? What is the idea behind mandatory access control? What are the relative merits of these two approaches? (Section 21.2) Security and Authorization 719 Describe the privileges recognized in SQL? In particular, describe SELECT, INSERT, UPDATE, DELETE, and REFERENCES. For each privilege, indicate who acquires it automatically on a given table. (Section 21.3) How are the owners of privileges identified? In particular, discuss autho- rization IDs and roles. (Section 21.3) What is an authorization graph? Explain SQL’s GRANT and REVOKE com- mands in terms of their effect on this graph. In particular, discuss what happens when users pass on privileges that they receive from someone else. (Section 21.3) Discuss the difference between having a privilege on a table and on a view defined over the table. In particular, how can a user have a privilege (say, SELECT) over a view without also having it on all underlying tables? Who must have appropriate privileges on all underlying tables of the view? (Section 21.3.1) What are objects, subjects, security classes, and clearances in mandatory access control? Discuss the Bell-LaPadula restrictions in terms of these con- cepts. Specifically, define the simple security property and the *-property. (Section 21.4) What is a Trojan horse attack and how can it compromise discretionary access control? Explain how mandatory access control protects against Trojan horse attacks. (Section 21.4) What do the terms multilevel table and polyinstantiation mean? Explain their relationship, and how they arise in the context of mandatory access control. (Section 21.4.1) What are covert channels and how can they arise when both discretionary and mandatory access controls are in place? (Section 21.4.2) Discuss the DoD security levels for database systems. (Section 21.4.2) Explain why a simple password mechanism is insufficient for authentica- tion of users who access a database remotely, say, over the Internet. (Sec- tion 21.5) What is the difference between symmetric and public-key encryption? Give examples of well-known encryption algorithms of both kinds. What is the main weakness of symmetric encryption and how is this addressed in public- key encryption? (Section 21.5.1) Discuss the choice of encryption and decryption keys in public-key encryp- tion and how they are used to encrypt and decrypt data. Explain the role of one-way functions. What assurance do we have that the RSA scheme cannot be compromised? (Section 21.5.1) 720 Chapter 21 What are certification authorities and why are they needed? Explain how certificates are issued to sites and validated by a browser using the SSL protocol; discuss the role of the session key. (Section 21.5.2) If a user connects to a site using the SSL protocol, explain why there is still a need to login the user. Explain the use of SSL to protect passwords and other sensitive information being exchanged. What is the secure electronic transaction protocol? What is the added value over SSL? (Section 21.5.2) A digital signature facilitates secure exchange of messages. Explain what it is and how it goes beyond simply encrypting messages. Discuss the use of message signatures to reduce the cost of encryption. (Section 21.5.3) What is the role of the database administrator with respect to security? (Section 21.6.1) Discuss the additional security loopholes introduced in statistical databases. (Section 21.6.2) EXERCISES Exercise 21.1 Briefly answer the following questions: 1. Explain the intuition behind the two rules in the Bell-LaPadula model for mandatory access control. 2. Give an example of how covert channels can be used to defeat the Bell-LaPadula model. 3. Give an example of polyinstantiation. 4. Describe a scenario in which mandatory access controls prevent a breach of security that cannot be prevented through discretionary controls. 5. Describe a scenario in which discretionary access controls are required to enforce a secu- rity policy that cannot be enforced using only mandatory controls. 6. If a DBMS already supports discretionary and mandatory access controls, is there a need for encryption? 7. Explain the need for each of the following limits in a statistical database system: (a) A maximum on the number of queries a user can pose. (b) A minimum on the number of tuples involved in answering a query. (c) A maximum on the intersection of two queries (i.e., on the number of tuples that both queries examine). 8. Explain the use of an audit trail, with special reference to a statistical database system. 9. What is the role of the DBA with respect to security? 10. Describe AES and its relationship to DES. 11. What is public-key encryption? How does it differ from the encryption approach taken in the Data Encryption Standard (DES), and in what ways is it better than DES? Security and Authorization 721 12. Explain how a company offering services on the Internet could use encryption-based techniques to make its order-entry process secure. Discuss the role of DES, AES, SSL, SET, and digital signatures. Search the Web to find out more about related techniques such as electronic cash. Exercise 21.2 You are the DBA for the VeryFine Toy Company and create a relation called Employees with fields ename, dept, and salary. For authorization reasons, you also define views EmployeeNames (with ename as the only attribute) and DeptInfo with fields dept and avgsalary. The latter lists the average salary for each department. 1. Show the view definition statements for EmployeeNames and DeptInfo. 2. What privileges should be granted to a user who needs to know only average department salaries for the Toy and CS departments? 3. You want to authorize your secretary to fire people (you will probably tell him whom to fire, but you want to be able to delegate this task), to check on who is an employee, and to check on average department salaries. What privileges should you grant? 4. Continuing with the preceding scenario, you do not want your secretary to be able to look at the salaries of individuals. Does your answer to the previous question ensure this? Be specific: Can your secretary possibly find out salaries of some individuals (depending on the actual set of tuples), or can your secretary always find out the salary of any individual he wants to? 5. You want to give your secretary the authority to allow other people to read the Employ- eeNames view. Show the appropriate command. 6. Your secretary defines two new views using the EmployeeNames view. The first is called AtoRNames and simply selects names that begin with a letter in the range A to R. The second is called HowManyNames and counts the number of names. You are so pleased with this achievement that you decide to give your secretary the right to insert tuples into the EmployeeNames view. Show the appropriate command and describe what privileges your secretary has after this command is executed. 7. Your secretary allows Todd to read the EmployeeNames relation and later quits. You then revoke the secretary’s privileges. What happens to Todd’s privileges? 8. Give an example of a view update on the preceding schema that cannot be implemented through updates to Employees. 9. You decide to go on an extended vacation, and to make sure that emergencies can be handled, you want to authorize your boss Joe to read and modify the Employees relation and the EmployeeNames relation (and Joe must be able to delegate authority, of course, since he is too far up the management hierarchy to actually do any work). Show the appropriate SQL statements. Can Joe read the DeptInfo view? 10. After returning from your (wonderful) vacation, you see a note from Joe, indicating that he authorized his secretary Mike to read the Employees relation. You want to revoke Mike’s SELECT privilege on Employees, but you do not want to revoke the rights you gave to Joe, even temporarily. Can you do this in SQL? 11. Later you realize that Joe has been quite busy. He has defined a view called AllNames using the view EmployeeNames, defined another relation called StaffNames that he has access to (but you cannot access), and given his secretary Mike the right to read from the AllNames view. Mike has passed this right on to his friend Susan. You decide that, even at the cost of annoying Joe by revoking some of his privileges, you simply have to take away Mike and Susan’s rights to see your data. What REVOKE statement would you execute? What rights does Joe have on Employees after this statement is executed? What views are dropped as a consequence? 722 Chapter 21 Exercise 21.3 You are a painter and have an Internet store where you sell your paintings directly to the public. You would like customers to pay for their purchases with credit cards, and wish to ensure that these electronic transactions are secure. Assume that Mary wants to purchase your recent painting of the Cornell Uris Library. Answer the following questions. 1. How can you ensure that the user who is purchasing the painting is really Mary? 2. Explain how SSL ensures that the communication of the credit card number is secure. What is the role of a certification authority in this case? 3. Assume that you would like Mary to be able to verify that all your email messages are really sent from you. How can you authenticate your messages without encrypting the actual text? 4. Assume that your customers can also negotiate the price of certain paintings and assume that Mary wants to negotiate the price of your painting of the Madison Terrace. You would like the text of this communication to be private between you and Mary. Explain the advantages and disadvantages of different methods of encrypting your communication with Mary. Exercise 21.4 Consider Exercises 6.6 to 6.9 from Chapter 6. For each exercise, identify what data should be accessible to different groups of users, and write the SQL statements to enforce these access control policies. Exercise 21.5 Consider Exercises 7.7 to 7.10 from Chapter 7. For each exercise, discuss where encryption, SSL, and digital signatures are appropriate. PROJECT-BASED EXERCISES Exercise 21.6 Is there any support for views or authorization in Minibase? BIBLIOGRAPHIC NOTES The authorization mechanism of System R, which greatly influenced the GRANT and REVOKE paradigm in SQL, is described in. A good general treatment of security and cryptography is presented in , and an overview of database security can be found in and. Security in statistical databases is investigated in several papers, including and. Multilevel security is discussed in several papers, including [409, 499, 694, 710]. A classic reference on crytography is the book by Schneier. Diffie and Hellman proposed the first public key cryptographic technique. The widely-used RSA encryption scheme was introduced by Rivest, Shamir, and Adleman. AES is based on Daemen and Rijmen’s Rijndael algorithm. There are many introductory books on SSL, such as and. More information on ditigal signatures can be found in the book by Ford and Baum.  PART VII ADDITIONAL TOPICS