In addition to the restrictions on table access provided by the SQL privileges, views also play a key role in SQL security. By carefully defining a view and giving a user permission to access the view but not its source tables, you can effectively restrict the user’s access to only selected columns and rows. Views thus offer a way to exercise very precise control over what data is made visible to which users.
For example, suppose you wanted to enforce this security rule in the sample database:
Accounts receivable personnel should be able to retrieve employee numbers, names, and office numbers from the SALESREPS table, but data about sales and quotas should not be available to them.
You can implement this security rule by defining a view as follows:
CREATE VIEW REPINFO AS
SELECT EMPL_NUM, NAME, REP_OFFICE
FROM SALESREPS
and giving the SELECT privilege for the view to the ARUSER user-id, as shown in Figure 15-3. This example uses a vertical view to restrict access to specific columns.
Horizontal views are also effective for enforcing security rules such as this one:
The sales managers in each region should have full access to SALESREPS data for the salespeople assigned to that region.
As shown in Figure 15-4, you can define two views, EASTVIEWS and WESTVIEWS, containing SALESREPS data for each of the two regions, and then grant each office manager access to the appropriate view.
Of course, views can be much more complex than the simple row and column subsets of a single table shown in these examples. By defining a view with a grouped query, you can give a user access to summary data but not to the detailed rows in the underlying table. A view can also combine data from two or more tables, providing precisely the data needed by a particular user and denying access to all other data. The usefulness of views for implementing SQL security is limited by the two fundamental restrictions described earlier in Chapter 14:
- Update restrictions. The SELECT privilege can be used with read-only views to limit data retrieval, but the INSERT, DELETE, and UPDATE privileges are meaningless for these views. If a user must update the data visible in a read-only view, the user must be given permission to update the underlying tables and must use INSERT, DELETE, and UPDATE statements that reference those tables.
- Performance. Because the DBMS translates every access to a view into a corresponding access to its source tables, views can add significant overhead to database operations. Views cannot be used indiscriminately to restrict database access without causing overall database performance to suffer.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.