8.4. Restricting Data Access — Security

As data security issues grow increasingly important for organizations around the globe, views offer a powerful alternative in controlling or restricting access to sensitive information. Views, like tables, can prevent unauthorized users from accessing sensitive portions of data. This is important because security breeches pose great risks not only to an organization’s data resources but to the customer as well.

Views can be constructed to show a view of data different from what physically exists in the underlying base tables. Specific columns can be shown while others are hidden. This helps prevent sensitive information such as salary, medical, or credit card data from getting into the wrong hands. Or a view can contain a WHERE clause with any degree of complexity to restrict what rows appear for a group of users while hiding other rows. In the next example, a view called SOFTWARE_PRODUCTS_VIEW is created that displays all columns from the original table except the product cost (PRODCOST) column and restricts all rows except “Software” from the PRODUCTS table.

SQL Code

PROC SQL;
  CREATE VIEW SOFTWARE_PRODUCTS_VIEW AS
    SELECT prodnum, prodname, manunum, prodtype
      FROM PRODUCTS
        WHERE UPCASE(PRODTYPE) IN ('SOFTWARE'),
QUIT;

SAS Log Results

     PROC SQL;
     CREATE VIEW SOFTWARE_PRODUCTS_VIEW AS
       SELECT prodnum, prodname, manunum, prodtype
         FROM PRODUCTS
           WHERE UPCASE(PRODTYPE) IN ('SOFTWARE'),
NOTE: SQL view WORK.SOFTWARE_PRODUCTS_VIEW has
 been defined.
   QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.17 seconds

The SOFTWARE_PRODUCTS_VIEW view functions just as if it were a base table, although it contains no rows of data. All other columns with the exception of product cost (PRODCOST) are inherited from the selected columns in the PRODUCTS table. A view determines what columns and rows are processed from the underlying table and, optionally, the SELECT query referencing the view can provide additional criteria during processing. In the next example, the view SOFTWARE_PRODUCTS_VIEW is referenced in a SELECT query and arranged in ascending order by product name (PRODNAME).

SQL Code

PROC SQL;
  SELECT *
					FROM SOFTWARE_PRODUCTS_VIEW
					ORDER BY prodname;
QUIT;

Results

                         The SAS System

  Product                             Manufacturer
   Number  Product Name                     Number
  Product Type
__________________________________________________
___________________

     5002  Database Software                   500
  Software
     5004  Graphics Software                   500
  Software
     5001  Spreadsheet Software                500
  Software
     5003  Wordprocessor Software              500
  Software