This chapter includes the following sections:
About Security In Your Integrated Excel Workbook
If you are using a Fusion web application that does not enforce authentication, the integrated Excel workbook verifies and creates a valid user session when it connects to the Fusion web application before downloading any data.
The session that is established is used for each and every data transfer between the integrated Excel workbook and Fusion web application. The session is also used for web pages displayed from the integrated Excel workbook.
In a Fusion web application that is enforcing authentication, the integrated Excel workbook ensures that a valid, authenticated user session is established before transferring data to or from the web application.
For both authenticated and non-authenticated Fusion web applications, ADF Desktop Integration relies on the establishment of cookie-based sessions. With no authentication mechanism in place, your Fusion web application is not completely safe. Hence, you should enable ADF Security in your Fusion web application before you deploy your web application with integrated Excel workbooks. For information about ADF Security, see Enabling ADF Security in a Fusion Web Application in Developing Fusion Web Applications with Oracle Application Development Framework.
When you open the integrated Excel workbook, ADF Desktop Integration detects if the Fusion web application that the workbook runs against is a secure application and enforces authentication automatically. For authenticated web applications, the end user will always be prompted for credentials, even though the workbooks are downloaded from an authenticated web browser. Since the web browser and Excel are different operating system processes, they cannot share credentials (unless some form of Integrated Windows Authentication is used, such as Kerberos or NTLM). For information about Microsoft Kerberos, see
Integrated Excel Workbook Security Use Cases and Examples
When you open the integrated Excel workbook of a secure Fusion web application, a connection confirmation dialog appears and prompts you to connect to the Fusion web application, as shown in Figure 12-1. Note that the connection confirmation dialog also appears when the Fusion web application is not secure.
Figure 12-1 Dialog to Verify Connection
Description of "Figure 12-1 Dialog to Verify Connection"
If you click Yes to connect, another dialog appears that prompts you to enter user credentials. The dialog that appears depends on how the Fusion web application is configured to enforce authentication. Figure 12-2, for example, shows the dialog that appears when the Fusion web application enforces form-based login using Oracle Access Management.
Figure 12-2 Form-Based Login Dialog
Additional Functionality for Integrated Excel Workbook in a Secure Fusion Web Application
After you have secured your integrated Excel workbook, you may find that you need to add additional functionality for your workbook. The following sections describe other functionality that you can use:
Validating integrated Excel workbook: You can configure server-side and client-side data entry validation for the Fusion web application and the integrated Excel workbook. See Adding Validation to Your Integrated Excel Workbook .
Testing integrated Excel workbook: Before publishing and deploying your integrated Excel workbook, you must test it. See Testing Your Integrated Excel Workbook.
Publishing and deploying integrated Excel workbook: The final step after you design and validate your integrated Excel workbook is to publish and deploy it. See Deploying Your Integrated Excel Workbook .
Authenticating the Excel Workbook User
The integration of an Excel workbook with a secure Fusion web application requires an authenticated web session established between the integrated Excel workbook and the server that hosts the Fusion web application. ADF Security determines the mechanism used to authenticate the user.
If the end user opens an Excel workbook without a valid authenticated session, a login mechanism is invoked to authenticate the end user.
What Happens at Runtime: How the Login Method Is Invoked
A modal dialog appears that contains a web browser control after the login method is invoked. The 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 dialog shown in Figure 12-3. If the end-user successfully logs in, a new session between the integrated Excel workbook and the Fusion web application is created.
Figure 12-3 Dialog That Appears When a Fusion Web Application Uses Basic Authentication
Description of "Figure 12-3 Dialog That Appears When a Fusion Web Application Uses Basic Authentication"
The end user enters user credentials and, assuming these are valid, an authenticated session is created.
Login method is invoked when a session has already been established, it first invokes the
Logout action internally to terminate that session.
What Happens at Runtime: How the Web Application Session is Terminated
After the logout method is invoked, a dialog appears informing users that they have logged out of the current session. The user is automatically logged out when the workbook is closed, or when the Clear All Data option is selected from the runtime custom tab in Excel ribbon.
Figure 12-4 Dialog That Appears When a User Logs Out
After logging out, the end user may continue to work with data in the spreadsheet. When the end user next attempts to interact with the server (for example, invoke an Upload action), the end user will be prompted to log in again.
If two or more workbooks are open (in test or runtime mode) and running against the same Fusion web application, closing one workbook does not initiate the logout mechanism. The user continues to stay logged in and may continue to work on remaining open workbooks, and can open the closed workbook without being asked for credentials again. The user is logged out when all workbooks running against the same Fusion web application are closed.
Checking the Integrity of an Integrated Excel Workbook's Metadata
ADF Desktop Integration verifies 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 ADF Desktop Integration client registry file (
adfdi-client-registry.xml) that it also creates when you publish the integrated Excel workbook as described in Publishing Your Integrated Excel Workbook. ADF Desktop Integration stores the
adfdi-client-registry.xml file in the
WEB-INF directory of the Fusion web application.
If you republish the integrated Excel workbook, ADF Desktop Integration generates a new hash code value and replaces the value in the
adfdi-client-registry.xml file. ADF Desktop Integration creates the
adfdi-client-registry.xml file if it does not exist.
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 ADF Desktop Integration can generate a hash code value.
ADF Desktop Integration generates the hash code value using most 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. ADF Desktop Integration excludes the
WebAppRoot property from the hash code calculation since its value is expected to change at runtime.
For information about the workbook properties discussed here, see Table A-19.
Tamper-check is not performed for customization-enabled workbooks.
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.
Before you begin:
It may be helpful to have an understanding of how to verify the integrity of integrated Excel workbook's metadata. See Checking the Integrity of an Integrated Excel Workbook's Metadata.
You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. See Additional Functionality for Integrated Excel Workbook in a Secure Fusion Web Application.
To reset a workbook ID:
- Open the integrated Excel workbook.
- In the Workbook group of the Oracle ADF tab, click Workbook Properties.
- In the Edit Workbook Properties dialog, click the Reset WorkbookID link.
- Click Yes to confirm and reset the
WorkbookIDworkbook property in the Reset WorkbookID dialog that appears., as shown in Figure 12-5.
Figure 12-5 Reset Workbook ID Dialog
- Click OK.
What Happens When the Metadata Tamper-Check Is Performed
At runtime, the integrated Excel workbook regenerates the metadata hash code and provides it to the Fusion web application with the first server request. If the Fusion web application cannot get a match on this hash code, it returns an error to the integrated Excel workbook. On receiving an error from the tamper check process, the integrated Excel workbook reports this failure to the end user and closes the integration framework.
What You May Need to Know About Securing an Integrated Excel Workbook
Describes additional steps to secure data in Excel workbooks that you have integrated with a Fusion web application.
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 in Microsoft Excel
You can enhance the security of an integrated Excel workbook using Excel's functionality to set a password on a workbook. It prevents unauthorized users from opening or modifying the workbook. For information about Excel security features, see Excel's documentation.
Integrated Excel workbooks can be configured to cache data, as described in Restore Server Data Context Between Sessions. Make sure that you do not cache sensitive data in the integrated Excel workbook.
If the Fusion web application is running on the
httpsprotocol, you may receive a certificate error while connecting from an integrated Excel workbook. You can either install the required certificate using Microsoft Internet Explorer, or choose to continue to log in and connect to the web application.
End users that download integrated Excel workbooks using Microsoft Internet Explorer may be prompted unexpectedly for credentials before the Excel application is visible, and then prompted again once the workbook opens. This may occur when the web application is configured to use certain authentication methods like
Digest. The extra prompt is due to Excel making an
OPTIONSrequest on the web directory containing the workbook.
To avoid the extra login prompt, end users can choose to save the workbook locally instead of opening it directly from the browser.
For a non-authenticated Fusion web application, end-users will not be prompted to log in. However if the application uses the
httpsprotocol, then end users may briefly see a connection confirmation dialog appear when the first connection is established to the web application. Workbook developers can control the size of the dialog with the
If you are an administrator, you should also see What You May Need to Know About Configuring Security in a Fusion Web Application.
Authorizing the Excel Workbook User
ADF Desktop Integration enforces view permission for integrated Excel worksheets through page definition authorization. At runtime, end users without proper permissions for a page definition (binding container) are prevented from interacting with the associated integrated Excel worksheet.
Any attempt to interact with an unauthorized binding container (for example, download or submit data) is aborted, the end user is informed of the authorization failure, and all ADF Desktop Integration activity on the worksheet is disabled. No further interaction with the ADF Desktop Integration-disabled worksheet is possible until a new user session is established. To allow end users to interact with the integrated Excel worksheet, assign them the roles that have been granted access to the page definition.
You may need to review the resource grants for all of the page definitions that are used with integrated Excel worksheets. For example, if your Fusion web application supports authorization, and you have a page definition
myWorksheetPageDef.xml that has no resource grants and is used by one (or more) integrated Excel worksheets, then you need to assign end users the roles that have been granted access to the page definition. During early development, you may find it helpful to temporarily create resource grants for the worksheet page definitions that are granted to authenticated-role, or some other generic role, allowing you to run those worksheets while you fine tune your roles and resource associations.
For information about authorization, roles, and resource grants, see Enabling ADF Security in a Fusion Web Application in Developing Fusion Web Applications with Oracle Application Development Framework.
ADF Desktop Integration only enforces authorization for resource grants that have the Web Page (page definition) resource type. Other resource types are not supported.
You can configure resources and grants from the Resource Grants page of the overview editor for the
jazn-data.xml file. For information, see Defining ADF Security Policies in Developing Fusion Web Applications with Oracle Application Development Framework.
On an authorization failure, the end user receives an error message, such as the following, and ADF Desktop Integration in the worksheet is disabled:
ADFDI-05589 You are not authorized to use this worksheet for interacting with the web application.
What You May Need to Know About ADF Desktop Integration-Disabled Worksheet
The following limitations apply to an ADF Desktop Integration-disabled worksheet:
All worksheet-level ribbon commands and worksheet-level events are disabled.
If the authorization failure occurs during worksheet initialization, no form labels or table column headers are drawn on the worksheet.
If the authorization failure occurs for an initialized worksheet, worksheet components (such as ADF Input Text and ADF Table) are not affected and are left visually unchanged.
End user can perform standard Excel interactions on the disabled worksheet. The user may alter the data in an ADF Table component in the worksheet, but the Changed column will not be updated.
There is no impact on workbook-level commands. End users can continue to use the following commands: Login, Logout, About, Edit Options, and Clear All Data.
An ADF Desktop Integration-disabled worksheet is automatically enabled when the end user reopens the integrated Excel workbook and establishes a new session, provided the new session is authorized. Logging out, and then logging in again, also re-enables ADF Desktop Integration in a disabled integrated Excel worksheet.