Tuesday, January 16, 2007

Database Security

As the use of the Web grows on both Intranets and the public Internet, information security is becoming crucial to organizations. The Web provides a convenient, cheap, and instantaneous way of publishing data. Now that it is extremely easy to disseminate information, it is equally important to ensure that the information is only accessible to those who have the rights to use it.

With many systems implementing dynamic creation of Web pages from a database, corporate information security is even more vital. Previously, strict database access or specialized client software was required to view the data. Now anyone with a Web browser can view data in a database that is not properly protected. Never before has information security had so many vulnerable points. As the computing industry moves from the mainframe era to the client/server era to the Internet era, a substantially increasing number of points of penetration have opened up.

For much of Internet security, database specialists have had to rely on network administrators implementing precautions such as firewalls to protect local data. Because of the nature of Intranet/ Internet information access, however, many security functions fall into a gray area of responsibility. This article describes the primary areas where security falls within the domain of the DBA, who must create the information solutions.

New security procedures and technology are pioneered daily, and this article explains the various security systems involved with solving the current problems. This article should provide a primer for further study of Web security and a framework for understanding current security methodology. For Web security, you must address three primary areas:

  1. Server security -- ensuring security relating to the actual data or private HTML files stored on the server
  2. User-authentication security -- ensuring login security that prevents unauthorized access to information
  3. Session security -- ensuring that data is not intercepted as it is broadcast over the Internet or Intranet
You can view these layers as layers of protection. For each layer of security added, the system becomes more protected. Like a chain, however, the entire shield may be broken if there is a weak link.

Server Security

Server security involves limiting access to data stored on the server. Although this field is primarily the responsibility of the network administrator, the process of publishing data to the Web often requires information systems specialists to take an active hand in installing and implementing the security policy.

The two primary methods in which information from databases is published to the Web are the use of static Web pages and active dynamic Web page creation. These two methods require almost completely different security mechanisms.

Static Web Pages

Static Web pages are simply HTML files stored on the server. Many database specialists consider static page creation the simplest and most flexible method of publishing data to the Web. In a nutshell, a client program is written to query data from a database and generate HTML pages that display this information.

When published as static Web pages, Web files can be uploaded to any server; for dynamic creation, however, the Web server usually must be modified (or new scripts or application software installed). Static pages have the secondary advantage of being generated by traditional client/server tools such as Visual Basic or PowerBuilder. Because almost any development system can output text files, only the necessary HTML codes must be added to make them Web pages. The creation of the pages, therefore, uses standard methods of database access control such as database security and login controls.

Once created, the files must be uploaded to the Web server. Protecting the documents stored there occurs in the same manner that any other Web documents would be secured. One of the most straightforward ways to protect sensitive HTML documents is to limit directory browsing. Most FTP and Web servers allow directories to be configured so that files stored within them may be read but the files may not be listed in the directory. This technique prevents any user who does not know the exact filename from accessing it. Access may be permitted by simply distributing the exact filenames to authorized personnel.

Directories may also be protected using the integrated operating system security. Some Web servers allow security limitations to be placed on particular folders or directories using standard operating system techniques (such as file attributes) and then use this security to restrict access. This implementation will vary among Web servers. These security implementations to gain access to particular files or folders fall under the user-authentication category of security (described in a later section of this article).

Dynamic Page Generation

Favored by large organizations, this method is gaining popularity as the technology to generate Web pages instantly from a database query becomes more robust. A dynamic Web page is stored on the Web server with no actual data but instead a template for the HTML code and a query. When a client accesses the page, the query is executed, and an HTML page containing the data is generated on the fly. The necessary data is filled into the slots defined in the template file in much the same way that a mail merge occurs in a word-processing program. A program may be active on the Web server to generate the necessary Web page, or a CGI script might dynamically create it.

One of the first security issues that a DBA must confront is setting up access to the database from the Web server. Whether using a CGI script, server-based middleware, or a query tool, the server itself must have access to the database.

Database Connections

With most of the dynamic connectors to databases, a connection with full access must be granted to the Web server because various queries will need to access different tables or views to construct the HTML from the query. The danger is obvious: A single data source on the server must be given broad access capabilities.

This makes server security crucial. For example, an ODBC data source given full administrator access could potentially be accessed by any other program on the server. A program could be designed to retrieve private information from a data source regardless of whether the program's author is permitted access. This security problem is most dangerous on a system where users are allowed to upload CGI scripts or programs to run on the server. To prevent unauthorized access to your data, make sure that the server that owns the database connector is physically secure and does not permit unrestricted program execution.

Table Access Control

Standard table access control, if featured in the user authentication system, is more important on Web applications than on traditional client/server systems. DBAs are often lax in restricting access to particular tables because few users would know how to create a custom SQL query to retrieve data from the database. Most access to a database on a client/server system occurs through a specifically built client that limits access from there.

Not so with Web-based applications: Client/server development requires substantial experience, but even some novices can program or modify HTML code, and most user productivity applications such as word processors or spreadsheets that can access databases also save documents as HTML pages. Therefore, more solutions will be created by intermediate users -- and so valid security is a must. Remember, a little knowledge can be a dangerous thing.