9.3 Views To refresh your memory, a view is a description of how data is to be retrieved from the underlying tables. It does not store data but it is treated as though it were a table in SQL statements. Views can be used to perform the following actions: -
Limit the rows accessible to a user (row-level security) -
Limit specific columns accessible to a user ( column-level security) -
Pre-join several tables (removing the requirement that the user understand the complexity of joining tables) Most of the views we discuss here are used for row-level security. One way to achieve row-level security is to use views that include qualifying conditions based on some characteristic of the user login id. If the table is never to be accessed via a query tool, then application-implemented security can be used. Even this form of security usually relies on the same login id characteristics. In this section we discuss the logic used to determine the structure of the views, and we provide examples of how the logic is incorporated into the view definitions. 9.3.1 View Syntax The view definitions we provide all use the command "SELECT *" for clarity. The * means retrieve all columns. In a real system, however, you would replace the * with a list of specific column names. The columns in the view take their names from the columns in the tables from which they are selected, but you can provide aliases for these columns. There are two syntax constructs you can use to declare column aliases in a view: with one you declare the aliases first, with the other you embed the aliases in the SELECT list. The following code examples both create the emp_v view on the employee table (the FROM and WHERE clauses are omitted for clarity): In this example you declare aliases first: CREATE OR REPLACE VIEW emp_v (employee_number, last_name, first_name, ...) AS SELECT emp_no, lname, fname, ... In this example, employee_number is an alias for emp_no, last_name is an alias for lname, etc. In this example you embed aliases within the clause: CREATE OR REPLACE VIEW emp_v AS SELECT emp_no employee_number, lname last_name, fname first_name, ... The aliases become the column names of the view when you either DESCRIBE the view or SELECT from the view. In the first example, the aliases listed after the view name are associated with the columns in the order in which they were declared and selected. In the second example, the same aliases are stated after each column name as part of the SELECT section of the statement. This is done by adding one or more spaces after the column, then stating the alias. There are three advantages to using the second method: -
The source code is easier to read and immediately identifies the column associated with an alias. -
Aliases only have to be provided for the columns that need them. -
It is easier to retrieve the source code of the second version from the data dictionary. Both approaches work, however, and create the same object. 9.3.2 Creating the Views In this section, we describe how the APPROVERS_V and CARD_HOLDER_V views are created. The third view listed in the matrix in Table 9.1 requires some tables that we haven't discussed, but the approach is generally the same. Naturally, applications generally have many more views than are shown here. 9.3.2.1 The APPROVERS_V view The APPROVERS_V view is used to provide a list of only the employees who have authority to approve purchases. Both credit card holder and approver data is stored in the CARD_HOLDER_B table. This view is simple but effective. CREATE OR REPLACE VIEW approvers_v AS SELECT * FROM card_holder_b WHERE card_holder_type = 'A'; As written here, this view will return all approvers. However, the requirement is that, for general usage, this view should show only the person who can approve purchases for a specific employee. Note that the person who provides the approver function will not necessarily be that employee's manager. Therefore, the view needs more qualifiers so only the one appropriate row will be returned. CREATE OR REPLACE VIEW approvers_v AS SELECT * FROM card_holder_b WHERE card_holder_type = 'A' AND emp_no = (SELECT approver_no FROM card_holder_b WHERE emp_no = (SELECT emp_no FROM employee WHERE emp_db_name = user ) ) ; The logic is to get the list of approvers, then only get the one whose employee number is registered to the user. To accomplish this, we have to get the user's employee number. This is done in the second (bottom) nested SELECT the one that is indented rightmost. Note that the emp_db_name from the EMPLOYEE table is matched with the pseudo column "user". This is one of several virtual columns available for use by anybody. The value of "user" is always the database login name. Once that value is obtained, the first nested SELECT will retrieve the employee number of the approver for that employee, then the SELECT part of the view will retrieve the CARD_HOLDER_B records for that emp_no. Only one record is returned: the employee's approver. Could this code have been written differently? Of course. Most SQL statements that use multiple tables and something other than a very simple WHERE clause can be written several ways. For example, the view could have been written as a join of the CARD_HOLDER_B and EMPLOYEE tables: CREATE OR REPLACE VIEW APPROVERS_V as SELECT ch.* FROM card_holder_b ch, employee e WHERE ch.card_holder_type = 'A' AND ch.emp_no = e.emp_no AND e.emp_no = (SELECT approver_no FROM card_holder_b chi, employee ei WHERE chi.emp_no = ei.emp_no AND ei.emp_db_name = user ) ; While both of these approaches will work, there is one more consideration. The view returns only one row, the user's approving manager. Administrators or other higher-level users will want to see all approvers. If either of the previous examples is used, another view similar to the first example will be needed. But, this will result in two pieces of code and additional maintenance. It would be easier to manage if the one view could provide one or more rows depending on the type of person using the view. This is the approach used in the Oracle data dictionary views. You can add an additional WHERE condition or a UNION, as shown in the following, to accomplish this goal: /* This part returns only the user's row */ CREATE OR REPLACE VIEW APPROVERS_V AS SELECT ch.* FROM card_holder_b ch, employee e WHERE ch.card_holder_type = 'A' AND ch.emp_no = e.emp_no AND e.emp_no = (SELECT approver_no FROM card_holder_b chi, employee ei WHERE chi.emp_no = ei.emp_no AND ei.emp_db_name = user ) UNION /* This part returns all approvers rows for managers, dept heads, and the dba. The UNION operator eliminates duplicate rows. */ SELECT ch.* FROM card_holder_b ch, employee e WHERE ch.card_holder_type = 'A' AND ch.emp_no = e.emp_no AND e.emp_no IN (SELECT approver_no FROM card_holder_b chi, employee ei WHERE chi.emp_no = ei.emp_no AND ei.emp_db_name = user AND UPPER(ei.job_title) IN ('MANAGER','DEPARTMENT HEAD','DBA') ) ; The type of syntax you should use is the one that performs best on your computer and database configuration. We suggest that before you decide on a production version, you perform timing tests to select the most efficient approach. 9.3.2.2 The CARD_HOLDER_V view The CARD_HOLDER_V view uses an approach similar to that of the APPROVERS_V view. Each employee is only allowed to see his or her own record. Managers can see the records of all employees in their own cost center. Note that the employee's manager is not necessarily the same as the employee's supervisor. Because this is a credit card system, the manager is concerned about cost center charges, and not all employees working in their cost center may be assigned to the manager. For this reason, we must use the associative table EMP_POS and the POSITION table as follows . CREATE OR REPLACE VIEW CARD_HOLDER_V AS SELECT ch.* FROM card_holder_b ch, employee e WHERE ch.card_holder_type = 'C' AND ch.emp_no = e.emp_no AND e.emp_db_name = user UNION SELECT ch.* FROM card_holder_b ch WHERE emp_no IN (SELECT emp_no FROM emp_pos ep, position p WHERE ep.pos_no = p.pos_no AND p.pos_no IN (SELECT pos_no FROM position pp, employee ee WHERE pp.cost_center = ee.cost_center AND ee.emp_db_name = user AND UPPER(ee.job_title) IN ('MANAGER','DEPARTMENT HEAD') ) ) ; Why is the base table called CARD_HOLDER_B instead of CARD_HOLDER? One reason is the security plan being followed. The naming conventions specify that tables accessed only through views or applications are to have the "_B" suffix to indicate that they are base tables. Only the administrator of the application and the DBA will use this table directly. Because the information in the table is quite sensitive, the users should not even know the name of the table. When privileges are granted on the view or on the view synonym, the privileges granted do not translate down to the underlying table. Thus, the view protects the name and structure of the underlying table.  | 
