Data Security

If you create a table and allow users to query it, they will be able to access every column and every row in the table. As I pointed out earlier, however, your table may include some columns that contain sensitive data, such as identification numbers or credit card numbers; not only is it a bad idea to expose such data to all users, but also it might violate your company’s privacy policies, or even state or federal laws, to do so.

The best approach for these situations is to keep the table private (i.e., don’t grant select permission to any users) and then to create one or more views that either omit or obscure (such as the '*****' approach taken with the customer_vw.email column) the sensitive columns. You may also constrain which rows a set of users may access by adding a where clause to your view definition. For example, the next view definition excludes inactive customers:

CREATE VIEW active_customer_vw
 (customer_id,
  first_name,
  last_name,
  email
 )
AS
SELECT
  customer_id,
  first_name,
  last_name,
  concat(substr(email,1,2), '*****', substr(email, -4)) email
FROM customer
WHERE active = 1;

If you provide this view to your marketing department, they will be able to avoid sending information to inactive customers, because the condition in the view’s where clause will always be included in their queries.

Data Aggregation

Reporting applications generally require aggregated data, and views are a great way to make it appear as though data is being pre-aggregated and stored in the database. As an example, let’s say that an application generates a report each month showing the total sales for each film category, so that the managers can decide what new films to add to inventory. Rather than allowing the application developers to write queries against the base tables, you could provide them with the following view:

CREATE VIEW sales_by_film_category
AS
SELECT
  c.name AS category,
  SUM(p.amount) AS total_sales
FROM payment AS p
  INNER JOIN rental AS r ON p.rental_id = r.rental_id
  INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
  INNER JOIN film AS f ON i.film_id = f.film_id
  INNER JOIN film_category AS fc ON f.film_id = fc.film_id
  INNER JOIN category AS c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total_sales DESC;

Using this approach gives you a great deal of flexibility as a database designer. If you decide at some point in the future that query performance would improve dramatically if the data were preaggregated in a table rather than summed using a view, you could create a film_category_sales table, load it with aggregated data, and modify the sales_by_film_category view definition to retrieve data from this table. Afterward, all queries that use the sales_by_film_category view will retrieve data from the new film_category_sales table, meaning that users will see a performance improvement without needing to modify their queries.

Hiding Complexity

One of the most common reasons for deploying views is to shield end users from complexity. For example, let’s say that a report is created each month showing information about all of the films, along with the film category, the number of actors appearing in the film, the total number of copies in inventory, and the number of rentals for each film. Rather than expecting the report designer to navigate six different tables to gather the necessary data, you could provide a view that looks as follows:

CREATE VIEW film_stats
AS
SELECT f.film_id, f.title, f.description, f.rating,
 (SELECT c.name
  FROM category c
    INNER JOIN film_category fc
    ON c.category_id = fc.category_id
  WHERE fc.film_id = f.film_id) category_name,
 (SELECT count(*)
  FROM film_actor fa
  WHERE fa.film_id = f.film_id
 ) num_actors,
 (SELECT count(*)
  FROM inventory i
  WHERE i.film_id = f.film_id
 ) inventory_cnt,
 (SELECT count(*)
  FROM inventory i
    INNER JOIN rental r
    ON i.inventory_id = r.inventory_id
  WHERE i.film_id = f.film_id
 ) num_rentals
FROM film f;

This view definition is interesting because even though data from six different tables can be retrieved through the view, the from clause of the query only has one table (film). Data from the other five tables are generated using scalar subqueries. If someone uses this view but does not reference the category_name, num_actors, inventory_cnt, or num_rentals column, then none of the subqueries will be executed. This approach allows the view to be used for supplying descriptive information from the film table without unnecessarily joining five other tables.

Joining Partitioned Data

Some database designs break large tables into multiple pieces in order to improve performance. For example, if the payment table became large, the designers may decide to break it into two tables: payment_current, which holds the latest six months’ of data, and payment_historic, which holds all data up to six months ago. If a customer wants to see all the payments for a particular customer, you would need to query both tables. By creating a view that queries both tables and combines the results together, however, you can make it look like all payment data is stored in a single table. Here’s the view definition:

CREATE VIEW payment_all
 (payment_id,
  customer_id,
  staff_id,
  rental_id,
  amount,
  payment_date,
  last_update
 )
AS
SELECT payment_id, customer_id, staff_id, rental_id,
  amount, payment_date, last_update
FROM payment_historic
UNION ALL
SELECT payment_id, customer_id, staff_id, rental_id,
  amount, payment_date, last_update
FROM payment_current;

Using a view in this case is a good idea because it allows the designers to change the structure of the underlying data without the need to force all database users to modify their queries.

Updating Simple Views

The view at the beginning of the chapter is about as simple as it gets, so let’s start there:

CREATE VIEW customer_vw
 (customer_id,
  first_name,
  last_name,
  email
 )
AS
SELECT
  customer_id,
  first_name,
  last_name,
  concat(substr(email,1,2), '*****', substr(email, -4)) email
FROM customer;

The customer_vw view queries a single table, and only one of the four columns is derived via an expression. This view definition doesn’t violate any of the restrictions listed earlier, so you can use it to modify data in the customer table.  Let’s use the view to update Mary Smith’s last name to Smith-Allen:

mysql> UPDATE customer_vw
    -> SET last_name = 'SMITH-ALLEN'
    -> WHERE customer_id = 1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

As you can see, the statement claims to have modified one row, but let’s check the underlying customer table just to be sure:

mysql> SELECT first_name, last_name, email
    -> FROM customer
    -> WHERE customer_id = 1;
+------------+-------------+-------------------------------+
| first_name | last_name   | email                         |
+------------+-------------+-------------------------------+
| MARY       | SMITH-ALLEN | [email protected] |
+------------+-------------+-------------------------------+
1 row in set (0.00 sec)

While you can modify most of the columns in the view in this fashion, you will not be able to modify the email column, since it is derived from an expression:

mysql> UPDATE customer_vw
    -> SET email = '[email protected]'
    -> WHERE customer_id = 1;
ERROR 1348 (HY000): Column 'email' is not updatable

In this case, it may not be a bad thing, since the main reason for creating the view was to obscure the email addresses.

If you want to insert data using the customer_vw view, you are out of luck; views that contain derived columns cannot be used for inserting data, even if the derived columns are not included in the statement. For example, the next statement attempts to populate only the customer_id, first_name, and last_name columns using the customer_vw view:

mysql> INSERT INTO customer_vw
    ->  (customer_id,
    ->   first_name,
    ->   last_name)
    -> VALUES (99999,'ROBERT','SIMPSON');
ERROR 1471 (HY000): The target table customer_vw of the INSERT 
is not insertable-into

Now that you have seen the limitations of simple views, the next section will demonstrate the use of a view that joins multiple tables.

Updating Complex Views

While single-table views are certainly common, many of the views that you come across will include multiple tables in the from clause of the underlying query. The next view, for example, joins the customer, address, city,and country tables so that all the data for customers can be easily queried:

CREATE VIEW customer_details
AS
SELECT c.customer_id,
  c.store_id,
  c.first_name,
  c.last_name,
  c.address_id,
  c.active,
  c.create_date,
  a.address,
  ct.city,
  cn.country,
  a.postal_code
FROM customer c
  INNER JOIN address a
  ON c.address_id = a.address_id
  INNER JOIN city ct
  ON a.city_id = ct.city_id
  INNER JOIN country cn
  ON ct.country_id = cn.country_id;

You may use this view to update data in either the customer or the address table, as the following statements demonstrate:

mysql> UPDATE customer_details
    -> SET last_name = 'SMITH-ALLEN', active = 0
    -> WHERE customer_id = 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE customer_details
    -> SET address = '999 Mockingbird Lane'
    -> WHERE customer_id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The first statement modifies the customer.last_name and customer.active columns, whereas the second statement modifies the address.address column. You might be wondering what happens if you try to update columns from both tables in a single statement, so let’s find out:

mysql> UPDATE customer_details
    -> SET last_name = 'SMITH-ALLEN',
    ->   active = 0,
    ->   address = '999 Mockingbird Lane'
    -> WHERE customer_id = 1;
ERROR 1393 (HY000): Can not modify more than one base table 
  through a join view 'sakila.customer_details'

As you can see, you are allowed to modify both of the underlying tables separately, but not within a single statement. Next, let’s try to insert data into both tables for some new customers (customer_id = 9998 and 9999):

mysql> INSERT INTO customer_details
    ->  (customer_id, store_id, first_name, last_name,
    ->   address_id, active, create_date)
    -> VALUES (9998, 1, 'BRIAN', 'SALAZAR', 5, 1, now());
Query OK, 1 row affected (0.23 sec)

This statement, which only populates columns from the customer table, works fine. Let’s see what happens if we expand the column list to also include a column from the address table:

mysql> INSERT INTO customer_details
    ->  (customer_id, store_id, first_name, last_name,
    ->   address_id, active, create_date, address)
    -> VALUES (9999, 2, 'THOMAS', 'BISHOP', 7, 1, now(),
    ->  '999 Mockingbird Lane');
ERROR 1393 (HY000): Can not modify more than one base table 
  through a join view 'sakila.customer_details'

This version, which includes columns spanning two different tables, raises an exception. In order to insert data through a complex view, you would need to know from where each column is sourced. Since many views are created to hide complexity from end users, this seems to defeat the purpose if the users need to have explicit knowledge of the view definition.

Exercise 14-1

Create a view definition that can be used by the following query to generate the given results:

SELECT title, category_name, first_name, last_name
FROM film_ctgry_actor
WHERE last_name = 'FAWCETT'; 

+---------------------+---------------+------------+-----------+
| title               | category_name | first_name | last_name |
+---------------------+---------------+------------+-----------+
| ACE GOLDFINGER      | Horror        | BOB        | FAWCETT   |
| ADAPTATION HOLES    | Documentary   | BOB        | FAWCETT   |
| CHINATOWN GLADIATOR | New           | BOB        | FAWCETT   |
| CIRCUS YOUTH        | Children      | BOB        | FAWCETT   |
| CONTROL ANTHEM      | Comedy        | BOB        | FAWCETT   |
| DARES PLUTO         | Animation     | BOB        | FAWCETT   |
| DARN FORRESTER      | Action        | BOB        | FAWCETT   |
| DAZED PUNK          | Games         | BOB        | FAWCETT   |
| DYNAMITE TARZAN     | Classics      | BOB        | FAWCETT   |
| HATE HANDICAP       | Comedy        | BOB        | FAWCETT   |
| HOMICIDE PEACH      | Family        | BOB        | FAWCETT   |
| JACKET FRISCO       | Drama         | BOB        | FAWCETT   |
| JUMANJI BLADE       | New           | BOB        | FAWCETT   |
| LAWLESS VISION      | Animation     | BOB        | FAWCETT   |
| LEATHERNECKS DWARFS | Travel        | BOB        | FAWCETT   |
| OSCAR GOLD          | Animation     | BOB        | FAWCETT   |
| PELICAN COMFORTS    | Documentary   | BOB        | FAWCETT   |
| PERSONAL LADYBUGS   | Music         | BOB        | FAWCETT   |
| RAGING AIRPLANE     | Sci-Fi        | BOB        | FAWCETT   |
| RUN PACIFIC         | New           | BOB        | FAWCETT   |
| RUNNER MADIGAN      | Music         | BOB        | FAWCETT   |
| SADDLE ANTITRUST    | Comedy        | BOB        | FAWCETT   |
| SCORPION APOLLO     | Drama         | BOB        | FAWCETT   |
| SHAWSHANK BUBBLE    | Travel        | BOB        | FAWCETT   |
| TAXI KICK           | Music         | BOB        | FAWCETT   |
| BERETS AGENT        | Action        | JULIA      | FAWCETT   |
| BOILED DARES        | Travel        | JULIA      | FAWCETT   |
| CHISUM BEHAVIOR     | Family        | JULIA      | FAWCETT   |
| CLOSER BANG         | Comedy        | JULIA      | FAWCETT   |
| DAY UNFAITHFUL      | New           | JULIA      | FAWCETT   |
| HOPE TOOTSIE        | Classics      | JULIA      | FAWCETT   |
| LUKE MUMMY          | Animation     | JULIA      | FAWCETT   |
| MULAN MOON          | Comedy        | JULIA      | FAWCETT   |
| OPUS ICE            | Foreign       | JULIA      | FAWCETT   |
| POLLOCK DELIVERANCE | Foreign       | JULIA      | FAWCETT   |
| RIDGEMONT SUBMARINE | New           | JULIA      | FAWCETT   |
| SHANGHAI TYCOON     | Travel        | JULIA      | FAWCETT   |
| SHAWSHANK BUBBLE    | Travel        | JULIA      | FAWCETT   |
| THEORY MERMAID      | Animation     | JULIA      | FAWCETT   |
| WAIT CIDER          | Animation     | JULIA      | FAWCETT   |
+---------------------+---------------+------------+-----------+
40 rows in set (0.00 sec)

Exercise 14-2

The film rental company manager would like to have a report that includes the name of every country, along with the total payments for all customers who live in each country. Generate a view definition that queries the country table and uses a scalar subquery to calculate a value for a column named tot_payments.​