11 Securing Your Integrated Excel Workbook

This chapter provides information on how to secure your integrated Excel workbook with a Fusion web application by providing authentication and authorization capabilities. It also describes a number of issues that you should be aware of so that you can secure your Excel workbook and its data.

This chapter includes the following sections:

• Section 11.1, "Introduction to Securing Your Integrated Excel Workbook"

• Section 11.2, "Authenticating the Excel Workbook User"

• Section 11.3, "Checking the Integrity of an Integrated Excel Workbook's Metadata"

• Section 11.4, "What You May Need to Know About Securing an Integrated Excel Workbook"

11.1 Introduction to Securing Your Integrated Excel Workbook

Configure Oracle ADF Security for the Fusion web application before you test or deploy an integrated Excel workbook. An integrated Excel workbook must use a cookie-based web session ID to determine if the client (Excel workbook) has established a valid session with the server that hosts the Fusion web application. After you configure security for the Fusion web application in JDeveloper, you configure a number of properties, such as ProtectedWebPage, in the Excel workbook so that the Excel workbook user can be authenticated by the server hosting the Fusion web application.

For information about Oracle ADF Security, see the "Enabling ADF Security in a Fusion Web Application" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

11.2 Authenticating the Excel Workbook User

An integration of an Excel workbook with a Fusion web application requires an authenticated cookie-based web session ID from the server that hosts the Fusion web application. Oracle ADF Security determines the mechanism used to authenticate the user.

If a user opens an Excel workbook without a valid authenticated cookie-based web session ID, a login mechanism to authenticate the end user is invoked when one of the following events occur:

• Invocation of the workbook Login action

• Invocation of a server request that requires a valid cookie-based web session ID before the request can be served

11.2.1 How a Fusion Web Application Determines If an Excel Workbook Has a Web Session ID

The server that hosts the Fusion web application determines whether the client has an authenticated cookie-based web session ID by locating and validating the web session ID in the request header from the client. If the client already has a valid cookie-based web session ID, all subsequent requests sent to the server hosting the Fusion web application include the web session ID attached to the request header. If the cookie-based web session ID is expired, the server responds with the login mechanism specified by the Fusion web application. If the client does not have a cookie-based web session ID, the client sends a request to the server for the page referenced by the ProtectedWebPage workbook property. The server responds with the login mechanism. In both instances (cookie-based web session ID expired and no cookie-based web session ID), the login mechanism to authenticate the user appears in the browser control hosted by the Excel workbook.

The value of ProtectedWebPage is a URL that references a protected web page. You specify a value for ProtectedWebPage when you want to run the integrated Excel workbook in test mode. For information about how to specify a value for ProtectedWebPage, see Section 4.4.2, "How to Open an Excel Workbook for the First Time".

11.2.2 What Happens at Runtime When the Login Mechanism Is Invoked

Once the login mechanism is invoked, a modal dialog box appears that contains a web browser control. This web browser control displays whatever login mechanism the Fusion web application uses. For example, if the Fusion web application uses HTTP Basic Authentication, the web browser control displays the simple dialog box shown in Figure 11-1.

Figure 11-1 Dialog Box That Appears When a Fusion Web Application Uses Basic Authentication

The user enters user credentials and, assuming these are valid, a cookie-based web session ID is created and assigned to the client (the web browser control hosted by the Excel workbook).

11.3 Checking the Integrity of an Integrated Excel Workbook's Metadata

The Oracle ADF Desktop Integration module provides a mechanism to check that the metadata it uses to integrate an Excel workbook with a Fusion web application is not tampered with after you publish the Excel workbook for end users. It generates a hash code value and inserts the value into the Oracle ADF Desktop Integration module client registry file (adfdi-client-registry.xml) that it also creates when you publish the integrated Excel workbook as described in Section 14.4, "Publishing Your Integrated Excel Workbook". The Fusion web application stores the adfdi-client-registry.xml file in the WEB-INF directory.

If you republish the integrated Excel workbook, the Oracle ADF Desktop Integration module generates a new hash code value and replaces the value in the adfdi-client-registry.xml file. The Oracle ADF Desktop Integration module generates an error message if the adfdi-client-registry.xml file does not exist.

The ApplicationHomeFolder and WebPagesFolder workbook properties allow the integrated Excel workbook to identify the location of the Fusion web application's WEB-INF directory. You must set valid values for these properties before you can publish the integrated Excel workbook and the Oracle ADF Desktop Integration module can generate a hash code value.

The Oracle ADF Desktop Integration module generates the hash code value using some of the elements in the metadata for the workbook and the value of the WorkbookID workbook property. The WorkbookID workbook property is read-only and uniquely identifies the integrated Excel workbook. You must reset the WorkbookID workbook property if you create a new integrated Excel workbook by copying an existing integrated Excel workbook. The elements in the metadata used to generate the hash code value include the entries for columns in rows 2 to N where N is the last used row. The Oracle ADF Desktop Integration module does not use the metadata entries for the WebAppRoot, Project, ApplicationHomeFolder and WebPagesFolder workbook properties when generating the hash code value.

For more information about the workbook properties discussed here, see Table A-17.

11.3.1 How to Reset the Workbook ID

The value of the WorkbookID workbook property is unique to each workbook and cannot be modified by you. You can, however, reset the WorkbookID workbook property. You must do this when you create a new integrated Excel workbook by copying an existing integrated Excel workbook.

To reset a workbook ID:

1. Click Tools > Reset WorkbookID in the Oracle ADF Desktop Integration Designer toolbar.

2. Click Yes to confirm that you want to reset the WorkbookID workbook property in the dialog box that appears.

3. Click OK.

11.3.2 How to Disable the Metadata Tamper-Check in the Fusion Web Application

By default, the Oracle ADF Desktop Integration module checks that the metadata it uses to integrate an Excel workbook with a Fusion web application is not tampered with after you publish the Excel workbook for end users. You can disable the metadata tamper-check by configuring a parameter in the deployment descriptor file (web.xml) of the Fusion web application, although this is not recommended.

To disable the metadata tamper-check in the Fusion web application:

1. Stop your Fusion web application.

2. Open the web.xml file of your Fusion web application.

3. Add an initialization parameter to the adfdiRemote servlet to disable the metadata tamper-check, as described in the following table.

   Property	Value
   Name	Enter the name of the initialization parameter as follows: TamperingCheck.Enabled
   Value	Set the value of TamperingCheck.Enabled to False.

   
   

4. Save the web.xml file.

   The web.xml file of your Fusion web application has the following entries:

   <servlet>
           <servlet-name>adfdiRemote</servlet-name>
           <servlet-class>...</servlet-class>
           <init-param>
               <param-name>TamperingCheck.Enabled</param-name>
               <param-value>False</param-value>
           </init-param>
   </servlet>
   

5. Rebuild and restart your Fusion web application.

For more information about the web.xml file, see Appendix E, "Desktop Integration Settings in the Web Application Deployment Descriptor".

11.3.3 How to Allow Missing Entries in the Oracle ADF Desktop Integration Client Registry

You can configure the metadata tamper-check so that a missing entry for the WorkbookID workbook property is allowed in the adfdi-client-registry.xml file. To do this, you configure a parameter in the deployment descriptor file (web.xml) of the Fusion web application, although this is not recommended.

To allow missing entries in the metadata of the Fusion web application:

1. Stop your Fusion web application.

2. Open the web.xml file of your Fusion web application.

3. Add an initialization parameter to the adfdiRemote servlet to allow missing entries in the metadata as described in the following table.

   Property	Value
   Name	Enter the name of the initialization parameter as follows: TamperingCheck.AllowMissingEntries
   Value	Set the value of TamperingCheck.AllowMissingEntries to True.

   
   

4. Save the web.xml file.

   The web.xml file of your Fusion web application has the following entries:

   <servlet>
           <servlet-name>adfdiRemote</servlet-name>
           <servlet-class>...</servlet-class>
           <init-param>
               <param-name>TamperingCheck.AllowMissingEntries</param-name>
               <param-value>True</param-value>
           </init-param>
   </servlet>
   

5. Rebuild and restart your Fusion web application.

For more information about the web.xml file, see Appendix E, "Desktop Integration Settings in the Web Application Deployment Descriptor".

11.3.4 What Happens at Runtime When the Fusion Web Application Checks the Hash Code

The Oracle ADF Desktop Integration module regenerates a hash code using the WorkbookID workbook property and the metadata in the instance of the published integrated Excel workbook that the end user accesses. The Oracle ADF Desktop Integration module regenerates this hash code when the end user executes an action that requires a connection to the Fusion web application. After the Fusion web application authenticates the end user, it compares the hash code it stores in the adfdi-client-registry.xml file of the WEB-INF directory (created when you published the workbook) with the newly regenerated hash code sent by the workbook the end user accesses.

If the values match, the Fusion web application performs the action that the end user requested. If the values do not match, the Fusion web application terminates the session created for the integrated Excel workbook as it considers the workbook untrustworthy.

11.4 What You May Need to Know About Securing an Integrated Excel Workbook

Note the following points about securing an integrated Excel workbook with a Fusion web application:

• Data security

  If you save an Excel workbook containing data downloaded from a Fusion web application to a location, such as a network directory, where other users can access the Excel workbook, the data stored in the Excel workbook is accessible to other users.

• Security and protection in Microsoft Excel

  Certain security and protection features that Microsoft Excel provides do not work for workbooks or worksheets that are integrated with a Fusion web application. For example, you cannot use the worksheet protection feature for a worksheet that you integrate with a Fusion web application. You can, however, use Excel's functionality to set a password on an integrated Excel workbook to prevent unauthorized users from opening or modifying it. For more information about Excel security features, see Excel's documentation.

• Integrated Excel workbooks can be configured to cache data, as described in Section 15.3, "Reestablishing Server Data Context Between Sessions". Make sure that you do not cache sensitive data in the integrated Excel workbook.

• Personally Identifying Information

  If the Fusion web application that you integrate an Excel workbook with uses a security mechanism, such as single sign-on, personally identifying information may be stored in cookies on the machine where an end user accesses the integrated Excel workbook. End users can remove this information using Microsoft Internet Explorer. End users must log out from and close all integrated Excel workbooks to invalidate all active cookie-based web session IDs.

  For information on removing personal information, see Microsoft Internet Explorer's documentation.