SQL Bible [Electronic resources] نسخه متنی

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

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.