1. Managing Metadata Repositories for Oracle Analytics Server
  2. Build Your Metadata Repository
  3. Apply Data Access Security to Repository Objects
  4. Set Query Limits

Set Query Limits

You can manage the query environment by setting query limits (governors) in the repository for particular application roles.

You can limit queries by the number of rows received, by maximum run time, and by restricting to particular time periods. You can also allow or disallow direct database requests or the Populate privilege.

You should always set query limits for particular application roles rather than for individual users.

This section contains the following topics:

Access the Query Limits Functionality in the Administration Tool

Learn how to access the Query Limits tab of the User/Application Role Permissions dialog.

If you're in offline mode, no application roles appear in the list unless you've first modified them in online mode, see About Applying Data Access Security in Offline Mode.

  1. Open your repository in the Model Administration Tool to access the query limits functionality for an application role.
  2. Select Manage, then select Identity.
  3. In the Identity Manager dialog, in the tree pane, select BI Repository.
  4. In the right pane, select the Application Roles tab, then double-click the application role for which you want to set query limits.
  5. In the Application Role dialog, click Permissions.
  6. In the User/Application Role Permissions dialog, click the Query Limits tab.

Limit Queries By the Number of Rows Received

You can control runaway queries by limiting queries to a specific number of rows.

Any query limits you set should exceed the Presentation Server settings for Maximum Number of Rows Processed when Rendering a Table View and Maximum Number of Rows to Download by at least 500 to avoid error messages. If you choose to impose data source rows limits on certain users or Application Roles using the repository Max Rows query limits setting, then those users may receive Max Row Limit Exceeded messages.

See Use Fusion Middleware Control to Set Configuration Options for Data in Tables and Pivot Tables and Use Fusion Middleware Control to Set the Maximum Number of Rows Processed to Render a Table in Administering Oracle Analytics Server.

The options for Status Max Rows are:

  • Enable: This limits the number of rows to the value specified. If the number of rows exceeds the Max Rows value, the query is terminated.

  • Disable: Disables any limits set in the Max Rows field.

  • Warn: Doesn't enforce limits, but logs queries that exceed the set limit in the Query log.

  • Ignore: Limits are inherited from the parent application role. If there is no row limit to inherit, no limit is enforced.

Follow the steps in Accessing the Query Limits Functionality in the Administration Tool to access the Query Limits tab.

  1. In the Max Rows column, type the maximum number of rows for users to retrieve from each source database object.
  2. In the Status Max Rows field, select an option for each database.
  3. Click OK, then click OK again to return to the Identity Manager.

Limit Queries By Maximum Run Time and Restricting to Particular Time Periods

You can forbid queries during certain time periods, or you can specify the maximum time a query can run on a database.

If you don't select a particular time period, access rights remain unchanged. If you allow or disallow access explicitly in one or more application roles, users are granted the least restrictive access for the defined time periods. For example, if a user is a member of an application role that's explicitly allowed access all day on Mondays, but that user also belongs to another application role that's disallowed access during all hours of every day, then the user has access on Mondays only.

  1. Follow the steps in Access the Query Limits Functionality in the Administration Tool to access the Query Limits tab.

  2. To specify the maximum time a query can run on a database, in the Max Time (Minutes) column, enter the maximum number of minutes you want queries to run on each database object. Then, in the Status Max Time field, select one of the following options for each database:

    • Enable: This limits the time to the value specified.

    • Disable: Disables any limits set in the Max Time field.

    • Warn: Doesn't enforce limits, but logs queries that exceed the set time limit in the Query log.

    • Ignore: Limits are inherited from the parent application role. If there is no time limit to inherit, no limit is enforced.

  3. To restrict access to a database during particular time periods, in the Restrict column, click the Ellipsis button. Then, in the Restrictions dialog, perform the following steps:

    1. To select a time period, click the start time and drag to the end time.

    2. To explicitly grant access, click Allow.

    3. To explicitly deny access, click Disallow.

    4. Click OK.

  4. Click OK, then click OK again to return to the Identity Manager.

Allow or Disallow Direct Database Requests

Use this task to allow or disallow the ability to run direct database requests for a particular application role.

For the selected role, this privilege overrides the Allow direct database requests by default property for the database object in the Physical layer.

The options for the Execute Direct Database Requests field are:

  • Allow

    Explicitly grants the ability to run direct database requests for this database.

  • Disallow

    Explicitly denies the ability to run direct database requests for this database.

  • Ignore

    Limits are inherited from the parent application role. If there is no limit to inherit, then direct database requests are allowed or disallowed based on the Allow direct database requests by default property for the database object.

Follow the steps in Access the Query Limits Functionality in the Administration Tool to access the Query Limits tab.

  1. In the Query Limits tab for each database object, in the Execute Direct Database Requests field, select an option.
  2. Click OK, then click OK again to return to the Identity Manager.

Allow or Disallow the Populate Privilege

When a criteria block is cached, the Populate stored procedure writes the Cache/Saved Result Set value to the database.

You can grant or deny this Populate privilege to particular application roles.

For the selected application role, this privilege overrides the Allow populate queries by default property for the database object in the Physical layer.

The options for the Populate Privilege field are:

  • Allow

    Explicitly grants the Populate privilege for this database. For all Marketing data warehouses, select Allow.

  • Disallow

    Explicitly denies the Populate privilege for this database.

  • Ignore

    Limits are inherited from the parent application role. If there is no limit to inherit, then the Populate privilege is allowed or disallowed based on the Allow populate queries by default property for the database object.

Follow the steps in Access the Query Limits Functionality in the Administration Tool to access the Query Limits tab.

  1. For each database object, in the Populate Privilege field, select an option.
  2. Click OK, and then click OK again to return to the Identity Manager.