| Oracle® Identity Manager Audit Report Developer's Guide Release 9.0 B28760-01 |
|
 Previous |
 Next |
| Oracle® Identity Manager Audit Report Developer's Guide Release 9.0 B28760-01 |
|
Previous |
Next |
Oracle Identity Manager includes a custom reporting engine so users can run predefined reports against the Oracle Identity Manager transactional database or a secondary database, if one is configured. The reporting module is flexible so that adding new reports is simple, without editing any Java code. You may obtain all the reporting data by invoking a stored procedure. Oracle Identity Manager comes with the following out-of-the-box operational and historical reports:
This chapter includes the following topics:
You can use the Oracle Identity Manager reporting features in the following ways:
Retrieve report data based on a predefined list of standard reports, available as stored procedures.
Select and view reports from a predefined list in the Administrative and User Console.
Use a delegated administration model that controls the reports available to a user, and the information included in those reports.
View reports paged on-screen.
Provide interactive reports.
The following sections explain data storage for reporting at the data, XML, and API layers in Oracle Identity Manager.
The data layer is where you can make changes to the database schema and add stored procedures. To support the reporting functionality, two new tables have been introduced. They are REP and RPG.
The REP table contains a list of all the reports present in the system. This table includes the name, report code, type, description, stored procedure name, data source name, maximum supported report size, and the number of filters to be displayed on the report page. It also contains the XML meta data for each report.
The RPG table is a link table between the REP and UGP table. This table stores information on group permissions for reports.
Each report is associated with a stored procedure. To run a report, you need to run the associated stored procedure with the relevant arguments. There is no facility to run a report based on a database query.
Since there can be many reports in the system, the stored procedure follows certain rules so that the report can be generically invoked. These rules are listed in detail in the How To Create A New Report section of this chapter.
Each stored procedure must have some generic input parameters that provide standard information to the stored procedure (like start row, page size, filter columns, and so forth.). Apart from these generic parameters, the stored procedure can 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 of the report data. With a standard format of the stored procedure, and the provided XML meta data, any report can be added and run without changing any Java code.
The XML meta data for each report is stored in the REP table for that report. The meta data provides the following information for each report:
Layout information for each report
Representation of all the report input parameters and their association with the corresponding stored procedure parameters
Support for user-defined parameters
Display information for each report input parameter, such as display field label, field type (whether TextField, LookupField, and so forth)
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 a detailed description of the meta data structure, please refer to the How To Create A New Report section of this chapter.
The process of creating a new report can be divided into the following tasks:
Each report is based on a single stored procedure. This stored procedure provides all the report data when invoked. The reporting functionality follows a set of rules for the stored procedures. These rules are:
It is a stored procedure and not a user-defined function.
Each 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 each time the stored procedure is run represents one page of the entire report data. The starting row and the size of this page is specified at the time of running the stored procedure.
The stored procedure handles filter parameters and user defined input parameters.
The parameters of each stored procedure are of two types: Generic Parameters and Specific Parameters. These are described in the following subsections.
Generic parameters are common to all the stored procedures. There are 12 generic parameters, a list of which follows. The order of the generic parameters is important because the reporting functionality expects these to be in a predefined order. The generic parameters are specified before any specific parameters.
All twelve generic parameters are necessary, even if most of the values are null. The generic parameters in the order of specification are:
Report Result Set (type=cursor, OUT): The result set that represents the report data.
User Key (type=int, IN): The key of the user who runs the report. This user key is required so that only those 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 should 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 starting row number from 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. When the value is 0, the result set is computed and returned, but the total number of rows of the entire report data is not computed. When the value is 1, the result set and the total number of rows is computed. When the value is 2, only the total number of rows is computed and the result set is not computed and returned. Instead, 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. Since the report data is paged, the value of the total number of rows is not the size of the result set being returned, but the size of the entire report.
For example, if a stored procedure returns a list of all users, and there are 200 users in the system, and start row=1, and page size=50, then the size of the result set returned 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. Since the stored procedure has no way of knowing which alias to use for the listed columns, it expects that the column names in this list are correctly qualified with the appropriate table aliases, if needed. An example of this is: "usr.usr_first_name,obj_name".
Filter Column Values (type=varchar, IN): This is a comma separated list of column values corresponding to the column names listed in the previous parameter. There is a one-to-one correspondence between the column names and column values. So if the previous parameter has a comma separated list having 2 column names, then this parameter is a comma separated list of 2 values. Also, the values support the wild card (%) character. An example of this is: "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 is appropriately aliased if needed. An example of this is: "USR.USR_UDF_SSN".
User-Defined Column Values (type=varchar, IN): This is a comma-delimited list of column values of user defined fields corresponding to the column names listed in the previous parameter. There is a one-to-one correspondence between the column names and the column values. Also, the values support the wild card (%) character. An example of this is: "1234567890".
The specific parameters are specified after the generic parameters. Each specific parameter represents one report input parameter on the Report Input page.
Specific parameters are, as indicated by the name, 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. All specific parameters that are of the varchar2 type support the wild card (%) character.
Each time an error is encountered, an exception is thrown with an error code embedded in it so that the calling Java code can receive 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 in the values of the 'filter column names' and 'filter column values'.
There is a one-to-one mapping in the values of the '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.
The following is the signature of the Who Has What report stored procedure for Oracle Database:
PROCEDURE XL_SP_WhoHasWhat (
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 this example, the first 12 parameters (upto strudfcolumnvaluelist_in) are all generic parameters and the remaining are specific parameters.
Here is the SQL Server Signature for the Who Has What stored procedure:
CREATE PROCEDURE XL_SP_WhoHasWhat(@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 creating the stored procedure, create the XML meta data for the report. Since the report functionality is generic, all the report-specific information goes into the meta data, so that the report can be run and displayed correctly. The report meta data provides information such as attributes of the report specific input parameters, the display properties of the report input parameters and also the display properties of the report data (such as report layout information, display labels, and so on).
The root tag of the meta data is the report tag. This tag provides the layout of the report. The following three display layouts are supported: Tabular layout, Sectional layout, and Sectional with Report Header layout.
The report tag has two child tags: StoredProcedure and ReturnColumns.
The StoredProcedure tag provides information about the stored procedure specific parameters and the user defined fields. It consists of a single InputParameters tag that consists of multiple InputParameter tags.
Each specific stored procedure parameter corresponds to one input parameter on the Report Input page, except for the DateRange field, which is represented by two stored procedure parameters. Each such input parameter is represented by one InputParameter tag. Apart from this, the Report Input page can also contain user-defined fields from any system form. In such a case, each user-defined field on the Report Input page is also represented by one InputParameter tag. The number of user-defined fields can change, but the number of stored procedure input parameters does not change each time. Hence, the user-defined fields are represented by comma-delimited lists.
For example, if the report needs to support 7 input parameters and 2 are user-defined fields, then there are 5 specific parameters in the signature of the stored procedure (apart from the 12 generic parameters). These are represented by 5 InputParameter tags. The 2 user-defined parameters are also represented by 2 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. Thus, there should be a total of 7 InputParameter tags in the meta data.
If the DateRange input type has to be supported, then that single input type on the Report Input page is 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 tag:
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 matches 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 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. It is required that the regular input parameters be listed first, and the user-defined input parameters come 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): A list of comma separated values that is populated in the drop down of the combo box. The combo box input type supports only static values.
required (required: No, default: false): If set to true, the user needs to provide a value for this field for the report to run.
udf (required: No, default:false): If the field represented by the InputParameter tag is a user defined field, then this attribute must be present and have a value of true.
If the attribute fieldType has a value of LookupField, then there needs to be a child tag under the InputParameter tag called ValidValues. 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 tag:
lookupCode (required: No): Must be present only if the lookup is by code. If it is, then 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, then 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, then 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.whoHasWhat.label.firstName" required="false" />
User-Defined input parameter:
<InputParameter name="USR.USR_UDF_SSN" parameterType="varchar2" order="11" fieldType="TextField" fieldLabel="report.whoHasWhat.label.SSN" required="false" udf="true" />
Input parameter of type Combobox:
<InputParameter name="struserstatus_in" parameterType="varchar2" order="10" fieldType="Combobox" allowedValues=",Active,Disabled,Deleted" fieldLabel="report.whoHasWhat.label.userStatus" required="false" />
Input parameter of type LookupField with lookupCode:
<InputParameter name="struseremptype_in" parameterType="varchar2" order="11" fieldType="LookupField" fieldLabel="report.whoHasWhat.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.whoHasWhat.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.whoHasWhat.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, then include the two child tags InputStartDate and InputEndDate, each of which having the following attributes:
name (required: Yes): The name of the parameter. As a standard, this matches 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 be provided if the user does not enter any 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 itself.
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 either in the table (in case of any layout) or the sectional header (in case of Sectional Layout and Sectional with Report Header Layout) or 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.
clickable (required: No, default: false): This attribute specifies whether the column value is a link. This attribute provides support for action-ability of reports.
The report module allows interactive reports, that is, selected column values can be links. In order to make the values links, extra information needs to be included in the meta data so that the user is taken to the appropriate page when the column value is clicked. Depending on how the links are configured, the user can either be taken to a page inside the Adminitrative and User Console, or to a separate page outside of the Adminitrative and User Console. The links can either have dynamic or static locations. Clicking on any link opens a new browser window that shares the same browser session but is otherwise self-sufficient.
In order to achieve this functionality, the ReturnColumn tag contains two child tags Link and RequestParameters if the clickable attribute has a value of true.
The Link tag has a single attribute called href which provides the base URL of the destination page. If the URL provided is absolute (begins with http, like http://www.xyz.com) then the destination is a page outside the Adminitrative and User Console. If the URL provided is relative (for example, searchResources.do), then the destination page is an Adminitrative and User Console page.
The RequestParameters tag has multiple RequestParameter tags. Each RequestParameter tag represents one request parameter that needs to be submitted for the destination page to display properly. The name attribute specifies the name of the request parameter. The value of the request parameter can be specified in two ways. If the value of the request parameter is static (that is, it does not depend on any other value in the result set) then the value attribute provides that value. 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 the value is specified by the column attribute. The column attribute contains the column code of the column whose value is to be returned.
After the XML metadata has been written, the new field label properties used in the metadata file must be introduced in the xlWebAdmin.properties file. These include all the properties included as the fieldLabel attributes of InputParameter tags and the label attributes of ReturnColumn tag. 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 xlWebAdmin.properties file. This makes it simpler for internationalization.
To edit the xlWebAdmin.properties file, extract the xlWebApp.war file present in the % XL_HOME%/webapp directory into a temporary directory. Navigate to the WEB-INF/classes directory and access the xlWebAdmin.properties file. Edit the xlWebAdmin.properties file in place. After the edits are done, re-create the xlWebApp.war file and put it in the % XL_HOME%/webapp directory. Then run the patch, depending on which application server is being used to host Oracle Identity Manager.
The following examples illustrate the usage of the InputParameter and ReturnColumn tags in the xlWebAdmin.properties file.
Example 1
<InputParameter name="struserlogin_in" parameterType="varchar2" order="1" fieldType="TextField" fieldLabel="report.whoHasWhat.label.userLogin" required="false" />
This InputParameter tag is from WhoHasWhat.xml metadata file for the Who Has What report. The fieldLabel attribute of this tag has the value of report.whoHasWhat.label.userLogin. The corresponding entry for this property in the xlWebAdmin.properties file is:
report.whoHasWhat.label.userLogin=Userid
Example 2
<ReturnColumn name="Users.First Name" label="report.whoHasWhat.label.firstName" position="SectionHeader" filterColumn="true" filterColumnName="usr.usr_first_name" />
This ReturnColumn tag is from WhoHasWhat.xml metadata file for the Who Has What report. The label attribute of this tag has the value of report.whoHasWhat.label.firstName. The corresponding entry for this property in the xlWebAdmin.properties file is:
report.whoHasWhat.label.firstName
|
Note: If the actual label names change (as a result of bug fixes, for example), the property names need not be changed. |
Once the report meta data 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 (like Create Date, Created By, Row Version, and so on), you need to specify the columns that are populated. The values in the parentheses are examples for Who Has What report.
REP_NAME: This column contains the name of the report that is displayed to the user. This name is unique in the REP table. (Who Has What)
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_WhoHasWhat)
REP_XML_META: This column is a clob that contains the entire meta data 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 amount of data for each record. In order to keep the response time for a report acceptable, 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 Who Has What 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, 'WhoHasWhat', 'Operational', 'Who Has What','Resource access rights for selected users', 'Default', 'XL_SP_WhoHasWhat', 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 Who Has What report data in SQL Server:
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 ('WhoHasWhat', 'Operational', 'Who Has What', 'Resource access rights for selected users', 'Default', 'XL_SP_WhoHasWhat', 5000, 3, 1, GETDATE(), <System Administrator User Key>, GETDATE(), <System Administrator User Key>, 0x0);
To provide access to the new report to a particular user group, search for the user group using the Manage User functionality and navigate to the detail page for that user group. Click Allowed Reports link in the additional details drop down, which will display the Reports page under Group Detail. On this page click Assign Reports button to navigate to the Assign Reports page in the Reports section under Group Detal. On this page you will see the name of the new report you have just created.Assign this report to the user group from this page.
|
Note: Oracle Identity Manager Administrative and User Console Guide |
Third-party reporting tools can run reports against Oracle Identity Manager by using the stored procedures provided. No understanding of the data model or writing queries for predefined reports is required. Any reporting tool can be used for custom stored procedures and custom reports.
Information about the snapshot and changes are stored in XML form in the UPA table and a third-party XML reporting tool can generate reports from this table. However, if XML is not a desired format, the reporting tool can rely on the reporting tables related to the User Profile Audit feature to retrieve data: UPA_USR, UPA_FIELDS, UPA_GRP_MEMBERSHIP, and UPA_RESOURCE.
