Using Views for Security

One of the mechanisms that can be used to implement security is SQL views, (discussed in Chapter 4). Using views, it is possible to restrict data accessible to a user, the type of operations the user can perform through the views, or both.

Consider the following DDL SQL statement, which is generic enough to be acceptable in all three major RDBMS implementations:

CREATE VIEW v_customer_status ( name, status ) AS SELECT cust_name_s, cust_status_s FROM customer

This view selects only two fields from the table CUSTOMER, which has a total of seven fields. This is called vertical restriction, as it restricts access to the subset of columns (fields). The other fields might contain confidential information that should be accessible only to upper management. If you grant SELECT privilege to the view to some role (for example, ROLE "staff"), then everyone who belongs to that role would be able to see customers' names and statuses, while the rest of the information that the table contains remains inaccessible to them.

If the SELECT statement from the view V_CUSTOMER_STATUS is executed by an authorized person, it will produce the following results (but the same statement issued by a person who was not granted privileges to the view would generate an error):

SELECT * FROM v_customer_status name status -------------------------------------------------- ------ WILE SEAL CORP. Y MAGNETICS USA INC. Y MAGNETOMETRIC DEVICES INC. Y . . . . . . . . . . CHGO SWITCHBOARD INC. N

You can also restrict the access horizontally — by specifying a subset of rows. For example, you may want to grant access to the historical data, something that was entered into the table a year ago or earlier, and prevent access to data added after that date; or — using the example from the ACME database — say you have a sales force that is split in two groups according to responsibility and experience level (one group has salespersons that deal with clients whose orders total above 15,000, and the other group handles customers generating less volume). For the latter example, the SQL syntax for all three databases would be as follows:

CREATE VIEW v_customer_totals_over_15000 AS SELECT * FROM v_customer_totals WHERE total_price > 15000 

Selecting from the following view will bring up only the records for the customers whose total is over 15000:

SELECT * FROM v_customer_totals_over_15000 customer_name order_number total_price ---------------------- --------------- ------------------ WILE ELECTRONICS INC. 523735 15613.60 WILE BESS COMPANY 523741 15464.10 . . . . . . . . . . . . WILE SEAL CORP. 523775 15613.60 WILE SEAL CORP. 523781 15464.10

Note 

The view V_CUSTOMER_TOTALS_OVER_15000 is built upon another view, V_CUSTOMER_TOTALS, and different privileges can be assigned for each of these objects. Using this method, one can build a sophisticated, fine-grained security hierarchy.

Of course, both horizontal and vertical selection could be combined into a single view.

Views also can limit displayed data to summary information, like in V_CUSTOMER_TOTALS, where information about sales is summed up while being grouped by order number (see Appendix B for the SQL statements that create this view).

Additional restrictions that can be implemented in views include WHERE clauses and JOIN conditions. These are useful when more than one table is involved in a view. For example, you can restrict your view to show only customers that have placed an order and hide all others, as follows (syntax is valid for all three databases):

SELECT DISTINCT cust_name_s FROM customer cu JOIN order_header oh ON cu.cust_id_n = oh.ordhdr_custid_fn CUST_NAME_S -------------------------------------------------- WILE BESS COMPANY WILE ELECTROMATIC INC. WILE ELECTROMUSICAL INC. . . . . WILE ELECTRONICS INC. WILE SEAL CORP. 

Views are used not only for SELECT but also for UPDATE, INSERT, and DELETE statements. Some of these operations are governed by the inherent properties of a view object, and some can be specified when the view object is created. For example, you cannot update or insert views that were created using aggregate functions — attempting to do so would generate an error. This is an inherent behavior. On the other hand, for an updateable view you could create a constraint, which could accept or reject data modifications based on some criteria. There is more about constraints in the next section.