0717-0719

Previous Table of Contents Next

Page 717

CHAPTER 29

Application Security

IN THIS CHAPTER

• Reasons for Using Application Security718
• Using Application-Specific Database Objects719
• Table-Driven Application Security723
• Application and Performance Considerations731

Page 718

Application security limits database access from the client side of the system. Database security limits access to specific database objects, whereas application security limits access to specific interface objects. In a broader sense, application security also includes the use of any
application-specific database objects that are created to enhance security.

There are several reasons why you should use application security in addition to database security. These reasons are discussed in the following section. This chapter also covers the use of application-specific database objects and table-driven user interfaces, as well as application and performance considerations relating to the use of application security.

Reasons for Using Application Security

In general, application security is used to enhance and supplement database security. But it would be dangerous to rely only on application security, because there is no guarantee that users will access the database through only a single application.

Application security can enhance database security by further limiting access to database objects and by providing an additional layer of abstraction (hiding the details of available database objects). If you limit access to application-specific stored procedures and functions and create application-specific roles, you can hide objects based on the specific user or the role of the user accessing the system. On the same basis, the application can limit access of specific columns or make specific columns read-only by using different views for different roles or
users.

In a typical database application, the interface enables the user to communicate with the database by using menus and forms related to business processes and objects rather than tables and columns. This is a convenient layer that prevents users from having to know the names of the tables and columns being accessed.

In many cases, information in the database is used to control the behavior of the interface, based on the role of the user. System tables contain security information used by the application to drive which capabilities are available to the interface. Menu options and forms to which the user has privileges to access are made visible, while others are hidden.

For example, if an application has an administration menu containing options specific to database administration functions, the entire menu is hidden from users who do not have the ADMINISTRATOR role. Any forms relating to these specific functions are inaccessible as well. This method of using application security is obviously preferable to providing a homogenous interface that enables users to try to perform operations on tables to which they have no privileges. If the application allowed the user to access a database object that he did not have privileges to, the resulting errors would have to be trapped and displayed to the user, which can result in misunderstanding and frustration on the user's part. Using the database to drive application security is also preferable to hard-coding rules into the client application. Table-driven application security makes it easy to update a user's privileges without having to reprogram the application.

Page 719

Another reason for using application security relates to databases that are accessed by multiple applications. In this case, a single user might have multiple roles, of which only one applies to the specific application. Use an Oracle table when there are different application roles that could apply. For example, an order entry system might have three different roles: one for order entry, one for management reporting, and one for system administration. A billing system and an accounts receivable system might share common tables and be used by many of the same users, and those users might have slightly different role definitions for each application. A simple Oracle table containing a user ID and a corresponding role can be used to determine the correct role to be used for the application at runtime. The privileges of the appropriate role can then be used to limit the user's view of the database to the scope that was intended for the particular application.

Using Application-Specific Database Objects

The distinction between application security and database security is sometimes blurred, as is the case when you use application-specific database objects. As illustrated by the previous example, different applications often access a common database. When you create objects to be used only by a specific application, application-level security is common. Although the objects exist in the database and database security is used, if the object exists only to service a specific application, it is said to be owned by the application. The argument can be made that rights granted to the stored object fall under the category of application security.

In the definition of overall systems security, the base objects (clusters, tables, indexes, and sequences) and rights granted to these objects are categorized as database security issues. Views, procedures, and functions are considered application-specific objects, and rights granted to them are categorized as application security issues.

Typically, in large database environments, the primary DBA will not be responsible for creating and maintaining all application-specific objects. One possible way to handle security for a large database accessed by multiple applications is to grant rights to sequences and views of the tables to lead developers or administrators who serve as DBAs for the individual applications. To do this, use the WITH GRANT OPTION clause. Listing 29.1 follows the example of the database shared by order entry and billing/accounts receivable applications. It demonstrates how the WITH GRANT OPTION clause might be used on a subset of the database objects.

Listing 29.1. This script grants limited rights to the common database objects.

 /* SEQUENCE */ CREATE PUBLIC SYNONYM account_no FOR account_no; CREATE OR REPLACE VIEW accounts AS     SELECT * FROM account; CREATE PUBLIC SYNONYM accounts FOR accounts; /* SEQUENCE */ CREATE PUBLIC SYNONYM order_no FOR order_no; 

 continues 

Previous Table of Contents Next