Database Security

The goal of database security is to protect your critical and confidential data from unauthorized access. Access in this context means not only changing or deleting the data in your database, but even just reading or disclosing it. Database security should provide controlled, protected access to the contents of your databases and, in the process, preserve the integrity, consistency, and overall quality of your data. You must protect your data, because it is a valuable, major organizational resource.

When discussing security, it is important to note the various threats to your data. Some threats are accidental, but they can lead to the disclosure, deletion, or destruction of the data in your databases. These threats include software, hardware, and human errors. However, attempts to deliberately bypass or violate the security facilities are by far the biggest security threats. Such attempts include the following:

• Unauthorized stealing, copying, changing, corrupting, or browsing through stored data.
• Electronic bugging of communication lines, terminal buffers, or storage media.
• Sabotaging, which can include erasing and altering the data, deliberately inputting erroneous data, or maliciously destroying the equipment or the storage media.
• Personnel aspects, such as position misuse, false identification, blackmail, bribery, or transferred authorization (where users can obtain other passwords).
• DBAs avoiding or suppressing the security facilities.
• Shared programs performing functions not described in their specifications by taking advantage of the rights of their environment.
• Masquerading, such as when a program poses as the operating system or the application, to obtain user passwords.

The approaches, techniques, and facilities you use for security control must cover external (or physical) security control as well as internal (computer system) security control. The external security controls include access control, personnel screening, proper data administration, clean desk policies, waste policies, and many, many more. However, this month I focus on the internal controls that ensure the security of the stored and operational data. These include the following:

Access controls: Ensure that only authorized accesses to objects are made -- doing so specifies and enforces who may access the database and who may use protected objects in which way. Authorization is often specified in terms of an access matrix, consisting of subjects (the active entities of the system), objects (the protected entities of the model), and access rights, where an entry for a [subject, object] pair documents the allowable operations that the subject can perform on the object. Two variations on access matrices are authorization lists and capabilities. Authorization lists or access-control lists (per object) specify which subjects are allowed to access the object and in what fashion. Capabilities are [objects, rights] pairs allocated to users; they specify the name or address of an object and the manner in which those users may access it.

Ownership and sharing: Users may dispense and revoke access privileges for objects they own or control.

Threat monitoring: An audit trail is recorded to examine information concerning installation, operations, applications, and fraud of the database contents. A usage log is kept of all the executed transactions, of all the attempted security violations, and of all the outputs provided.

The security mechanisms you use to protect your databases should have the following properties:

Completeness: Defense is maintained against all possible security-threatening attacks.

Confidence: The system actually does protect the database as it is supposed to.

Flexibility: A wide variety of security policies can be implemented.

Ease of use: The database administrator (DBA) has an easy interface to the security mechanisms.

Resistance to tampering: The security measures themselves are secure.

Low overhead: The performance costs can be predicted and are low enough for efficiency.

Low operational costs: The mechanisms utilize the available resources efficiently.

In most DBMSs, authorization rules enforce security. Authorization rules are controls incorporated in the database and enforced by the DBMS. They restrict data access and also the actions that people may take when they access the data. The authorization rules are usually implemented by issuing DDL commands to the DBMS. Most DBMSs support a superset of the SQL grant statement. The grant statement, as defined in the ANSI SQL-92 standard, is:

GRANT privilege-list

ON object

TO user-list

[ WITH GRANT OPTION ]

The OBJECT can be one of the following: TABLE TableName, DOMAIN DomainName, CHARACTERSET CharacterSetName, COLLATION CollationName, and TRANSLATION TranslationName.

The USER in the user list is defined as PUBLIC or a valid authorization ID.

In the remainder of this month's column, I investigate how security is implemented and enforced in IBM DB2, Informix, Microsoft SQL Server, Oracle, and Sybase SQL Server (listed alphabetically). I review each DBMS's access-control mechanisms (with reference to the ANSI SQL-92 standard), database object sharing capabilities, and mechanisms for deterring security threats.

IBM DB2

Several privilege types let users create and access the database resources: database privileges, table and view privileges, package privileges, and index privileges. Various authority levels provide a method for grouping the privileges and higher-level database manager maintenance and utility operations, such as system administration authority (SYSADM), database administration authority (DBADM), system control authority (SYSCTRL), and system maintenance authority (SYSMAINT). You use the privileges and authorities together to control the access to the database manager and the objects in your databases.

A user or a group (a named collection of users) can have one or more of the authority levels or individual privileges. SYSADM and DBADM give you full privileges for a set of objects. SYSCTRL and SYSMAINT give you full privileges to manage the system but no access to the data. The CONTROL privilege gives you all the privileges for a specific object. Users with SYSADM, DBADM, or CONTROL privileges can grant and revoke the privileges of the other users.

Privileges are given to users with the GRANT statement. You can grant a privilege to a single user, a group of users, or PUBLIC (namely all users). When a user and group have the same name, you can use the keywords USER and GROUP to distinguish between them. Privileges are removed with the REVOKE statement. The database privileges are used to control the actions on the database as a whole. Only a SYSADM and DBADM authority can grant and revoke these privileges. You must have CONNECT privilege to the database to use the table and view privileges. The package privileges let users create and manipulate packages. A package is a database object that contains the information that the database manager needs in order to access the data. Table 1 summarizes the DB2 privileges. You can manually create an audit-trail table to record the changes made to your data. The audit-trail table can be populated automatically through triggers.

Informix

The database privileges also have three levels: connect, resource, and database administration (DBA). The connect privilege lets users perform basic queries on the database tables, as well as modify the tables. These users can execute SQL SELECT, INSERT, UPDATE, and DELETE statements, and they can execute stored procedures (provided they have the necessary table-level privileges). These users can also create views if they have the privileges to query the table on which the views are based. The resource privilege includes all the connect privileges, and these users can create tables, indexes, and stored procedures. DBA is the highest privilege; these users can delete any database object, regardless of the owner. They can create tables, views, and indexes to be owned by other users, and they can grant database privileges, including DBA, to other users.

The table privileges apply to tables, views, and synonyms. An object's owner has all the rights to the object and can alter or drop the object.The object's owner can grant or revoke certain privileges -- namely SELECT, INSERT, UPDATE, DELETE, INDEX (to create and alter indexes), ALTER TABLE, and REFERENCES (to create constraints). You can enforce additional restrictions by specifying individual column names in the GRANT statement.

The EXECUTE privilege must be granted on a stored procedure for any user to execute the stored procedure. A DBA-privileged procedure is a procedure created using the DBA option in the CREATE statement, or it is an owner-privileges procedure. A DBA-privileged procedure is created by a user with the DBA database-level privilege. If the database is ANSI-compliant, execute privilege is granted only to the owner of the stored procedure and to users with DBA database-level privilege. In non-ANSI-compliant databases, the database server grants the EXECUTE privilege to all users automatically.

You can use roles to grant or change privileges of many users at the same time. A role is a group of standard privileges defined for a specific group of users. The GRANT statement is used to specify the users included in the role or the privileges on the objects that the users in the role can access, to grant a role to another role, and to grant one or more users in the role the permission to grant the role to another user. Database privileges cannot be granted to a role. The WITH GRANT OPTION clause in the GRANT statement can be used to start a privilege chain. In a privilege chain, one user grants privileges to another user, and that user can grant privileges to other users. If the privilege is revoked, the link is broken. To prevent the chain from being broken, the AS GRANTOR clause can be used, where the grantor is the user name. When the AS clause has been used, only the DBA can revoke the privilege -- and even then, only if the DBA granted the privilege. A role (A) may also be granted to another role (B), so that Role B will now also have Role A's privileges. A role is deleted using the DROP ROLE statement.

The Informix-OnLine/Secure Dynamic Server is a licensed component for secure Unix and CMW platforms. It supports mandatory access controls, systemwide discrete privileges, labeled data, and an audit trail mechanism.

A systemwide discrete privilege is required by the OnLine/Secure Dynamic Server for any action that may involve a tradeoff with security. Examples of the systemwide discrete privileges are: resetting your login identity, resetting your session level, instantiating a unique query that returns a single row, and performing referential integrity checks across security levels. The systemwide discrete privileges can be granted to specific users by the database system security officer.

Each data record in an OnLine/Secure Dynamic Server database is associated with the security level of the user who created or modified the data record most recently. The security levels and the secure users are defined by the System Security Officer within the secure Unix operating system. An individual user is only allowed to see, modify, or be aware of the presence of data that is classified at a more restricted security level.

With the Informix-OnLine/Secure Dynamic Server, you can create an audit trail in a file, which can be used to record any activity or operation that could potentially access or alter your data. The operations are recorded and monitored by the OnLine/Secure auditing facility.

Microsoft SQL Server

After the login IDs have been created, the system manager can give users individual access to the various databases available on the server. This can be done using either the Enterprise Manager or system-stored procedures. When a user is assigned to a database, the system manager can decide to alias the login ID to an existing user. This process is useful if a user needs to perform maintenance tasks on the database, because the user can be automatically aliased to the database owner, which gives owner rights on any of the objects in the database.

Individual users can create private objects in the database, if the database owner (or someone aliased to the database owner) has given them the CREATE TABLE statement privilege. To distinguish among the various user objects, users must use the OWNERNAME OBJECTNAME syntax. Users can only access objects if the owner of the object has granted them access using the GRANT statement. Table 2 lists the rights that you can grant using an extended version of the SQL GRANT statement.

If a stored procedure or view references another object, the end user may be granted access to the high-level object (view or stored procedure) without being given any access to the base objects. In this way, the base objects can be protected from direct manipulation by adventurous end users. You can implement an audit trail using triggers and alerts. The alerts can be coded to send mail to an administrator when a security violation occurs.

Oracle

The Oracle database privileges are divided into system privileges and object privileges. The powerful system privileges let users perform the specified actions systemwide. The system privileges support the WITH ADMIN OPTION clause, which gives granted users the right to re-grant the right to other users. You use the object privileges to let users perform particular actions on specified database objects -- for example, to delete the rows from a specific table. The object privileges support the WITH GRANT OPTION clause, which lets the granted user re-grant the right to other users.

You can grant a privilege explicitly to a user, or the user can inherit the privilege as part of a role. A role is a named group of privileges, and the role can be granted to one or more users. You can also assign a role to an application. If the role is password-protected, you may have to enter a valid password before you may use an operation granted by the rights specified for the role. Table 3 lists the Oracle objects and their associated privileges.

With Oracle7 release 7.3, you can also limit the amount of system resources a user may use. This prevents the uncontrolled consumption of valuable system resources such as CPU execution time. These limits can be set per session or per SQL call. Oracle 7.3 also supports profiles for easier security management. A profile is a named set of specified resource limits, similar to a role for a collection of object privileges.

Oracle has extensive auditing facilities. You can selectively monitor specified SQL statements using statement auditing, you can track the use of specified access rights using privilege auditing, and you can record the use of specified operations on specified database objects using object auditing. You can set the audit options to determine the type of information that is collected in the audit trail. For example, you can specify whether you want to record successful statement executions, unsuccessful statement executions, or both. The audit trail is written to a single table named AUD$ in the SYS schema of the database's data dictionary. There are several predefined views to access the data recorded in the audit trail.

Trusted Oracle7 is a separately licensed component of Oracle7, which supports multilevel security enforcement: It can process data of various security levels without any risk of compromise.

Trusted Oracle7 uses mandatory access controls to make sure that a user has the correct security clearance before accessing any data. Each row has a security label, and each user has a formal authorization and a security clearance level. Users can write data at a security sensitivity level equal to their own, and they can read data at a sensitivity level equal to or less sensitive than their own.

Sybase SQL Server

A database object is automatically owned by its creator and the creator is automatically granted all permissions on the object. All users with the system administrator role also automatically have all privileges on the object. However, the object owner must explicitly grant the permissions to any other users before they can access the object -- including the database owner. (However, the database owner can always assume the ID of the database object owner using the setuser command). You have to use the OWNERNAME OBJECTNAME syntax to access the objects owned by another user.

In Sybase, access privileges are also assigned and removed using an extended version of the SQL GRANT and REVOKE statements. Privileges may be granted to users, groups, or roles. It also supports the WITH GRANT OPTION clause. Table 4 shows the Sybase objects and their associated privileges.

Many events that happen in the Sybase SQL Server can be recorded in an audit trail, such as user logins and logouts, system errors, remote procedure calls, remote database accesses, security privilege changes, attempts to access specified database objects, and the actions of a specified user, including the text of the statement executed by the user.

More than Meets the Eye

Privacy concerns the non-technical notion of protecting sensitive information. It involves the social, lawful, and ethical aspects of people's rights to control the disclosure of their thoughts, beliefs, attitudes, and the facts of their personal lives. People usually provide this information with the expectancy that it will only be used for the stated intention and not be disclosed to others for other purposes.

Privacy is often an issue in so-called statistical databases that contain sensitive information about individuals, from which macro statistics (summaries) are to be obtained. These databases can pose a threat to privacy, because no one is to have access to the micro statistics (individual records) stored in them. The problem is that by manipulating some statistics, sensitive information can be deduced. For example, by repeatedly asking questions, individuals can be separated by the intersection of various query results. In statistical databases, it is necessary to put inference controls in place that would prevent these deductions.

Statistical database privacy is even more important now that many organizations are publishing their data on the World Wide Web. A user has, in theory, unlimited time to deduce information about specific individuals, because on most Web pages you can request new queries as often as you wish. The Web in general introduces security-management complexities that were never thought of before. Now, instead of having a handful of users, you have literally an unlimited number of users on the Web. The accesses of these users must be controlled in such a way that they remain secure and consistent, but not a maintenance nightmare for the DBA.