3.5 Views Oracle allows a definition to be stored in the data dictionary that describes how data is to be retrieved from one or more tables. This logical definition is called a view. A view may be thought of as a layer on top of the tables that actually contain the data. Views do not store any data themselves ; they only define what data is to be retrieved and, in many cases, the restrictions for retrieving the data. Views are treated exactly like tables when data is being selected. In fact, in the sample statement below, there is no way to tell if the table emp_sal specified in the query is really a table or a view: SELECT employee_name, current_salary FROM emp_sal; Figure 3.1 shows how a user would access the view as though it were a table and how execution of the view causes required data to be retrieved from several tables. Figure 3.1. View implementation  Views can be used for several purposes relevant to security; for example, they can simplify user access by pre-joining tables and they can limit the data retrieved. 3.5.1 Using Views Consider the two tables referenced in Figure 3.1: employee and salary. The employee table contains the constant employee information such as name, social security number, and other basic data which is not expected to change. The salary table contains the employee number along with the employee's job, when the job was started and ended, as well as the salary. This type of structure would be used to maintain a history of employment. To simplify access, a view could be written to join the two tables and provide salary data along with the employee's name so that an authorized user could write a simple query as shown in the SELECT statement. Using the employee and salary tables, the view could be created as follows . CREATE OR REPLACE VIEW emp_sal AS SELECT a.employee_num, a.employee_name, a.init_employment_date, b.salary FROM employee a, salary b WHERE a.employee_num = b.employee_num; This view returns all rows with no limiting qualifications. In addition to simplifying the query itself, a view can be used to limit the data in different ways so that, depending on who is performing the query, different information will be returned. For example, when an employee who is not a manager queries the data, he sees only his own records. A manager, on the other hand, would see his records and all of the records of the people in his group , but not the records of his own manager. This approach is frequently referred to as row-level security . 3.5.2 Using Views for Security The employee table in this example could be modified to include an additional column: login_name. This column would be populated when the employee's account is created, or when the employee is given access to the database. The view can then be modified with an expanded WHERE clause: CREATE OR REPLACE VIEW emp_sal AS SELECT a.employee_num, a.employee_name, a.init_employment_date, b.salary FROM employee a, salary b WHERE a.employee_num = b.employee_num AND a.employee_num in (SELECT employee_num FROM employee WHERE login_name = user UNION SELECT b.employee_num FROM employee a, employee b WHERE a.login_name = user AND a.employee_name = b.manager_name); The first-order row is the employee's own record. This is specified by the first part of the nested SELECT that has only the one limiting condition: WHERE login_name = user. Second-order rows are the people who report directly to a manager. These are returned when the second part of the nested SELECT (the part after the UNION) returns all of the employee numbers of people who report to the employee running the query. This view returns multiple rows when an employee has people who report directly to him, and always returns the employee's row. For instance, the president will only see the managers who report to her, not the clerks who report to the managers. This is one method for implementing data security through the use of a view. 3.5.3 A Caution About Using Views A word of caution about views. The SQL that defines a view is executed and the data is generally assembled in a temporary table using the tablespace defined as TEMPORARY for the user. If the GROUP BY clause or a UNION statement is used in the definition, the data is gathered in the temporary tablespace before any user-added WHERE conditions are applied. If the view will return one million rows, and the additional WHERE conditions will reduce that million rows to 30, the one million are first assembled in the temporary table within the user's tablespace, then the additional WHERE conditions are applied to select the 30 of interest. Enough space must be present in the user's defined temporary tablespace to support the largest possible storage requirement to support the view. The potential for degradation of performance exists if a grouping function is used. Because Oracle must load a temporary table with the initial data values before any user-added WHERE conditions are applied, you may see a slower response time for a query based on one or more views than for a query based on the underlying tables. You may have created the view on a set of tables which have been joined together. This situation can also produce a slower response time. To determine whether a potential performance degradation exists, create an EXPLAIN PLAN for the query. If the view operator appears in the plan, the potential for degradation does exist. 3.5.4 Updating Views There is one other aspect of a view you need to remember. When creating a view that can be updated, (that is, either columns can be updated or new rows created), you can add the WITH CHECK OPTION clause at the end of the WHERE clause. This causes the WHERE clause to be evaluated when updates or inserts are made. By adding the WHERE clause evaluation up front, a user will be prevented from updating a row or entering a value that would subsequently not be able to be retrieved because the data would not satisfy the WHERE clause. Suppose you have a view being used by a user in department 20: SELECT * FROM employee WHERE department_num = (SELECT department_num FROM employee WHERE employee_name = user) ; where the assumption is that the employee_name column in the employee table contains the database login name. In this case, the user will only see records that match his own department. However, the user can insert a record into the view with a department of 30 and will not be able to retrieve the row later because of the "department_num = ... " clause. If the view is modified to add checking during UPDATE or INSERT like this: SELECT * FROM employee WHERE department_num = (SELECT department_num FROM employee WHERE employee_name = user) WITH CHECK OPTION; then the user will be prevented from adding a record with a department other than 20 because the insert is validated against the "WHERE department_num = ..." clause. The record will not be retrievable by the user, so the transaction is rejected. When the view is created, its definition specifies the columns (or fields) to be retrieved from each table. This approach provides column-level security. In the preceding examples, the login_name column was not included in the SELECT list for the view. Consequently, that column remains hidden from the users. When you are using updateable views that join tables, you must also consider any primary or foreign keys present on the underlying tables. The views must preserve the keys or the views will fail during update.  | 
