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



January 01, 2002
URL:http://www.drdobbs.com/database-developer-web-databases-fun-wi/184414022

Web Techniques: Sidebar

Sidebar


Microsoft, OLAP, and the Web: An Insider's View

In previous Database Developer columns Ive discussed technology trends related to databases, and the purposes for which we use databases. Interest in the Web has surged in recent years, along with interest in decision support applications, OLAP, and business intelligence. Oracle, Informix, Sybase, and IBM have all supplemented their mainstay DBMSs with OLAP servers. With the release of SQL Server 7.0, Microsoft recently added OLAP services to its inventory of software products. Microsoft is also providing OLAP client software, and data access APIs that have a wide following among OLAP vendors.

Bill Baker is a long-time participant in the database, decision support, and data warehousing industry. He helped move Microsoft into the OLAP market with software such as SQL Server OLAP Services (included with SQL Server 7.0). Before moving to Microsoft, Bill was senior vice president for Development at IRI. When IRI sold its Express product to Oracle, Bill became an Oracle VP before moving to Microsoft. Bill recently provided an insiders view of the OLAP phenomenon. He discussed the marriage of OLAP and Web technology and Microsofts contribution to the growth of OLAP.

Web Techniques: As a veteran of the computer industry, youve seen data warehousing and OLAP grow to become a major area of competition between DBMS vendors. OLAP is becoming more affordable technology and more widely deployed. Has the business case and economic justification for OLAP undergone a fundamental change, or are we simply seeing the effect of ever-improving hardware price/performance ratios?

Bill Baker: I think we are seeing both. The improvement in hardware price/performance ratios is pretty obvious. PC servers are both cheap enough, and easy enough, to deploy that its easy to support OLAP in places that never used it before. But the more important driver for OLAP is a craving inside businesses of all sizes to multiply the value of all the operational data theyve been collecting for years and years. By now OLAP has proven its ability to enhance decision making in corporations. Its reached critical mass in terms of product offerings, consulting support, and integration with the rest of the corporate computing environment. So growing demand and more affordable platform prices have driven the market.

WT: Microsoft is shipping an OLAP server with SQL Server 7.0 and it has updated the Universal Data Access APIs to operate with multidimensional data sets. It has also extended SQL with multidimensional expressions (MDX) and added OLAP client capabilities to Excel. How well does this integrate with the Internet/intranet/extranet computing model? And how big a role will browsers such as Netscape and Internet Explorer play as decision-support clients?

BB: Browsers are very important to OLAP and data warehousing. One way to look at the Web, especially the intranet, is that it is a data projector. Its a way to push data and analysis of data into every finger of the organization. There is really a spectrum here. It runs from browser-based OLAP, to spreadsheets such as Excel with built-in access to OLAP data, all the way up to analysis tools from any number of independent software vendors (ISVs). Were aware of one ISV readying a $5000-per-seat analysis client. With SQL Server OLAP Services, we made sure that the client component, the OLE DB for OLAP data provider (PivotTable Services), had two attributes. First, it can run on the middle-tier (usually a Web) server. Second, it exports its functionality via Automation. This allows scripts running on the Web server, typically Active Server Pages, to access OLAP data and analysis.

WT: The growth in databases in the 1980s created a demand for DBAs. The Web explosion of the 90s created a demand for Webmasters, and analysts forecast an OLAP explosion in the next decade. Data mining and Web farming are also likely to play a greater role in the next decade. Microsoft has pushed to make SQL Server easier to administer. Will there be a similar push to make OLAP a tool for the masses, so that companies can draw from a talent pool of DBAs and Webmasters to do business intelligence projects?

BB: This reminds me of a forcast from much earlier in the century regarding the telephone. People realized that if the phone was to be completely ubiquitous, the phone company would need literally millions and millions of telephone operators. Of course, the solution was to allow each telephone user to be his or her own operator. We need to do the same thing with OLAP and data warehousing.

Are we there yet? No. But we've made a greatr start. OLAP has previously been both expensive and esoteric. Our goal is to increase the approachability of OLAP by orders of magnitude. This means lowering the cost, increasing the integration with the corporate computing environment and, as you suggest, making it possible for any DBA or power user to manage an OLAP database. TO go back to your previous question, using the Web and Excel to give literally all of your users access to OLAP requires an approach like the one taken with the telephone. That's exciting. -KN


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.

Privileges

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:

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 (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.

Web Techniques: Online

Online


Informix
www.informix.com

NIST Research on Role-Based Access Control
hissa.ncsl.nist.gov/rbac/newpaper/rbac.html

Oracle
www.oracle.com

Raima Velocis
www.raima.com


Terms of Service | Privacy Statement | Copyright © 2024 UBM Tech, All rights reserved.