Web Databases: Fun with Guests or Risky Business?
This month's column explores two topics of great importance: database security and mission-critical databases. Database security has always been a topic to be taken seriously, but exposing your data to the Internet and its hacker subculture increases the threat level. Selecting the wrong DBMS software for managing mission-critical data also increases your exposure. However, awareness of external security threats seems to be much greater than concerns about using a database manager that could put mission-critical data at risk from those threats. The possibility of online analytical processing (OLAP) becoming as widespread as transaction processing (see the text box titled " Microsoft, OLAP, and the Web: An Insider's View") heightens concerns about data at risk, because OLAP programs use valuable, sensitive data.
Not all products marketed as database managers are suitable for managing mission-critical databases. A database is mission critical if its lack of data integrity has serious consequences, such as causing the loss of customers or even lives. A chamber of commerce's calendar of local events is not mission-critical data. Pharmacy orders in a hospital are mission critical because lives are at stake. A travel agency or bookseller may find that going out of business is the price paid for losing data. If losing or corrupting data is a bet-the-business proposition, then a database is mission critical and you need a robust DBMS.
Several years ago there were serious online debates about the merits of SQL DBMSs and desktop database products. Desktop developers questioned why they needed to use the more expensive DBMSs. What they didn't understand is that mission-critical applications require features such as intrinsic security, transaction journaling, concurrency controls, and the ability to enforce data integrity constraints. Without those features, you do not have secure, robust databases. Connecting a database to a Web server adds other requirements, such as a multithreaded architecture and the ability to do database backups without taking the server down. Freeware and PC DBMSs are suitable for certain classes of applications, but not for high-volume Web sites and mission-critical databases. In any case, don't bet your business, or lives, on such software unless you have the source code and the expertise to understand and repair it.
Database security is a topic of serious online discussion and the subject of a fair amount of research. The security restrictions applied by databases are usually part of a bigger security picture. Rather than supplanting encryption, firewalls, secure sockets, and similar technologies, database security works best when combined with these security solutions.
SQL provides intrinsic security capabilities that may explain its popularity compared to alternative database models. The Web and e-commerce have increased our awareness of security issues, which has contributed to the number of Web sites using SQL databases. SQL did not introduce the concept of database security, nor is it the only model for managing security. For example, a Committee on Data Systems Languages (CODASYL) network-model DBMS controls access to the subschemas, the compile-time and runtime bindings that define the sets of data being managed by the DBMS. However, some SQL products do include notably advanced features such as secure network services and secure-auditing capabilities. Auditing facilities can be so sophisticated as to divide responsibilities among a server administrator, database security officer, and audit analysis officer. However, this article discusses more pedestrian security capabilities.
The SQL language includes commands for managing who has what rights to what data. It enables you to control access to database objects such as tables, columns, and views. SQL also lets you manage individual users and groups, and their rights to access schema objects. This granular approach to controlling access objects is more flexible and more secure than granting blanket access based on application or IP address (discussed later).
Users, Groups, Authentication, and Authorization
Several concepts are integral to the SQL model. A SQL DBMS provides services to users. Database administrators assign each user a user ID, which the SQL standard calls an authorization ID. For authentication and authorization, authorization IDs are usually associated with password protection. Passwords enable us to authenticate with logins, which can occur outside of the DBMS. For example, you might use DCE Security Services to authenticate users for DB2. Another alternative is to use Windows NT Security Accounts Manager or Active Directory to authenticate users for Microsoft SQL Server. After a user is authenticated, the DBMS uses authorization-id as the basis for granting data access. Some SQL products enable you to manage authorization by group or role, which are terms that refer to a collection of users having defined privileges.
SQL databases also use an intrinsic group authorization-id known as
PUBLICgroup provides an ID that you can use to define the privileges available to all users.
PUBLICis analogous to anonymous FTP because you use it when you want to specify unrestricted access. Databases connected to the Internet often are accessed by the equivalent of anonymous users. Because there might be thousands or millions of users, you cannot expect to create individual accounts. To grant anonymous access to the database, use the
PUBLICgroup and/or a
An SQL database uses privileges to control access to database objects (also called schema objects). Users write SQL statements to manipulate data in databases, and the DBMS keeps track of the statements it allows a user to run. SQL-92 enables you to specify several classes of rights: viewing, inserting, deleting, updating, using, and referencing. The
USAGEprivilege extends to domains, character sets, collations, and translations. The
REFERENCESprivilege refers to constraints, such as referential integrity constraints. Database administrators use
REVOKEstatements to define what privileges on what objects are available to a user. The following is the format of the
GRANT privilege ON object TO grantee [WITH GRANT OPTION]
If, for example, you run a pizza business, you'd probably let Web customers see the toppings menu. To do so, you might use the following
GRANT SELECT ON pizza_toppings to PUBLIC
Since you probably don't want customers to be capable of deleting the toppings list, you might use the
REVOKEstatement in this way:
REVOKE DELETE SELECT ON pizza_toppings FROM PUBLIC
SQL DBMS products do not have identical privileges, in part because their grammars differ. Table 1 lists the privileges available with Oracle 8.
Groups, Roles, and Role Separation
Some DBMS products let you use roles and role separation to manage authorities and responsibilities. This allows you to divide administrative responsibilities so different roles have different responsibilities and permissions. Whereas you can grant administrators the equivalent of an "all access" pass, you can "use restrict" the object access of other roles.
Groups and roles are convenient for authorizing a collection of users. Microsoft SQL Server 6.5 and Sybase System 11 managed authorizations by user and group. Recent releases from other vendors have added support for roles and role separation. Role-based security, also called role-based access control (RBAC), is becoming a favored solution among SQL vendors. Products such as Oracle 8, Informix Dynamic Server, and Sybase Adaptive Server Enterprise use a role-based security model. Microsoft SQL Server developers can use a role-based security model in Microsoft Transaction Server (MTS) when building transactional components.
IBM has been revising DB2 UDB security to support roles and separation of duties between database administration and security administration. Informix divides database administration functions into three roles (auditing, security administration, and general administration). Role separation is an installation option for Informix Dynamic Server. If you opt for role separation, Informix provides three groups (
IXAAO) for administrative tasks and one group (
IXUSERS) for user tasks. The
IXDBSArole is for persons doing general administration tasks. The
IXDBSSOrole is for security-related tasks, and the
IXAAOrole is for auditing. If you don't enable role separation at installation time, any member of
INFORMIX-Admincan perform administrative tasks.
Oracle predefines several system roles. The
CONNECTrole, for example, allows a user to query schema objects whereas
RESOURCEroles enable users to create schema objects. The
DBArole has all system privileges, except exporting and importing the full database. Oracle 8 manages those privileges using the
Role-based security is an area of active research, such as that being done by researchers studying federated databases. The National Institute of Standards and Technology (NIST) is also conducting research on RBAC.
Views, Procedures, and Packages
Organizations with multiple users need a security model that lets an administrator define access controls at several levels. For mission-critical applications, you want a SQL DBMS and tool set that can enable and disable access to specific servers, specific databases, and individual schema objects. You usually don't want anonymous users to have access to your base tables, so you need to understand data hiding techniques. SQL enables you to hide data by using views, stored procedures, and, in some cases, packages. Views contain columns derived from base tables; you can create views that leave out those base-table columns with sensitive information. Then you grant
SELECTprivileges only on views, not on tables. If your DBMS supports stored procedures or packages, you'll find they're often a better solution than views. A procedure is a program or block of SQL code that performs a specific function. SQL requires you to have specific permissions to create and execute store procedures.
Understanding domain terminology is important when reading technical documentation and making design decisions. Unfortunately, a term such as package is used in different software contexts. You compile Java source code to create a Java package. You compile static SQL and create bind files for a DB2 package. You use a
PACKAGEstatement to create an Oracle package. You use a programming language such as Visual Basic to create component packages for transaction servers such as Sybase Enterprise Application Server and Microsoft Transaction Server.
One thing all of these packages do is provide the ability to restrict access. Oracle and DB2 packages are installed in individual databases, so they enable you to expose the database's information only to authorized users. Oracle and DB2 manage packages as schema objects, so you need appropriate permissions to access a package.
Application Rights and Trusted Hosts
I'm not a big fan of administrators who designate application use as the primary qualifier for granting access to critical data. This can produce scenarios such as, "Our financial people use Excel and they need to be able to access any table in our databases. Therefore, anyone using Excel has all rights to all tables." Granting access by application is an easy solution that's an invitation to disaster. Instead you should do the work to analyze informational needs and define the appropriate users, groups, or roles.
Likewise you must be careful about establishing trusted connections based on an IP address. Some SQL DBMSs permit you to identify a node that can establish a trusted connection, or act as a trusted host. Informix, for example, uses the hosts-equiv file for identifying trusted hosts. That type of connection is often used by system and network administrators, but there is potential for abuse. IP spoofing is a known attack, so setting up trust relationships based on IP address is could lead to problems for a Web database. Even for physically controlled environments with no external connections, you should avoid granting all access on the basis of an application or particular IP address. If you use those qualifiers, include selective permissions and other solutions in your security model.
Several techniques are helpful for granting appropriate access without compromising security. One is to segregate databases and keep confidential data on servers that are inaccessible from the Net, or failing that, behind a firewall.
PUBLIC, views, stored procedures, and packages to restrict what anonymous users can do. Use
PUBLICto specify privileges for everyone who drops in. For example, if you're a bookseller on the Web, you probably want anyone with a browser to view your book catalog. However, you want to limit who can execute
UPDATEstatements on the tables in your database. You can grant to
PUBLICpermissions on views, packages, and procedures, but not to underlying tables. Going back to the pizza example, you could design the database so all menu displays and ordering are handled by stored procedures. This lets you limit what a
guestcan do. Grant to
EXECUTEprivilege for procedures that display the menu and order.
Matching Security to Requirements
There's a broad spectrum of database applications for which security requirements vary greatly. Let's assume you're running an application that uses a zero-administration embedded DBMS such as Raima Velocis on a LAN. You have no remote connections to your site and you control physical access to your facility. You've been using simple security procedures because you want database and server administration to be a no-brainer. This approach affords little protection from malevolent users and staff, but you can keep the amount of resources devoted to security administration to a minimum.
The situation changes when management decides to offer e-commerce features and provide a Web connection to your application's database. Moving a database to the Web increases your security risks by several orders of magnitude. When that happens, you can't let ease of operation override concerns for protecting data. Be prepared to step up your security by using
REVOKE, data partitioning, stored procedures, views, roles, and other security measures. (see " Online").
Ken consults, writes, and speaks. He wrote Database Magic with Ken North (Prentice Hall) and Windows Multi-DBMS Programming (Wiley). He also teaches Expert Series seminars.