9 Audit

This chapter describes how to configure and use the auditing functionality of Oracle WebCenter Ensemble. Auditing provides information about the creation, modification, and deletion of Oracle WebCenter Ensemble resources and policies from within the Ensemble Console, along with usage information for resources proxied by Oracle WebCenter Ensemble.

This chapter is divided into the following sections:

Enabling Audit, describes how audit is enabled for Oracle WebCenter Ensemble resources and policies.
Generating Audit Reports, describes how to use the Oracle WebCenter Ensemble database to generate audit reports.

Enabling Audit

Auditing data is automatically recorded when Oracle WebCenter Ensemble resources and policies are created, modified, or deleted.

You can enable and disable auditing of usage for each proxied resource. When you create a resource, its audit status defaults to disabled.

To change the audit status of a resource:

Launch the Ensemble Console.
Click the AUDIT tab.
Click the name of the resource you want to configure.
To enable or disable auditing, next to Audit Status, select Enabled or Disabled.
Click Save.

Generating Audit Reports

Audit information is stored in the Oracle WebCenter Ensemble database. You can generate audit reports using SQL queries. The following sections provide sample SQL scripts and describe the data returned by the queries:

Auditing Access to Proxied Resources
Auditing Creation, Modification, and Deletion of Oracle WebCenter Ensemble Resources
Auditing Creation, Modification, and Deletion of Oracle WebCenter Ensemble Policies

Auditing Access to Proxied Resources

Audit information regarding access to proxied resources is stored in the ACCESSAUDITRECORDS table in the Oracle WebCenter Ensemble database.

Example SQL Queries

The following query displays all accesses to any resource by a specific username. Replace owner with the database owner of the ACCESSAUDITRECORDS table.

select ID, CREATE_DATE, USERNAME, USERTYPE, SERVICENAME, RESOURCE_ID, RESOURCENAME, ACCESSSUCCESS, ACCESSURL, ACCESSPRIMAUTHENTICATIONMETHOD, ACCESSRESAUTHENTICATIONMETHOD
from owner.ACCESSAUDITRECORDS
where USERNAME='username';

The following query displays all accesses by a specific username to a specific resource. Replace owner with the database owner of the ACCESSAUDITRECORDS table.

select ID, CREATE_DATE, USERNAME, USERTYPE, SERVICENAME, RESOURCE_ID, RESOURCENAME, ACCESSSUCCESS, ACCESSURL, ACCESSPRIMAUTHENTICATIONMETHOD, ACCESSRESAUTHENTICATIONMETHOD
from owner.ACCESSAUDITRECORDS
where USERNAME='username' and RESOURCENAME='resource';

You should create custom queries to meet your reporting needs.

Schema Description

The following table describes the ACCESSAUDITRECORDS schema.

Table 9-1 ACCESSAUDITRECORDS

Column	Description
ID	A unique identifier for the audit record in this table.
CREATE_DATE	The date and time the audit event was created.
USERNAME	The name of the user accessing the resource.
USERTYPE	A comma-delimited list of roles. Roles are assigned by the experience definition associated with the user when the resource is accessed.
SERVICENAME	The fully-qualified domain name of the Ensemble Proxy server.
ACCESSSUCCESS	1 if the resource is successfully accessed. 0 if access to the resource fails. Note: Access to the resource fails if the HTTP request from the proxy service to the proxied resource fails, or if the user does not have a role required to access the resource. If the user does not authenticate with Oracle WebCenter Ensemble, no audit event is generated.
ACCESSURL	The URL the user used to access the resource.
ACCESSIPADDRESS	The user's IP address.
ACCESSPRIMAUTHENTICATIONMETHOD	The authentication method used to authenticate the user. Possible values include: Basic authentication: [class com.plumtree.runner.authentication.integrated.BasicAuthIntegratedAuthenticator] Form authentication: INTERACTIVE Oracle COREid authentication: Oracle COREid Authenticator SPNEGO authentication: SPNEGO SiteMinder authentication: CA SiteMinder Authenticator
ACCESSRESAUTHENTICATIONMETHOD	The authentication method used by Oracle WebCenter Ensemble to access the proxied resource. Possible values are: Autologin Disabled Basic Form None Selected

Auditing Creation, Modification, and Deletion of Oracle WebCenter Ensemble Resources

Audit information regarding the creation, modification, and deletion of Oracle WebCenter Ensemble resources is stored in two tables in the Oracle WebCenter Ensemble database: RESOURCECONFIGAUDITRECORDS and RESOURCECONFIGDATA.

RESOURCECONFIGAUDITRECORDS stores information about who modifies which resources, and when.

RESOURCECONFIGDATA stores snapshot of the properties of the resource. This allows you to see how the resource changes with each modification.

Example SQL Queries

The following query displays all creation, modification, or deletion of resources by a specific username. Replace owner with the database owner of the RESOURCECONFIGAUDITRECORDS table.

select ID, CREATE_DATE, USERNAME, USERTYPE, OWNERNAME, POLICYOWNERNAME, ENABLED_FLAG, SERVICENAME, RESOURCE_ID, RESOURCENAME, ACTIONTYPE
from owner.RESOURCECONFIGAUDITRECORDS
where USERNAME='username';

The following query displays the details of how a specific resource was modified.

select owner.RESOURCECONFIGDATA.record_id, owner.RESOURCECONFIGDATA.pageNumber, owner.RESOURCECONFIGAUDITRECORDS.USERNAME, owner.RESOURCECONFIGAUDITRECORDS.RESOURCENAME, owner.RESOURCECONFIGDATA.properties
from owner.RESOURCECONFIGAUDITRECORDS, owner.RESOURCECONFIGDATA
where owner.RESOURCECONFIGAUDITRECORDS.ID=owner.RESOURCECONFIGDATA.record_id
and owner.RESOURCECONFIGAUDITRECORDS.RESOURCENAME='resource';

You should create custom queries to meet your reporting needs.

Schema Description

The following table describes the RESOURCECONFIGAUDITRECORDS schema.

Table 9-2 RESOURCECONFIGAUDITRECORDS

Column	Description
ID	A unique identifier for the audit record in this table.
CREATE_DATE	The date and time the audit event was created.
USERNAME	The name of the user creating, modifying, or deleting the resource.
USERTYPE	The highest administrative role of the user that allows him to make the resource configuration change. Possible values, from highest to lowest, are: Administrators Managers Resource Owners
OWNERNAME	GUID of the resource owner.
POLICYOWNERNAME	GUID of the policy owner of the policy associated with this resource.
SERVICENAME	The fully-qualified domain name of the Ensemble Proxy server.
RESOURCE_ID	The ID of the resource in the RESOURCES table.
RESOURCENAME	The name of the resource.
ACTIONTYPE	An integer from 0-2 that indicates what has been done to the resource: 0: Resource created. 1: Resource modified. 2: Resource deleted.

Note:

OWNERNAME and POLICYOWNERNAME GUIDs come from the Oracle WebCenter Interaction portal database. These values are stored in the PTMIGRATION table, which can be joined with the PTUSERS table to match user names with GUIDs.

The following table describes the RESOURCECONFIGDATA schema:

Table 9-3 RESOURCECONFIGDATA

Column	Description
record_id	Associates this entry with a record in RESOURCECONFIGAUDITRECORDS.
properties	A CR-delimited string of name/value pairs that provides a snapshot of the resource's configured values.
pageNumber	The properties column may require multiple rows. In cases where multiple rows are required, pageNumber is incremented for each additional row for a given record_id.

Auditing Creation, Modification, and Deletion of Oracle WebCenter Ensemble Policies

Audit information regarding the creation, modification, and deletion of Oracle WebCenter Ensemble policies is stored in two tables in the Oracle WebCenter Ensemble database: AUTHORIZATIONCONFIGAUDITRECS and AUTHORIZATIONCONFIGDATA.

AUTHORIZATIONCONFIGAUDITRECS stores information about who modifies which policies, and when.

AUTHORIZATIONCONFIGDATA stores snapshot of the properties of the policy. This allows you to see how the policy changes with each modification.

Example SQL Queries

The following query displays all creation, modification, or deletion of policies by a specific username. Replace owner with the database owner of the AUTHORIZATIONCONFIGAUDITRECS table.

select ID, CREATE_DATE, USERNAME, USERTYPE, OWNERNAME, POLICYOWNERNAME, ENABLED_FLAG, SERVICENAME, RESOURCE_ID, RESOURCENAME, ACTIONTYPE
from owner.AUTHORIZATIONCONFIGAUDITRECS
where USERNAME='username';

The following query displays the details of how a specific policy policy was modified.

select owner.AUTHORIZATIONCONFIGDATA.record_id, owner.AUTHORIZATIONCONFIGDATA.pageNumber, owner.AUTHORIZATIONCONFIGAUDITRECS.USERNAME, owner.AUTHORIZATIONCONFIGAUDITRECS.RESOURCENAME, owner.AUTHORIZATIONCONFIGDATA.properties
from owner.AUTHORIZATIONCONFIGAUDITRECS, owner.AUTHORIZATIONCONFIGDATA
where owner.AUTHORIZATIONCONFIGAUDITRECS.ID=owner.AUTHORIZATIONCONFIGDATA.record_id
and owner.AUTHORIZATIONCONFIGAUDITRECS.RESOURCENAME='policy';

Custom queries should be created to meet your reporting needs.

Schema Description

The following table describes the AUTHORIZATIONCONFIGAUDITRECS schema.

Table 9-4 AUTHORIZATIONCONFIGAUDITRECS

Column	Description
ID	A unique identifier for the audit record in this table.
CREATE_DATE	The date and time the audit event was created.
USERNAME	The name of the user creating, modifying, or deleting the resource.
USERTYPE	The highest administrative role of the user that allows him to make the resource configuration change. Possible values, from highest to lowest, are: Administrators Managers Resource Owners
OWNERNAME	GUID of the resource owner this policy is associated with.
POLICYOWNERNAME	GUID of the policy owner of the policy set.
SERVICENAME	The fully-qualified domain name of the Ensemble proxy server.
RESOURCE_ID	The ID of the policy set in the POLICIES table.
RESOURCENAME	The name of the policy set.
ACTIONTYPE	An integer from 0-2 that indicates what has been done to the resource: 0: Policy set created. 1: Policy set modified. 2: Policy set deleted.

Note:

The following table describes the AUTHORIZATIONCONFIGDATA schema:

Table 9-5 AUTHORIZATIONCONFIGDATA

Column	Description
record_id	Associates this entry with a record in AUTHORIZATIONCONFIGAUDITRECS.
properties	A CR-delimited string of name/value pairs that provides a snapshot of the policy set's configured values.
pageNumber	The properties column may require multiple rows. In cases where multiple rows are required, pageNumber is incremented for each additional row for a given record_id.