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).
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 |