Channels ▼

Database Developer | Web Databases: Fun with Guests or Risky Business? (Web Tech

Database Developer | Web Databases: Fun with Guests or Risky Business? (Web Techniques, Mar 1999)

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.

Mission-Critical Databases

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

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 PUBLIC. The PUBLIC group provides an ID that you can use to define the privileges available to all users. PUBLIC is 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 PUBLIC group and/or a guest account.


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 USAGE privilege extends to domains, character sets, collations, and translations. The REFERENCES privilege refers to constraints, such as referential integrity constraints. Database administrators use GRANT and REVOKE statements to define what privileges on what objects are available to a user. The following is the format of the GRANT statement:

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 statement:

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 REVOKE statement in this way:


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 (IXDBSA, IXDBSSO, and IXAAO) for administrative tasks and one group (IXUSERS) for user tasks. The IXDBSA role is for persons doing general administration tasks. The IXDBSSO role is for security-related tasks, and the IXAAO role is for auditing. If you don't enable role separation at installation time, any member of INFORMIX-Admin can perform administrative tasks.

Oracle predefines several system roles. The CONNECT role, for example, allows a user to query schema objects whereas CONNECT and RESOURCE roles enable users to create schema objects. The DBA role has all system privileges, except exporting and importing the full database. Oracle 8 manages those privileges using the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, respectively.

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 SELECT privileges 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 CREATE PACKAGE statement 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.

Security Suggestions

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.

Use guest accounts, PUBLIC, views, stored procedures, and packages to restrict what anonymous users can do. Use guest and PUBLIC to 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 INSERT, DELETE, and UPDATE statements on the tables in your database. You can grant to guest or PUBLIC permissions 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 guest can do. Grant to guest (or PUBLIC) only EXECUTE privilege 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 GRANT and 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.

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.