Once the domain of a small group of knowledge workers within an organization, data warehouses are increasingly becoming a critical part of an overall I.T. infrastructure. Organizations have come to depend on the information held in data warehouses and data marts, and often the warehouse is the only source of information within an organization that provides a complete, 360 degree view of customers and partners . In the rush to build a data warehouse, however, one thing that is often neglected is putting in place a proper security system.
It's all too easy to leave data warehouse security to the last minute, and indeed many data warehouse architects take the approach that all information within the warehouse should be available without restriction; however, as Ralph Kimball points out in this DBMS Magazine article,
"In many ways the average data warehouse team still lives in a world of naive innocence. The team is so busy sourcing data and deciding on hardware and software that a comprehensive security plan simply hasn't been done ... In many cases, sensitive information is lying right on the table and hasn't been abused only because no one has tried to grab it yet. The situation is similar to leaving a car unlocked in a shopping center parking lot. You might go for years without having the car broken into just because the thieves have not turned their attention to it yet."
It's clear then, that whilst in the early stages of a warehousing project the user base is small and probably can be trusted, as the system is rolled out to a wider user base steps need to be taken to put in place a proper security regime that, on the one hand restricts users to the data they need to see, but on the other doesn't adversely impact on the performance of queries. Given this requirement, what can make this challenge interesting when working with the Oracle 9i technology stack is the amount of options open to implementers when putting a security regime together.
A typical data warehouse build on the Oracle 9i technology stack involves the Oracle 9i Server for the storage of relational and multidimensional data, and the Oracle 9iAS Application Server to provide end user access to the data in the warehouse. Security within the Oracle database is based around user accounts and roles, which are granted access to objects in the database such as tables, views, summaries and analytic workspaces. With Oracle 9iAS, security is based around a feature known as Single Sign-On, a technology where users can log in via a web interface to one Oracle 9iAS-hosted product (such as Oracle Portal), with access then being granted to other 9iAS applications (such as Forms, Discoverer and Reports). Additionally, it is usually a requirement in enterprise data warehouses to not only restrict users to SELECTing on a group of warehouse tables, but also to restrict the rows within the tables that they can view, for example to only return the products and customers that they are responsible for. The security challenge for a data warehouse based on the Oracle 9i and Oracle 9iAS platform could therefore be summed up as;
- Provide a seamless security system that can be applied across all the technologies, both the Oracle 9i database server and the Oracle 9iAS application server
- Administering user accounts and privileges has to be kept as simple as possible and with the minimum of duplicated work
- Any security that is applied has to have the minimum impact on query performance
- Ideally, any choice of methods and technologies has to be 'future proof' and in line with Oracle's published direction.
The good news is, however, that by using a few simple techniques and some of the new security features in 9iAS and 9i, security can be simple and straightforward to implement, in a way that is consistent across all of the products, is enforced at database level, and does not adversely affect query response time.
Looking initially at the application server, Oracle 9iAS uses a feature called 'Single Sign-On' to authenticate users, holding their accounts and access priviledges in Oracle Internet Directory, an LDAP server bundled with 9iAS. SSO allows users to enter their username and password once when logging on to any 9iAS application, with these credentials then being used to grant access to all the other 9iAS applications. Being based on LDAP, OID is the obvious choice for enterprise-wide user authentication as it scales well across thousands of users, allows integration with any other LDAP, and is the preferred technology within Oracle for storing rich information about users and access groups.
It's preferable then if our data warehouse can use OID as a repository of user accounts and roles, but an issue then arises when considering applying this to the 9i database server, which normally uses its own mechanism to store and authenticate users, holding account and role information in a number of data dictionary tables. How, then, can user details and priviledges be shared across Oracle 9i and 9iAS when the two systems seem incompatable?
The answer to this is Oracle 9i Advanced Security Release 2, an add-on option for Oracle 9i that, amongst other things, allows the Oracle 9i database to use OID to store user details and groups, rather than holding them in tables in the database. Advanced Security comes with number of features such as Kerebos and RADIUS authentication, Public Key Infrastruture Support, and options for advanced encryption, which will be implemented at a later date to add additional layers of security to communications outside of their intranet. For the time being, however, the Advanced Security option potentially allowed us to use the 9iAS Release 2 OID instance to store our user, role and group details.
The key to implementing Advanced Security is a feature known as Enterprise User Security (EUS), a set of server and client technologies that allows users to directly authenticate against the OID LDAP Server. By implementing Advanced Security and EUS, the 9iAS OID instance can be used to store database account and role information, in such a way that account information is only stored once, and in a central place. EUS allows us to create a single account for our users that authenticates them against the database, and provides single sign-on access to Portal, Discoverer and Reports. For programmatic access to the information held within the OID directory, a package called DBMS_LDAP is used, providing similar functionality to the DBA_ users and roles views.
Like all good things, though, there's often a catch and as you would expect with new technologies such as this, there's a few points to be aware of when combining 9i and 9iAS OID authentication. First, with the current 9i implementation of OID, it is still the case that whilst users have a single user account in the OID instance, at present they have two separate passwords (one for 9i, one for 9iAS) stored against the account, a shortfall that is being addressed in the forthcoming 10g release of OID. You also need to be careful when configuring EUS with 9iAS OID 9.0.2, making sure that you configure a new config set and start a new oidldapd server rather than changing the default config set that came with 9iAS.With this in mind, an often preferable solution is to actually install the OID server that comes with Oracle 9i Advanced Security (ensuring it is patched up to 9.2.0.4), set up synchronization (using OID DIP) with the 9iAS OID instance, and then maintain user information using the 9iAS OID tools. Whichever route is taken, however, we've achieved our aim of having a single store of user information, using a scalable future proof technology, that can be applied across the complete 9i technology stack.
Now that we've created framework for end-to-end user authentication, we need to consider how to apply security at the database level, to ensure that users get to see just the data that they're entitled to see. Applying security at the database level is safer than relying on application security, especially if users are also given direct access to the warehouse through tools such as SQL*Plus.
Access to subject areas in the data warehouse is usually secured first by the use of Oracle roles, which allow us to put together groups of SELECT access rights to fact and dimension tables within the warehouse and grant these to groups of users. Role-based security is excellent for controlling access to database objects, but often data warehouse implementers need to further restrict access at a row level, for example to restrict queries to a subset of products or customers depending on the department or branch the user belongs to. In the past, row-level security was usually implemented through making users access the warehouse tables through views, including a WHERE clause in the view to restrict the data returned to only certain rows; however, making users access data through views can impact performance, can confuse the cost-based optimizer and is cumbersome to administer. To address these shortcomings, from Oracle 8i and onwards a new database feature known as Virtual Private Databases can now be used instead.
Virtual Private Databases, explained in this excellent article by Don Burleson, are an Oracle Enterprise Edition feature that transparently adds predicates to user statements to limit down their access in a way that is transparent to the user, and the application. For example, if a user issues the statement
SELECT year, prod_category, sum(sales)
FROM sales_mv
WHERE year = '2002';
then the VPD feature might modify that query as follows;
SELECT year, prod_category, sum(sales)
FROM sales_fact
WHERE year = '2002'
AND prod_category in ('VIDEOS','RADIOS');
As this modification is carried out by the Oracle server directly against the users' query, and does not involve views, control tables, synonyms and the like, the VPD-modified query will fully utilize Oracle's query optimization features, such as materialized views, indexes, partitioning and parallelism. Using the DBMS_RLS package, Virtual Private Database policies can be created, dropped, enabled, disabled and refreshed and a framework can be set up to enabled fine-grained access control to the data warehouse tables. For further information about Virtual Private Databases, including details of the forthcoming enhancements due with Oracle 10g, take a look at this paper written by George Lumpkin over at the Oracleworld website.
In summary then, it's clear that by using the Single Sign-On feature within 9iAS, Oracle Advanced Security within Oracle 9i, and the Virtual Private Databases feature within Oracle 9i, a simple, seamless and complete data warehouse security architecture can be put together that makes life simple for users, and doesn't adversely affect query performance and response times. I'd be keen to hear from any other readers who've implemented Advanced Security or VPDs, and if I get any feedback, I'll make it available on the weblog.
11:35:26 PM 
|
|
