NIST Special Publication 800-8 Security Issues in the Database Language SQL W. Timothy Polk and Lawrence E. Bassham The Database Language SQL (SQL) is a standard interface for accessing and manipulating relational databases. An SQL-compliant database management system (DBMS) will include a minimum level of functionality in a variety of areas. However, many additional areas are left unspecified by the SQL standard. In addition, there are multiple versions of the SQL standard; the functionality will vary according to the particular version. This document examines the security functionality that might be required of relational DBMS's, and compares them with the requirements and options of the SQL specifications. The comparison will show that the security functionality of an SQL-compliant DBMS may vary greatly. A variety of security policies are considered which can be supported by SQL. The document ends by showing which types of functions are required by the examined security policies. This is a special ascii version of the document. Indices are not included, and figures are include at the end of the document in ugly ascii. These features are included in the PostScript version and in the hardcopy version. Hardcopy may be ordered from the Government Printing Office when available. 1 Introduction 1 1.1 Audience 1 1.2 The Standards 1 1.3 Using This Document 2 1.4 Security Considerations 3 2 SQL Architecture 5 2.1 SQL Functionality 5 2.2 SQL Implementation 5 2.3 Security Responsibilities: The SQL Component 6 2.4 Security Responsibilities: Non-SQL Components 7 2.4.1 Application Interface to SQL 7 2.4.2 SQL Interface to Physical Database 7 2.4.3 SQL Interface to Non-SQL DBMS 8 2.4.4 Interface to Remote Databases 8 3 Security Policy 9 3.1 Discretionary Access Control 9 3.1.1 Privileges 9 3.1.2 Authorization Identifier 11 3.1.3 Roles 12 3.2 Mandatory Access Control 12 3.2.1 Polyinstantiation 13 3.2.2 TCB Subset Architecture 14 3.2.3 Trusted Subject Architecture 15 3.2.4 Integrity Lock Architecture 15 3.3 Schema Manipulation 16 3.4 Integrity Constraints 16 3.4.1 Table Constraints 16 3.4.2 Column Constraints and Check Constraints 17 3.4.3 Assertions 18 3.4.4 Domains 19 3.4.5 The SQL'89 Security Bug 19 3.5 Object Reuse 19 3.6 Labels 19 3.7 Inference 20 3.8 Aggregation 21 4 Accountability 22 4.1 Identification & Authentication label 22 4.2 Auditing 23 5 Assurance 24 5.1 Testing and Evaluation 24 5.1.1 FIPS Conformance 25 5.1.2 NCSC Evaluation 26 5.2 Reliability 26 5.2.1 Fault Tolerant Systems 26 5.2.2 Disk Array Technology 27 5.3 Transaction Management (Integrity) 28 5.4 Diagnostics Management 28 6 Summary/Recommendations 29 7 References 31 8 Figures and Tables 33 1 Introduction Federal agencies maintain an increasing amount of valuable and sensitive information in relational database management systems (DBMS). These agencies are required to utilize Federal Information Processing Standard (FIPS) 127-compliant database management systems. FIPS 127 specifies the Database Language SQL (SQL)1 for accessing and manipulating relational databases. SQL requires certain levels of functionality in schema specification, retrieval and modification of data, and transaction management. However, a number of security-relevant areas are not addressed. As a result, SQL-compliant DBMS systems offer varying levels of security functionality. This document examines the various security aspects of SQL. Security-relevant features are identified, in conjunction with the version of the SQL standard that supports them. Critical but unspecified security features are noted, as well as the types of mechanisms that could be offered by vendors. Finally, three broad security policies are examined. The level of support offered by the various SQL versions is contrasted for each policy, and critical controls unspecified by any version of SQL are identified. 1.1 Audience This document is intended to assist information technology (IT) managers in the selection of DBMS's with appropriate security functionality. IT managers with knowledge of security policies and mechanisms, and familiarity with DBMS's will find it most useful. However, the document does not assume that familiarity. Background information regarding both security and databases is included to assist the reader. 1.2 The Standards SQL is a widely used language for accessing and manipulating relational databases. Several levels of SQL are defined; these levels are generally upwardly compatible. Certain security-relevant features are required in an SQL-compliant DBMS. Other security features are not specified by SQL, but may appear in particular products. The exact functionality of an SQL-compliant DBMS varies according to the particular SQL specification and the set of unspecified enhancements which are 1 SQL is not an acronym, although it derives from "Structured Query Language." The complete name is Database Language SQL. 1 also included. The basic SQL definition is ANSI X3.135-1989, "Database Language - SQL with Integrity Enhancement" [ANS89a], and will be referred to as SQL'89. The functionality of SQL'89 includes schema definition, data manipulation, and transaction management. SQL'89 and ANSI X3.168-1989, "Database Language - Embedded SQL" [ANS89b], form the basis for FIPS 127-1 [FIP90]. ANSI X3.135-1992 [ANS92] describes an enhanced SQL, known as SQL'92. The enhancements include schema manipulation, dynamic creation and execution of SQL statements, and network environment features for connection and session management. FIPS 127-2 [FIP90b] is based upon X3.135-1992. Finally, a third version of SQL is currently under development in ANSI and ISO. This version will be referred to as SQL3 in the remainder of this document. SQL3 enhancements will include the ability to define, create, and manipulate user-defined data types in addition to tables. ISO/IEC Draft International Standard 9579-1 [ISO90a] and 9579-2 [ISO90b] define the Remote Database Access (RDA) standard. RDA provides a method for interconnecting database management systems. 9579-1 describes the generic model; 9579-2 presents the SQL specialization information. 1.3 Using This Document Section 2, SQL Architecture, provides an overview of the functionality and interaction of the components of a system supporting an SQL-compliant DBMS. The section includes a survey of the security responsibilities of each component in such a system. Sections 3, 4, and 5 present required features and enhancemants of SQL-compliant DBMS systems.2 These sections are structured to reflect the Security Requirements described in [TCS85]. Section 3 presents mechanisms that can be used to enforce Security Policy. Section 4 addresses Accountability mechanisms. Section 5 includes Assurance measures. (The TCSEC security requirement Documentation is omitted.) The content of these sections does not strictly adhere to the TCSEC security requirements. Items are added or omitted to reflect the requirements of non-DoD federal agencies. 2 "Enhancements" are features which are not specified in the SQL specifications, but are not ruled out by the standard either. The vendor has the option of including such features for product differentiation. 2 Modifications include: o augmenting the integrity requirements in security policy; o omission of covert channels; o inclusion of fault tolerant hardware, such as Redundant Array of Inexpensive Disks (RAID) storage units; o discussion of assurance value of FIPS conformance testing; and o discussion of inference and aggregation. The final section of this paper presents a review of required and optional security features. These features are examined in conjunction with general security choices (e.g., mandatory vs. discretionary access control). The level of support offered by the various SQL versions is contrasted for each of these choices, and critical controls that are not specified by any version of SQL are identified. 1.4 Security Considerations The basic security requirements are, as always, preservation of confidentiality and integrity while maintaining availability. There are a number of specific threats within these categories that merit special consideration here. Inference and aggregation are usually considered threats to mandatory access control policies. There are also a number of DBMS specific security issues, such as referential integrity and polyinstantiation. Classic operating systems problems such as deadlock and transaction completion problems must also be considered. The following definitions will be used in this document: o inference: Derivation of new information from known information. The inference problem refers to the fact that the derived information may be classified at a level for which the user is not cleared. The inference problem is that of users deducing unauthorized information from the legitimate information they acquire.[Thu92] o aggregation: The result of assembling or combining distinct units of data when handling sensitive information. Aggregation of data at one sensitivity level may result in the total data being designated at a higher sensitivity level.[Rob91] o polyinstantiation: Polyinstantiation allows a relation to contain multiple rows with the same primary key; the multiple instances are distinguished by their security levels.[SFD92] o referential integrity: A database has referential integrity if all foreign keys reference existing primary keys.[Cam90] 3 o entity integrity: A tuple in a relation cannot have a null value for any of the primary key attributes.[DJ92] o granularity: The degree to which access to objects can be restricted. Granularity can be applied to both the actions allowable on objects, as well as to the users allowed to perform those actions on the object. An example of polyinstantiation is included in section 3.2, Mandatory Access Control. Examples of inference and aggregation may be found in sections 3.4 and 3.5, Inference Controls and Aggregation respectively. 4 2 SQL Architecture This section begins with a brief description of the functionality of SQL. Secondly, a model of an SQL implementation is presented. Finally, the security problems associated with each component of the model are highlighted. 2.1 SQL Functionality SQL defines standard components and facilities for relational database management systems. The components of an SQL database are schemas, tables, and views. A schema describes the structure of related tables and views. Tables hold the actual data in the database; they consist of rows and columns. Each row is a set of columns; each column is a single data element. Views are derived tables, and may be composed of a subset of a table or the result of table operation (e.g., a join of different tables). The SQL standard describes facilities to perform four specific functions: o Schema Definition: Used to define the structure of the database, integrity constraints, and access privileges; o Retrieval: Retrieve data from a database with a standard query interface; o Data Manipulation: Populate and modify the contents of a database by adding, modifying or deleting rows and columns; o Schema Manipulation3: Modify the structure, integrity constraints, and privileges associated with the tables and views in the database; and o Transaction Management: The ability to define and manage SQL transactions. Each of these components is related to certain security threats. Schema definition and manipulation relate to problems of inference and aggregation. Data retrieval tasks must conform to confidentiality policies. Data manipulation must conform to integrity policy. Transaction management contributes to maintaining the integrity of the database. 2.2 SQL Implementation 3 Schema manipulation is introduced in SQL'92. These facilities are unspecified in SQL'89. 5 To perform the security analysis, it is necessary to assume some architecture for an SQL implementation. Figure 1 depicts a standalone model, which can be implemented with any level of SQL. Figure 2 depicts a client/server model, which can be implemented in a standard fashion with SQL'92 or SQL3 together with RDA (ISO 9579). Client/server implementations with SQL'89 will be proprietary and may not be interoperable with other SQL products without the use of special gateways. The first model shows an application interfacing with an SQL processor, which interfaces with a physical database on a local system. This model can be implemented with any version of SQL. The second model, a simplification of the model presented in [GS92] requiring SQL'92 or SQL3, has the following features: o An application, written in the SQL query language or utilizing embedded SQL, will communicate with an SQL server. o The SQL server may directly access an SQL-compliant database. o The SQL server may access a database that is not SQL-compliant through an appropriate database processor. o The SQL server may act as a client and access a remote database. o The SQL server may access a remote database by utilizing implementation-defined communications software and de facto standards. o The SQL server utilizes the OS services of the local host to access and store data on the system.4 2.3 Security Responsibilities: The SQL Component There are valid security considerations for each of the four areas of SQL functionality: o Database Schema: The database schema must be well designed to ensure that aggregation and inference are not threats. o Retrieval: The SQL server is responsible for maintaining access control for SQL level objects. o Modification: The SQL server is responsible for maintaining access control for SQL level objects. The SQL server is responsible for enforcing type checking and ranges; these are external consistency issues. The SQL server is responsible for enforcing check constraints and uniqueness 4 In certain cases, such as database machines, the system may not have a general purpose operating system. The system will still offer certain services to the SQL layer, though. 6 requirements; these are internal consistency issues. o Transaction Management: The SQL server must ensure orderly access to data when concurrent transactions attempt to access and modify the same data.5 The SQL server must provide appropriate transaction management features: incomplete transactions can result in loss of external consistency6 - the tables and elements are no longer "synchronized." 2.4 Security Responsibilities: Non-SQL Components 2.4.1 Application Interface to SQL The interface between the SQL processor and an application may utilize the embedded SQL language or the SQL language (interactively or invoked by a front-end processor). The application must supply accurate information regarding the identity of the user to the SQL processor. This places two requirements on the system: appropriate selection and management of identification and authentication (I&A) controls and control of this critical attribute's propagation. If the I&A control is weak or poorly managed, there is little assurance of accuracy for this attribute. Consider passwords where the account name and password are identical (a.k.a., "joe accounts"). If an application accesses SQL for such an account, there is an increased probability that the actual user is not the authorized account user. Identity-based controls become severely weakened. Some systems include programs or features that allow users to modify their identity. The UNIX operating system, for instance, includes the file attributes setuid, which re-sets the user id, and setgid, which re-sets the group id. Termination of the program is intended to cause the old user and group id's to resume. However, flaws in the implementation of these features may allow a user to continue to masquerade as the other user, executing SQL programs with unauthorized privileges. 2.4.2 SQL Interface to Physical Database The operating system provides the basic services that enable the SQL processor to store, retrieve, and modify data on the system. The operating system is responsible for guaranteeing the simple 5 Deadlock (and denial of service) is one possible result of such concurrent transactions; loss of data integrity is another. 6 as defined by Clark and Wilson in [CLARK87]. 7 integrity7 of the data and preventing denial of service. The operating system must also prevent data from being accessed outside the SQL processor. Access to raw DBMS files, database export files, or journal files may violate security policy. Such actions can result in loss of integrity (e.g., improper modification of data) or confidentiality (e.g., by circumventing internal access controls of SQL). 2.4.3 SQL Interface to Non-SQL DBMS The SQL interface to non-SQL DBMS's is unspecified in SQL'89. SQL'92 introduces the concepts of an SQL client and an SQL server. By matching an SQL client with a non-SQL server, SQL queries may be performed on non-conforming databases; however, this requires that the non-SQL server provide an SQL-conformant view of its services and data. The interface between a client and server must be protected. Other processes on the system could eavesdrop, insert incorrect information, or perhaps even delete information. These actions would result in loss of integrity or confidentiality. 2.4.4 Interface to Remote Databases The RDA standard is designed as a generic interface between local and remote database servers. RDA also has an SQL Specialization for connecting SQL-compliant databases. Currently, RDA is only defined for use in an OSI network environment. Partially conformant products which use TCP/IP are also available. Use of RDA on an open network may expose the system to many threats. Eavesdropping, packet replay, and host spoofing are likely threats. These threats can be minimized by employing encryption techniques and strong authentication measures. The RDA standard allows for exchange of authentication data. However, it is not required. Encryption techniques may be employed at several different layers of the OSI stack. RDA does not require or forbid such techniques; therefore, the security achieved will be dependent upon the implementation. Where proprietary protocols are used, the system will be exposed to all the same threats as use of RDA. From a security standpoint, the same threats must be addressed. (From an open systems standpoint, there are also interoperability problems.) In addition, if a translator gateway is required this may add a single point of failure to a distributed database architecture. 7 Simple integrity refers to the operating system reading and writing data in a predictable manner. 8 3 Security Policy This section addresses mechanisms for enforcing security policy on computer systems. It concentrates on controlling the access to, and modification of, data. This corresponds roughly to confidentiality and integrity policy although availability can sometimes be affected. The section begins by addressing access control mechanisms. Discretionary and mandatory access controls are examined in turn. Next, the section reviews integrity constraints. This is followed by the more traditional security features of object reuse and labeling. The section closes by examining mechanisms for controlling inference and aggregation. 3.1 Discretionary Access Control Discretionary access control (DAC) is a means by which access to objects is restricted to specific users or groups of users. The access control is discretionary in that access privileges may be passed on to other users, either directly or indirectly, by the owner of the object. 3.1.1 Privileges Privileges are the means by which SQL enforces DAC. Privileges are granted with a Grant statement and are used to specify an allowable action on a specific object, e.g., to UPDATE the rows in a specific table, to a grantee. 3.1.1.1 SQL'89 SQL'89 defines the following five privileges which establish the granularity of access available to users of the database: INSERT, DELETE, SELECT, UPDATE, and REFERENCES. o The INSERT privilege grants a user the ability to create new rows in a base table or a viewed table. If the new row is to be added to a base table, the candidate row must include every column of the base table for which no default value has been either implicitly or explicitly defined. If the candidate row is to be added to a viewed table, the candidate row must include every column in the base table from which the viewed table is derived. Additionally, the view must be updatable. o The DELETE privilege grants a user the ability to delete rows from a table. In order to delete rows from a table or view, the delete privilege needs to have been granted and the view must not be read-only. 9 o The SELECT privilege grants a user the ability to retrieve values from a table. Essentially, the select privilege allows users to read tables. o The UPDATE privilege grants users the ability to update, or change, the contents of a row in a table. In addition, to perform updates on a view, the view must not be read-only. Column constraints can be specified when granting this privilege; that is, a user may be allowed to update certain columns within a table or view. o The REFERENCES privilege grants a user the ability to specify a foreign key reference across schemas. Foreign keys are fields which coincide with a unique field (e.g., primary key fields) in the grantor's table. A security implication of the references privilege is that it can be used, possibly inadvertently, to implement a denial of service attack. When table B references table A, records from table A cannot be deleted or the primary key field cannot be changed if a record from table B corresponds to that record from table A. Another security implication of the references privilege is that a user could, based on the constraint definition, determine all legal values for the referenced column. A policy violation would occur if this information was used to infer knowledge from which a user had been exluded. Privileges can be granted to individuals or to everyone (if PUBLIC is specified). Caution should be used when utilizing the PUBLIC specifier. Additionally, privileges can be granted with the WITH GRANT OPTION. This gives the grantee of a privilege the ability to subsequently grant that privilege to other users. As a result, the owner may loose control over his own table. Finally, privileges can be granted one by one, as a comma separated list, or with the ALL specifier. The ALL specifier, however, refers only to those privileges grantable by that user. A significant security flaw in SQL'89 is the fact that there is no standard way to revoke privileges. As job requirements change, necessary access to the database could also change. It should be noted that the standard does not exclude vendors from incorporating a statement for revoking privileges into an implementation, and many vendors do include such a statement.8 The standard simply does not require such a statement, or specify the semantics of such a statement if included. 8 In fact, the authors do not know of any product which omits a mechanism for revoking privilege. 10 3.1.1.2 SQL'92 A new privilege has been defined for SQL'92. The USAGE privilege is used to allow or restrict access to domains, collations, character sets, and translations. Additionally, all privileges from SQL'89 hold with the following extensions: o The INSERT privilege can be specified with a column constraint as well as on whole tables. (SQL'89 specified column constraints only for the UPDATE and REFERENCES privileges.) o The REFERENCES privilege has several extensions. These extensions maintain referential integrity on delete and update operations in the base table. Instead of a delete or update operation in the base table being blocked because the record is referenced in another table, the reference can be specified with one of the following actions: o The CASCADE specifier will propagate the change from the base table to the referencing table. On update, the update will appear in the referencing table. On delete, matching rows in the referencing table will also be deleted. o The SET NULL specifier will set, for both update and delete, the referencing column in all matching rows to the null value. o The SET DEFAULT specifier will set, for both update and delete, the referencing column in all matching rows to the default value, as specified with the "."" o The NO ACTION specifier performs no referential integrity function. It is included for backward compatibility reasons. It results in the same functionality as SQL'89. When no referential integrity constraint is specified, the NO ACTION specifier is implicit. SQL'92 adds the REVOKE statement. With this statement, all grantable access privileges can be revoked. The revoke statement can also be used to revoke the grant option from a user. As a result that user could no longer grant privileges to other users. 3.1.2 Authorization Identifier For both SQL'89 and SQL'92 , the SQL 11 non-terminal used to specify users (e.g., and ), are defined in an implementation-dependent way. This means SQL does not define how operating system users are mapped to SQL users. See Section 4.1, Identification and Authentication, for more information. 3.1.3 Roles With current versions of the SQL standard, maintenance of appropriate access control restrictions is difficult. The Grant and Revoke statements are available to allocate individual privileges, but this leaves much for an administrator to maintain. A change in job requirements by one user can require many changes to database access for that user. To complicate matters further, if the user whose job requirements changed has granted privileges to other users, those privileges must be examined for correctness. Another drawback of the current privilege system is that a user accumulates privileges required for different job functions. For example, separate job functions for one individual may include payroll clerk and purchasing agent. It may be desirable to allow the user access to only payroll related objects while performing payroll clerk functions and purchasing related objects while performing purchasing agent functions. SQL3, the newest version of SQL being developed, has an enhanced facility for the manipulation of access rights. In addition to the existing Grant and Revoke statements, a new construct, called Roles, is being developed. This facility will allow database administrators to create individual roles with corresponding database access requirements. Then, for example, when a user's job requirements change to no longer include payroll clerk activities, only one Role needs to be revoked instead of revoking access privileges to all objects needed only for payroll clerk activities. An additional benefit of Roles is that a user can have only one Role active at a time. This would allow a user to access only payroll related objects when working under the Payroll Role, and access procurement related objects when working under the Purchasing Role. 3.2 Mandatory Access Control Mandatory access control (MAC) is not supported directly in SQL. However, there are several different methods for implementing a mandatory access control model. The major architectures for trusted DBMS products [Cam90] are the Trusted Computing Base (TCB) Subset Architecture (Figure 6), Trusted Subject Architecture (Figure 7), and Integrity Lock Architecture (Figure 8). Each is intended for use with a Trusted Operating System 12 (OS), but requires different controls. It should be noted that an SQL-based DBMS with mandatory access controls can be designed without modification of the SQL syntax. However, certain modifications in SQL semantics must be made if polyinstantiation is used to control inference. 3.2.1 Polyinstantiation Polyinstantiation is frequently used with mandatory access control database systems to control inference. This section is intended to explain polyinstantiation. Inference, and the application of polyinstantiation for inference control, are described in Section 3.4, Inference. In the following example, the database is a single relational table. The table contains two columns: Patient name and Disease. The Patient name field is the key for this table. There are two clearance levels, HIGH and LOW. Two sets of data exist; the first set is HIGH data (Figure 3) and the second is the LOW data set (Figure 4). The HIGH data include patients under police guard, such as Jackson, or patients with confidential diseases. The LOW data include all other patients, and perhaps some of the HIGH patients with different data. When users with LOW security level browse the database, they are only permitted to see the LOW data. If a user wishes to add a LOW record with primary key X, the command is accepted even if a HIGH record exists with that key. When a user with HIGH security level browses the database, he sees all of the HIGH records, as well as the LOW records with a primary key that is not found in the HIGH data. The resulting table is shown in Figure 5. Note that the record for Howard does not appear twice; only the HIGH level record appears. This feature may be useful in a number of ways. LOW users cannot determine if a HIGH record exists with key Gordon by attempting to create a record and checking for an error message. Dual records could be used, as in the case of Howard, to prevent LOW users from discovering the true nature of Howard's illness. This is intended to prevent disclosure by inference. In many situations, polyinstantiation may be implemented by a local database security administrator using only standard features from SQL'89. The above example is easily implemented with two base tables, known only to the security administrator, and a single view available to all other users. BaseTable1(PatientName,Disease,Level) with Primary 13 Key(PatientName,Level) BaseTable2(UserName,SecurityLevel) with Primary Key(UserName) CREATE VIEW PatientInfo(PatientName,Disease) AS SELECT PatientName,Disease FROM BaseTable1 WHERE BaseTable1.Level = (SELECT SecurityLevel FROM BaseTable2 WHERE UserName = CURRENT_USER ) OR (BaseTable1.Level = "LOW" AND NOT EXISTS (SELECT * FROM BaseTable1 AS X WHERE X.PatientName = BaseTable1.PatientName AND X.Level = "HIGH" ) ) With view optimization techniques available in most SQL-conformant processors, user access through the PatientInfo view should suffer no significant performance penalty over direct access to BaseTable1. This view is always updateable, provided that a DEFAULT value exists for Level in BaseTable1. In SQL'89 and SQL'92, the default clause does not allow a case expansion to determine the default, so one cannot specify in the schema that insert values for Level are HIGH for high level users and LOW for low level users. Instead, one would specify a default and all new inserts would have that Level initially assigned.9 Ensuring that new inserts are all assigned the appropriate security level requires a second view, to be used only by HIGH level users. The second view would assign inserts a HIGH value for Level by default. The first view would have a default of LOW for Level. 3.2.2 TCB Subset Architecture The TCB Subset model implements MAC by maintaining the database in multiple, single-level, files. The operating system enforces the access control policy, restricting the DBMS process to appropriate information. This means that the DBMS does not have to be trusted, so evaluation is simplified. The DBMS might still enforce privilege based access controls, but would not enforce MAC policy. If no DAC policy is required, all users would have 9 The emerging SQL3 specification includes facilities that easily get around this problem. 14 all privileges for all tables. The TCB Subset model implies polyinstantiation. If a record r1 exists with keys K, but is classified SYSTEM HIGH, a SYSTEM LOW user cannot see it. If a SYSTEM LOW user attempts to add a record r2 with keys K, the system must do so. Now the DBMS has two records with the same set of keys in the same table. SYSTEM LOW users will see r2. SYSTEM HIGH users will see r1 instead; r2 is considered incorrect. This can reduce inference problems, but results in a variety of integrity problems. The data in the two records may become out-of-date. Then if one record is changed, but the other is not, the DBMS loses integrity. Note that this is quite complicated if categories are being utilized. The TCSEC suggests that MAC systems support a minimum of eight security levels and 256 categories. Since the files must support combinations of categories for each level, the number of files is unmanageable. To avoid this problem, the DBMS could have to enforce the category aspect, but this defeats the purpose of the architecture: the DBMS process must be a trusted process. 3.2.3 Trusted Subject Architecture A Trusted Subject Architecture DBMS enforces both MAC and DAC. The database is stored on the system as SYSTEM HIGH OS objects. Within those files, DBMS objects are labeled according to security policy. Those labels are used as the basis for MAC enforcement. DAC enforcement is based upon the usual SQL DAC specifications. The Trusted Subject Architecture does not imply or rule out polyinstantiation. Support for polyinstantiation must be built in if it is required. 3.2.4 Integrity Lock Architecture The Integrity Lock Architecture uses an untrusted DBMS in conjunction with a trusted OS and trusted filter to enforce security policy. The DBMS could enforce DAC policy, but MAC policy and labeling would be enforced by the trusted filter. Encryption and cryptographic checksums are employed to protect the security label from modification. The Integrity Lock Architecture implies support for polyinstantiation. This architecture allows use of off-the-shelf DBMS software. It has disadvantages due to high overhead. These are the most common architectures for MAC DBMS's, but are not the only ones. They can also be combined to some extent. For instance, the Integrity Lock Architecture could be used to add category enforcement to the TCB Subset Architecture. 15 3.3 Schema Manipulation SQL'92 allows manipulation of the schema itself, rather than just the data. Columns and constraints on columns can be added or removed from tables. Additionally, schema object, such as domains and constraints, can be altered or deleted. Also new to SQL'92 are schema definition tables. These tables are created by the SQL processor and are treated as views, in that they can be accessed but not directly changed in SQL. 3.4 Integrity Constraints Data integrity is addressed by a variety of data constraints specified in the database schema. These constraints describe relationships between tables, relationships between rows in a table, and permissible values for elements. Relationships between tables, or between rows in a table, are known as table constraints. Range checks and other specifications for data values are element constraints. 3.4.1 Table Constraints 3.4.1.1 SQL'89 SQL'89 defines three types of integrity constraints that may be placed upon tables. These constraints are: o unique constraints; o referential constraints; and o check constraints. SQL'89 also defines with CHECK option on views. A unique constraint definition specifies a list of columns in the table T. T cannot contain multiple rows where the values of each of the corresponding columns are identical. Each of the listed columns must be defined as NOT NULL. Example: Assume T has columns {a,b,c,d} and is constrained for uniqueness on {a,c,d}. Row Ti has values {ai,bi,ci,di}. T meets the constraint if there are no rows Tj and Tk such that {aj = ak, cj = ck, and dj = dk}. In Table 1, T-1 meets the constraints, but T-2 does not. Rows T1 and T4 are not unique for the specified columns. A referential constraint definition specifies columns in T which reference keys in another table F. If all specified columns in a row of T are non-null, then a row in F must exist such that all corresponding columns match. The table has referential integrity if every row meets this criteria, or has a null value in the 16 specified columns.10 A check constraint definition specifies a condition which all rows in T must satisfy. The condition may restrict a column, or may restrict relationships between columns. (For example, within a row MAX-TEMP >= MIN-TEMP.) The condition is ternary; it may evaluate to "true," "false," or "unknown." The condition may specify illegal values or legal value ranges. The table does not satisfy the check condition if and only if there exists a row for which the condition evaluates to "false." Views with check options are similar to check constraints upon tables. However, the constraint is satisfied if and only if the condition evaluates to "true." 3.4.1.2 SQL'92 SQL'92 enhanced referential constraints with the MATCH FULL and MATCH PARTIAL specifications. If no match type is specified, the functionality is identical to SQL'89. If MATCH FULL is specified, then for each row in T: o all referencing columns must be null; OR o all referencing columns must be non-null and there must be a row in F such that all corresponding referencing columns are equal value. If MATCH PARTIAL is specified, then for each row in T: o there must be a row in F such that all corresponding referencing columns are equal value, or a referencing column value in T is null. 3.4.2 Column Constraints and Check Constraints SQL'89 defines six types of integrity constraints that may be placed upon columns. These constraints are: o data type; o precision; o references specification; o default clause; o CHECK constraint definition; and o NOT NULL. The defined data types for SQL'89 are character strings and numbers. The character strings are specified with a fixed length. 10 If one or more specified columns in the row are null, correspondence can not be established. 17 Numbers may be exact numeric values or approximate numeric values. Exact numeric values include integers and real numbers with a precision and scale. A real number of exact numeric value is a string of decimal digits of length precision. The exact numeric value is the integer value of the significant digits multiplied by 10-scale. Approximate real numbers have an exponent and mantissa. The mantissa is a signed numeric value; the exponent is a signed integer that specifies the magnitude of the mantissa. Approximate real numbers have precision; precision is a positive integer specifying the number of binary digits in the mantissa. Integers come in two sizes. Real numbers can be defined in five different ways, allowing the database designer to create fields tailored for the data. The references specification allows the specification of a referential integrity clause for a single column (rather than a list of columns). Any non-null value entered into that column must exist in the corresponding table and column. Default clauses may be stated explicitly or implicitly. SQL'89 permits default values of NULL, USER, or a literal (constant of appropriate data type). Additional default options, such as , are available in SQL'92. A CHECK constraint definition specifies a condition which the column element must satisfy in each row. This differs from the table check constraint; the condition can only involve the specified column element. Again, the condition is ternary; it may evaluate to "true," "false," or "unknown." The condition may specify illegal values or legal value ranges. The table does not satisfy the check condition if and only if there exists a row for which the condition evaluates to "false." The NOT NULL constraint is an implicit check constraint. It corresponds to CHECK IS NOT NULL. SQL'92 adds two new data types, datetime and interval. The type datetime includes DATE, TIME, and TIMESTAMP. The type interval allows specification of a year-month or day-time interval. Variable length character strings are also added in SQL'92, and the character set for the character string may also be defined. User-defined data types are under consideration for SQL3. 3.4.3 Assertions In SQL'92, assertions enhance the SQL'89 check constraints. Assertions are named constraints that "may relate to the content 18 of individual rows of a table, the entire contents of a table, or to a state required to exist between a number of tables." [FIP93] This is a significant enhancement, since SQL'89 check constraints applied to column(s) in a single row. (In SQL'92, check constraints are allowed to contain subqueries.) 3.4.4 Domains Domains were introduced in SQL'92. Domains are a significant enhancement for data abstraction used to specify a set of permissible values. Domain definitions can also be used to enforce a variety of format constraints, such as position of hyphens in a date field. 3.4.5 The SQL'89 Security Bug SQL'89 allowed a user with UPDATE or DELETE privileges to use WHERE clauses even if they did not have SELECT privileges. This allowed users to "probe" the system for data they were not privileged to have. They could confirm the existence of a record with certain column values even though they could not directly read the record. This bug was fixed in an SQL'89 Errata and is specified correctly in SQL'92. Note that a workaround exists for older SQL'89 systems. By defining updatable views, the columns users can access may be limited to the appropriate subset of the data. In any case, a user with DELETE privileges will be able to determine values of primary keys. 3.5 Object Reuse Object reuse is defined in [Rob91] as: The reassignment to some subject of a medium (e.g., page frame, disk sector, magnetic tape) that contained one or more objects. To be securely reassigned, such media must contain no residual data from the previously contained object(s). SQL'89 and SQL'92 have no specification regarding object reuse. In order to accommodate object reuse issues, both the SQL processor and the operating system will need to address the issue jointly. The operating system is responsible for deallocating system resources, such as files used to store whole tables, and the SQL processor is responsible for deallocating SQL objects, such as individual rows of tables. To maintain confidentiality, data stored in these resources and objects must be zeroed out or replaced with random data before reassignment. 3.6 Labels 19 Mandatory access controls require support for security labels. These labels are used as the basis for access control decisions. In order to correctly label data, the system must request and receive the security level of data. This can be accomplished in several ways. On a trusted system, the user may specify the security level of each session. (The specified security level must be "less than" or equivalent to the user's clearance level, of course.) That information would be passed to the DBMS, and all input would be labeled at that level by default. Alternatively, the DBMS could include a mechanism to request and set the current level interactively. If the DBMS is not trusted, as in the Integrity Lock Architecture, all mechanisms regarding labeling will be placed in the trusted filter or operating system. In addition, if the DBMS labels DBMS objects with greater granularity than OS objects, the DBMS must maintain label integrity. For instance, if all DBMS objects are stored in a single SYSTEM HIGH OS object, as in Figure 7, then the DBMS must maintain labels for the DBMS objects. This is in contrast to Figure 6, where the OS keeps track of all labeling information. 3.7 Inference Consider a research hospital, which has a database of doctors and patients. Patient information includes address, Social Security number, doctor name, known allergies, current prescriptions, and scheduled appointments. Each patient's medical history is kept on-line in a series of medical records. Scheduled hours, appointments, and specialty are associated with each doctor. This database is used for scheduling appointments, billing patients, and filling prescriptions. The hospital wishes to protect the patients' anonymity, and prevent disclosure of their ailments to anyone other than a patient's individual doctor. For this reason, the average user is not allowed to access the patient history/medical records. However, the database may still be vulnerable to disclosure through inference. o Doctors generally specialize in the treatment of particular diseases. It may be possible for hospital staff to infer a patient's ailment from the identity of the doctor. This could be determined by viewing the patient information or the doctor's schedule. o Drugs are generally associated with the treatment of particular diseases. It may be possible for hospital staff to infer a patient's ailment from the prescription. 20 Polyinstantiation is the key method used in these situations for limiting inference in multi-level secure systems. 3.8 Aggregation Consider a database of parts for a missile. Each part's information includes sufficient information for a manufacturer to fabricate the part. This information would include materials, physical geometry, and finishing treatment(s). (A screw might be described as follows: steel; 1 x 8 pan head with fine left-hand threads; rust-inhibiting paint). In addition, the database includes assembly information and the quantity of each part required to assemble one missile. Each part is unclassified. The combined schematic and missile design are classified as SECRET. If each manufacturer is limited to accessing a few part descriptions, he will not learn anything about the missile itself. However, if the manufacturer can access the entire database, they may be able to figure out how to build the missile.11 To limit aggregation, one should limit access as tightly as possible. Inference is a problem that is derived primarily from poor database design. There are several methods for detecting and reducing the potential for disclosure by inference, including those described in [Thu92]. These methods can be used in conjunction with SQL, but could not be performed within the confines of SQL itself. These methods require additional information regarding the relationships between the elements of different relations. The inference problem gets the most attention in MAC environments, but can occur in DAC systems as well. Fortunately, the same tools should apply to DAC systems. The aggregation problem occurs when two pieces of information A and B are classified at level X individually, but level Y (Y higher than X) collectively. This problem may in fact be insolvable. Denying access to A if User 1 with clearance X has already viewed B would require an infinite history, and quickly leads to inference problems. Aggregation is primarily a MAC problem. 11 It is a lot like assembling a bicycle on Christmas Eve without directions. You know what a bicycle looks like and you have a pile of parts. You just try to use them all. 21 4 Accountability Accountability is not addressed in the SQL specifications. Accountability is primarily achieved with two classes of mechanisms: identification and authentication controls; and auditing. 4.1 Identification & Authentication label Identification and authentication (I&A) mechanisms are not specified in SQL. However, they are required implicitly. The DAC mechanisms all assume that such information is available. Such mechanisms are usually provided by the host system, and the information is passed to the SQL processor. (In SQL'89 this is implementation defined; in SQL'92 it is performed via the CONNECT statement.) The quality of this information will vary according to authentication technique and when that authentication is performed. In the simplest case, the user authenticates his identity to the system at logon. That information is maintained throughout the session. The information is passed to the DBMS when the DBMS is accessed. The strength of authentication varies with the type, implementation, and management of the authentication mechanisms. That information may be incorrect by the time the DBMS is accessed. The user may have left the terminal or workstation unattended, and another person may actually be at the keyboard. The information may be improved if the user re-authenticates when the DBMS session begins. A stronger method requires re-authentication with every transaction. This is too burdensome for systems relying on passwords, but smart card based systems can support this requirement. This method provides high assurance that the user identification was correct at the time the transaction was initiated. If such mechanisms are not supplied by the host system, the SQL processor could incorporate its own I&A mechanism. However, the lack of I&A implies a lack of access control by the host. In such a case, the processor would have to utilize encryption to protect the data. That is, a host without access control requires a DBMS based upon the Integrity Lock Architecture. Selection of I&A mechanisms must be tempered with common sense, of course. If passwords will be used to authenticate both system and DBMS session, the same mechanism (and password) should be used. Requiring users to remember multiple passwords is likely to result in misuse (e.g., they will write them down). Both passwords and biometrics are inappropriate for authenticating 22 transactions; the burden upon the user is too great. 4.2 Auditing Auditing concerns for trusted database systems are described in [SFD92] as follows: Auditing of security-related activities is required in [trusted] DBMS's. Security-relevant events include logins, granting and revoking of access permissions to relations, etc. The level at which auditing needs to be done is variable. The performance effects of the optional auditing features should be carefully examined, since their use may be a significant factor in the performance of data management functions. This statement is equally applicable to all database systems. The SQL specification does not include auditing requirements, but SQL products may include some auditing functionality. If the SQL processor includes auditing, the OS must have sufficient access controls to prevent modification of, or access to, the audit trail. Warning mechanisms are closely related to auditing requirements. Such mechanisms notify the system or DBMS administrator if critical events occur. (An example might be an attempt to access tables without sufficient privilege.) Again, SQL has no requirement for such mechanisms, but processors may include them. 23 5 Assurance Assurance describes a broad range of mechanisms and procedures. These mechanisms and procedures address the behavior of the system. A system with high assurance is more likely to operate as expected than a system with low assurance. Unexpected behavior is generally the result of hardware failure or software bugs. Hardware failure may be subtle (such as data transmission errors) or catastrophic (such as a disk crash). Software bugs may be in the OS, DBMS, or locally developed applications. This section examines four areas of assurance: o testing and evaluation; o reliability of hardware; o SQL transaction management; and o SQL diagnostic reporting. 5.1 Testing and Evaluation Two primary sources for assurance that software functions as expected are testing and formal evaluation of software. Testing is performed by supplying inputs to the DBMS while it is in various states, and analyzing the results. Formal evaluation involves review of design specifications and code as well as testing. Testing can be very informal, consisting of execution of a few test applications, or quite rigorous. The features that are specified in the versions of the SQL standard can be tested in a very structured fashion to demonstrate compliance to the standard. This is known as conformance testing. FIPS conformance testing is a primary source of this information. NIST maintains the NIST SQL Test Suite for validating conformance to FIPS SQL. Formal evaluation involves reviewing the architecture, source code, and documentation to detect any flaws in the system. This process may include formal verification of design and program correctness. Formal evaluation by third parties currently concentrates upon security functionality in general, and confidentiality in particular, due to the expense. However, the process could be performed against any standard or standard set of criteria. The primary source for formal evaluations by third parties has been the National Computer Security Center (NCSC). The NCSC performs evaluations of systems and subsystems against the Department of Defense Trusted Computer System Evaluation Criteria (TCSEC), which is commonly known as the Orange Book. Other organizations providing formal evaluations are the European Community evaluations against the Information Technology Security Evaluation Criteria (ITSEC) and the Canadian Security 24 Establishment evaluations against the TCSEC and Canadian security criteria. In each case, the evaluations are limited to security-relevant features (as defined by the particular criteria). Conformance testing and security evaluation of SQL processors are complimentary processes. For example, NCSC evaluations do not look at code that falls outside the "trusted computing base" of security-relevant code. As a result, NCSC evaluation will not review many areas of SQL functionality that are reviewed in FIPS conformance testing. The TCSEC supplies criteria in many areas where the SQL standard is silent, so NCSC evaluations will cover many areas that are outside the scope of conformance testing. 5.1.1 FIPS Conformance FIPS 127-1 specifies ANSI X3.135-1989, "Database Language - SQL with Integrity Enhancement", and X3.168-1989, "Database Language - Embedded SQL".12 FIPS conformance testing provides assurance that the features specified in the SQL standard function as expected. This testing addresses the entire range of SQL functionality, not just security. Conformance testing is performed by executing a suite of test programs and evaluating the results.13 Conformance testing does not review the code itself. Security flaws such as trapdoors or bugs in the code may not be detected. The evaluation is platform independent, so platform dependent security problems will probably go undetected. Many security concerns, such as covert channels, are simply not an issue in this type of testing. Conformance testing is also "flat" with respect to MAC. The database is defined, populated, and queried at a single security level. This makes sense, since SQL does not define any functionality regarding MAC. However, polyinstantiation would violate SQL's uniqueness clauses, but is not detected because of the single level testing method. SQL'92 has been approved as both ANSI and ISO standards. FIPS 127-2, which adopts ANSI X3.135-1992, was approved in June 1993. Conformance tests have been developed only for Entry Level SQL'92, because it represents a minor enhancement over the SQL'89 tests. Conformance testing for Intermediate and Full SQL'92 represent a major development effort which has not yet begun. 12 FIPS 127-1 does not require all features of SQL'89: see the standard itself for details. 13 The conformance testing procedures are described in \cite{cals:sql}. 25 5.1.2 NCSC Evaluation The TCSEC and [TDI91] provide the basis for NCSC security evaluations of trusted database management systems. These evaluations consider security policy14, accountability, audit, and assurance. NCSC evaluations are platform dependent, and result in certification of a DBMS for use on a particular computer system, with a particular operating system. A number of issues, such as covert channels, are addressed where results are invalidated by changes in the platform. Evaluations may consider a wide range of assurance levels. At higher levels, NCSC evaluations include review of security relevant code (the TCB). At the highest levels, NCSC evaluations require formal verification of all code in the TCB. 5.2 Reliability The most valuable asset in a database system is often the data itself. Loss of access to this data may be measured per minute in some cases. This loss, known as denial of service, may arise from a number of situations. One of these is the physical failure of hardware. Insuring reliability of hardware is the primary technique to address hardware failure. Fault tolerant systems address system failure; disk array technology can be used to address storage media failure. Fault tolerance is not required by any SQL specification, but is a feature of certain SQL implementations. 5.2.1 Fault Tolerant Systems If the hardware platform itself is down, there will be no access to the system. Fault tolerant systems are designed to continue correct operation in the event of failure of any single component. They typically exhibit both redundancy physically and conceptually (two or more CPU's, buses, disk controllers, etc.) and perform fault-detection tests with error-detecting codes, disagreement detectors, and self-checking logic circuits. They rely on disk array technology and WAFER storage technologies15 for fault tolerance in their peripherals. Many systems are not designed to be completely fault tolerant, but include redundancy at common points of failure. These 14 The TCSEC security policy criteria is weak in the area of integrity policy. 15 WAFER storage are solid state storage subsystems. They are expensive, but have performance advantages when compared to disk array peripherals. 26 systems might be called fault resistant. For example, a disk cabinet with four disk drives might include redundant power supplies. A single power supply would be sufficient to run the disks; however, a power supply failure would take them all out of service. Redundant supplies mean two power supply failures are required to halt the systems. This feature does not make the disk drives in the cabinet fault tolerant; a single drive failure will result in loss of that disk's data and storage. It does make failure due to power supply less likely. Fault tolerance for disks requires use of disk array technology. 5.2.2 Disk Array Technology Disk array technology uses several disks in a single logical subsystem. Disk arrays were introduced in [KGP89], which described six classifications for "Redundant Array of Inexpensive Disks," or RAID systems. They were numbered RAID-0 through RAID-5. RAID-1 through RAID-5 offer varying degrees of security functionality.16 5.2.2.1 Disk Shadowing To reduce or eliminate downtime from disk failure, DBMS servers may employ disk shadowing or data mirroring. A disk shadowing, or RAID-1, subsystem includes two physical disks. User data is written to both disks at once. In this case, if one disk fails, all of the data is immediately available from the other disk. Disk shadowing incurs some performance overhead (during write operations) and increases the cost of the disk subsystem since two disks are required. However, the major problem with RAID-1 is the 50% disk overhead; for every 100 megabytes of disk space, 200 megabytes are required. 5.2.2.2 RAID-2 through RAID-4 RAID levels 2 through 4 are more complicated than RAID-1. Each involves storage of data and error correction code (ECC) information, rather than a shadow copy. Since the error correction data requires less space than the data, the subsystems have lower disk overhead. Each level has its own performance implications. 5.2.2.3 RAID-5 RAID level 5 involves storage of data and error correction information but does not require a dedicated shadow or ECC disk. 16 RAID-0 provides only balanced performance. RAID-6 and RAID-7 have also been proposed since that time. 27 RAID-5 has good performance characteristics, since it has the ability to read and write in parallel. Unfortunately, RAID-5 implementations work best with large numbers of physical disks (10 to 20+) which rules out small to mid-sized disk subsystems. 5.3 Transaction Management (Integrity) A database may be in a consistent or inconsistent state. A consistent state implies that all tables (or rows) reflect some real-world change. An inconsistent state implies that some tables (or rows) have been updated but others still reflect the old world. Transaction management mechanisms are applied to ensure that a database remains in a consistent state at all times. These mechanisms allow the database to return to the previous consistent state if an error occurs. Statements available in SQL'89 for transaction management include the rollback and commit statements. These statements are used to terminate transactions. SQL3 adds the concept of savepoints. o The rollback statement terminates a transaction and cancels all changes to the database, including data or schema changes. This returns the database to the previous consistent state. o The commit statement terminates a transaction and commits all changes to the database, including both data or schema changes. This makes the changes available to other applications. If a commit statement cannot complete successfully, for example a constraint is not met, an exception is raised and an implicit rollback is performed. Note that both statement rejects and transaction rollbacks are permitted by the SQL standard. o The savepoint feature allows a user to mark points in a transaction, creating subtransactions. With this feature, a user can rollback portions of a transaction without affecting other subtransactions. For examples of savepoints and subtransaction management see [Gal91, pages 23-24]. 5.4 Diagnostics Management SQL'92 SQL'92 adds a new area of functionality: diagnostics management. This standardizes the return codes and completion codes for SQL statements. This may have some security functionality, especially when combined with external procedures. 28 6 Summary/Recommendations SQL-compliant DBMS's can be applied to any scenario, no matter what security policy is required. However, not all SQL-compliant DBMS's will be appropriate for every security policy. Many critical features are not specified by SQL; others are specified in one version of SQL but omitted from earlier versions. The systems acquisition phase must begin with a clear and concise statement of the security policy. The exact features required will be a function of that policy. This section provides a short review of security features that may be found in SQL-compliant DBMS's. These features are classified as unspecified or required. Where required, the version of SQL is specified and a brief summary of the functionality is provided. For unspecified features, the feature is classified as an OS, hardware, or DBMS feature. The remainder of this section discusses the various features and provides guidance regarding their relative importance. Table 2 summarizes the types of security-relevant controls that might be offered in an SQL-compliant DBMS. Controls are grouped according to security requirements. For each control, a variety of mechanisms is listed.17 Each control is either required by a version of SQL, or represents unspecified functionality. If the control was a new requirement, the Status field will state required. If functionality is added to that requirement in later versions of SQL, the Status field will say enhanced. If the control is probable for SQL3, the Status will be planned. Note that functionality denoted unspecified or planned may exist in products today; however, implementations will be no-standard. SQL processors can support a variety of security policy mechanisms. In the area of security policy, the most important decision regards the type of access controls desired. If discretionary controls are desired, SQL'89 does include powerful controls. SQL'92 significantly enhances these controls with the specification of the privilege revocation mechanism. Roles will be a significant enhancement for SQL3. This functionality may be available in SQL processors even before the SQL3 specification becomes stable. Mandatory controls are not specified in any version of SQL but can be supported by SQL implementations. The SQL integrity constraints are powerful tools for enforcing and maintaining integrity. SQL'89 includes a powerful suite of integrity constraints. SQL'92 does include enhancements such as 17 In some cases, controls map to mechanisms on a one-to-one basis. For example, mandatory access control is basically performed one way. In these cases, the mechanism field is omitted. 29 assertions and domains. These constraints may be used to enforce internal or external consistency constraints. If the system will utilize MAC, design tools should be obtained to limit the threat of aggregation. Whether MAC or DAC policies are envisioned, inference and object reuse are threats to confidentiality. Inference is addressed through add-on tools; object reuse must be addressed within both the DBMS and OS. Accountability is unspecified in SQL, but the choices are of great importance. Most important is the selection of appropriate authentication points. Should each transaction require re-authentication, or is the session information sufficient? The type of authentication mechanism is also important, but falls outside the SQL specification as well. Auditing and warning mechanisms are similarly unspecified but required for any reasonably secure system. These mechanisms, especially auditing mechanisms, depend upon the identity previously authenticated. They are of limited value if authentication is weak. Finally, consider assurance mechanisms. If the system will rely on SQL-specified DAC functionality, SQL conformance testing may be sufficient. If the system will rely upon MAC controls, evaluation may be more applicable. Fault tolerance is an expensive option, but may be justified if the value of the data is correspondingly high. Disk array technology provides fault tolerance for data storage. Transaction management features can add assurance that applications are well-behaved, and the database remains in a consistent state. These features add assurance only if they are used consistently and appropriately. If the concept of consistent state is not well understood for the database in question, it will be difficult to use these features appropriately. 30 7 References [ANS89a] Database language - SQL with integrity enhancements. American National Standard X3.135, American National Standards Institute, 1989. [ANS89b] Database language - embedded SQL. American National Standard X3.168, American National Standards Institute, 1989. [ANS92] Database language SQL. American National Standard X3.135-1992, American National Standards Institute, 1992. [Bur89] Rae K. Burns. "DBMS integrity and security controls" In Report of the Invitational Workshop on Data Integrity}. NIST Special Publication 500-168, 1989. [Cam90] John Campbell. "A brief tutorial on trusted database management systems (executive summary)" In 13th National Computer Security Conference Proceedings, 1990. [CO92] S.J. Cannan and G.A.M. Otten. "SQL - The Standard Handbook" McGraw-Hill Book Co., Berkshire SL6 2QL England, October 1992. [DD92] C.J. Date and Hugh Darwen. "A Guide to the SQL Standard" Addison-Wesley Publishing, Reading, MA 01867 USA, October 1992. [DJ92] Vinti M. Doshi and Sushil Jajodia. "Enforcing entity and referential integrity in multilevel secure databases" In 15th National Computer Security Conference Proceedings, 1992. [FIP90] Database language SQL. Federal Information Processing Standard 127-1, National Institute of Standards and Technology, 1990. [FIP93] Database language SQL. Federal Information Processing Standard 127-2, National Institute of Standards and Technology, June 1993. [Gal91] Leonard Gallagher. "SQL3 support for {CALS} applications" NISTIR 4494, National Institute of Standards and Technology, February 1991. [GS92] Leonard Gallagher and Joan Sullivan. "Database language SQL: Integrator of {CALS} data repositories" NISTIR 4902, National Institute of Standards and Technology, September 1992. [ISO90a] Remote database access - part 1: Generic model. ISO/JTC1/SC21 N4282, Information Processing Systems - Open Systems Interconnect, 1990. [ISO90b] Remote database access - part 2: SQL specialization. 31 ISO/JTC1/SC21 N4281, Information Processing Systems - Open Systems Interconnect, 1990. [KGP89] Randy H. Katz, Garth A. Gibson, and David A. Patterson. "Disk system architecture for high performance computing" In Proceedings of the IEEE, Vol. 77, No. 12. Institute of Electrical and Electronics Engineers, 1989. [MS92] Jim Melton and Alan Simon. "Understanding the New SQL: A Complete Guide" Morgan Kaufman Publishers, San Mateo, CA 94403 USA, October 1992. [Rob91] Edward Roback. "Glossary of computer security terminology" NISTIR 4659, National Institute of Standards and Technology, September 1991. [SFD92] Linda M. Schlipper, Jarrellann Filsinger, and Vinti M. Doshi. "A multilevel secure database management system benchmark" In 15th National Computer Security Conference Proceedings, 1992. [TCS85] Trusted computer system evaluation criteria. DOD 5200.28-STD, National Computer Security Center, December 1985. [TDI91] Trusted database management system interpretation. NCSC-TG 021, National Computer Security Center, April 1991. [Thu92] Bhavani Thuraisingham. "Knowledge-based inference control in a multilevel secure database management system" In 15th National Computer Security Conference Proceedings, 1992. [Wag89] Grant Wagner. "System services - group 3 report" In Report of the Invitational Workshop on Data Integrity. NIST Special Publication 500-168, 1989. 32 8 Figures and Tables ---------------------- | Application | ---------------------- | | V ---------------------- | SQL Processor | ---------------------- | | V ------------- | SQL | | Database | ------------- Figure 1. Standalone model 33 ---------------------- | Application | ---------------------- | | V ---------------------- RDA ------------------ | SQL Processor | -------------------> | Remote | ---------------------- | Database | | | | Server | | | ------------------ | V | ------------ | | non-SQL | | | processor|----- | ------------ | V V ------------- ---------------- ---------------- | SQL | | non-SQL | | remote | | data | | data | | data | ------------- ---------------- ---------------- Figure 2. Client/Server model Patient Name | Disease ========================= Howard | AIDS Gordon | Syphilis Jackson | Gun shot Figure 3. High Data Patient Name | Disease ========================= Smith | Lung Cancer Howard | pneumonia Jones | 2nd Degree Burns Hamp | heart failure Figure 4. Low Data Patient Name | Disease ========================= Howard | AIDS Smith | Lung Cancer Gordon | Syphilis Jackson | Gun shot Jones | 2nd Degree Burns Hamp | heart failure Figure 5. High user's view 34 High User Low User /^\ /^\ | | | | V V ------------------------------- | ------------- ------------ | | | High DBMS | | Low DBMS | | | | Process | | Process | | | ------------- ------------ | ------------------------------- | trusted operating system | ------------------------------- /^\ \ /^\ | \ | | \ | V \ V high low database database file file [Note: that should be a line the low dbms file to the high dbms process] Figure 6. TCB Subset Architecture 35 User Application /^\ | | V ---------------------------- | trusted subject dbms | ---------------------------- | trusted operating system | ---------------------------- /^\ | | V database file (high) Figure 7. Trusted Subject Architecture Single-Level User Single-Level User Front End (Low) Front End (high) /^\ /^\ | | | | V V ---------------------------- | untrusted front end | ---------------------------- | trusted filter | ---------------------------- | trusted operating system | ---------------------------- /^\ | | V database file (high) Figure 8. Integrity Lock Architecture 36 -------------------------- -------------------------- | T-1 | | T-2 | -------------------------- -------------------------- | Ti | a | b | c | d | | Ti | a | b | c | d | -------------------------- -------------------------- | T1 | 2 | 2 | 3 | 4 | | T1 | 2 | 2 | 3 | 4 | -------------------------- -------------------------- | T2 | 2 | 2 | 4 | 4 | | T2 | 2 | 2 | 4 | 4 | -------------------------- -------------------------- | T3 | 2 | 2 | 3 | 5 | | T3 | 2 | 2 | 3 | 5 | -------------------------- -------------------------- | T4 | 6 | 2 | 3 | 4 | | T4 | 2 | 6 | 3 | 4 | -------------------------- -------------------------- Table 1: Uniquesness Examples: contents of T-1 and T-2 37 Security | Security Mechanisms | Status | Where It's Requirements | Class | Mechanism | In SQL | Found ================================================================ Security | DAC | Privileges | required | SQL'89 Policy | | | enhanced | SQL'92 | |------------------------------------ | | Roles | planned | SQL3 |-------------------------------------------------- | Mandatory Access Control | not | DBMS and OS | | specified | |-------------------------------------------------- | | Constraints| required | SQL'89 | Integrity | on Tables | enhanced | SQL'92 | |------------------------------------ | Constraints | Constraints| required | SQL'89 | | on Columns | enhanced | SQL'92 |--------------------------------------------- | Inference | not | DBMS add-on | | specified | tools |--------------------------------------------- | Aggregation | not | DBMS add-on | | specified | tools |--------------------------------------------- | Object Reuse | not | design of | | specified | OS and | | | DBMS ------------------------------------------------------------- Account- | | authenti- | not | OS ability | Identifi- | cation of | specified | dependent | cation | system | | | | session | | | and ------------------------------------ | | authenti- | not | DBMS | Authenti- | cation of | specified | implemen- | cation | DBMS | | tation | | session | | dependent | ------------------------------------ | | authenti- | not | DBMS | | cation of | specified | implemen- | | each trans-| | tation | | action | | dependent -------------------------------------------------- | Auditing | Journal | not | design of | | generation | specified | DBMS | ------------------------------------- | | Journal | not | design of | | Protection | specified | OS Table 2, Part 1 Security Features in SQL Standards 38 ---------------------------------------------------------------- Assurance | Software | SQL | not | FIPS con- | | features | specified | formance | | | | tests | |------------------------------------ | Quality | Security | not | NCSC | | features | specified | evaluation -------------------------------------------------- | | processor | not | hardware | | | specified | and OS | |------------------------------------ | Fault | disk | not | hardware | Tolerance | shadowing | specified | and OS | |------------------------------------ | | disk | not | hardware | | mirroring | specified | and OS |-------------------------------------------------- | Transaction Management | required | SQL'89 ---------------------------------------------------------------- Table 2. cont'd. Security features in SQL standards. 39