Alias Tab of Presentation Layer Dialog Boxes

An Alias tab appears on the Presentation Catalog, Presentation Table, and Presentation Column dialog boxes. You can use this tab to specify or delete an alias for the Presentation layer objects.

To add an alias

1. Double-click a presentation catalog.
2. In the Presentation Layer dialog box, click the Aliases tab.
3. Click the new button, and then type the text string to use for the alias.
4. Click OK.

To delete an alias

1. Double-click a presentation catalog.
2. In the Presentation Layer dialog box, click the Aliases tab.
3. In the Aliases list, select the alias you want to delete.
4. Click the delete button, and then click OK. 

Presentation Columns

The presentation column names are, by default, identical to the logical column names in the Business Model and Mapping layer. However, you can present a different name by clearing both the Use Logical Column Name and the Display Custom Name check boxes in the Presentation Column dialog box.

To provide a convenient organization for your end users, you can drag and drop a column from a single logical table in the Business Model and Mapping layer onto multiple presentation tables. This allows you to create categories that make sense to the users. For example, you can create several presentation tables that contain different classes of measures—one containing volume measures, one containing share measures, one containing measures from a year ago, and so on.

The Presentation Column dialog box has the following tabs:

• General. Use this tab to create or edit presentation columns.
• Aliases. Use this tab to specify or delete an alias for a presentation column.

To create a presentation column

1. Right-click a presentation table in the Presentation layer, and then choose New Presentation Column.
2. In the Presentation Column dialog box, to use the name of the logical column for the presentation column, select the Use Logical Column check box.
   The name of the column and its associated path in the Business Model and Mapping layer appears in the Logical Column Name field.
3. To specify a name that is different from the Logical Column name, clear the Use Logical Column check box, and then type a name for the column.
4. To assign user or group permissions to the column, click Permissions.
5. In the Permissions dialog box, assign permissions, and then click OK.
   For more information about assigning permissions, refer to Setting Permissions for Repository Objects.
6. To select the logical column, click Browse.
7. In the Browse dialog box, select the column, and then click Select.
8. (Optional) Type a description of the presentation column.
9. To define any aliases for the logical column, click the Aliases tab.

To edit a presentation column

1. In the Business Model and Mapping layer, double-click the presentation column.
2. In the Presentation Column dialog box, click Edit.
3. In the Logical Column dialog box, make any changes or review the information, and then click OK.

To delete a presentation column

1. Right-click a presentation table in the Presentation layer, and then select Properties.
2. Click the Columns tab.
3. Select the column you want to delete.
4. Click Remove, or press the Delete key, and then click Yes.

To reorder a presentation column

1. Right-click a presentation table in the Presentation layer, and then select Properties.
2. Click the Columns tab.
3. Select the column you want to reorder.
4. Use drag-and-drop to reposition the column, or click the Up and Down buttons.
5. Click OK. 

Presentation Tables

You can use presentation tables to organize columns into categories that make sense to the user community. Presentation tables in the Presentation layer contain columns. A presentation table can contain columns from one or more logical tables. The names and object properties of the presentation tables are independent of the logical table properties.

The Presentation Tables dialog box has three tabs: General, Columns, and Aliases. The functionality provided in each tab is described in the following list:

Tab	Comment
General	Use this tab to create or edit a presentation table.
Columns	Use this tab to reorder or sort the Presentation layer columns in the Administration Tool workspace, and to delete columns. You can also use this tab to access the Presentation Column dialog box, where you can create and edit columns.
Aliases	Use this tab to specify or delete an alias for a presentation table.

To create a presentation table

1. Right-click a catalog folder in the Presentation layer, and then select New Presentation Table from the shortcut menu.
   The Presentation Table dialog box appears.
2. In the General tab, specify a name for the table.
3. Click the Permissions button to open the Permissions dialog box, where you can assign user or group permissions to the table.
   For more information about assigning permissions to a presentation table, refer to Setting Permissions for Repository Objects.
4. (Optional) Type a description of the table.
   NOTE:  To give the appearance of nested folders in Answers, prefix the name of the presentation folder to be nested with a hyphen and a space and place it after the folder in which it nests (- <folder name>). For example, to nest the Sales Facts folder in the Facts folder, place the Sales Facts folder directly after Facts in the metadata and change its name to - Sales Facts. When Answers displays the folder name in the left pane, it omits the hyphen and space from the folder name. To nest a second folder, for example Marketing Facts, in the Facts folder, change its name to - Marketing Facts and place it directly after Sales Facts. The standard preconfigured repositories provide additional examples for you to review.

To delete a presentation table

1. In the Presentation layer, right-click a catalog and select Properties.
2. In the Presentation Catalog dialog box, click the Presentation Tables tab.
3. In the Presentation Tables tab, select a table and click Remove.
   A confirmation message appears.
4. Click Yes to remove the table, or No to leave the table in the catalog.
5. Click OK.

To reorder a table or sort all tables in a presentation catalog

1. In the Presentation layer, right-click a catalog and select Properties.
2. In the Presentation Catalog dialog box, click the Presentation Tables tab.
3. To move a table, perform the following steps:
   1. In the Presentation Tables tab, in the Name list, select the table you want to reorder.
   2. Use drag-and-drop to reposition the table, or click the Up and Down buttons.
4. To sort all tables in alphanumeric order, click the Name column heading.
   This toggles the sort between ascending and descending alphanumeric order.

Presentation Catalogs

In the Presentation layer, presentation catalogs (subject areas) allow you to show different views of a business model to different sets of users. Presentation catalogs have to be populated with contents from a single business model. They cannot span business models.

When creating a presentation catalog, selecting the option Export logical keys causes any columns in the Presentation layer that are key columns in the Business Model and Mapping layer to be listed as key columns to any ODBC client. This is the default selection. In most situations, this option should be selected. Many client tools differentiate between key and nonkey columns, and the option Export logical keys provides client tools access to the key column metadata. Any join conditions the client tool adds to the query, however, are ignored; the Oracle BI Server uses the joins defined in the repository.

If you set an implicit fact column this column will be added to a query when it contains columns from two or more dimension tables and no measures. The column is not visible in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives.

The Presentation Catalog dialog box has three tabs: General, Presentation Tables, and Aliases. The functionality provided in each tab is described in the following list:

Tab	Comment
General	Use this tab to create or edit a presentation catalog.
Presentation Table	Use this tab to reorder or sort the Presentation layer tables in the Administration Tool workspace, and to delete tables. You can also use this tab to access the Presentation Table dialog box, where you can create and edit tables.
Aliases	Use this tab to specify or delete an alias for a catalog folder.

To create a presentation catalog

1. In the Presentation layer, right-click and select New Presentation Catalog.
2. In the Presentation Catalog dialog box, in the General tab, type a name for the presentation catalog and click Permissions.
3. In the Permissions dialog box, assign user or group permissions to the catalog folder, and then click OK.
   For more information about assigning permissions to a presentation catalog, refer to Setting Permissions for Repository Objects.
4. In the Presentation Catalog dialog box, from the Business Model drop-down list, select a business model.
   After you add columns to the presentation catalog, the drop-down list becomes inactive because you can add columns from only one business model in each presentation catalog.
5. To expose the logical keys to other applications, select the option Export logical keys.
   NOTE:  If you are using a tool that issues parameterized SQL queries, such as Microsoft Access, do not select the Export logical keys option. Not exporting logical keys stops the tool from issuing parameterized queries.
6. (Optional) Type a description of the catalog folder.
   This description will appear in a mouse-over ToolTip for the presentation column in Oracle Business Intelligence Answers.
   CAUTION:  When you move columns into presentation catalog folders, be sure columns with the same name or an alias of the same name do not already exist in the catalog.
7. Set an Implicit Fact Column.
8. Click OK. 

Presentation Layer Objects

The Presentation layer adds a level of abstraction over the Business Model and Mapping layer. It is the view of the data seen by client tools and applications.

The Presentation layer provides a means to further simplify or customize the Business Model and Mapping layer for end users. For example, you can hide key columns or present the schema as a single table. Simplifying the view of the data for users makes it easier to craft queries based on users' business needs.

The section provides instructions for using the Administration Tool's Presentation layer dialog boxes to create and edit repository objects.

This section includes the following topics:

• Working with Presentation Catalogs
• Working with Presentation Tables
• Working with Presentation Columns
• Using the Alias Tab of Presentation Layer Dialog Boxes

Presentation Layer

The Presentation layer provides a way to present customized views of a business model to users. Presentation Catalogs in the Presentation layer (called Subject Area in Oracle Answers) are seen as business models by Oracle BI Presentation Services users. They appear as catalogs to client tools that use the Oracle BI Server as an ODBC data source. The following topics describe the Process of creating the Presentation layer.

NOTE:  In offline editing, remember to save your repository from time to time. You can save a repository in offline mode even though the business models may be inconsistent.

Copy Business Models to Publish to Users

There are several ways to create a Presentation Catalog in the Presentation layer. The recommended method is to drag and drop a business model from the Business Model and Mapping layer to the Presentation layer, and then modify the Presentation layer based on what you want users to see. You can move columns between presentation tables, remove columns that do not need to be seen by the users, or even present all of the data in a single presentation table. You can create presentation tables to organize and categorize measures in a way that makes sense to your users.

Remove Any Unneeded or Unwanted Columns

One important reason to use a custom Presentation layer is to make the schema as easy to use and understand as possible. Therefore, users should not be able to view columns that have no meaning to them. The following columns are examples of columns that you might want to remove from the Presentation layer:

• Key columns that have no business meaning.
• Columns that users do not need to view (for example, codes, when text descriptions exist).
• Columns that users are not authorized to see.

NOTE:  You can also restrict access to tables or columns in the security layer. For more information, refer to Security in Oracle BI.

Rename Presentation Columns to User-Friendly Names

By default, presentation columns have the same name as the corresponding logical column in the Business Model and Mapping layer. However, you can specify a different name to be shown to users by changing the name in the Presentation Column dialog box. Whenever you change the name of a presentation column, an alias is automatically created for the old name, so compatibility to the old name remains.

Export Logical Keys in the Presentation Catalog

For each presentation catalog in the Presentation layer, decide whether to export any logical keys as key columns to tools that access it in the Presentation Catalog dialog box. Exporting logical keys is irrelevant to users of Oracle BI Presentation Services, but it may be advantageous for some query and reporting tools. If you decide to export logical keys, be sure the logical key columns exist in the table folders. In this situation, your business model should use logical key/foreign key joins.

When you select the option Export logical keys in the Presentation Catalog dialog box, any columns in the Presentation layer that are key columns in the Business Model and Mapping layer are listed as key columns to any ODBC client. This is the default selection. In most situations, this option should be selected.

NOTE:  If you are using a tool that issues parameterized SQL queries, such as Microsoft Access, do not select the option Export logical keys. This will stop the tool from issuing parameterized queries. 

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. 

Order of Authentication

If the user does not type a logon name, then OS authentication is triggered, unless OS authentication is explicitly turned off in the NQSConfig.INI file. For more information, refer to Oracle Business Intelligence Enterprise Edition Deployment Guide. Additionally, OS authentication is not used for Oracle BI Presentation Services users.

The Oracle BI Server populates session variables using the initialization blocks in the desired order that are specified by the dependency rules defined in the initialization blocks. If the server finds the session variable USER, it performs authentication against an LDAP server or an external database table, depending on the configuration of the initialization block with which the USER variable is associated.

Oracle BI Server internal authentication (or, optionally, database authentication) occurs only after these other possibilities have been considered.

Maintaining Oracle BI Server User Authentication

You can maintain lists of users and their passwords in the Oracle BI repository using the Administration Tool. The Oracle BI Server will attempt to authenticate users against this list when they log on unless another authentication method has already succeeded, or database authentication has been specified in the NQSConfig.INI file.

Refer to Order of Authentication for additional information.

The Oracle BI Server user IDs are stored in nonencrypted form in an Oracle BI repository and are caseinsensitive. Passwords are stored in encrypted form and are casesensitive. The Oracle BI Server user IDs can be used to access any business model in a repository provided that the users have the necessary access privileges. User IDs are valid only for the repository in which they are set up. They do not span multiple repositories.

NOTE:  If you are using LDAP or external table authentication, passwords are not stored in the Oracle BI repository.

For information about configuring user authentication, refer to Oracle Business Intelligence Enterprise Edition Deployment Guide.

Changing Oracle BI Server User Passwords

You can change user passwords in the Administration Tool.

To change a user password

1. Select Manage > Security.
2. In the Security Manager dialog box, select Users in the left pane.
3. In the right pane, right-click the user whose password you want to change.
4. Select Properties from the shortcut menu.
5. In the User tab, type the new password.
6. In the Confirm Password text box, type the password again, and then click OK

Delivers and Database Authentication

In Oracle BI Applications, users are always created in the operational application database, never in the Oracle BI repository. The Oracle BI repository is preconfigured for database authentication.

Oracle BI Scheduler Server runs Delivers jobs for users without accessing or storing their passwords. Using a process called impersonation, the Scheduler uses one user ID and password with Oracle BI Administrator privileges that can act on behalf of other users. The Scheduler initiates an iBot by logging on to Oracle BI Presentation Services with that Oracle BI Administrator ID and password.

For Delivers to work, all database authentication must be performed in only one connection pool, and that connection pool can only be selected in an initialization block for the USER system session variable. This is typically called the Authentication Initialization Block. When impersonation is used, this initialization block is skipped. All other initialization blocks must use connection pools that do not use database authentication.

CAUTION:  Using an authentication initialization block is the only initialization block in which it is acceptable to use a connection pool in which :USER and :PASSWORD are passed to a physical database.

For other initialization blocks, SQL statements can use :USER AND :PASSWORD. However, because Oracle BI Scheduler Server does not store user passwords, the WHERE clause must be constructed as shown in the following example:

select username, groupname, dbname, schemaname from users
where username=':USER'
NQS_PASSWORD_CLAUSE(and pwd=':PASSWORD')NQS_PASSWORD_CLAUSE

NOTE:  When impersonation is used, everything in the parentheses is extracted from the SQL statement at runtime.

For more information, refer to the Oracle BI Delivers examples in Selecting and Testing the Data Source and Connection Pool.

Database Authentication

The Oracle BI Server can authenticate users through database logons. If a user has read permission on a specified database, the user will be trusted by the Oracle BI Server. Unlike operating system authentication, this authentication can be applied to Oracle BI Presentation Services users. For information, refer to About Oracle BI Delivers and Database Authentication.

Database authentication can be used in conjunction with external table authentication. If external table authentication succeeds, then database authentication is not performed. If external table authentication fails, then database authentication is performed.

Refer to Setting Up External Table Authentication and Order of Authentication for additional details.

Database authentication requires the user ID to be stored in the Oracle BI repository.

To set up database authentication

1. Create users in the repository named identically to the users in a database. Passwords are not stored in the repository.
2. Assign the permissions (including group memberships, if any) you want the users to have.
3. Specify the authentication database in the Security section of the NQSConfig.INI file.
   For more information, see Oracle Business Intelligence Infrastructure Installation and Configuration Guide.
4. Create a DSN for the database.
5. Import the database into the Physical layer. You do not need to import the physical table objects. The database name in the Physical layer has to match the database name in the NQSConfig.INI file (as specified in Step 3).
6. Set up the connection pool without a shared logon.

When a user logs on to the Oracle BI Server, the server attempts to use the logon name and password to connect to the authentication database using the first connection pool associated with it. If this connection succeeds, the user is considered to be authenticated successfully.

If the logon is denied, the Oracle BI Server issues a message to the user indicating an invalid user ID or password

External Table Authentication

Instead of storing user IDs and passwords in an Oracle BI repository, you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes. The external database table contains user IDs and passwords, and could contain other information, including group membership and display names used for Oracle BI Presentation Services users. The table could also contain the names of specific database catalogs or schemas to use for each user when querying data.

NOTE:  If a user belongs to multiple groups, the group names should be included in the same column separated by semicolons.

External table authentication can be used in conjunction with database authentication. If external table authentication succeeds, then database authentication is not performed. If external table authentication fails, then database authentication is performed.

Refer to Setting Up Database Authentication, and Order of Authentication for additional details.

External table authentication uses Oracle BI session variables that you define using the Variable Manager of the Administration Tool. For more information about the Variable Manager, refer to Using the Variable Manager.

Session variables get their values when a user begins a session by logging on. Certain session variables, called system variables, have special uses. The variable USER is a system variable that is used with external table authentication.

To set up external table authentication, you define a system variable called USER and associate it with an initialization block that is associated with an external database table. Whenever a user logs in, the user ID and password will be authenticated using SQL that queries this database table for authentication. After the user is authenticated successfully, other session variables for the user could also be populated from the results of this SQL query. For more information about session variables, refer to Understanding and Creating Session Variables.

The presence of a defined system variable USER determines that external authentication is done. Associating USER with an external database table initialization block determines that the user will be authenticated using the information in this table. To provide other forms of authentication, associate the USER system variable with an initialization block associated with a LDAP server or XML source. For more information, refer to Setting Up LDAP Authentication.

To set up external table authentication

1. Import information about the external table into the Physical layer. In this illustration, the database sql_nqsecurity contains a table named securitylogons and has a connection pool named External Table Security.
2. Select Manage > Variables to open the Variable Manager.
3. Select Initialization Blocks on the left tree pane.
4. Right-click on white space in the right pane, and then click on New Initialization Block from the right-click menu.
5. In the Initialization Block dialog box, type the name for the initialization block.
6. Select Database from the Data Source Connection drop-down list.
7. Click Browse to search for the name of the connection pool this block will use.
8. In the Initialization String area, type the SQL statement that will be issued at authentication time.
   The values returned by the database in the columns in your SQL will be assigned to variables. The order of the variables and the order of the columns will determine which columns are assigned to which variables. Consider the SQL in the following example:
   select username, grp_name, SalesRep, 2 from securitylogons where username = ':USER' and pwd = ':PASSWORD'
   This SQL contains two constraints in the WHERE clause:
   • :USER (note the colon) equals the ID the user entered when logging on.
   • :PASSWORD (note the colon again) equals the password the user typed.
     The query will return data only if the user ID and password match values found in the specified table.
     You should test the SQL statement outside of the Oracle BI Server, substituting valid values for :USER and :PASSWORD to verify that a row of data returns.
9. If this query returns data, the user is authenticated and session variables will be populated. Because this query returns four columns, four session variables will be populated. Create these variables (USER, GROUP, DISPLAYNAME, and LOGLEVEL) by clicking New in the dialog's Variables tab.
   If a variable is not in the desired order, click on the variable you want to reorder and use the Up and Down buttons to move it.
10. Click OK to save the initialization block. 

LDAP Authentication

Instead of storing user IDs and passwords in an Oracle BI repository, you can set up the Oracle BI Server to take the user ID and password typed by a user and pass them to an LDAP server for authentication. The server uses clear text passwords in LDAP authentication. Make sure your LDAP servers are set up to allow this.

In addition to basic user authentication, the LDAP server can also provide the Oracle BI Server with other information, such as the user display name (used by Oracle BI Presentation Services) and the name of any groups to which the user belongs. The LDAP server can also provide the names of specific database catalogs or schemas to use for each user when querying data. This information is contained in LDAP variables that get passed to Oracle BI session variables during the process of user authentication. For more information about session variables, refer to Understanding and Creating Session Variables.

LDAP authentication uses Oracle BI session variables, that you define using the Variable Manager of the Administration Tool. For more information about the Variable Manager, refer to Using the Variable Manager.

You need to perform the following steps to set up LDAP authentication:

1. Create an LDAP Server using the Administration Tool menu path: Manage > Security. For instructions, see Setting Up an LDAP Server.
2. Create an LDAP initialization block and associate it with an LDAP server. Setting up an LDAP initialization block is explained in Process of Creating Initialization Blocks.
3. Define a system variable named USER and map the USER variable to an LDAP attribute (uid or sAMAccountName).
   Session variables get their values when a user begins a session by logging on. Certain session variables, called system session variables, have special uses. The variable USER is a system variable that is used with LDAP authentication. For more information about the USER system variable, refer to Using System Session Variables and Defining a USER Session Variable for LDAP Authentication.
4. If applicable, delete users from the Oracle BI repository file.
5. Associate the USER system variable with the LDAP initialization block. For more information, see About Authenticating Users Using Initialization Blocks.

Defining a USER Session Variable for LDAP Authentication

To set up LDAP authentication, you define a system variable called USER and associate it with an LDAP initialization block that is associated with an LDAP server. When a user logs into the Oracle BI Server, the user ID and password will be passed to the LDAP server for authentication. After the user is authenticated successfully, other session variables for the user could also be populated from information returned by the LDAP server.

NOTE:  If you create a variable for the same user in both the repository and in a LDAP server, the local repository user definition takes precedence and LDAP authentication will not occur.

The information in this section assumes that an LDAP initialization block has already been defined.

For users not defined in the repository, the presence of a defined session system variable USER determines that external authentication is performed. Associating USER with an LDAP initialization block determines that the user will be authenticated by LDAP. To provide other forms of authentication, associate the USER variable with an initialization block associated with an external database or XML source. For more information, refer to Setting Up External Table Authentication.

To define the USER session system variable for LDAP authentication

1. Select Manage > Variables from the Administration Tool menu.
2. Select the System leaf of the tree in the left pane.
3. Right-click on the right pane and select New USER.
4. In the Session Variable - USER dialog box, select the appropriate LDAP initialization block from the Initialization Block drop-down list.
   The selected initialization block provides the USER session system variable with its value.
5. Click OK to create the USER variable.

Setting the Logging Level

Use the system variable LOGLEVEL to set the logging level for users who are authenticated by an LDAP server. Refer to Setting a Logging Level for more information.

Authentication Options

Authentication is the process by which a system uses a user ID and password ti verify that a user has the necessary permissions and authorizations to log in and access data. The Oracle BI Server authenticates each connection request it receives.

The Oracle BI Server supports the following authentication types:

• Setting Up LDAP Authentication
• Setting Up External Table Authentication
• Setting Up Database Authentication
• About Oracle BI Delivers and Database Authentication
• Maintaining Oracle BI Server User Authentication

Importing Users and Groups from LDAP

If your organization uses Lightweight Directory Access Protocol (LDAP), you can import your existing LDAP users and groups to a repository. After imported, all normal Oracle BI Server user and group functions are available. You can resynchronize your imported list at any time.

You can also authenticate against LDAP as an external source. When you do this, users are not imported into the repository. Users are authenticated, and their group privileges determined, when they log on. For more information about using LDAP authentication, refer to Setting Up LDAP Authentication.

NOTE:  If you create a variable for the same user in both the repository and in a LDAP server, the local repository user definition takes precedence and LDAP authentication will not occur. This allows the Oracle BI Administrator to reliably override users that exist in an external security system.

This section includes the following topics:

• Setting Up an LDAP Server
• Importing Users from LDAP
• Synchronizing Users and Groups with LDAP

Setting Up an LDAP Server

This section explains how to set up LDAP authentication for the repository.

NOTE:  For information about the basics of security and setting up authentication, refer to Oracle BI Security Manager.

For instances of Oracle BI that use ADSI as the authentication method, the following options should be used when setting up the AD instance:

• In Log On To, check All Computers or, if you list some computers, include the AD server as a Logon workstation.
• The following option must not be checked:
  User must change password at next logon

In the Administration Tool, the CN user used for the BIND DN of the LDAP Server section must have both ldap_bind and ldap_search authority.

NOTE:  The Oracle BI Server uses clear text passwords in LDAP authentication. Make sure your LDAP Servers are set up to allow this.

To set up LDAP authentication for the repository

1. Open a repository in the Administration Tool in offline or online mode.
2. From the application menu, choose Manage > Security.
3. From the Security Manager menu, choose Action > New > LDAP Server.
4. In the LDAP Server dialog box, in the General tab, complete the necessary fields. The following list of fields (or buttons) and descriptions contain additional information to help you set up the LDAP server:
   • Host name. The name of your LDAP server.
   • Port number. The default LDAP port is 389.
   • LDAP version. LDAP 2 or LDAP 3 (versions). The default is LDAP 3.
   • Base DN. The base distinguished name (DN) identifies the starting point of the authentication search. For example, if you want to search all of the entries under the o=Oracle.com subtree of the directory, o=Oracle.com is the base DN.
   • Bind DN and Bind Password. The optional DN and its associated user password that are required to bind to the LDAP server.
     If these two entries are blank, anonymous binding is assumed. For security reasons, not all LDAP servers allow anonymous binding.
     These fields are optional for LDAP V3, but required for LDAP V2, because LDAP V2 does not support anonymous binding.
     These fields are required if you select the ADSI check box. If you leave these fields blank, a warning message appears asking if you want to leave the password empty anyway. If you click Yes, anonymous binding is assumed.
   • Test Connection. Use this button to verify your parameters by testing the connection to the LDAP server.
5. Click the Advanced tab, and type the required information. The following list of fields and descriptions contain additional information to help you set up the LDAP server:
   NOTE:  The Oracle BI Server maintains an authentication cache in memory that improves performance when using LDAP to authenticate large numbers of users. Disabling the authentication cache can slow performance when hundreds of sessions are being authenticated.
   • Connection timeout. When the Administration Tool attempts to connect to an LDAP server for import purposes or the Oracle BI Server attempts to connect to an LDAP server for user authentication, the connection will time out after the specified interval.
   • Domain identifier. Typically, the identifier is a single word that uniquely identifies the domain for which the LDAP object is responsible. This is especially useful when you use multiple LDAP objects. If two different users have the same user ID and each is on a different LDAP server, you can designate domain identifiers to differentiate between them. The users log in to the Oracle BI Server using the following format:
     domain_id/user_id
     If a user enters a user id without the domain identifier, it will be authenticated against all available LDAP servers in turn. If there are multiple users with the same ID, only one user can be authenticated.
   • ADSI. (Active Directory Service Interfaces) A type of LDAP server. If you select the ADSI check box, Bind DN and Bind password are required.
   • SSL. (Single Socket Layer) Check this box to enable this.
   • User Name Attribute Type. This uniquely identifies a user. In many cases, this is the RDN (relative distinguished name). Typically, you accept the default value. For most LDAP servers, you would use the user ID. For ADSI, use sAMAccountName.

NOTE:  For information about configuring cache settings and SSL, refer to Oracle Business Intelligence Enterprise Edition Deployment Guide.

Importing Users from LDAP

You can import selected users or groups, or you can import all users or groups. If you have previously performed an import, you can choose to synchronize the repository with the LDAP server.

To import LDAP users and groups to a repository

1. Open a repository in the Administration Tool in offline or online mode.
2. From the application menu, choose Manage > Security.
3. In the Security Manager, select LDAP Servers in the left pane to display existing LDAP servers in the right pane. Select the LDAP server from which you want to import users or groups, and select Import... from the right-click menu. (You can also select the server and then select LDAP > Import.)
   You can choose to import selected users or groups, or you can import all users and groups. If you have previously done an import, you can choose to synchronize the repository with the LDAP server.
4. Select the users you want to import and click Import.
   You can import groups by selecting Groups from the drop down list instead of Users.

Synchronizing Users and Groups with LDAP

You can refresh the repository users and groups with the current users and groups on your LDAP server. After selecting the appropriate LDAP server, select LDAP > Synchronize (or choose Synchronize from the right-click menu).

Synchronization updates your list of repository users and groups to mirror your current LDAP users and groups. Users and groups that do not exist on your LDAP server are removed from the repository. The special user Administrator and the special group Administrators always remain in your repository and are never removed.

Properties of users already included in the repository are not changed by synchronization. If you have recycled a login name for another user, drop that name from your repository prior to synchronization. This assures that the process will import the new LDAP user definition.

NOTE:  With external LDAP authentication (discussed in the next section), import and synchronization are not really necessary. The primary use for import is to make it easy to copy LDAP users as Oracle BI users for testing.

Working with Groups

The Oracle BI Server allows you to create groups and then grant membership in them to users or other groups.

You can think of a group as a set of security attributes. The Oracle BI Server groups are similar to groups in Windows NT and Windows 2000, and to groups or roles in database management systems (DBMS). Like Windows NT and Windows 2000, and database groups or roles, Oracle BI Server groups can allow access to objects. Additionally, Oracle BI Server groups can explicitly deny particular security attributes to its members.

Groups can simplify administration of large numbers of users. You can grant or deny sets of privileges to a group and then assign membership in that group to individual users. Any subsequent modifications to that group will affect all users who belong to it. Externally defined users can be granted group membership by use of the GROUP session variable. For more information about session variables, refer to Using System Session Variables.

This section includes the following topics:

• Predefined Administrators Group
• Defined Groups
• Group Inheritance
• Adding a New Group
• Viewing Member Hierarchies

Predefined Administrators Group

The Oracle BI Server has one predefined group, the Oracle BI Administrators group. Members of this group have the authority to access and modify any object in a repository. The predefined Oracle BI Administrator user ID is automatically a member of the Oracle BI Administrators group.

Use caution in granting membership in the Oracle BI Administrators group to users or other groups. Membership in the Oracle BI Administrators group supersedes all privileges granted to a user, either through groups or explicitly through the user privileges. Any user who is a member of the Oracle BI Administrators group has all of the privileges of the Oracle BI Administrator user.

Defined Groups

You can create an unlimited number of groups in an Oracle BI repository. Each group can contain explicitly granted privileges or privileges granted implicitly using membership in another group. For more information about setting up a group, refer to Adding a New Group.

For example, you can create one group that denies access to the repository on Mondays and Wednesdays (Group1), another group that denies access on Saturdays and Sundays (Group2), and another that denies access on Tuesdays, Thursdays, and Fridays (Group3). Users who are members of Group2 can access the system only during weekdays, users who are members of Group1 and Group3 can access the system only on weekends, and so on.

Group Inheritance

Users can have explicitly granted privileges. They can also have privileges granted through membership in groups, that in turn can have privileges granted through membership in other groups, and so on. Privileges granted explicitly to a user have precedence over privileges granted through groups, and privileges granted explicitly to the group take precedence over any privileges granted through other groups.

If there are multiple groups acting on a user or group at the same level with conflicting security attributes, the user or group is granted the least restrictive security attribute. Any explicit permissions acting on a user take precedence over any privileges on the same objects granted to that user through groups.

Example 1

Suppose you have a user (User1) who is explicitly granted permission to read a given table (TableA). Suppose also that User1 is a member of Group1, that explicitly denies access to TableA. The resultant privilege for User1 is to read TableA, as shown in Figure 21.

Because privileges granted directly to the user take precedence over those granted through groups, User1 has the privilege to read TableA.

Figure 21. User Privileges and Group Privileges

Example 2

Consider the situation shown in Figure 22.

Figure 22. Privileges Example

These are the resulting privileges:

• User1 is a direct member of Group1 and Group2, and is an indirect member of Group3, Group4, and Group5.
• Because Group5 is at a lower level of precedence than Group2, its denial of access to TableA is overridden by the READ privilege granted through Group2. The result is that Group2 provides READ privilege on TableA.
• The resultant privileges from Group1 are DENY for TableA, READ for TableB, and READ for TableC.
• Because Group1 and Group2 have the same level of precedence and because the privileges in each cancel the other out (Group1 denies access to TableA, Group2 allows access to TableA), the less restrictive level is inherited by User1; that is, User1 has READ access to TableA.
• The total privileges granted to User1 are READ access for TableA, TableB, and TableC.

Adding a New Group

The following procedure explains how to add a new group to a repository.

To add a new group to a repository

1. Open a repository in the Administration Tool. (The repository can be opened in either online or offline mode.)
2. Display the security window by selecting Manage > Security.
3. Select Action > New > Group from menu.
   The Group dialog box appears.
   NOTE:  You can also select the Group icon in the left pane, and then right-click on white space in the left pane and select New Security Group from the right-click menu.
4. Type a name for the group and click OK.
   This creates a new group with no rights granted to it.
5. To modify the group's permissions, open the Group dialog by double-clicking on the group icon you want to modify. If you click on Permissions, you can change permissions for multiple columns.
6. You can grant rights to the group by adding other groups, by explicit configuration for the group, or a combination of the two. To grant membership to a group, click Add and select any users or groups you want to grant membership. Click OK after you have selected the groups and users.
7. Set up any query permissions for the group. For information, refer to Managing Query Execution Privileges.
   NOTE:  Unlike the User dialog box, the Group dialog box does not allow you to select a logging level. The logging level is a user attribute and cannot be specified for a group.

Viewing Member Hierarchies

Use the following procedures to view member hierarchies.

To view member hierarchies in the Security Manager

• Click the hierarchy icon in the left pane of the Security Manager, and then expand the tree in the right pane.

To view member hierarchies in the Query Repository dialog box

1. Select Tools > Query Repository from the main menu of the Administration Tool.
2. To view all groups, select Security Groups from the Type drop-down list and click Query.
3. To view all users, select Users from the Type drop-down and click Query.
4. To view the groups that a group is a member of, select the group and click Parent. For example, to view what groups Group1 is a member of, select Group1 and click the Parent button.