Oracle® Identity Manager Audit Report Developer's Guide Release 9.1.0 Part Number E10365-03 |
|
|
View PDF |
Oracle Identity Manager includes a custom reporting engine that enables you to run predefined reports against the Oracle Identity Manager transactional database or a secondary database if one is configured. You can add new reports without editing any Java code, and you can obtain reporting data by invoking a stored procedure.
This chapter discusses the following topics:
Note:
The Oracle Identity Manager reporting engine is not meant to be a replacement for enterprise reporting solutions. The Oracle Identity Manager reporting engine is not optimized for very large data volume and does not provide the rich features you would find in an enterprise reporting application.For large-scale deployments, especially those taking advantage of the extensive auditing capabilities of Oracle Identity Manager, it is highly recommended that you deploy a dedicated enterprise-class reporting solution. A solution based on tools such as Oracle Business Intelligence Enterprise Edition can provide the flexibility, automation, and performance required for a large-scale enterprise.
The following are Oracle Identity Manager reporting features:
Select and view reports from a predefined list in the Administrative and User Console.
Use a delegated administration model to control the reports available to a user and the information included in those reports.
View reports on-screen.
Provide interactive reports.
Provide support for Crystal Reports.
The following sections explain data storage for reporting at the data, XML, and API layers in Oracle Identity Manager.
You can change the database schema and add stored procedures in the data layer.
The REP
and RPG
tables support reporting. The REP
table contains the following:
A list of all reports in the system
The report name
The report code
The report type
The report description
The name of the stored procedure for the report
The name of the data source
The maximum report size
The number of filters to be displayed on the report page
XML metadata for each report
The RPG
table is a link table between the REP
and UGP
table. This table stores information about the reports and the groups that have access permissions for the reports.
Each report is associated with a stored procedure. To run a report, you run the associated stored procedure with relevant arguments. You cannot run a report based on a database query.
There can be many reports in the system. As a result, the stored procedure follows rules to enable the report to be invoked generically. See "How to Create a Report" for details.
Each stored procedure has a set of required generic parameters. These parameters provide standard information, for example, starting row, page size, filter columns, and so on. The stored procedure can also have any number of report-specific parameters. Each stored procedure returns two values: a result set representing a page of the entire report data, and a total count for the report data. The standard format for a stored procedure and the XML metadata enable you to add and run any report without changing any Java code.
XML metadata for each report is stored in the REP
table for the report. The metadata provides the following information for the report:
Layout information
Representation of all the input parameters and their association with the corresponding stored procedure parameters
Support for user-defined parameters (operational reports only)
Display information for each report input parameter, for example, a field label or field type (for example, TextField
, LookupField
, and so on)
Location of each column on the report display page
Display information for each report data column
Columns to be included in the filter drop downs
Clickable columns for interactive reports
For details on the metadata structure, see "How to Create a Report".
This section lists the operational and historic reports that are available.
Table 5-1 lists the names and description of the operational reports.
Table 5-1 List of Operational Reports
Name | Description |
---|---|
Entitlements Summary |
Lists the number of users for each status within each resource. |
Policy List |
Displays a snapshot of all policies defined within the system. |
Delegated Administrators By Organization |
Lists all the delegated administrator user groups for organizations. |
Attestation Requests by Reviewer |
Lists attestation requests by reviewer. |
Approval Status By Approver |
Provides a summary of all approval tasks. |
User Resource Access |
Lists the access rights to resources for selected users. |
Resource Access List |
Lists all users who have access to a selected resource. |
Policy Detail |
Lists complete details about specific policies defined within the system. |
Group Membership Profile |
Lists the number of users in different numbers of groups. |
OIM Password Expiration |
Lists users whose Oracle Identity Manager passwords are about to expire. |
Group Membership |
Provides a snapshot of users in each group. |
Resource Password Expiration |
Lists users whose resource passwords are about to expire (as determined by Oracle Identity Manager). |
Organization Structure |
Lists the hierarchical organization structure and user memberships. |
Requests Initiated |
Lists all requests initiated in a specified time interval. |
Requests Details By Status |
Returns details of all requests with a specified status. |
Attestation Process List |
Provides a snapshot of all defined attestation processes. |
Attestation Requests by Process |
Lists attestation requests by process. |
Attestation Request Detail |
Lists complete details of selected attestation requests. |
Financially Significant Resources |
Lists complete details of financially significant resources. |
Delegated Administrators & Permissions By Organization |
Lists all administrator user groups and permissions for organizations. |
Delegated Administrators & Permissions By Resource |
Lists all administrator user groups and permissions for resources. |
Delegated Administrators By Resource |
Lists all administrator and authorizer user groups for resources. |
Table 5-2 lists the names and description of the historical reports that are available.
Table 5-2 List of Historical Reports
NAME | DESCRIPTION |
---|---|
User Resource Access History |
Returns the history of a user's resource access. |
Resource Access List History |
Returns a history of all users who have had access to a selected resource. |
User Profile History |
Returns the history of a user's profile. |
User Membership History |
Returns the history of a user's memberships in a user group. |
Group Membership History |
Returns the history of a group's memberships. |
Resource Activity |
Returns the history of all provisioning and approval activities for a resource. |
Task Assignment History |
Returns the history of all task assignment based on the tasks. |
Password Reset Success Failure |
Returns the password change metrics for Oracle Identity Manager users. |
Account Activity In Resource |
Lists all account activities in each resource. |
Rogue Accounts By Resource |
Lists all the rogue accounts in each resource. |
Fine Grained Entitlement Exceptions By Resource |
Lists all fine-grained entitlement exceptions by a resource. |
Users Created |
Lists all users created in a specified time interval. |
Users Deleted |
Lists all users deleted in a specified time interval |
Users Disabled |
Lists all users disabled in a specified time interval. |
Users Unlocked |
Lists all users unlocked in a specified time interval. |
See Also:
These reports are also listed in Chapter 14 of the Oracle Identity Manager Administrative and User Console Guide.In Oracle Identity Manager, exception refers to the difference between accounts that a user is entitled to and the accounts that are actually assigned to a user. The user is assigned these accounts as a result of access policies, provisioning of resources, approval requests, and reconciliation events. Any difference of these accounts assigned to a user in the target system and the ones assigned to the user in Oracle Identity Manager comprises an exception.
The following exception reports have been introduced in this release:
Rogue Accounts By Resource
This report returns a list of all the rogue accounts existing in a resource. The following exceptions are reported:
An account that exists in the target system but is not provisioned to the corresponding user in Oracle Identity Manager
An account that exists in the target system but has been deprovisioned for the corresponding user in Oracle Identity Manager
An account that exists in the target system but the corresponding user to whom the account is provisioned has been deleted in Oracle Identity Manager
The following exception is not reported in spite of being a rogue account:
An account that exists in the target system but the corresponding user to whom the account is provisioned has never existed in Oracle Identity Manager
Fine Grained Entitlement Exceptions By Resource
This report returns a list of all the accounts in a resource for which the process form data being reconciled is different from the expected values. It means that this report returns any account existing in the target system that is also provisioned to the corresponding user in Oracle Identity Manager, but for which the process data does not match.
Note:
After completion of initial target reconciliation, all account-related activities performed directly on a target resource are tracked as exception activity. Account-related activities include account creation, account modification, and entitlement assignment/revocation. The exception reports should be used only if the organization policies enforce that all account-related activities in target resources would always be initiated in Oracle Identity Manager. In addition, remember that exception detection and recording are an extension of account data reconciliation and, therefore, may result in a drop in performance during reconciliation.
Both the exception reports depend on reconciliation data. Therefore, these reports will not display any data if the corresponding reconciliation events are archived.
By default, these two exception reports are not enabled. In order to enable them, you must set the value of the XL.EnableExceptionReports
property to TRUE
in the system configuration. If the value of the property is FALSE
(default value), then the exception reports will not be available on the Historical Reports page of the Administrative and User Console. As a result, the data for the exception reports will not be populated in the RCX
, UPA_UD_FORMS
, and UPA_UD_FORMFIELDS
tables. These reports rely on reconciliation data to be populated.
Suppose you do not enable these exception reports immediately after Oracle Identity Manager installation. Instead, you enable them sometime after you start using Oracle Identity Manager. In such a scenario, the UPA_UD_FORMS, and UPA_UD_FORMFIELDS tables must be populated with baseline form data before you can run the exception reports. To do this, you must use the UPA Form Data Upgrade Utility. See "Using the UPA Form Data Upgrade Utility" for instructions.
The UPA Form Data Upgrade Utility populates the UPA_UD_FORMS
and UPA_UD_FORMFIELDS
tables with baseline data that acts as a starting point for the exception reports. You must run this utility whenever you enable the exception reporting feature. This utility runs through all the entitlements. For provisioning instances that have a resource with either a process or an object form attached, the utility reads the data on the form and stores them in the two database tables. This forms the baseline. Subsequent changes to the form are automatically captured because exception reporting is enabled. With the baseline available, the exception reports start reporting on exceptions. The utility creates a log file named UPA_Form_Data_Upgrade_Timestamp.log
after the process is complete.
To use the UPA Form Data Upgrade Utility:
Copy the UPA Form Data Upgrade Utility files to your local computer.
Before using the utility, copy the UPAFormDataUpgradeUtility
directory from the following directory on the installation media to your local computer:
For Oracle Database:
installServer/Xellerate/db/oracle/Utilities/UPAFormDataUpgradeUtility
Note:
In the rest of this procedure, the full path and name of the local directory containing the UPA Form Data Upgrade Utility files is referred to asUPAFORMDATAUPGRADEUTILITY
.Settings for the Oracle Database Batch/Shell file:
For Microsoft Windows: Edit the following file:
UPAFORMDATAUPGRADEUTILITY\UPAFormDataUpgrade.bat
For Linux or UNIX: Edit the following file:
UPAFORMDATAUPGRADEUTILITY/UPAFormDataUpgrade.sh
Table 5-3 shows the values of the variables that must be set before you run the utility:
Table 5-3 Variables of the UPA Form Data Upgrade Utility
Variables | Description |
---|---|
|
Oracle home directory. |
|
Describes if the database is running on a remote computer. Set a value for this parameter only if |
|
SID of the database. Set a value for this parameter only if |
|
TNS service name that points to the remote database. Set a value for this parameter only if |
For Oracle Database, perform the following steps:
Log in to SQL*Plus with the credentials of the Oracle Identity Manager release database schema owner.
Open the following file in a text editor:
UPAFORMDATAUPGRADEUTILITY/compile_all_XL_SP_UPA.sql
Make the following changes in the compile_all_XL_SP_UPA.sql
script:
Change:
@@XL_SP_LoadFormData.sql
To:
@UPAFORMDATAUPGRADEUTILITY/XL_SP_LoadFormData.sql
Change:
@@XL_SP_UPA_FormData_Upgrade.sql
To:
@UPAFORMDATAUPGRADEUTILITY/XL_SP_UPA_FormData_Upgrade.sql
Run the compile_all_XL_SP_UPA.sql
script.
UPAFORMDATAUPGRADEUTILITY/compile_all_XL_SP_UPA.sql
For Oracle Database on Linux or UNIX:
Run the UPAFormDataUpgrade.sh
file from the following location:
UPAFORMDATAUPGRADEUTILITY/UPAFormDataUpgrade.sh OIM_DB_User_ID OIM_DB_User_Password
Note:
On UNIX, you might also want to clear the command history of the shell by running the following command:history -c
For Oracle Database on Microsoft Windows:
Run the UPAFormDataUpgrade.bat
file from the following location:
UPAFORMDATAUPGRADEUTILITY\UPAFormDataUpgrade.bat OIM_DB_User_ID OIM_DB_User_Password
The following are tasks for creating a new report:
Each report is based on a single stored procedure. The following are rules for the stored procedure:
Use a stored procedure, not a user-defined function.
A stored procedure returns two values: the report data result set and the total number of rows in the report.
The report result set is paged.
The result set that is returned when you run the stored procedure represents one page of the entire report data. The starting row and the size of the page is specified at the time of running the stored procedure.
The stored procedure handles filter parameters and user defined input parameters.
Each stored procedure uses generic parameters, and each can use parameters specific to the stored procedure. These parameter types are described in the following sections.
Generic parameters are common to all the stored procedures. You specify generic parameters in a required sequence before any specific parameters.
There are twelve generic parameters. All twelve generic parameters are required, even if their values are null. The following are the generic parameters in the order that you must specify them:
Report Result Set (type=cursor
, OUT): The result set that represents the report data.
Note:
- For Microsoft SQL Server, the return type is Integer (int
) and not cursor. In Microsoft SQL Server, data is returned in the last query. There is no actual return parameter. This parameter type meets the requirements of the stored procedure query.
- Microsoft SQL Server is not supported in Oracle Identity Manager release 9.1.0. See ÒCertified ComponentsÓ in Oracle Identity Manager Release Notes for information about certified components.
User Key (type=int
, IN): The key of the user who runs the report.
This user key ensures that only records are returned for which the user has read permissions.
Sort Columns (type=varchar
, IN): A list of comma-delimited column names on which the report result set can be sorted.
Reserved for future use.
Sort Order (type=varchar
, IN): The sort order (ascending or descending) for the report result set.
Reserved for future use.
Start Row (type=int
, IN): The row number where the result set starts.
Page Size (type=int
, IN): The size of the result set, or the number of entries in a single page in a multi-page report.
Do Count (type=int
, IN): Can have values 0, 1, or 2.
A value of 0 indicates that the result set is computed and returned, but the total number of rows in the entire report data is not computed. A value of 1 indicates that the result set and the total number of rows are computed. When the value is 2, only the total number of rows is computed and an empty result set is returned.
Total Rows (type=int
, OUT): This is an OUT parameter that returns the total number of rows when the value of the Do Count variable is either 1 or 2.
Because the report data is paged, the value of the total number of rows is not the size of the returned result set. It is the size of the entire report.
For example, suppose that a stored procedure returns a list of all users. There are 200 users in the system, the start row=1, and the page size=50. For this stored procedure, the size of the result set is 50, but the value of the total rows OUT parameter is 200.
Filter Column Names (type=varchar
, IN): This is a comma-delimited list of column names on which the report data can be filtered.
A stored procedure has no way of knowing the alias to use for the listed columns. The stored procedure expects that the column names in this list are correctly qualified with the appropriate table aliases, if needed, for example:
usr.usr_first_name,obj.obj_name
Filter Column Values (type=varchar
, IN): This is a comma-separated list of column values that have a one-to-one correspondence with the column names listed in the previous parameter.
If the previous parameter contains a comma-separated list with two column names, this parameter is a comma separated list of two values. You can use a wild card (%) character, for example, Jo%,Laptop
.
User-Defined Column Names (type=varchar
, IN): This is a comma-separated list of column names that represent user-defined columns on system forms.
These names must be appropriately aliased if needed, for example:
USR.USR_UDF_SSN
User-Defined Column Values (type=varchar
, IN): This is a comma-delimited list of column values for user defined fields.
These values have a one-to-one correspondence with the column names listed in the previous parameter. You can use the wild card (%) character, for example:
1234567890
Specific parameters are specific to each report. These parameters have a one-to-one correspondence with the report input parameters, except for the date range input parameter and user-defined parameters.
You must add specific parameters after the generic parameters. Each specific parameter represents one report input parameter on the Report Input page.
All specific parameters that are of the varchar2
type support the wild card (%) character.
Each time an error is encountered, an exception is thrown. The exception contains an error code. The calling Java code receives the error as a SQLException with the error code embedded in it. The stored procedure checks the code for errors based on the following rules:
The value of Start Row cannot be 0 or null.
The value of Page Size cannot be 0 or null.
The value of User Key cannot be 0 or null.
The value of Do Count can only be 0, 1 or 2.
There is a one-to-one mapping between Filter Column Names and Filter Column Values.
There is a one-to-one mapping between User-Defined Column Names and User-Defined Column Values.
Even if there is no data to return for a report, an empty result set is returned.
Example 5-1 shows the signature for the User Resource Access report stored procedure for Oracle Database:
Example 5-1 Signature of the User Resource Access Stored Procedure for Oracle Database
PROCEDURE XL_SP_UserResourceAccess ( csrresultset_inout IN OUT sys_refcursor, intuserkey_in IN NUMBER, strsortcolumn_in IN VARCHAR2, strsortorder_in IN VARCHAR2, intstartrow_in IN NUMBER, intpagesize_in IN NUMBER, intdocount_in IN NUMBER, inttotalrows_out OUT NUMBER, strfiltercolumnlist_in IN VARCHAR2, strfiltercolumnvaluelist_in IN VARCHAR2, strudfcolumnlist_in IN VARCHAR2, strudfcolumnvaluelist_in IN VARCHAR2, struserlogin_in IN VARCHAR2, strfirstname_in IN VARCHAR2, strmiddlename_in IN VARCHAR2, strlastname_in IN VARCHAR2, struseremail_in IN VARCHAR2, strorgname_in IN VARCHAR2, strusergroup_in IN VARCHAR2, strmgrfirstname_in IN VARCHAR2, strmgrlastname_in IN VARCHAR2, struserstatus_in IN VARCHAR2, struseremptype_in IN VARCHAR2 )
In Example 5-1, the first twelve parameters (upto strudfcolumnvaluelist_in
) are generic parameters. The remaining parameters are specific parameters.
Example 5-2 illustrates the Microsoft SQL Server signature for the User Resource Access stored procedure:
Note:
Microsoft SQL Server is not supported in Oracle Identity Manager release 9.1.0. See ÒCertified ComponentsÓ in Oracle Identity Manager Release Notes for information about certified components.Example 5-2 Microsoft SQL Server Signature for the User Resource Access Stored Procedure
CREATE PROCEDURE XL_SP_UserResourceAccess (@csrResultSet_inout INT OUTPUT, @intUserKey_in INT, @strSortColumn_in VARCHAR(4000), @strSortOrder_in VARCHAR(4000), @intStartRow_in INT, @intPageSize_in INT, @intDoCount_in INT, @intTotalRows_inout INT OUTPUT, @strFilterColumnList_in VARCHAR(8000), @strFilterColumnValueList_in VARCHAR(8000), @strudfcolumnlist_in VARCHAR(8000), @strudfcolumnvaluelist_in VARCHAR(8000), @strUserLogin_in varchar(256), @strFirstName_in varchar(80), @strMiddleName_in varchar(80), @strLastName_in varchar(80), @strUserEmail_in varchar(256), @strorgname_in varchar(256), @strUserGroup_in varchar(30), @strMgrFirstName_in varchar(80), @strMgrLastName_in varchar(80), @strUserStatus_in varchar(25), @strUserEmptype_in varchar(255) )
After you create the stored procedure, you create the XML metadata for the report. All report-specific information is included in the metadata so that the report can be run and displayed correctly. The report metadata provides information such as attributes of the report-specific input parameters, the display properties of the report input parameters and the display properties of the report data, for example, report layout, display labels, and so on.
The root element of the metadata is report
. This element provides the layout of the report. Three display layouts are supported: Tabular layout, Sectional layout, and Sectional with Report Header layout.
The report
element has two child elements: StoredProcedure
and ReturnColumns
.
The StoredProcedure
element provides information about the stored procedure-specific parameters and user defined fields. It consists of a single InputParameters
element that contains multiple InputParameter
elements.
Each specific stored procedure parameter corresponds to one input parameter on the Report Input page. The exception to this rule is the DateRange
field, which is represented by two stored procedure parameters. Each input parameter is represented by one InputParameter
element. The Report Input page can also contain user-defined fields from any system form. Each user-defined field on the Report Input page is also represented by one InputParameter
element. The number of user-defined fields can change, but the number of stored procedure input parameters does not change each time. User-defined fields are represented by comma-delimited lists.
For example, suppose a report must support seven input parameters. Two parameters are user-defined fields, and five are specific parameters in the signature of the stored procedure (in addition to the twelve generic parameters). These are represented by five InputParameter
tags. The two user-defined parameters are also represented by two InputParameter
tags, but they do not have corresponding parameters in the stored procedure signature. Instead, they are passed as comma-delimited lists of column names and their values. Therefore, the metadata must contain a total of seven InputParameter
tags.
If you want to support the DateRange
input type, the Report Input page must be supported by two stored procedure parameters: one for the From date and the other for the To date.
The following are the attributes of the InputParameter
element:
name
(required:Yes): The name of the input parameter.
In case of non-user-defined input parameters, this value can be anything. However, for clarity, it should match the name of the corresponding stored procedure input parameter. For user-defined input parameters, this name is the column name of the user-defined column, prefixed by the required table alias, if needed.
parameterType
(required:Yes): The SQL data type of the corresponding stored procedure parameter.
In case of user-defined input parameters, this value is varchar
.
order
(required:Yes): The order of the report-specific input parameters.
The ordering starts from 1. You must list the regular input parameters, and the user-defined input parameters later.
fieldType
(required:Yes): The type of the display field on the Report Input page.
The supported input types are: TextField
, Date
, DateRange
, LookupField
, and Combobox
.
fieldLabel
(required: Yes): The property value of the field label for this field.
The property value is a value from the message resources property file (xlWebAdmin.properties
in this case), which represents the actual label.
allowedValues
(required: No, unless fieldType
is Combobox
): Lookup codes associated with a combo box.
required
(required: No, default: false): If set to true, then the user must provide a value for this field for the report to run.
udf
(required: No, default:false): For a user defined field that is represented by the InputParameter
element, this attribute must be present and have a value of true. Operational reports only.
If the attribute fieldType
has a value of LookupField
, a child element called ValidValues
must be under the InputParameter
element. The reporting functionality supports three types of lookups: Lookup by code, lookup by method, and lookup by column. The following are the supported attributes of the ValidValues
element:
lookupCode
(required: No): Must be present only if the lookup is by code.
If it is, the value of this attribute is the lookup code.
lookupColumn
(required: No): Must be present only if the lookup is by column.
If it is, the value of this attribute is the column code of the lookup column.
lookupMethod
(required: No): Must be present only if the lookup is by class or method.
If it is, the value of this attribute is the name of method that provides the lookup values.
operationClass
(required: No): Must be present only if the lookupMethod attribute is present.
The value of this attribute is the fully qualified name of the class that contains the lookup method.
displayColumns
(required: No): Must be present only if the lookupMethod
attribute is present.
It is a comma-delimited list of column codes which represent columns that is displayed in the lookup.
selectionColumn
(required: No): Must be present only if the lookupMethod
or lookupColumn
attributes are present.
It represents the column code of the column, the value of which is saved in the database.
Examples of InputParameter tags
Regular TextField
input parameter:
<InputParameter name="strfirstname_in" parameterType="varchar2" order="2" fieldType="TextField" fieldLabel="report.userResourceAccess.label.firstName" required="false" />
User-Defined input parameter:
<InputParameter name="USR.USR_UDF_SSN" parameterType="varchar2" order="11" fieldType="TextField" fieldLabel="report.userResourceAccess.label.SSN" required="false" udf="true" />
Input parameter of type Combobox
:
<InputParameter name="struserstatus_in" parameterType="varchar2" order="10" fieldType="Combobox" allowedValues="Lookup.WebClient.Users.Status" fieldLabel="report.userResourceAccess.label.userStatus" required="false" />
Input parameter of type LookupField
with lookupCode
:
<InputParameter name="struseremptype_in" parameterType="varchar2" order="11" fieldType="LookupField" fieldLabel="report.userResourceAccess.label.employeeType" required="false" > <ValidValues lookupCode="Lookup.Users.Role"/> </InputParameter>
Input parameter of type LookupField
with lookupColumn
<InputParameter name="struseremail_in" parameterType="varchar2" order="5" fieldType="LookupField" fieldLabel="report.userResourceAccess.label.userEmail" required="false" > <ValidValues lookupColumn="Users.Xellerate Type" selectionColumn="Lookup Definition.Lookup Code Information.Decode" /></InputParameter>
Input parameter of type LookupField
with lookupMethod
<InputParameter name="struserlogin_in" parameterType="varchar2" order="1" fieldType="LookupField" fieldLabel="report.userResourceAccess.label.userLogin" required="false" > <ValidValues lookupMethod="findUsersFiltered" operationClass="Thor.API.Operations.tcUserOperationsIntf" displayColumns="Users.User ID,Users.Last Name,Users.First Name" selectionColumn="Users.User ID"/> </InputParameter>
If the attribute fieldType
has a value of DateRange
, you must include the two child elements InputStartDate
and InputEndDate
. Each element must have the following attributes:
name
(required: Yes): The name of the parameter.
This should match the name of the stored procedure parameter that represents this date parameter.
parameterType
(required: Yes): The SQL type of the stored procedure parameter that represents this date parameter.
order
(required: Yes): The order of the stored procedure parameter
defaultValue
(required: No, default: 01/01/1900 and 12/31/2049): The default value to provide if the user does not enter a date in the start or end date fields.
format
(required: No, default: reports.generic.message.internalDateFormat
): The format of the default date.
The ReturnColumns
tag represents the list of all the columns that are being returned by the result set. This tag contains multiple ReturnColumn
tags, each of which represents one column in the returned result set. The attributes of the ReturnColumn
tag provides information that is useful for displaying the report data.
The following are the attributes of the ReturnColumns
tag:
name
(required: Yes): This name represents the column code of the result set column that is represented by this particular tag.
If the column code is not available, it can be the alias or the column name.
label
: (required: Yes): This provides the property value of the column header/label for this column.
The property value is a value from the message resources property file (xlWebAdmin.properties
in this case), which represents the actual label.
position
(required: Yes): This attribute can have three values: Table
, Sectional Header
, or Report Header
.
This attribute specifies the location of each column. Each column can reside in the table (for any layout), the section header (for Sectional Layout and Sectional with Report Header Layout), or the report header (in case of Sectional with Report Header layout).
filterColumn
(required: No, default: false): This attribute specifies whether the column is a filter column.
If the value is true, then the column name is included in the filter drop down lists at the top of the Report Display page.
filterColumnName
(required: No, unless filterColumn
is present): This attribute represents the actual name of the column prefixed by the table alias, if needed.
filterType
(required: No, unless filterColumn
is false): The type of the filter display field on the Report result page. The only supported input type is Combobox
.
filterLookupKey
(required: No, unless filterColumn
is false): Represents a lookup code associated with a combo box that is shown for a filter display field on the Report result page.
clickable
(required: No, default: false): This attribute specifies whether the column value is a link.
This attribute provides support for action-ability of reports.
You can configure interactive reports in the report module. In an interactive report, you define column values to be links that take the user to a page in or outside of the Administrative and User Console when he or she clicks the column value. To configure the links, you add information to the metadata so that the user is taken to the appropriate page. The links can have dynamic or static locations. Clicking on a link opens a new browser window that shares the same browser session but is otherwise self-sufficient.
To configure the links, set the clickable
attribute to true
, and configure the ReturnColumn
element and its two child elements Link
and RequestParameters
, as follows:
Link
element: Configure the single attribute href
for this element.
This attribute specifies the base URL of the destination page. An absolute URL begins with http
, for example, http://www.xyz.com
. The destination page for an absolute URL is outside the Administrative and User Console. A relative URL, for example, searchResources.do
, leads to a destination page in the Administrative and User Console.
RequestParameters
element: Configure multiple RequestParameter
elements for this element.
Each RequestParameter
element represents one request parameter that must be submitted for the destination page to display properly. The name
attribute specifies the name of the request parameter. If the request parameter is static, that is, it does not depend on any other value in the result set, you configure its value by using the value
attribute. If the value of the request parameter is dynamic, that is, it depends on another column of the result set, for example, the Resource Key, then you specify the value by using the column
attribute. The column
attribute contains the column code for the column whose value is to be returned.
Once the report metadata is complete, update the REP
and RPG
tables to make the report available.
The REP
table contains a list of all the reports in the system. Defining a new report requires creating a new row in the REP
table representing this report. Apart from the common columns, for example, Create Date, Created By, Row Version, and so on, you must specify the columns that are populated. The values in the parentheses are examples for User Resource Access reports.
REP_NAME
: This column contains the name of the report that is displayed to the user.
This name is unique in the REP
table. (User Resource Access)
REP_CODE
: A unique code for the report. (UserResourceAccess
)
REP_DESCRIPTION
: A description for the report that is displayed to the user. (Resource access rights for selected users).
REP_SP_NAME
: The name of the stored procedure that provides the data for the report. (XL_SP_UserResourceAccess
)
REP_XML_META
: This column is a clob
that contains the entire metadata for the report defined in the previous section.
REP_TYPE
: The type of the report. This can have two values: Operational
or Historical
. (Operational
)
REP_DATASOURCE
: The name of the data source against which the report is run.
This can have two values: Default
or Reporting
. Usually, the Operational reports are run against the Default database while the Historical reports are run against the reporting database. (Default)
REP_MAX_REPORT_SIZE
: This represents the maximum number of records a report can return.
Different reports will return different amounts of data for each record. To ensure a fast response time for a report, a maximum number of records that a report can return is enforced for each report. (5000)
REP_FILTER_COUNT
: The number of filter drop downs on the Report Display page for this report (3).
The following is an example of the insert statement that populates the REP
table with the User Resource Access report data for an Oracle Database:
INSERT INTO REP (REP_KEY, REP_CODE, REP_TYPE, REP_NAME, REP_DESCRIPTION, REP_DATASOURCE, REP_SP_NAME, REP_MAX_REP_SIZE, REP_FILTER_COUNT, REP_DATA_LEVEL, REP_CREATE, REP_CREATEBY, REP_UPDATE, REP_UPDATEBY, REP_ROWVER) VALUES (rep_seq.nextval, 'UserResourceAccess', 'Operational', 'User Resource Access','Resource access rights for selected users', 'Default', 'XL_SP_UserResourceAccess', 5000, 3, 1, SYSDATE, <System Administrator User Key>, SYSDATE, <System Administrator User Key>, HEXTORAW('0000000000000000'));
The following is the example of the INSERT
statement that populates the REP
table with the User Resource Access report data in Microsoft SQL Server:
Note:
Microsoft SQL Server is not supported in Oracle Identity Manager release 9.1.0. See ÒCertified ComponentsÓ in Oracle Identity Manager Release Notes for information about certified components.INSERT INTO REP (REP_CODE, REP_TYPE, REP_NAME, REP_DESCRIPTION, REP_DATASOURCE, REP_SP_NAME, REP_MAX_REP_SIZE, REP_FILTER_COUNT, REP_DATA_LEVEL, REP_CREATE, REP_CREATEBY, REP_UPDATE, REP_UPDATEBY, REP_ROWVER) VALUES ('UserResourceAccess', 'Operational', 'User Resource Access', 'Resource access rights for selected users', 'Default', 'XL_SP_UserResourceAccess', 5000, 3, 1, GETDATE(), <System Administrator User Key>, GETDATE(), <System Administrator User Key>, 0x0);
After you create an entry in the REP
table for the report, you must load the XML metadata for the report into the REP table's REP_XML_META
column. See "Creating the Report XML Metadata" for information about creating metadata.
The following procedure describes how to provide access to a report.
See Also:
Oracle Identity Manager Administrative and User Console GuideTo provide access to the new report to a particular user group:
Search for the user group by using the Manage User application.
Navigate to the detail page for the user group.
Click the Allowed Reports link in the additional details drop down.
The Reports page under Group Detail is displayed.
Click the Assign Reports button.
The Assign Reports page is displayed in the Reports section under Group Detail. The report you have just created is listed on this page.
To provide access to the report, assign it to at least one group.
This section describes how to modify property files for translating label names, report names, and report descriptions. It contains the following topics:
After you write the XML metadata, you must introduce the new field label properties in the metadata file in the properties files. You add the properties to the files corresponding to the locales that you want to support. The languages that are supported in Release 9.1.0 and their associated property files are as follows:
Chinese (Simplified) (xlWebAdmin_zh_CN.properties
)
Chinese (Traditional) (xlWebAdmin_zh_TW.properties
)
Danish (xlWebAdmin_da.properties
)
English (xlWebAdmin_en_US.properties
)
French (xlWebAdmin_fr.properties
)
German (xlWebAdmin_de.properties
)
Italian (xlWebAdmin_it.properties
)
Japanese (xlWebAdmin_ja.properites
)
Korean (xlWebAdmin_ko.properties
)
Portuguese (Brazilian) (xlWebAdmin_pt_BR.properties
)
Spanish (xlWebAdmin_es.properties
)
When Oracle Identity Manager encounters an unsupported locale, it refers to the xlWebAdmin.properties
file. You add to these files the properties included as fieldLabel
attributes of InputParameter
tags and label attributes of ReturnColumn
tags. Instead of providing the actual name of the input field labels or return column labels, you specify the property name, which is then looked up from the respective properties file. This makes it simpler for internationalization.
The following examples illustrate how to set the fieldLabel
attributes of the InputParameter
and ReturnColumn
tags.
Example 1
<InputParameter name="struserlogin_in" parameterType="varchar2" order="1" fieldType="TextField" fieldLabel="report.userResourceAccess.label.userLogin" required="false" />
This InputParameter
tag is from UserResourceAccess.xml
metadata file for the User Resource Access report. The fieldLabel
attribute of this tag has the value of report.UserResourceAccess.label.userLogin
. The corresponding entry for this property in the xlWebAdmin_en_US.properties
file is:
report.UserResourceAccess.label.userLogin=Userid
Example 2
<ReturnColumn name="Users.First Name" label="report.userResourceAccess.label.firstName" position="SectionHeader" filterColumn="true" filterColumnName="usr.usr_first_name" />
This ReturnColumn
tag is from UserResourceAccess.xml
metadata file for the User Resource Access report. The label
attribute of this tag has the value of report.userResourceAccess.label.firstName
. The corresponding entry for this property in the xlWebAdmin_en_US.properties
file is:
report.userResourceAccess.label.firstName=First Name
Note:
If the actual label names change (as a result of customization, for example), the property names need not be changed.Perform the following steps to edit the xlWebAdmin_en_US.properties
file:
Extract the xlWebApp.war
file in the OIM_HOME
/webapp
directory to a temporary directory.
Open in a text editor the xlWebAdmin_en_US.properties
file in the temporary directory where you extracted the xlWebApp.war
file.
Add the appropriate properties as fieldLabel
attributes of InputParameter
tags and label attributes of ReturnColumn
tags.
Re-create the xlWebApp.war
file, and copy it to the OIM_HOME
/webapp
directory.
After you create an entry in the REP
table, you must add translation properties for the report name and description to the properties files. This procedure is necessary to localize the English entries in the database and must be performed even if you only want to provide support for the English locale.
To add properties for translating report names and descriptions:
Extract the xlWebApp.war
file in the OIM_HOME
/webapp
directory to a temporary directory.
Open in a text editor the xlDefaultAdmin.properties
file from the temporary directory where you extracted the xlWebApp.war
file.
In the xlDefaultAdmin.properties
file, locate the global.resultSet.Reports.Report~Name
property. This property identifies the names of all reports in the system. Each report assigned to the property is separated by the vertical bar (|) character. Append a vertical bar, and add the name of the new report to the property value. Ensure that there are no spaces between the pipe character and the report name, and that there are no trailing spaces following the report name.
Locate in the xlDefaultAdmin.properties
file the global.resultSet.Reports.Report~Description
property, which contains descriptions of all reports in the system. Each report assigned to the property is separated by a vertical bar (|) character. Append a pipe character and the description of the new report to the property value. Ensure that there are no spaces between the pipe character and the description, and that there are no trailing spaces following the description.
Open in a text editor the properties file representing the locale for which you want to translate report names and descriptions.
Add a global.resultSet.Reports.Report~Name.ReportName property, which identifies the report name and its localized value. The ReportName portion of the property name represents the name of the report. Spaces in the property name are represented by tildes (~). You assign a localized value to the property. For example, you add the following property to the xlWebAdmin_en_US.properties
file to represent a report named Group Information:
global.resultSet.Reports.Report~Name.Group~Information=Group Information
Add a global.resultSet.Reports.Report~Description.Report~Description property, which contains a report description along with and its localized value. The Report~Description portion of the property name represents the default report descriptions. Spaces in the report description are represented by tildes (~). You assign a localized value to the property. For example, you add the following property to the xlWebAdmin_en_US.properties
file for a description of the Group Information:
global.resultSet.Reports.Report~Description.Description~of~the~Group~Information~report=Description of the Group Information report
Add global.ReportName.Lookup.Report-Name and global.ReportDesc.Lookup.Report-Description lookup properties for each new report. The Report-Name and Report-Description portions of each property name represent the default report name and description, respectively. Spaces in the report description are represented by hyphens (-). You assign localized values to each property. For example, you add the following properties to the xlWebAdmin_en_US.properties
file for the Group Information report:
global.ReportName.Lookup.Group-Information=Group Information global.ReportDesc.Lookup.Description~of~the~Group~Information~report=Description of the Group Information report
Recreate the xlWebApp.war
file and copy it to the OIM_HOME
/webapp
directory.
Third-party reporting tools can run reports against Oracle Identity Manager by using the provided stored procedures. You do not need to understand the data model or write queries for predefined reports. You can use any reporting tool for custom stored procedures and custom reports.
Information about the snapshot and changes are stored in XML form in the UPA
table. A third-party XML reporting tool can generate reports from this table. If XML is not a desired format, the reporting tool can use the reporting tables related to the user profile audit feature to retrieve data: UPA_USR
, UPA_FIELDS
, UPA_GRP_MEMBERSHIP
, UPA_RESOURCE
, UPA_UD_FORMS
, and YPA_UD_FORMFIELDS
.
The historical reports accept two different date ranges as input parameters, the Data Snapshot Date Range and the Data Changes Date Range.
The Data Snapshot Date Range is actually the existing date range for historical reports. It has been labeled this release onward. It filters records depending on whether the records are applicable in the specified date range.
The Data Change Date Range is a new feature of this release. It filters the records depending on whether the records were changed in the specified date range.
You need to specify only one date range. The Data Snapshot Date Range is a superset of the Data Change Date Range. All changes in data are automatically included in the Data Snapshot Date Range.
Note:
If you specify values in both the date ranges, then the Data Change Date Range overrides the Data Snapshot Date Range and the latter is ignored.The following are the limitations of reporting for this release:
If you use the filter or input parameters while retrieving reports, the "_" character behaves as a single character wildcard.
If the "User Id reuse" system property is set to true
, a user can be created with a same ID as that of a deleted user. In case you create a new user through this feature, all the reports in which the user data is displayed in a section would show the data for both deleted and active users in a single section. This is because both the users will share the same user ID.