Database Reference
In-Depth Information
practiced. The importance of security in information systems has increased dramatically in
recent years. DBA personnel should regularly search for security information on the Web in
general and at the DBMS vendor's Web site.
Application Security
Although DBMS products such as Oracle Database, SQL Server, and MySQL do provide
substantial database security capabilities, those capabilities are generic. If the application
requires specific security measures, such as “No user can view a row of a table or of a join of
a table that has an employee name other than his or her own,” the DBMS facilities will not
be adequate. In these cases, the security system must be augmented by features in database
applications.
For example, as you will learn in Chapter 11, application security in Internet applications
is often provided on the Web server. Executing application security on this server means that
sensitive security data need not be transmitted over the network.
To understand this better, suppose that an application is written so that when users click
a particular button on a browser page, the following query is sent to the Web server and then
to the DBMS:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH09-03 *** */
SELECT
*
FROM
EMPLOYEE;
This statement will, of course, return all EMPLOYEE rows. If the application security
policy only permits employees to access their own data, then a Web server could add the fol-
lowing WHERE clause to this query:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH09-04 *** */
SELECT
*
FROM
EMPLOYEE
WHERE
EMPLOYEE.Name = '<% = SESSION(("EmployeeName"))%>';
An expression like this one will cause the Web server to fill the employee's name into the
WHERE clause. For a user signed in under the name 'Benjamin Franklin', the statement that
results from this expression is:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH09-05 *** */
SELECT
*
FROM
EMPLOYEE
WHERE
EMPLOYEE.Name = 'Benjamin Franklin';
Because the name is inserted by a program on the Web server, the browser user does not know
that it is occurring and cannot interfere with it even if he or she did.
Such security processing can be done as shown here on a Web server, but it also can be
done within the application programs themselves or written as stored procedures or triggers
to be executed by the DBMS at the appropriate times.
This idea can be extended by storing additional data in a security database that is accessed
by the Web server or by stored procedures and triggers. That security database could con-
tain, for example, the identities of users paired with additional values of WHERE clauses. For
example, suppose that the users in the personnel department can access more than just their
own data. The predicates for appropriate WHERE clauses could be stored in the security data-
base, read by the application program, and appended to SQL SELECT statements as necessary.
Search WWH ::
Custom Search