The Abiquo reporting schema is the database interface for standard Abiquo reports and third-party database access to the Abiquo DBMS. This document describes:
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.
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.
There are two key security benefits of the architecture:
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.
By abstracting the DB logic into stored procedures, and further abstraction of the RLS logic into the 'kinton_reporting' schema, reports now benefit from: