Managing Query Execution Privileges

The Oracle BI Server allows you to exercise varying degrees of control over the repository information that a user can access.

Controlling query privileges allows you to manage the query environment. You can put a high level of query controls on users, no controls, or somewhere in between. The following list contains some types of activities you may want to limit:

• Restricting query access to specific objects, including rows and columns, or time periods
  • Objects. If you explicitly deny access to an object that has child objects, the user will be denied access to the child objects. For example, if you explicitly deny access to a particular physical database object, you are implicitly denying access to all of the physical tables and physical columns in that catalog.
    If a user or group is granted or disallowed privileges on an object from multiple sources (for example, explicitly and through one or more groups), the privileges are used based on the order of precedence, as described in Group Inheritance.
    You can grant or disallow the ability to execute direct database requests for a user or group.
  • Time periods. If you do not select a time period, access rights remain unchanged. If you allow or disallow access explicitly in one or more groups, the user is granted the least restrictive access for the defined time periods. For example, suppose a user is explicitly allowed access all day on Mondays, but belongs to a group that is disallowed access during all hours of every day. This means that the user will have access on Mondays only.
• Controlling runaway queries by limiting queries to a specific number of rows or maximum run time
• Limit queries by setting up filters for an object

All restrictions and controls can be applied at the user level, at the group level, or a combination of the two.

To limit queries by objects for a user or group

1. From the Administration Tool menu bar, choose Manage > Security.
2. In the Security Manager dialog box, in the tree pane, select Users or Groups.
3. In the right pane, right-click the name that you want to change and select Properties.
4. In the User or Group dialog box, click Permissions.
5. In the User/Group Permissions dialog box, click the General tab and perform the following steps:
   1. In the General tab, to explicitly allow or disallow access to one or more objects in the repository, click Add.
   2. In the Browse dialog box, in the Name list, select the objects you want to change, and then click Select.
   3. In the User/Group Permissions dialog box, assign the permissions by selecting or clearing the Read check box for each object.
      (Default is a check) If the check box contains a check, the user has read privileges on the object. If the check box contains an X, the user is disallowed read privileges on the object. If it is blank, any existing privileges (for example, through a group) on the object apply.
      For more information about assigning permissions, refer to Setting Permissions for Repository Objects.
6. To explicitly allow or disallow populate privilege or the ability to execute direct database requests for specific database objects, perform the following steps:
   1. Click the Query Limits tab and select the database.
   2. In the Populate Privilege drop-down list, select Allow or Disallow.
      NOTE:  For the selected user or group, this overrides the database property Allow populate queries for all.
   3. To explicitly allow or disallow the ability to execute direct database requests for specific database objects, in the Execute Direct Database Requests drop-down list, select Allow or Disallow.
      NOTE:  For the selected user or group, this overrides the database property Allow direct database requests for all.
7. Click OK twice to return to the Security Manager dialog box.

To limit queries by number of rows received by a user or group

1. From the Administration Tool menu bar, choose Manage > Security.
2. In the Security Manager dialog box, in the tree pane, select Users or Groups.
3. In the right pane, right-click the name that you want to change and select Properties.
4. In the User or Group dialog box, click the Permissions tab.
5. In the User/Group Permissions dialog box, click the Query Limits tab and expand the dialog box to view all columns.
6. To specify or change the maximum number of rows each query can retrieve from a database, in the Query Limits tab, perform the following steps:
   1. In the Max Rows column, type the maximum number of rows.
   2. In the Status Max Rows field, select a status using Table 39 as a guide.
7. Click OK twice to return to the Security Manager dialog box.

To limit queries by maximum run time or to time periods for a user or group

1. From the Administration Tool menu bar, choose Manage > Security.
2. In the Security Manager dialog box, in the tree pane, select Users or Groups.
3. In the right pane, right-click the name that you want to change and select Properties.
4. In the User or Group dialog box, click the Permissions tab.
5. In the User/Group Permissions dialog box, click the Query Limits tab and expand the dialog box to view all columns.
6. To specify the maximum time a query can run on a database, in the Query Limits tab, perform the following steps:
   1. In the Max Time column, select the number of minutes.
   2. From the Status Max Time drop-down list, select a status using Table 39 as a guide.
7. To restrict access to a database during particular time periods, in the Restrict column, click the ellipsis button.
8. In the Restrictions dialog box, perform the following steps:
   1. To select a time period, click the start time and drag to the end time.
   2. To explicitly allow access, click Allow.
   3. To explicitly disallow access, click Disallow.
9. Click OK twice to return to the Security Manager dialog box.

To limit queries by setting up a filter on an object for a user or group

1. From the Administration Tool menu bar, choose Manage > Security.
2. In the Security Manager dialog box, in the tree pane, select Users or Groups.
3. In the right pane, right-click the name that you want to change and select Properties.
4. In the User or Group dialog box, click Permissions.
5. In the User/Group Permissions dialog box, click the Filters tab.
6. In the Filters tab, to add an object to filter, perform the following steps:
   1. Click Add.
   2. In the Browse dialog box, in the Names list, locate and double-click the object on which you want to filter.
   3. Select the object and click Select.
7. In the User/Group Permissions Filters dialog box, perform the following steps:
   1. Scroll to the right to view the Business Model Filter column.
   2. Click the Business Model Filter ellipsis button for the selected object.
8. In the Expression Builder dialog box, create a logical filter, and then click OK.
9. In the User/Group Permissions Filters dialog box, from the Status drop-down list, select a status using Table 39 as a guide.
10. Click OK twice to return to the Security Manager dialog box.

    Table 39. Query Privileges Status Fields

    Status	Description
    Disable	Status Max Rows or Status Max Time. When selected, disables any limits set in the Max Rows or Max Time fields. Filter. The filter is not used and no other filters applied to the object at higher levels of precedence (for example, through a group) are used.
    Enable	Status Max Rows or Status Max Time. This limits the number of rows or time to the value specified. If the number of rows exceeds the Max Rows value, the query is terminated. Filter. The filter is applied to any query that accesses the object.
    Ignore	Status Max Rows or Status Max Time. Limits will be inherited from the parent group. If there is no row limit to inherit, no limit is enforced. Filter. The filter is not in use, but any other filters applied to the object (for example, through a group) are used. If no other filters are enabled, no filtering will occur.

Assigning Populate Privilege to a User or Group

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

NOTE:  Any Marketing user who writes a cache entry or saves a result set needs to be assigned the POPULATE privilege for the target database. All Marketing segmentation users and groups need to be assigned this privilege. Typically, all Marketing users are associated with a group and this group is granted the privilege. For more information about marketing cache, refer to the topic about setting up cache for target levels in the documentation for Oracle's Siebel Marketing application.

To assign Populate privilege to a user or group

1. From the Administration Tool menu bar, choose Manage > Security.
2. In the Security Manager dialog box, in the tree pane, select Users or Groups.
3. In the right pane, right-click the name that you want to change and select Properties.
4. In the User or Group dialog box, click Permissions.
5. In the User/Group Permissions dialog box, select the Query Limits tab.
6. In the Query Limits list, expand the dialog box to view all columns.
7. From the Populate Privilege drop-down list, select Allow or Disallow.
   NOTE:  For all Marketing data warehouses, set Populate Privilege to Allow.
8. Click OK twice to return to the Security Manager dialog box.