Abiquo reporting schema and architecture reference

Abiquo reporting schema

The Abiquo reporting schema is the database interface for standard Abiquo reports and third-party database access to the Abiquo DBMS. This section describes:

• How reporting integrates with kinton and kinton_accounting

• The security benefits of using separate schemas for reporting

• How the Abiquo Jaspersoft JasperReports uses reporting

• How the customers can use reporting to develop their own custom reports

• A reference guide to the stored procedures and views found in kinton_reporting

Architectural overview

The following diagram shows the components of the reporting schema architecture.

Open

Abiquo Reporting architecture

At the top layer of the architecture is the Jaspersoft JasperReports Server. This is where the report presentation is defined, managed, and executed. The JasperReports report definition specifies a report stored procedure to obtain the raw report data for the report. These report stored procedures are defined in a separate kinton_reports schema - this allows us to modularise the separate report components and improves the DBMS security, as the Reports Application does not need direct access to the main Abiquo databases. Customers can create their own reporting schema containing any reports they need for the platform, which should be maintained in their own schema, and not in the same schema as the Abiquo standard reports.

The report stored procedures generate the data set returned to the reports. This is done by utilizing objects in the kinton_reporting schema, which is the interface to the Abiquo DBs. It defines stored procedures and tables to manage Database Row Level Security (RLS) - which ensure that users only have access to the data they are entitled to see. The kinton_reporting schema also defines a set of views, which provide an RLS-controlled interface to the data stored in the underlying kinton and kinton_accounting' database schemas.

Row level security (RLS)

RLS ensures that third parties (such as reporting systems) can only retrieve data appropriate for the user requesting it - for example that an Abiquo Enterprise User can only see data for their Enterprise, even though the underlying table contains data for all Enterprises. In the case of the 'virtualmachine' table it contains details of the VMs for all enterprises, but an enterprise administrator should only be allowed to see the rows in the table belonging to their own VMs - this 'row level filtering' is the job of RLS.

Internally, RLS relies on the database user calling the InitialiseRLS() stored procedure, supplying the name of the Abiquo user for which data should be prepared. This procedure identifies the Enterprises, VDCs, and Datacenters which the user is entitled to know, and stores them in the RLS tables. Each stored entry also identifies the DBMS connection making the RLS call, so that the RLS tables can concurrently store different RLS configurations for multiple users. After that, a number of RLS views are defined, which are used to join the RLS tables to the underlying 'kinton' schema tables, filtering rows accordingly.

The RLS views rely on the use of 'SQL Definer rights', which means that users are given access to the tables used by the view as if they are the user who defined the view, rather than the user referencing the view. This means that it is very important that the 'kinton_reporting' schema is created in the same method as the main application schemas. Typically this will mean importing the 'kinton_reporting.sql' script directly onto the machine hosting the Abiquo DBMS, when logged in as 'root'.

Finally, the CleanupRLS() procedure is used to remove unneeded data for the database connection from the RLS tables.

If a connection attempts to use any of the RLS views without first calling InitialiseRLS(), the views will return no data.

Benefits of the architecture

Security

There are two key security benefits of the architecture:

• The database user accounts used by reporting systems can be restricted to require just EXECUTE privileges to only the kinton_reports and custom_reports schemas. No access needs to be granted to the other Abiquo database schemas, even if the reports themselves reference their tables.

• Row level security (RLS) provides application-level security at the database layer 

Well-defined public interface to the DBMS

kinton_reporting provides a view based interface for accessing Abiquo data for reporting purposes. It is designed to provide a consistent view of the underlying Abiquo DB tables, even when these tables are altered. When the underlying DB tables change, the implementation of the RLS views also changes, but the view output columns do not, thus minimizing any changes required to any reports which use the RLS views.

Simplified report creation

By abstracting the DB logic into stored procedures, and further abstraction of the RLS logic into the 'kinton_reporting' schema, reports now benefit from:

• Separation of the business and presentation logic for the reports

• Abstraction of the RLS implementation away from the report writer, allowing report SQL to contain only the business logic needed to retrieve data.

• RLS is now implemented in a single location in the 'kinton_reports' schema (rather than in each report), making the RLS logic easier to maintain and extend as required.

• Simplifying the report SQL, through the use of 'Helper' and 'Summary' database Views, which provide access to report level information without needing to understand the complexities of the 'kinton' and 'kinton_accounting' schemas. Removing the RLS logic also simplifies the report SQL.

How to create a report with JasperReports server

This section describes how to create reports that query Abiquo data using the JasperReports Server application. It explains how to create and call your own custom report stored procedures from your own custom reports.

Note that this section assumes that the user is familiar with creating reports for JasperReports Server - this section does not attempt to describe how to create report presentation layers (a good starting point for such information is this: http://jasperforge.org/projects/ireport).

How JasperReports server is configured to use Abiquo RLS

When creating a JasperReports Server reports, we have seen that the report logic is encapsulated in a report stored procedure, and that the report procedure must be supplied the name of the Abiquo user the report is intended for. Therefore, a key part of the report definition in the report application is to identify the Abiquo user, and then supply it to the report stored procedure.

Identifying the Abiquo user

Since Abiquo supply an authentication plugin to allow Jaspersoft Server to perform its user authentication against an Abiquo Enterprise server, identification of the Abiquo user running the report is straightforward - because the JasperReports Server user name will be the Abiquo user who logged in to the JasperReports Server.

Specifying report procedure parameters

JasperReports Server provides access to details of the user who is running the report, and we can use that user information to create a report parameter containing the name of the Abiquo user - we then simply use that parameter during the call to the report stored procedure.

Note that all parameters required by a report stored procedure should have an equivalent JasperReports Parameter defined for it - this applies to all report procedure parameters including the name of the Abiquo user used to initialise RLS.

Step 1 - Create a custom reports schema

When creating your very first custom report, ensure that you create it in your own 'custom_reports' schema, and not in the 'kinton_reports' schema. By placing them in your own schema you are ensuring that they will not be removed by future upgrades of Abiquo software.

_Note that 'custom_reports' is just a recommended name for your schema and you can choose a more suitable name yourself. _

Step 2 - Copy the report template stored procedure

The 'kinton_reports' schema (which contains all of the procedures for the standard Abiquo reports) also contains a template report procedure called 'RPT_Template_Level_v1_0'. It is recommended that you make a copy of this stored procedure in your custom reports schema, and use it as the starting point for your own custom report procedures, since it contains the necessary parameters and RLS initialisation and cleanup to get you started.

When creating your own report procedure, it is simply a question of adding a query in the 'TODO' section of the report template.
Please refer to the 'kinton_reports.RPT_EnterpriseLimits_VDC_v1_0' for an example of a simple report query.

Additionally, if your report needs to supply parameters to the stored procedure (for example to specify drilldown details) then these should be added to the procedure parameters.
Once your reports stored procedure is complete, it is recommended that you manually test it directly in MySQL using CALL statements, and review the result set to ensure it contains the correct data that you need.

Step 3 - Create a separate JasperReports server report folder

The standard Abiquo reports are all contained within a 'reports' folder on the JasperReports Server. It is strongly recommended that you create and store your own reports in a separate folder (e.g. 'custom_reports'), as the 'reports' folder (and all of its content) may be deleted when the standard reports are upgraded in future Abiquo versions.

Step 4 - Create your report with iReport (or Eclipse)

When defining the database SQL to identify the report's result set, instead of supplying a SELECT query to obtain the report data, you call your report stored procedure instead. This can be done by supplying a CALL SQL statement instead of a SELECT statement, e.g.

Note that when you first create your report, you will have to manually supply the procedure parameters - the parameters are automated in the next steps.

Step 5 - Prepare RLS details within your report

When you have created your report, you will need to ensure that the report data returned is in the context of the Abiquo user it is being run by.

This can be done using the following steps:
#Create a Jaspersoft report parameter called 'LoggedInUser'. Ensure that:

1.  

   1. 'Parameter Class' of the variable is set to: 'com.jaspersoft.jasperserver.api.metadata.user.domain.User'

   2. 'Use as a prompt' is FALSE.

   3. 'DefaultValue Expression' is empty.

2. Create a Jaspersoft report parameter called 'LoggedInUserName'. Ensure that:

   1. 'Parameter Class' of the variable is set to: 'java.lang.String'

   2. 'Use as a prompt' is TRUE.

   3. 'DefaultValue Expression' is '$P{LoggedInUser}.getUsername()'.

Step 6 - Using RLS in the call your report stored procedure

The final mandatory step is to adjust the report's call to your report stored procedure. This is done by replacing the hard coded parameters from step 4 with appropriate parameters from your report. At the very least you need to supply the 'LoggedInUserName' from step 5, e.g.

This will ensure that your report always initialises RLS for the appropriate Abiquo user.

Step 6 (optional) - JasperReports server input control support

If your JasperReports Server report needs to run a query to obtain data for a report Input Control, you can create stored procedures for each Input Control and call them from the control's SQL, using the same technique as the reports use to call the report stored procedures. Note that the Input Control stored procedures can also use the '$P{LoggedInUserName}' report parameter to initialise RLS.

Reference Input Control procedures can be found in the 'kinton_reports' schema, the procedure names all begin with a 'LST' prefix (e.g. 'LST_Enterprises_v1_0')._

Reporting schema views summary

The reporting schema has the following views.

Core RLS views

RLS_ENTERPRISE_V1_0_VW
RLS_VDC_V1_0_VW
RLS_DATACENTER_V1_0_VW
RLS_ENTERPRISE_LIMITS_BY_DATACENTER_V1_0_VW
RLS_USER_V1_0_VW
RLS_VAPP_V1_0_VW 

VM-centric RLS views

RLS_VIRTUAL_MACHINE_V1_0_VW
RLS_SUM_VM_IN_USE_BY_VDC_V1_0_VW
RLS_SUM_VM_RESOURCES_BY_STATE_BY_VDC_V1_0_VW
RLS_SUM_VM_DETAILS_V1_0_VW

Network-centric RLS views

RLS_NETWORK_USED_BY_VDC_V1_0_VW
RLS_NETWORK_USED_BY_ENTERPRISE_V1_0_VW

Storage-centric RLS views

RLS_STORAGE_USED_BY_VM_V1_0_VW
RLS_STORAGE_USED_BY_VDC_V1_0_VW
RLS_STORAGE_USED_BY_ENTERPRISE_V1_0_VW
RLS_STORAGE_TIER_USED_BY_VM_V1_0_VW
RLS_STORAGE_TIER_USED_BY_VDC_V1_0_VW
RLS_STORAGE_TIER_USED_BY_ENTERPRISE_V1_0_VW

Repository-centric RLS views

RLS_REPOSITORY_USED_BY_ENTERPRISE_V1_0_VW
RLS_VIRTUAL_IMAGE_V1_0_VW
RLS_VIRTUAL_IMAGE_USE_BY_ENTERPRISE_V1_0_VW

Pricing-centric RLS views

RLS_PRICING_TEMPLATE_V1_0_VW
RLS_PRICING_TIERS_V1_0_VW
RLS_PRICING_COST_CODES_V1_0_VW
RLS_PRICING_PER_HOUR_V1_0_VW

Event-centric RLS views

RLS_EVENTS_V1_0_VW
RLS_ADD_REMOVE_EVENTS_V1_0_VW
RLS_ADD_REMOVE_EVENTS_V2_0_VW 

Reporting schema reference

This section provides reference material regarding the 'kinton_reporting' schema row-level security (RLS) procedures and tables. It is intended to be a reference guide for customers who are writing their own reports using Abiquo data.

Core RLS stored procedures

InitialiseRLS

Used to initialise DBMS-based RLS. MUST be run before referencing any of the RLS views, otherwise the views will be empty or may contain 'out of date' information.
This stored procedure should be called as the first operation in any report stored procedure.
Note: The supplied 'rls_abiquo_user' is the abiquo user name, not a DBMS user name.
e.g.

CleanupRLS()

This procedure should be a corresponding call to InitialiseRLS(), to be made once all queries have been run. It prevents the underlying RLS tables from gathering orphaned data.
e.g.

TruncateRLSTables

This SP is provided to remove any orphaned RLS data form the RLS tables, however please note it it removes ALL data from the RLS tables, and should be called no more than periodically, to clean up any orphaned data. Additionally, this procedure should only be called when no RLS users are active on the system.
e.g.

Core RLS views

These views provide the low-level 'core' RLS filtering, and are often used by the views in the following sections to perform RLS filtering.

RLS_ENTERPRISE_V1_0_VW

This view provides a filtered version of the kinton.enterprise table. Currently, a user can only see the Enterprise to which they belong, unless they have the 'ENTERPRISE_ENUMERATE' privilege, in which case they can see all Enterprises.

RLS_VDC_V1_0_VW

This view provides a filtered version of the kinton.virtualdatacenter table. Users can only see the virtual datacenters which they have been granted access, unless they have the 'USERS_PROHIBIT_VDC_RESTRICTION' privilege, in which case they can see all virtual datacenters in each Enterprise they are entitled to see.

RLS_DATACENTER_V1_0_VW

This view provides a filtered version of the kinton.datacenter table. Currently a user can see no datacenter unless they have the 'PHYS_DC_RETRIEVE_DETAILS' privilege, in which case they can see all datacenters.

RLS_ENTERPRISE_LIMITS_BY_DATACENTER_V1_0_VW

Not really a 'core' view, this provides filtered access to the kinton.enterprise_limits_by_datacenter table. Rows are filtered according to the datacenters and the enterprises the user can access.

RLS_USER_V1_0_VW

This view provides a filtered version of the kinton.user table. The users returned are restricted to those that are associated with the Enterprises that the user can access.

RLS_VAPP_V1_0_VW

This view provides a filtered version of the kinton.virtualapp table. The virtualapps returned are restricted to those that are associated with the Enterprises and VDCs that the user can access.

VM-centric RLS views

This group of views return data centered around virtual machines, and their resources.
Unless stated otherwise, each view applies RLS by VDC restriction.

RLS_VIRTUAL_MACHINE_V1_0_VW

This view returns a list of all VMs (and their detailed attributes, including VDC and VApp membership).

RLS_SUM_VM_IN_USE_BY_VDC_V1_0_VW

A view that returns a sum of all hypervisor-deployed VM resource usage (CPU, Memory, Local Storage), totalled by VDC.

RLS_SUM_VM_RESOURCES_BY_STATE_BY_VDC_V1_0_VW

View that returns a breakdown of VM resources by their state, per VDC.

RLS_SUM_VM_DETAILS_V1_0_VW

View that returns physical & logical VM details, plus numerous other VM details (but not including network or storage tier VM usage). One row per VM.
RLS is by ENTERPRISE, VDC and DATACENTER restriction.

Network-centric RLS views

This group of views return data about VLAN usage (Internal, External, Public networks) and Public IP addresses.

RLS_NETWORK_USED_BY_VDC_V1_0_VW

View that returns a sum of all network resource usage (Total VLANs,Internal,External,Public), totalled by VDC.
RLS is applied by VDC restriction.

RLS_NETWORK_USED_BY_ENTERPRISE_V1_0_VW

View that returns a sum of all network resource usage (Total VLANs, Internal,External,Public), totalled by Enterprise.
RLS is applied by Enterprise restriction.

Storage-centric RLS views

This group of views returns information about the use of external storage. There are two types of views, those which return information groups by storage tier, and those which return information independent of storage tier.

RLS_STORAGE_USED_BY_VM_V1_0_VW

View that returns details of external storage usage, summed at VM level.
Note: This view is independent of storage tiers - please use RLS_STORAGE_TIER_USED_BY_VM_V1_0_VW for a tier-based breakdown by VDC
RLS is applied by VDC restriction.

RLS_STORAGE_USED_BY_VDC_V1_0_VW

View that returns details of external storage usage, summed at VDC level. Note: This view is independent of storage tiers - please use RLS_STORAGE_TIER_USED_BY_VDC_V1_0_VW
for a tier-based breakdown by VDC.
RLS is applied by VDC restriction.

RLS_STORAGE_USED_BY_ENTERPRISE_V1_0_VW

View that returns details of external storage usage, summed at ENTERPRISE level. Note: This view is independent of storage tiers - please use RLS_STORAGE_TIER_USED_BY_ENTERPRISE_V1_0_VW for a tier-based breakdown by Enterprise.
RLS is applied by ENTERPRISE restriction.

RLS_STORAGE_TIER_USED_BY_VM_V1_0_VW

View that returns the details of external storage usage by storage tier, summed at VM level.
RLS is applied by VDC restriction.

RLS_STORAGE_TIER_USED_BY_VDC_V1_0_VW

View that returns details of external storage usage by storage tier, summed at VDC level.
RLS is applied by VDC restriction.

RLS_STORAGE_TIER_USED_BY_ENTERPRISE_V1_0_VW

View that returns details of external storage usage by storage tier, summed at ENTERPRISE level.
RLS is applied by Enterprise restriction.

Repository-centric RLS views

This group of views return information about enterprise usage of repository space within Appliance Libraries.
Note that repository usage includes Virtual Machine Templates and VM Instances; it DOES NOT include 'Persistent Images' which are stored outside of the repository using external storage.

RLS_REPOSITORY_USED_BY_ENTERPRISE_V1_0_VW

View that returns details of repository usage, summed at Enterprise level.
RLS is applied by Enterprise restriction.

RLS_VIRTUAL_IMAGE_V1_0_VW

View that returns details of virtual images from the kinton.virtualimage table.
RLS is applied by Enterprise restriction.

RLS_VIRTUAL_IMAGE_USE_BY_ENTERPRISE_V1_0_VW

View that returns IDs of virtual images (and the Enterprises which own them) along with counts of the VMs using the images.
RLS is applied by Enterprise restriction.

Pricing-centric RLS Views

RLS_PRICING_TEMPLATE_V1_0_VW

View that returns pricing template and its associated currency details.
RLS is applied by Enterprise restriction.

RLS_PRICING_TIERS_V1_0_VW

View that returns pricing information for the storage tiers.
RLS is applied by Enterprise restriction.

RLS_PRICING_COST_CODES_V1_0_VW

View that returns pricing information for cost codes
RLS is applied by Enterprise restriction.

RLS_PRICING_PER_HOUR_V1_0_VW

View that returns pricing information 'per hour' (i.e. for a given Enterprise's pricing template, works out how one hour's resource usage costs for each resource).
RLS applied by ENTERPRISE restriction.

Event Centric RLS views

RLS_EVENTS_V1_0_VW

View that returns events that have occurred in the system from the kinton.metering table. The events returned are restricted to those associated with Enterprises that are accessible  to the user.

RLS_ADD_REMOVE_EVENTS_V1_0_VW

View that performs the very specific task of identifying from the metering table those events that specifically correspond to the creation and deletion of various components,
specifically it analyses the following components types:- USER,ENTERPRISE,VIRTUAL_MACHINE,VIRTUAL_APPLIANCE,VIRTUAL_DATACENTER.
The events returned are  restricted to those associated with Enterprises that are  accessible to the User.

RLS_ADD_REMOVE_EVENTS_V2_0_VW

Semantically identical to RLS_ADD_REMOVE_EVENTS_V1_0_VW , but has additional functionality that it is able to interpret the USER_MODIFY event when
a user moves form one enterprise to another and returns two rows one for a Remove_Event for the old Enterprise and one for an Add_Event for the new Enterprise. 
This functionality requires abiquo version 2.6.1 (and data created by that version) to operate which is the reason a separate version 2 exists.