Secure Views

Oracle has supported database views for many years. Views can be used to solve many challenges, and views can be a tremendous security tool. Views can hide columns. Views can mask data values. Views can aggregate data to remove personally identifiable information for maintaining privacy. The

An effective security design usually involves the judicious use of views for any or all of the reasons just cited. Views are database objects and access to them occurs at the object level. However, privileges on the view are separate and distinct from the privileges on the underlying objects the view accesses. Allowing users access to a view and not to the underlying objects is an effective security technique for insulating your sensitive data.

Consider the example where the user SCOTT wishes to allow certain users to ascertain the number of employees for each department. He does not have to allow access to the EMP and DEPT tables. He can simply create a view that performs the calculation. Granting access to the view then allows users to retrieve this summary data while simultaneously maintaining separate security for the underlying objects:

scott@KNOX10g> -- Create a view showing employee scott@KNOX10g> -- distribution by department. scott@KNOX10g> CREATE OR REPLACE VIEW emp_dist   2  AS   3    SELECT   INITCAP (d.dname) "Department",   4             COUNT (e.ename) "Total Employees"   5        FROM dept d, emp e   6       WHERE e.deptno = d.deptno   7    GROUP BY dname; View created. scott@KNOX10g> -- Grant privileges to query view to user blake scott@KNOX10g> GRANT SELECT ON emp_dist TO BLAKE; Grant succeeded. scott@KNOX10g> CONN blake/blake Connected. blake@KNOX10g> -- BLAKE can query view blake@KNOX10g> SELECT * FROM scott.emp_dist; Department     Total Employees -------------- --------------- Accounting                   3 Research                     5 Sales                        6 blake@KNOX10g> -- BLAKE cannot query base tables blake@KNOX10g> SELECT * FROM scott.emp; SELECT * FROM scott.emp                     * ERROR at line 1: ORA-00942: table or view does not exist

This is an excellent technique in cases where privacy needs to be maintained. The view could easily be showing a medical researcher the number of patients that have been diagnosed with a certain illness. Likewise, the view could show a bank manager the number of customers with certain financial status. As the actual names are hidden by the view, any sensitive information that can be derived by correlating the department, diagnosis, or financial status with an individual is prevented.

Views for Column-Level Security

Views are an ideal tool for providing column-level security (CLS). What is CLS? CLS has three possible definitions:

• Preventing access to the column

• Masking the column values

• Controlling access to the values within a column

Column Removal

In the first definition of CLS, preventing access to the column means that the column is inaccessible to your users. For example, your security policy for the EMP table may dictate that you want to remove access to the SAL column because it contains sensitive data. The column values should not just be hidden—the column should not exist.

The view solution for CLS simply selects all columns except the SAL column. Then, by granting the users access to the view and not to the underlying table, you have successfully removed user access to your sensitive column data.

This example is largely a security by design solution—the security was done prior to developing and deploying any application code. This example may or may not be a possible solution for existing designs and fielded applications. For many applications, views can replace tables because many applications have no bias for querying directly against tables or directly against views.

However, a challenge may exist if an application can only access tables or if an application is already written and you expect it to go against a specifically defined table. In the last case, you may be able to rename the table and create the view with the name the table originally had. For example, if your application queries the EMP table and you want to provide a view that consists only of the ENAME and JOB columns, you could issue the following SQL to rename the table and create a view in its place:

-- rename existing table ALTER TABLE emp RENAME TO emp$; -- create view that removes sensitive columns CREATE VIEW emp AS   SELECT ename, job     FROM emp$; -- grant access to view GRANT SELECT ON emp TO user1; -- revoke access from table REVOKE SELECT ON emp$ FROM user1;

This trick may not work in all cases because some applications may depend on the existence of some or all of the columns you are attempting to remove. Removing access to the column may break an already developed application. Note that this is an indication that the application requires exclusive security rights over your data.

In many cases, views are the best and easiest method for providing CLS when the requirement dictates that users should not have access to a column.

Masking Column Values

The second CLS definition says some but not all of the column values are accessible. For this definition, I am only referring to the user reading or issuing SELECT statements. You will see how to implement CLS for data modification later in this section.

For the values that are not to be seen, you can mask the values returned to the user. For example, you may elect to return the string “NOT AUTHORIZED” or return the value zero when a user queries a column to which you wish to hide its real value. Be careful: a value of zero may imply one meaning (the actual value is zero) versus your intended meaning (the user does not have access).

Another masking option is to return a null value. Returning null values is a good choice because they are a standard value for data that does not exist. However, just as returning a zero value has a caveat, null values may incorrectly indicate the absence of a value when in reality the value exists but the user is not authorized to see it. If the application needs to distinguish between actual null values and masked data, then null values should not be used.

Consider an example where users can access only their salary. Users should be prohibited from accessing other users’ salary data. Because the user can access their salary, you cannot simply omit the SAL column from the view definition.

To meet this CLS requirement, a view can be used with a function that masks the values of the salary column. Views with functions are an effective column-level security technique. The functions can return different values for different rows based on a policy decision that is implemented within the function.

Putting this example to life begins by creating a simple table:

scott@KNOX10g> CREATE TABLE people   2  AS SELECT ename username, job, sal salary, deptno   3    FROM emp   4    WHERE deptno = 20; Table created.

The view will use the Oracle built-in DECODE function to implement the column masking. If the user accessing the record is the same as the person in the record, then they are allowed to see the salary; otherwise, a null value is returned.

scott@KNOX10g> -- create view that only returns the salary scott@KNOX10g> -- for the user issuing the query scott@KNOX10g> CREATE OR REPLACE VIEW people_cls   2  AS   3    SELECT username,   4           job,   5           deptno,   6           DECODE (username,   7                   USER, salary,   8                   NULL) salary   9      FROM people; View created. 

When granting privileges on the view, you can allow the user to read all records because the DECODE function provides the CLS to mask other users’ salaries. Do not grant privileges on the PEOPLE base table.

The column masking requirement is very popular in applications such as those used by some reporting tools. The reports have a predefined structure that often requires the existence of a column value for all records. A challenge exists in producing the report and maintaining security because you may not want the report users to see the actual values in all the columns for all the records. The view can be used as the source for the report generation:

scott@KNOX10g> SELECT * FROM people_cls; USERNAME        JOB           DEPTNO     SALARY --------------- --------- ---------- ---------- SMITH           CLERK             20 JONES           MANAGER           20 SCOTT           ANALYST           20       3000 ADAMS           CLERK             20 FORD            ANALYST           20

Updates to CLS Views

The previous view provides security for select statements. Note that even aggregate queries are subject to the security:

scott@KNOX10g> -- Computing salaries for everyone will only scott@KNOX10g> -- return user's salary. This is good security. scott@KNOX10g> SELECT SUM (salary) FROM people_cls; SUM(SALARY) -----------        3000

For data updates, a user cannot issue direct updates on the view. This could break your applications:

scott@KNOX10g> -- Give everyone a 10% raise. scott@KNOX10g> -- This will fail on the derived column scott@KNOX10g> UPDATE people_cls   2     SET salary = salary * 1.1;    SET salary = salary * 1.1        * ERROR at line 2: ORA-01733: virtual column not allowed here

This is an easy problem to solve. Oracle provides Instead-of triggers for performing DML operations on complex views. You can simply create an Instead-of trigger for this view. When you do this, you want to ensure the trigger’s behavior is consistent with the security policy provided by the view. For this example, this translates to having the user update only the records for the salaries they can see:

scott@KNOX10g> -- Create "instead of" trigger to perform updates on scott@KNOX10g> -- base table. Trigger code is based on view security. scott@KNOX10g> -- If user can see the salary, then update the record. scott@KNOX10g> CREATE OR REPLACE TRIGGER people_sal_update   2    INSTEAD OF UPDATE   3    ON people_cls   4    FOR EACH ROW   5  DECLARE   6  BEGIN   7    -- If salary is not null, then user has read access   8    -- so perform update to base table   9    IF :OLD.salary IS NOT NULL  10    THEN  11      -- Update base table  12      UPDATE people  13         SET salary = :NEW.salary  14       WHERE username = :NEW.username;  15    END IF;  16  END;  17  / Trigger created.

To demonstrate the effectiveness, you can view the current values, issue an update, and then validate the effects of the update. Note that the intention of this view is to transparently provide the CLS for the table. The base table should not be accessible to the application or users:

scott@KNOX10g> -- Display current values. scott@KNOX10g> SELECT username, salary FROM people; USERNAME       SALARY ---------- ---------- SMITH             800 JONES            2975 SCOTT            3000 ADAMS            1100 FORD             3000 scott@KNOX10g> -- Give everyone a 10% raise by updating view. scott@KNOX10g> -- This will succeed now the trigger has been created. scott@KNOX10g> -- Only the invoking user’s record will be updated. scott@KNOX10g> UPDATE people_cls   2     SET salary = salary * 1.1; 5 rows updated. scott@KNOX10g> -- show effects of update scott@KNOX10g> SELECT username, salary FROM people; USERNAME       SALARY ---------- ---------- SMITH             800 JONES            2975 SCOTT            3300 ADAMS            1100 FORD             3000

The update statement succeeds for all view records. The actual table update occurred only for the SCOTT user, as bolded in the previous output.

Performance of Views with CLS Functions

Using functions, such as DECODE, in the view definition is a powerful and convenient way to mask column values. However, security often competes with performance. You should always conduct a performance test on your security design before you begin building your applications on it. To test the performance of this design, create a PL/SQL function. Oracle built-in functions such as DECODE generally perform well and always outperform user-created PL/SQL functions, so this test will use the latter.

sec_mgr@KNOX10g> -- Creating performance test view sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION view_filter (   2    p_owner  IN  VARCHAR2)   3    RETURN VARCHAR2   4  AS   5  BEGIN   6    RETURN p_owner;   7  END;   8  / Function created.

There is no security in the function because the test should isolate the cost of calling the PL/SQL function for each record. This design is consistent with the previous implementation example because the view uses a function’s return value to generate a column.

Tip 

Always conduct a performance test on your security design before you begin building your applications on it. It’s easier to try alternative approaches before the application is built.

This test will time how long it takes to count records from a base table and compare that result to accessing the function-computed column in the view. To ensure your time quantities are measurable (Oracle queries can be done very quickly), you’ll need a large table. The following SQL creates the large table:

sec_mgr@KNOX10g> CREATE TABLE big_tab AS   2  SELECT * FROM all_objects; Table created. sec_mgr@KNOX10g> -- increase table size sec_mgr@KNOX10g> BEGIN   2    FOR i IN 1 .. 5   3    LOOP   4      INSERT INTO sec_mgr.big_tab   5        (SELECT *   6           FROM sec_mgr.big_tab);   7      COMMIT;   8    END LOOP;   9  END;  10  / PL/SQL procedure successfully completed. sec_mgr@KNOX10g> ANALYZE TABLE big_tab COMPUTE STATISTICS; Table analyzed.

Next, two views are created. The first uses the VIEW_FILTER PL/SQL function just created. The second uses the Oracle built-in DECODE function:

sec_mgr@KNOX10g> -- Create view calling user-defined PL/SQL function sec_mgr@KNOX10g> CREATE OR REPLACE VIEW big_ud_view   2  AS   3    SELECT owner, view_filter (owner) function_owner   4      FROM big_tab; View created. sec_mgr@KNOX10g> -- Create view calling a built-in PL/SQL function sec_mgr@KNOX10g> CREATE OR REPLACE VIEW big_bi_view   2  AS   3    SELECT owner,   4           DECODE (owner, owner, owner) function_owner   5      FROM big_tab; View created.

The performance test consists of counting all the records from the base table and the two views:

sec_mgr@KNOX10g> SET timing on sec_mgr@KNOX10g> -- time the query on the base table sec_mgr@KNOX10g> SELECT COUNT (owner) FROM big_tab; COUNT(OWNER) ------------      1323520 Elapsed: 00:00:06.57 sec_mgr@KNOX10g> -- time the query on the PL/SQL function view sec_mgr@KNOX10g> SELECT COUNT (function_owner) FROM big_ud_view; COUNT(FUNCTION_OWNER) ---------------------               1323520 Elapsed: 00:00:14.53 sec_mgr@KNOX10g> -- time the query on the DECODE view sec_mgr@KNOX10g> SELECT COUNT (function_owner) FROM big_bi_view; COUNT(FUNCTION_OWNER) ---------------------               1323520 Elapsed: 00:00:06.81

The Oracle built-in DECODE function adds almost no time to the overall query. The user-defined PL/SQL function view processed 1.3 million records in under 15 seconds. You can consider this either acceptable or twice as slow.

Also, note this is in some ways a best-case scenario. Any security code that is implemented in the function will slow the performance. Optimizing that code is important to performance. You should consider using application contexts, and Oracle built-in functions whenever possible to increase performance. Other optimization suggestions can be found in the “Tuning PL/SQL Applications for Performance” chapter of the Oracle PL/SQL User’s Guide and Reference.

CLS for Controlling Access to All Records within a Column

The database provides a CLS mechanism by default. This is the third definition of CLS. For inserts and updates, you can control at the object level whether a user has the ability to affect the values within the column. For example, to ensure the user can update only their phone number, you can specifically restrict the update privileges to just this column:

scott@KNOX10g> ALTER TABLE people ADD (phone_number VARCHAR2(20)); Table altered. scott@KNOX10g> GRANT UPDATE (phone_number) ON people TO user1; Grant succeeded.

The ability for the database to support column-level privileges allows the user to insert or update specific columns in a table while simultaneously restricting modifications to other columns. In cases where a user is allowed to change the column value for all records, this definition of CLS satisfies the security requirements.

Understand that the column-level privileges allow the user to update the column value for all records in the table. This may or may not be desirable. In the previous example, it may be unlikely that a user should have the ability to update other users’ phone numbers.

user1@KNOX10g> UPDATE scott.people   2     SET phone_number = '555-1212'; 5 rows updated.

The unqualified update just changed everyone’s phone number. This is a critical and often overlooked point: the database column privileges do not provide security for the individual rows. You have to provide that yourself.

Views for Row-Level Security

One particularly useful aspect of views is that they provide row-level security. Row-level security, sometimes referred to as fine-grained access control, ensures that security is applied not only to the object (for example, a database table) but also to each row within the object. When combined with a check constraint, views in this manner are simple to understand, implement, and manage.

Let’s investigate views as an implementation of row-level security. In this example, a view is used for an application that allows a user to update their personal record. Your requirements dictate that the Edit screen of your application will need to query all sensitive fields of the record. The user is allowed to issue updates but only to their record. Enforcing security at the database requires you to ensure that a user (or hacker) can’t update or modify someone else’s record.

This example will be based on a view that ensures the only record displayed to the user will be theirs. The view’s security will eliminate all other records. This is done by adding a predicate or where clause to the query on the base table.

scott@KNOX10g> CREATE OR REPLACE VIEW people_edit   2  AS   3    SELECT * FROM emp   4     WHERE ename =   5             SYS_CONTEXT ('userenv', 'session_user')   6           WITH CHECK OPTION; View created.

This example assumes the users will be directly authenticated to the database. Furthermore, each user has an exclusive schema in which the schema name matches the value stored in the ENAME column. However, this view could be easily modified to support a user identity based on the client identifier, the enterprise user’s external name, or some identifying value set in an application context.

You can see the queries are restricted to user records by querying from the view. Updates behave in a similar manner. When SCOTT tries to update another record, the update simply doesn’t occur:

scott@KNOX10g> SELECT empno, ename, sal   2    FROM people_edit;      EMPNO ENAME             SAL ---------- ---------- ----------       7788 SCOTT            3000 scott@KNOX10g> -- try to update another user's record scott@KNOX10g> UPDATE people_edit   2     SET ename = 'Bozo'   3   WHERE ename = 'KING'; 0 rows updated.

Just as updating records outside of the view definition has no effect, deletes are also constrained to the view definition. Inserts are allowed as long as the ENAME value matches the user’s name:

scott@KNOX10g> -- Try to delete all records. scott@KNOX10g> DELETE FROM people_edit; 1 row deleted. scott@KNOX10g> -- Inserts work as long as compliant scott@KNOX10g> -- with view's definition scott@KNOX10g> INSERT INTO people_edit   2              (empno, ename, sal)   3       VALUES (7788, 'SCOTT', '3000'); 1 row created. scott@KNOX10g> INSERT INTO people_edit   2              (empno, ename, sal)   3       VALUES (7788, 'SCOTT2', '3000'); INSERT INTO people_edit             * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

By introducing the CHECK option with the view (seen in line 6 of the view creation), the database will enforce inserts and updates on the view so that the resulting values don’t conflict with the view’s definition. While this could be considered a security solution, the database actually enforces the CHECK option for data integrity reasons.

Functions in Views for Row-Level Security

A popular approach to creating security within views is to place a PL/SQL function inside the view definition. When placed in the select list (before the FROM in the SQL) of the view definition, the function performs the role of column-level security. When the function is placed after the WHERE, or SQL predicate, the function acts as a row-level security mechanism. While functions in views for row-level security may be operationally correct and an interesting solution, overall view performance should be evaluated.

To illustrate the effect on performance, consider a view that calls a PL/SQL function in the SQL where clause. The function performs the security. The security policy for this example once again says the user can see only their records. You can determine if the record belongs to a user because your function accepts a record owner parameter. An exemption exists for a user with the SYSDBA role—these users get to see all records. Assume this added condition will require the use of a PL/SQL function:

sec_mgr@KNOX10g> -- Create the row security function. sec_mgr@KNOX10g> -- This function will be called by the view. sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION view_filter (   2    p_owner  IN  VARCHAR2)   3    RETURN NUMBER   4  AS   5  BEGIN   6    IF (   p_owner = USER   7        OR SYS_CONTEXT ('userenv', 'isdba') = 'TRUE')   8    THEN   9      RETURN 1;  10    ELSE  11      RETURN 0;  12    END IF;  13  END;  14  / Function created.

The view for this example queries BIG_TAB, the approximately1.3 million row table based on an aggregated copy of the ALL_OBJECTS table created previously in the “Performance of Views with CLS Functions” section. The view places the security function in the where clause.

By doing so, the function acts as a row-level security enforcement mechanism.

sec_mgr@KNOX10g> -- Creating view over table with function to filter rows sec_mgr@KNOX10g> CREATE OR REPLACE VIEW big_view   2  AS   3    SELECT *   4      FROM big_tab   5     WHERE 1 = view_filter (owner); View created.

The point of this exercise is to show that while the results are technically correct, the performance may not be acceptable. For performance comparison, first query by counting all records on the base table. Modify this query adding the security (the security the function ultimately implements) into the where clause. This will return the same number of records as the view, so your performance number shouldn’t be influenced by the number or records returned:

sec_mgr@KNOX10g> SET timing on sec_mgr@KNOX10g> SELECT COUNT (*)   2    FROM big_tab   3   WHERE 1 = DECODE (owner, USER, 1, 0)   4      OR SYS_CONTEXT ('userenv', 'isdba') = 'TRUE';   COUNT(*) ----------       1184 Elapsed: 00:00:06.90 

Run a query against the view for comparison. The where clause is implemented in the view function, so you don’t need to specify it in the query:

sec_mgr@KNOX10g> SELECT COUNT(*) FROM big_view;   COUNT(*) ----------       1184 Elapsed: 00:01:07.60 

The difference in times is due to the overhead of invoking a PL/SQL function for every record (all 1.3 million rows) in the table. The query results are the same, but the performance certainly isn’t. This is because the security implementation isn’t an effective performance implementation. You could have modified the view definition to include the security logic there:

sec_mgr@KNOX10g> CREATE OR REPLACE VIEW big_view2   2  AS   3    SELECT * FROM big_tab   4     WHERE 1 = DECODE (owner, USER, 1, 0)   5        OR SYS_CONTEXT ('userenv', 'isdba') = 'TRUE'; View created.

Doing this increases the performance while maintaining the security. Calling the built-in database functions, such as DECODE and CASE statements, is an effective way to implement security while maintaining performance.

There may be a point at which the security logic becomes so complex that a PL/SQL function call is desired or needed. Making the call to a PL/SQL function will reduce the performance, which may or may not make the security implementation an effective solution. Helping to solve this problem is the Virtual Private Database technology discussed in Chapter 11.

Viewing Problems

While views are popular methods for implementing security, mixing security policies for different DML statements adds complexity. Usually multiple views have to be created, one for each policy and statement. For example, the SELECT view may allow users to see all records, INSERT and UPDATE views can be created to allow updates within the user’s department, and a DELETE view can ensure users can delete only their records.

Additionally, the object security has to be maintained so that the DML privileges are granted directly to the view objects in congruence with their desired functionality (for example, grant select on SELECT_VIEW, grant delete on DELETE_VIEW, etc.). Instead-of triggers may also need to be developed to support the DML operations on columns in the views.

Finally, to get all this to work correctly, you need to ensure the application knows to issue selects on the SELECT_VIEW, deletes on the DELETE_VIEW, and so on. It would be undesirable to allow the user to issue deletes on the SELECT_VIEW.

The process of creating different views for different users with different policies can create a problem that gets exacerbated as the user roles and policies increase in complexity. For this reason, Oracle created integrated row-level security and branded the functionality as Virtual Private Database.