Kevin Hoffman

Case Study: Implementing Role-Based Security

This chapter walks you through a case study examining how to use SQL and stored procedures to implement role-based security. You can implement the case study on any back-end relational database that supports stored procedures, such as SQL Server, DB2, or Oracle, and for any kind of application, be it e-commerce, intranet, or anything else.

Summarizing and Grouping Data

The previous two chapters introduced the basics of SQL usage, and in fact this is all you’ll need for some applications. However, there’s a lot more that becomes possible when you delve into the wealth of additional facilities contained in SQL. In this chapter, you’ll look at ways of summarizing and grouping data. This enables you to perform many versatile data manipulation techniques. For example, you can find out the maximum value or the average value in a column (say, the highest mark scored by a student in any exam). Or, perhaps more usefully, you can look at maximum or average values for columns based on groupings defined by other columns (so you could group the rows in a table according to the exam taken and then find out the minimum/maximum/average scores for each exam).

Hiding Complex SQL with Views

So far, when you’ve wanted to restructure data with calculated columns or extract data from multiple tables based on comparisons between column values, you’ve had to resort to relatively complex SQL statements. Typically, though, you’ll want to obtain data in this modified form more than once. Certain tasks, such as getting the name of the customer associated with an order, will be performed so often that it can seem awkward to have to query multiple tables all the time.

Querying Multiple Tables

Querying data from multiple tables using subqueries is a great capability, but it doesn’t always give you everything you need, and the syntax can become a bit messy and confusing. Also, as discussed in the previous chapter, when a sub-query executes for each match of an outer query, performance can be affected. For this reason, SQL allows you to query multiple tables simultaneously with a simpler syntax. This capability doesn’t completely replace some of the techniques you saw in the previous chapter involving subqueries across multiple tables, but it’s extremely powerful when you simply want a result set that contains information from more than one table. Because relational databases are designed to hold data in more than one table, where records in one table are usually associated with records in other tables, this functionality is essential.

Users and Security

It’s a fact of life that although storing and managing information is vital to the success of any business, having it fall into the wrong hands can bring a business down. As a result of this, database administrators have to be familiar with some complex security systems in order to protect a company’s data.

Combining SQL Queries

In this chapter, you’ll look for the first time at ways that you can combine several SELECT statements in a single query. The first topic discussed is the use of subqueries. Put simply, a subquery is a query that’s embedded in some way inside another query.

Working with Database Objects

In this chapter, you’ll learn how to create, alter, and drop databases and other kinds of objects that live inside databases, such as tables, sequences, and so on.

Retrieving Data with SQL

In this chapter, you’ll get started with SQL by examining how you can use SQL statements to query a relational database and extract data from it. Here, you’ll be querying just one table in a database, but later on in the book (in Chapter 6, “Combining SQL Queries”), you’ll see how to query data from multiple tables.

Using Stored Procedures

In the previous chapter, you saw how you can store a SQL query in a Relational Database Management System (RDBMS) as a view, making it easy to access complex, shaped data without having to use a lengthy query. Stored procedures fulfill a similar role but do so in a much more powerful way. With stored procedures, you’re not limited to queries or even single statements. A single stored procedure could add a record, modify some data, perform some calculation or other, and return some data—all with a single command from the user. Unfortunately, they can’t make you dinner and record Buffy while you’re out shopping, but they’re pretty useful nevertheless.

Understanding SQL and Relational Databases

At the heart of almost every large-scale application, whether it be a Web application or a corporate intranet, is the database. The modern database has moved on a long way from the flat-file address-card style of database storage, and you now have the ability to store terabytes of information about customers, products, contacts, and so on. It’s all very well storing all of this data in a central database, but simply storing data doesn’t give you much in return if you can’t find some way to organize, access, and work with this data. Structured Query Language (SQL) is a standardized language designed to access and manipulate data stored in relational databases and to work with the databases themselves.