Benefits - MySQL

At least some of the advantages of using views should be apparent. For instance, suppose you have a user who is interested in seeing which product categories have the most products and by which departments these products are sold. Without bogging down the discussion with an additional schema at this point, let’s just suppose that you can create a view fulfilling these requirements as follows:

CREATE VIEW product_view (catid, catname, catcount, deptname) AS
SELECT c1.cid, c2.name, c1.ct, d.name AS category
FROM
    ( SELECT
        category_id AS cid ,
        department.id AS did,
        COUNT(category_id) AS ct
      FROM products
      GROUP BY category_id 
    ) AS c1
JOIN categories c2
ON c1.cid = c2.id
JOIN departments d
ON d.id = c2.department_id
WHERE c1.ct > 1
GROUP BY d.id;

Once this is done, your user can obtain the current listing by running a simple SELECT query on the view, like so:

SELECT * FROM product_view;

The view acts in all respects like any other table, except that your user does not need to worry about doing a join on three tables in order to obtain the data that he or she actually wants to see. Your user can order or filter the rows of this view:

SELECT * FROM product_view ORDER BY deptname, catname;

Or even use it in a join on another table:

SELECT DISTINCT(p.deptname), CONCAT(m.firstname, ' ', m.lastname) AS manager FROM product_view p
JOIN departments d
ON p.deptname = d.deptname
JOIN managers m
ON d.manager_id = managers.id
ORDER BY m.lastname ASC;

Of course, this join could very likely be done more efficiently by writing a new query against the original tables involved rather than using the view, and if the data were needed often enough in this form, you might want to define a new view that does so. If you’re in doubt, you can always compare the two methods using the EXPLAIN command to see how much of an efficiency boost you would gain from doing so.

Security and privacy protection are another issue that views can help to address. Let’s look at a scenario involving employee records. Suppose we want to build an address book application for our company intranet allowing employees to obtain name, department, and internal contact information for other employees, but not giving access to confidential data such as salary history or home address and phone. We could define a view such as

CREATE VIEW address_book_view (fname, laname, dept, title, email, ext) AS
SELECT e.firstname, e.lastname, d.name, j.jobtitle, e.email, e.workphone
FROM jobs j
JOIN employees e
ON e.job_id = j.id
JOIN departments d
ON e.department_id = d.id;

The address book application could then query this view as desired:

#  List of mail room employees
SELECT * FROM address_book_view WHERE deptname = 'Mail Room';
#  List all receptionists
SELECT * FROM address_book_view WHERE title LIKE '%receptionist%';
#  Get the number of receptionists in each department
SELECT deptname, COUNT(deptname) AS num
  FROM address_book_vie w
  WHERE title LIKE '%receptionist%'
  GROUP BY deptname;
# etc., etc. ...

These queries can include whatever sorting, filtering, and grouping criteria that are needed.

You don’t have to worry about address book users obtaining confidential information like home telephone numbers, rates of pay, marital status, and so on, because that data is never available to them—or even to the programmers writing the address book application.

On the other hand, for the payroll department, a view such as this one would be more applicable:

CREATE VIEW payroll_view
  (fname, minitial, lname, addr1, addr2, city, state, postcode, title, rate,
    mstatus, numdependents, taxid)
AS
  SELECT e.firstname, e.lastname, e.address1, e.address2, e.city, e.state,
    e.postcode, j.jobtitle, j.rate, e.mstatus, e.numdependents, e.taxid
  FROM employees e
  JOIN jobs j
  ON e.job_id = j.id
  WHERE e.startdate <= NOW()
    AND e.enddate >= DATE_SUB(NOW(), INTERVAL 30 DAY);

By now, you’re probably getting the idea, but there’s more to keeping data secure than this. Not only do we sometimes need to prevent unauthorized par ties from reading certain types of information, but we must also be able to protect data from being overwritten when it shouldn’t be. If you look back for a moment at the views that we created in the previous section, you’ll recall that it’s sometimes possible to update a view and thus update the data in the base table from which it derives. However, there are often times when we don’t want this to happen. Some databases, like Oracle, allow you to deal with this type of situation by declaring a view to be read-only. MySQL doesn’t afford this option.

So how do we accomplish this task? The answer lies in leveraging the MySQL privileges system, which applies to views in much the same way as it does to tables. For instance, we can use table-level permissions in such a way that users may access views but not their base tables. Still referring to the examples in the previous section, suppose we want to create a user named student who can access either of the big_cities or big_cities_students views, but not any of the base tables in the world database. You can create such a user using standard GRANT commands, as shown here:

This user then logs in using the MySQL Monitor, and runs a SELECT query on big_cities. However, when the user tries to run an UPDATE on this table, he or she gets an access denied error from MySQL:

When this user attempts to list all the tables and views in the world database, the user sees only those views for which he or she has been granted privileges on, just as with regular tables:

Then there is the question of users accessing information about how views are defined. First of all, you can rest assured that users can’t find out what’s in base tables upon which they have no privileges by running a SHOW CREATE VIEW , as you can see here:

Users also cannot create views without restriction even on views (or tables) on which they have SELECT privileges, as shown here:

Beginning with MySQL 5.0.1, two new privilege types are introduced: the CREATE VIEW and SHOW CREATE VIEW privileges. Users must have the first of these privileges in order to create views and the second in order to see how existing views were created. In addition, users must have the proper privileges on the tables or views that they wish to use as the basis for new views.

Columns corresponding to these two privileges, named Create_view_priv and Show_view_priv, are added to the db, host, and user tables of the mysql database to accommodate these. Here’s the output of a SHOW CREATE TABLE statement showing how these columns are defined in the updated user table:

These privileges can be granted and revoked by setting the proper column values in the user, host, or db tables, or (preferably) through the use of one or more of the following:

1. GRANT CREATE VIEW
   
2. GRANT SHOW CREATE VIEW
   
3. REVOKE CREATE VIEW
   
4. REVOKE SHOW CREATE VIEW

A full discussion of these is beyond the scope of this chapter; however, if you’re familiar at all with the MySQL privilege system, you should not have a great deal of trouble figuring out how these commands ought to be used.