Abiquo 5.0

Skip to end of metadata
Go to start of metadata

Introduction

The Abiquo reporting schema is the database interface for standard Abiquo reports and third-party database access to the Abiquo DBMS. This document 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.


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.