|
Oracle App Server Tips
Mark Rittman |
|
Securing Data Warehouse with Advanced Security and OIDOnce 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;
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 privileges 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 privileges be shared across Oracle 9i and 9iAS when the two systems seem incompatible? 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 Kerberos and RADIUS authentication, Public Key Infrastructure 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.
Regards,
John Garmany
|
|
