Virtual Private Archibus (Oracle VPD server-side security)

Overview

Users or Oracle8i and later can take advantage of Archibus’s support for the Oracle Virtual Private Database Security features. The Oracle Virtual Private Database (VPD) provides server-enforced, very specific access control to data. Some advantages to server-side security over application security are:

  • Lower cost of ownership . Organizations can realize cost savings by building security once, on the server side, instead of implementing the same security in each application that accesses data. For example, if Archibus, your HRIS, and your Building Security System are accessing the same data, you can set one database security policy to enforce data access rules for all three applications.
  • Elimination of the "application security problem." Since the security is attached directly to the data, users cannot bypass security policies embedded in applications. Regardless of how a user accesses data (through an application such as Archibus, a report-writing tool or a query) the same security policy is automatically enforced.

This topic overviews the Oracle Virtual Private Database model and explains how to set up a simple security policy implementation with Oracle and Archibus. It is, however, beyond the scope of this documentation to provide all the details regarding Oracle Virtual Private Databases and their possible configurations. Consult your Oracle documentation or visit www.oracle.com for more detailed information.

You may also wish to see Application-Level Virtual Private Archibus.

Dynamically Modified Queries

The Oracle Virtual Private Database model relies on "dynamic query modification" to enforce security on the data objects with which security policies are associated. In this case, a query refers to any selection from a table or view, including insert or delete statements, or a sub-query; a query is not limited to statements that begin with SELECT.

When a user directly or indirectly attempts to access a table or view that has an associated security policy, the server dynamically modifies the associated SQL statement based on a WHERE condition returned by a function of the security policy. The modification occurs dynamically and is transparent to the user.

Secure Application Contexts

Most organizations make access control decisions based on the user (their role in the organization, their department assignment, their physical location). Application contexts provide an easy mechanism to define, set, and validate the security attributes on which to base access control. Application contexts and their attributes are user-definable. This means that each application, such as Archibus, can have its application-specific context, with different attributes. For example, you may wish to set access to your Archibus data based on the user’s location and position. In this case, "location" and "job_title" are both attributes of an application context.

Use Cases

There are many possible uses for the Oracle VPD in combination with Archibus security. For example, you may wish to make your facilities data available to users outside of the facilities department, such as sharing it with Finance and Human Resources personnel. Using Oracle VPD and Archibus, you can provide these personnel with access to very specific data in your Archibus database. You can allow Human Resources representatives to access just the fields in the Employees table that they need, eliminating the data you don’t want them to see. Or, you can restrict access for Finance personnel to just work order financial information, eliminating unnecessary details about a work order.

Large organizations with many geographically dispersed facilities and facilities personnel can create a "Virtual Private Archibus" to speed application and database server performance and increase usability by filtering unnecessary data based on the geographic location of the user. For example, using Oracle VPD and Archibus, you can establish users in Archibus based on their location, and establish attributes for each Archibus user type inside of Oracle. When a user logs in, the Oracle database server sets the application context and implements the security policy. The following example illustrates how to configure a Virtual Private Archibus database using the user’s geographic location.

Example

In this example you will establish two users in two regions of two different countries (the London region of the United Kingdom and the Pacific region of the United States). Each user will have access only to facilities data for properties located in those particular regions.

Notes: For your convenience, the example uses sample SQL scripts located in D:\Afmxx\Schema\Oracle. You will need to modify these scripts since they refer to generic server names and system manager accounts.

For this example (restricting by region code), you should restrict user privileges to the regn and ctry tables. Deleting and/or updating records from these tables would adversely affect the ability of the VPD to restrict record access.

In addition, to ensure that the user enters the appropriate regn and ctry codes, you may wish to add "not null" constraints for the appropriate fields in the appropriate tables. If records are entered with null values for region codes, the user can successfully enter the record in the database; however, the user will not have access to the new record since it will not meet the user’s restriction.

Prerequisites

  • You need to have DBA (database administrator privileges) to execute the procedure in this example.
  • Only certified Oracle DBAs should attempt to implement an Oracle Virtual Private Database.
Application-Side (Archibus) Configuration
  1. Open the sample HQ project.
  2. Add the region "London" to the Regions table with a country code of "United Kingdom."
  • On the Navigator, select R eal Property / Properties / Manage Property Abstracts / Background Data / Regions . Alternately you can browse to D:\Afmxx\Schema\Common\regn.avw.
  1. Add a "Region" and "Country" field to the Archibus Users table. These fields validate on the Country (CTRY) and Region (REGN) tables.
  • On the Domain Navigator, select System / Schema / Define/ Fields by Table and locate the record defining the AFM_USERS table.
  • Add ctry_id (char, 16)
  • Add regn_id (char, 16)
  1. In the Archibus_users table, create the Archibus user accounts based on their geographic location.
  • On the Navigator, select System / Security /Secure /Users.
  • Create a new user "Ian" and assign it to the United Kingdom-London region.
  • Create a new user "Cal" and assign it to the USA-Pacific region.
  1. Create the necessary sample data needed to test the Virtual Private Archibus configuration. To access these tables, use File/View/Open to open D:\Afmxx\Schema\Common\bl.avw, fl.avw, rm.avw, and em.avw.
  • Add several buildings under the United Kingdom-London region. Within these buildings, add several floors, rooms, and employees.
  • Add several buildings under the USA-Pacific region. Within these buildings, add several floors, rooms, and employees.

Server-Side (Oracle) Configuration

  1. Create a corresponding Oracle SQL User ID for each Archibus user created in the application-side procedure above.
  • Run the D:\Afmxx\Projects\HQ\Create_IAN.sql script to create the new user "Ian" and assign AFM_ROLE and PRIVATE SYNONYMS.
  • Run the D:\Afmxx\Projects\HQ\Create_CAL.sql script to create the new user "Cal" and assign AFM_ROLE and PRIVATE SYNONYMS.
  1. You need to create a package in Oracle that validates against the Archibus User ID attributes – in this case the Region Code and Country Code for each user (Oracle refers to this as the "application context"). After validating the application context, the package implements a policy function based on these attributes using a WHERE condition. For example, the package in this example must limit database access based on the region and country code for the User ID. In other words, "WHERE the User ID = Pacific/USA," the user can view the properties in the USA-Pacific region, but not those in United Kingdom-London region.
  • Run the D:\Afmxx\ Projects\HQ\Afm_Region_Pkg.sql script to create the Oracle package and package body to define the security requirements.
  1. Next, you need to associate the policy function with a table or view in Archibus. In other words, you need to tell the database server the tables or views to which to limit access based on the Region Code and Country Code for the User ID.
  • Run the D:\Afmxx\ Projects\HQ\\Afm_Region_Policy.sql script to associate the policy function to the necessary tables.

Testing the Virtual Private Archibus Configuration

  1. First, test database access from the side of the Oracle server. This ensures that users cannot access data outside of the Archibus application. Using a tool such as Oracle SQL Worksheet or SQL Plus, run the following tests:

Connect SYSTEM/MANAGER@YOUR_DATABASE_SERVER
SELECT site_id, name, regn_id, ctry_id FROM site;
SELECT bl_id, name, site_id, regn_id, ctry_id FROM bl;
SELECT bl_id, fl_id, name FROM fl;
SELECT bl_id, fl_id, rm_id FROM rm;
SELECT em_id, bl_id, fl_id, rm_id FROM em;

As system manager, you will have full access to all data, tables, and fields in the HQ database.

Connect CAL/CAL@YOUR_DATABASE_SERVER
SELECT site_id, name, regn_id, ctry_id FROM site;
SELECT bl_id, name, site_id, regn_id, ctry_id FROM bl;
SELECT bl_id, fl_id, name FROM fl;
SELECT bl_id, fl_id, rm_id FROM rm;
SELECT em_id, bl_id, fl_id, rm_id FROM em;

As CAL you will have access only to the data whose region equals Pacific-USA.

Connect IAN/IAN@YOUR_DATABASE_SERVER
SELECT site_id, name, regn_id, ctry_id FROM site;
SELECT bl_id, name, site_id, regn_id, ctry_id FROM bl;
SELECT bl_id, fl_id, name FROM fl;
SELECT bl_id, fl_id, rm_id FROM rm;
SELECT em_id, bl_id, fl_id, rm_id FROM em;

As User ID "Ian" you will  have access to only the data whose region equals United Kingdom-London.

  1. Next, test database access from the Archibus application-side. Make sure that you have enabled Archibus security for the HQ sample database and run the following tests.
  • To enable Archibus security, on the Domain Navigator, select System/Security/Enable/Set Security On.
  • Open the HQ database and log in as user "System." Open the Properties, Buildings, Floors, Rooms, and Employees tables. You will be able to view all tables, fields, and records in the database.
  • Open the HQ database and log in as user "Ian." Open the Properties, Buildings, Floors, Rooms, and Employees tables. You will be limited to only those records under the region United Kingdom-London.
  • Open the HQ database and log in as user "Cal." Open the Properties, Buildings, Floors, Rooms, and Employees tables. You will be limited to only those records under the region Pacific-USA.