7 Controlling Access to Information

This chapter explains how to control access to information using Discoverer Administrator, and contains the following topics:

7.1 About Discoverer and security

As a Discoverer manager, it is your responsibility to control the information that users can access and what they can do with that information. You use Discoverer access permissions and task privileges as follows:

  • you use Discoverer access permissions to control who can see and use the data in business areas

  • you use Discoverer task privileges to control the tasks each user is allowed to perform

You can grant Discoverer access permissions and task privileges to database roles and to database users. When you grant access permissions or task privileges to a role, all users with that role have the role's access permissions and task privileges. If you run Discoverer Administrator in Oracle Applications mode, you grant access permissions or task privileges to Oracle Applications Responsibilities instead of roles. For more information about Oracle Applications mode, see "What features does Discoverer support for Oracle Applications users?".

The access permissions and task privileges that you grant in Discoverer Administrator only apply to Discoverer's business areas and not to the underlying database tables. Data access rights to the database tables remain under the control of the database administrator.

Regardless of the access permissions and task privileges that you set in Discoverer Administrator, a Discoverer end user only sees folders if that user has been granted the following database privileges (either directly or through a database role):

  • SELECT privilege on all the underlying tables used in the folder

  • EXECUTE privilege on any PL/SQL functions used in the folder

You can enable a user to perform administrative tasks (for example, the creation of folders, calculations, conditions, hierarchies, summaries) in a business area by granting that user Administration privilege on the business area. A user with the Administration privilege on a particular business area can also grant Administration privilege on that business area to other users. Note that although you can devolve business area administration to multiple users, it is often easier to maintain control with a single administrator for each business area.

7.2 About Discoverer access permissions

Discoverer access permissions enable you to control who can see and use the data in business areas.

You control access to business areas in two ways:

Before Discoverer end users see folders in a business area, Discoverer confirms that the user has database access to the tables referenced by the folders. If the user does not have access to a table referenced by a folder, Discoverer does not display the folder. You can override this behavior (for example, to improve performance where access privileges rarely change) by changing the value of the ObjectsAlwaysAccessible registry setting (for more information, see Chapter 21, "Discoverer Registry Settings").

7.3 About Discoverer and the Oracle Advanced Security Option (ASO)

Discoverer is certified with the Oracle Advanced Security Option (ASO) encryption technology provided by the Oracle database (in Oracle Enterprise Edition databases). The certification has four encryption types (RC4, DES, Triple-DES and AES). Oracle ASO encryption incurs little performance overhead, although performance will vary depending on several factors (for example, the operating system, the encryption algorithm). For more information about Oracle ASO encryption, see the Oracle9i Security Overview.

7.4 About Discoverer task privileges

Discoverer task privileges enable you to control the tasks each user is allowed to perform.

You use task privileges to specify whether a Discoverer end user is able to:

  • create new worksheets or edit existing ones (without this option, a user only has the ability to run predefined worksheets)

  • use item drills, drill to related items, and drill from summary to detail items

  • drill out to launch other applications

  • grant access to workbooks to other users

  • create and edit scheduled workbooks

  • save workbooks to the database

  • collect query performance statistics

You also use task privileges to specify whether a user of Discoverer Administrator is able to:

  • edit only the formatting information in an existing business area

  • create new business areas and edit existing ones

  • create summary tables

  • grant and revoke EUL privileges

  • maintain the scheduled workbooks of end users

7.5 How to specify a user or role (responsibility) that can access a business area

Note: When Oracle Applications database users are connected, Discoverer Administrator displays responsibilities instead of roles.

To specify the users or roles that can access a specific business area:

  1. Choose Tools | Security and select the "Security dialog: Business Area - > User tab".

    Discoverer Administrator displays the following warning dialog.

    Figure 7-1 Warning dialog

    Surrounding text describes Figure 7-1 .
  2. Click Yes to display the "Security dialog: Business Area - > User tab".

    Figure 7-2 Security dialog: Business Area->User tab

    Surrounding text describes Figure 7-2 .

    Note: To change the maximum number of rows that Discoverer will display in the Available users/roles list, you edit the value of the Discoverer registry setting MaxNumListRows (for more information, see Chapter 21, "Discoverer Registry Settings").

  3. Select the business area to which you want to grant access from the Business area drop down list.

  4. Specify the content of the Available users/roles list by selecting the Users check box and the Roles check box, as appropriate.

  5. Move the users or roles that you want to have access to the selected business area from the Available users/roles list to the Selected users/roles list.

    You can select more than one user or role by pressing the Ctrl key and clicking another user or role.

  6. For each new user or role you add to the Selected users/roles list, follow the instructions below to specify whether they have administration access to the business area:

    1. Click the user or role in the Selected users/roles list.

    2. Select or clear the Allow Administration check box as required.

    The setting of a user's Allow Administration privilege controls which administration tasks the user can perform. For more information, see "How to specify the tasks a user or role (responsibility) can perform".

  7. Click OK to save the changes you have made and close the Security dialog.

Notes

  • To remove access to a business area from a user or role, move the user or role from the Selected users/roles list to the Available users/roles list.

  • The Available users/roles list includes a role called PUBLIC. Select this role to view or edit the default access permissions for users or roles whose permissions you have not yet defined.

  • If you run Discoverer Administrator as an Oracle Applications user, the Security dialog shows Oracle Applications Responsibilities instead of roles. For more information about Applications mode, see "What features does Discoverer support for Oracle Applications users?".

7.6 How to specify the business areas a user or role (responsibility) can access

Note: When Oracle Applications database users are connected, Discoverer Administrator displays responsibilities instead of roles.

To specify the business areas that a user or role can access:

  1. Choose Tools | Security and display the "Security dialog: Users - > Business Area tab"

    Figure 7-3 Security dialog: Users-> Business Area tab

    Surrounding text describes Figure 7-3 .
  2. Click Select to display the "Select User/Role dialog" where you can search for and select the database user or role to which you want to grant access.

    Figure 7-4 Select User/Role dialog

    Surrounding text describes Figure 7-4 .
  3. Enter the search criteria in the Search For field and click Go.

    Discoverer Administrator displays the search results in the Results list.

  4. Select a user or role from the Results list.

  5. Click OK to display the "Security dialog: Users - > Business Area tab" with the business areas for the selected user or role.

    Figure 7-5 Security dialog: Users-> Business Area tab

    Surrounding text describes Figure 7-5 .
  6. Move the business areas that you want the selected user or role to have access to from the Available business areas list to the Selected business areas list.

    You can select more than one business area by pressing the Ctrl key and clicking another business area.

  7. For each new business area you add to the Selected business areas list, follow the instructions below to specify whether the selected user or role has administration access to the business area:

    1. Click the business area in the Selected business areas list.

    2. Select or clear the Allow Administration check box as required.

    The setting of a user's Allow Administration privilege controls which administration tasks the user can perform. For more information, see "How to specify the tasks a user or role (responsibility) can perform".

  8. Click OK to save the changes you have made and close the Security dialog.

Notes

  • To remove access to a business area from a user or role, move the business area from the Selected business areas list to the Available business areas list.

  • The list of available users/roles includes a role called PUBLIC. Select this role to view or edit the default access permissions for users or roles whose permissions you have not yet explicitly defined.

  • If you run Discoverer Administrator as an Oracle Applications user, the Security dialog shows Oracle Applications Responsibilities instead of roles. For more information about Applications mode, see "What features does Discoverer support for Oracle Applications users?".

7.7 How to specify the tasks a user or role (responsibility) can perform

Note: When Oracle Applications database users are connected, Discoverer Administrator displays responsibilities instead of roles.

To specify the tasks a user or role can perform:

  1. Choose Tools | Privileges and display the "Privileges dialog: Privileges tab".

    Figure 7-6 Privileges dialog: Privileges tab

    Surrounding text describes Figure 7-6 .
  2. Click Select to display the "Select User/Role dialog" where you can search for and select the user or role whose task privileges you want to change.

    Figure 7-7 Select User/Role dialog

    Surrounding text describes Figure 7-7 .
  3. Enter the search criteria in the Search For field and click Go.

    Discoverer Administrator displays the search results in the Results list.

  4. Select a user or role from the Results list.

  5. Click OK to display the "Privileges dialog: Privileges tab" for the selected user or role.

    Figure 7-8 Privileges dialog: Privileges tab

    Surrounding text describes Figure 7-8 .
  6. Grant or revoke specific task privileges for the user or role by selecting or clearing the appropriate check boxes in the Administration Privilege list or the Desktop and Plus Privilege list.

    Note: To grant a privilege, you must first grant the parent privilege using the Administration Privilege check box or the Desktop and Plus Privilege check box. If a user has access to Discoverer Desktop or Discoverer Plus through a responsibility and you clear the Desktop and Plus check box, any selected check boxes in the list of privileges will still be active (although they appear grayed out). The total privileges for a user are determined by the combination of responsibility and user privileges.

    Tip: Click a privilege in the Administration Privilege list or the Desktop and Plus Privilege lists to see a brief description of that privilege in the area below the lists.

    Note: The Desktop and Plus privilege also applies to Discoverer Viewer

  7. Choose an Oracle system profile to apply to the user or role from the Select an Oracle System Profile drop down list.

    Oracle system profiles are created by the database administrator to control access to database resources.

    Note: To assign Oracle system profiles within Discoverer Administrator you must have access to the following database views:

    • DBA_PROFILES

    • DBA_USERS

  8. Click OK to save the changes you have made and close the Privileges dialog.

Notes

  • The changes you have made will take effect when users re-connect to Discoverer.

  • The list of available users/roles includes a role called PUBLIC. Select this role to view or edit the default task privileges for users or roles whose privileges you have not yet explicitly defined.

  • If you want to grant Administration privileges to a user or role, you must also grant that user Administration access to the business area. For more information, see "How to specify a user or role (responsibility) to perform a specific task".

  • If you run Discoverer Administrator as an Oracle Applications user, the Privileges dialog shows Oracle Applications Responsibilities instead of roles. For more information about Oracle Applications mode, see Chapter 17, "Using Discoverer with Oracle Applications".

7.8 How to specify a user or role (responsibility) to perform a specific task

Note: When Oracle Applications database users are connected, Discoverer Administrator displays responsibilities instead of roles.

To specify the users or roles that can perform a specific task:

  1. Choose Tools | Privileges to display the "Privileges dialog: User/Role tab".

    Discoverer first displays the following warning dialog.

    Figure 7-9 Warning dialog

    Surrounding text describes Figure 7-9 .
  2. Click Yes to display the "Privileges dialog: User/Role tab".

    Figure 7-10 Privileges dialog: User/Role tab

    Surrounding text describes Figure 7-10 .

    Note: To change the maximum number of rows that Discoverer will display in the Show Users/Roles list, you edit the value of the Discoverer registry setting MaxNumListRows (for more information, see Chapter 21, "Discoverer Registry Settings").

  3. Select the task privilege that you want to grant to (or revoke from) users or roles from the drop down list.

    When you select a privilege from the drop down list, a brief description of the privilege appears on the lower area of the dialog.

  4. Specify the content of the Users/Roles list by selecting the Users check box and the Roles check box, as appropriate.

    The content of the Users/Roles list is sorted alphabetically. If you include both users and roles, users appear first.

  5. Grant or revoke privileges for the currently selected task by selecting or clearing the appropriate check boxes in the Users/Roles list.

  6. Click OK to save the changes you have made and close the Privileges dialog.

Notes

7.9 How to set query retrieval limits

You can maintain overall system performance by setting query limits for users and roles.

Note: When Oracle Applications database users are connected, Discoverer Administrator displays responsibilities instead of roles.

To set query retrieval limits for a user or role:

  1. Choose Tools | Privileges and display the "Privileges dialog: Query Governor tab".

    Figure 7-11 Privileges dialog: Query Governor tab

    Surrounding text describes Figure 7-11 .
  2. Click Select to display the "Select User/Role dialog" where you can search for and select the user or role for which you want to specify a query limit.

    Figure 7-12 Select User/Role dialog

    Surrounding text describes Figure 7-12 .
  3. Enter the search criteria in the Search For field and click Go.

    Discoverer Administrator displays the search results in the Results list.

  4. Select a user or role from the Results list.

  5. Click OK to display the "Privileges dialog: Query Governor tab" for the selected user or role.

  6. Specify the following Query Governor options as required:

    Option Use to:
    Warn user if predicted time exceeds: issue a warning if the estimated time to perform a query exceeds the limit you specify
    Prevent queries from running longer than: stop queries that have been running for longer than the limit you specify
    Limit retrieved data to: restrict the number of rows a query can retrieve to the limit you specify

  7. Click OK to save the changes you have made and close the Privileges dialog.

Notes

7.10 How to set scheduled workbook limits

You can control how a Discoverer end user uses scheduled workbooks by setting scheduled workbook limits.

To set scheduled workbook limits for a user or role:

  1. Choose Tools | Privileges and display the "Privileges dialog: Scheduled Workbooks tab".

    Figure 7-13 Privileges dialog: Scheduled Workbooks tab

    Surrounding text describes Figure 7-13 .
  2. Click Select to display the "Select User/Role dialog" where you can search for and select the user or role for which you want to specify scheduled workbook limits.

    Figure 7-14 Select User/Role dialog

    Surrounding text describes Figure 7-14 .
  3. Enter the search criteria in the Search For field and click Go.

    Discoverer Administrator displays the search results in the Results list.

  4. Select a user or role from the Results list.

  5. Click OK to display the "Privileges dialog: Scheduled Workbooks tab" for the selected user or role.

  6. Select the user to own the result tables created in the database (that contain the results of scheduled workbooks).

    The results of scheduled workbooks are stored in database tables. These tables can belong to a different user to the one that originally ran the scheduled workbook.

    Note:

    Oracle Applications users should use the Oracle Applications user (created with the batchuser_app.sql script) as the scheduled workbook results schema in which to store scheduled workbook results.

    Alternatively, Oracle Applications users can use the Oracle Applications APPS schema.

  7. Specify when you want to force the user to schedule workbooks.

  8. Set the scheduled workbook options as required.

  9. Click OK to save the changes you have made and close the Privileges dialog.

Note: You might decide to have a single database user owning the tables containing the results of all users' scheduled workbooks (with the benefit that individual users do not need additional privileges to run scheduled workbooks). Alternatively, you might decide to have a scheduled workbook results schema owning the tables containing the results for each user's scheduled workbooks (with the benefit that space quota is not shared and is therefore less likely to be exhausted than if the tables were owned by a single database user). For more information, see "Where to store the results of scheduled workbooks?".

7.11 How to share Discoverer workbooks using a database role

You might want to share Discoverer workbooks in an End User Layer (EUL) using a database role. For example you might only want database users that have been granted the database role of a manager, to access specific Discoverer workbooks. You can determine the database users that can use specific Discoverer workbooks by associating the database users with a database role, and sharing the Discoverer workbooks with the database role as follows:

  • identify an existing database role or create a new database role

  • make sure that the database role can select data from the database tables that will be used by your Discoverer workbooks

  • grant the database role to the database users, whose workbooks you want to share with the database role

  • identify or create the Discoverer workbooks using a database user that has been granted the database role

  • share the Discoverer workbooks with the database role

Each database user that is granted the database role will be able to access workbooks that are shared with that database role.

How to share workbooks using a database role:

  1. Start SQL*Plus (if it is not already running) and connect as the EUL owner.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> connect jchan/tiger@database;
    

    Where jchan is the EUL owner and tiger is the EUL owner password.

  2. Create a database role as the EUL owner.

    For example, in SQL*Plus you might enter the following SQL statement:

    SQL> create role role1;
    

    The above statement creates a database role named role1.

  3. Grant the SELECT privilege on the appropriate database tables to the database role you just created.

    For example, in SQL*Plus you might enter the following SQL statement:

    SQL> grant select on product to role1;
    SQL> grant select on store to role1;
    SQL> grant select on sales_fact to role1;
    

    The above statement grants the select privilege on the product, store and sales_fact tables to the database role role1.

    Note: The select privilege must be granted to the role on all the tables that are referenced by items used in Discoverer workbooks.

  4. Grant the role that you just created to each database user with whom you want to share Discoverer workbooks.

    For example, in SQL*Plus you might enter the following SQL statement:

    SQL> grant role1 to jchen;
    

    The above statement grants the database role role1 to the database user jchen.

    The remaining task steps must be carried out using Discoverer Plus or Discoverer Desktop.

    For more information, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.

  5. In Discoverer Plus or Discoverer Desktop, connect as the EUL owner and choose File | Manage Workbooks | Sharing to display the Share Workbooks dialog.

  6. Select the database role that you previously created (for example, role1).

  7. Move the workbooks you want to share with the database role role1, from the Available field to the Shared field.

  8. Click OK to close the dialog and save the changes you made.

The database users that have been granted the database role will now be able to use any Discoverer workbooks that have been shared with the database role.

7.12 About Transparent Application Failover

Discoverer provides Transparent Application Failover support for Oracle Enterprise Edition databases, described in the following topics:

7.12.1 What is Transparent Application Failover?

Transparent Application Failover is a feature included in Oracle Enterprise Edition databases (8.1.7 or later). If a database failure occurs, Transparent Application Failover masks the database failure by relocating processing from the failed database component to an alternative backup component.

7.12.2 What impact does Transparent Application Failover have on Discoverer users?

For most database failures, Discoverer users are not aware that Transparent Application Failover has relocated database processing. In some circumstances, Discoverer might display an error message. If users see such an error message, they have to repeat the Discoverer operation that was interrupted.

7.12.3 How to enable Discoverer support for Transparent Application Failover

To enable Discoverer support for Transparent Application Failover:

  1. Add the FAILOVER MODE parameter to the CONNECT_DATA section of a connect descriptor in the tnsnames.ora file (typically located in the <ORACLE_HOME>\network\admin directory).

  2. Set the value of the TYPE subparameter to SELECT.

  3. Specify values for other subparameters of the FAILOVER_MODE parameter as required.

    For example:

    MYSERVICENAME=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTNAME)(PORT = 1521)))
    (CONNECT_DATA=(SERVICE_NAME=MYSERVICENAME)(FAILOVER_MODE=(TYPE=SELECT)(RETRIES=5)))) 
    

    For more information about enabling Transparent Application Failover, see the Oracle Database Net Services Administrator's Guide 11g Release 1, Chapter 13 Enabling Advanced Features of Oracle Net Services".

7.12.4 Notes on Discoverer support for Transparent Application Failover

Note the following:

  • Transparent Application Failover does not relocate processing for the PL/SQL packages that provide Discoverer workbook scheduling and summary folder refresh functionality. If the database fails when a scheduled workbook is running or when a summary folder is being refreshed, these operations will fail.

  • Transparent Application Failover does not relocate processing for user-defined PL/SQL functions that are referenced in a worksheet, and users will need to rerun the query.

  • In the event of a database failure, Discoverer Portlet Provider can obtain Portlet Provider metadata by accessing the EUL from a failover database instance. However, Discoverer Portlet Provider cannot obtain portlets-specific metadata, because the metadata repository (MR) schema, which contains portlet-specific metadata, does not support Transparent Application Failover.

    In effect, if the database that contains the metadata repository schema fails, even if a RAC database is being used, portlets would not work.