Connecting to Oracle from Ruby on Rails

Non-windows developers can install by typing sudo gem install ruby-oci8. Windows developers should download the binary distribution of the driver from www.rubyforge.org/projects/ruby-oci8 and install it manually:

A simple test of your Ruby OCI8 driver is to query a database containing the demo (HR) schema, with the following command-line Ruby program. Substitute the name of your Oracle database and password in the following command:

If the connection and query succeed, the output should look something like this:

AC_ACCOUNT | Public Accountant | 4200 | 9000
 AC_MGR | Accounting Manager | 8200 | 16000
 AD_ASST | Administration Assistant | 3000 | 6000
 AD_PRES | President | 20000 | 40000
 AD_VP | Administration Vice President | 15000 | 30000
 FI_ACCOUNT | Accountant | 4200 | 9000
 FI_MGR | Finance Manager | 8200 | 16000
 HR_REP | Human Resources Representative | 4000 | 9000
 IT_PROG | Programmer | 4000 | 10000
 MK_MAN | Marketing Manager | 9000 | 15000
 MK_REP | Marketing Representative | 4000 | 9000
 PR_REP | Public Relations Representative | 4500 | 10500
 PU_CLERK | Purchasing Clerk | 2500 | 5500
 PU_MAN | Purchasing Manager | 8000 | 15000
 SA_MAN | Sales Manager | 10000 | 20000
 SA_REP | Sales Representative | 6000 | 12000
 SH_CLERK | Shipping Clerk | 2500 | 5500
 ST_CLERK | Stock Clerk | 2000 | 5000
 ST_MAN | Stock Manager | 5500 | 8500

Rails Configuration

The parameters used by Rails to connect to a database are stored in config/database.yml within your Rails application directory. The following example references the host xe, which corresponds to an entry in tnsnames.ora. Three different schemas are used, as specified by the differing usernames.

development:
 adapter: oci
 host: 
 
 xe
 username: 
 
 development
 password: 
 
 password
 test:
 adapter: oci
 host: 
 
 xe
 username: 
 
 test
 password: 
 
 password
 production:
 adapter: oci
 host: 
 
 xe
 username: 
 
 production
 password: 
 
 password

It is also possible to use Oracle Easy Connect Naming with the Ruby OCI8 driver. Replace the SID in the host field with an Oracle Easy connect string as shown below:

development:
 adapter: oci
 host: 
 
 //server:port/instance_name
 username: 
 
 development
 password: 
 
 password

Authentication

As with most three-tier application architectures, Rails assumes that one set of credentials is used (as specified in database.yml) to authenticate to Oracle. The credentials specified must have enough rights to perform whatever actions will be done via the Rails application.

If due to security concern you don't want to include credentials as plain text in a configuration file, you can take advantage of the fact that database.yml allows dynamic content via ERb tags. The syntax is identical to how values are inserted into Rails view templates using <%= %> tags.

Here is an example which picks up its username and password from environment variables set at startup:

production:
 adapter: oci
 host: 
 
 xe
 username: <%= ENV['ORACLE_USERNAME'] %>
password: <%= ENV['ORACLE_PASSWORD'] %>

Pass-Through Authentication

There is currently no standard way of accomplishing so-called pass-through authentication to Oracle from a Rails application, which refers to configurations where an end-user authenticates himself by using his/her Oracle credentials after which use of the application is subject to Oracle-defined access rights and restrictions. Custom solutions involving pass-through authentication with Rails are theoretically possible but are subject to all sorts of practicality and performance concerns.

For instance, it is theoretically possible to always create a new connection to the database using the logged-in user's credentials. It would be accomplished by establishing a connection (using the user's credentials) in a controller's before_filter method. Teardown of the connection would need to be manually implemented in an after_filter as well. Among other problems, the approach would be very slow since a connection must be established for each request.

Couldn't the Oracle connection simply be cached in a user-session? Unfortunately not; Rails applications do not have in-memory session storage that stays alive between requests in the same way that say a JEE application might have. All session content is subject to serialization and storage in the file system, the database, or a distributed memory cache.

Assuming that the challenges of authenticating specific users on top of existing connections was found, the benefits of pass-through authentication in conjunction with ActiveRecord might provide some value.

For instance, row-level security for a user specified at the database level would transparently dictate the objects visible to the user, without further implementation code needed. Furthermore, ActiveRecord's dynamic generation of attributes could prove to be a huge plus, since it would have the effect of transparently reflecting column level security into the object model. Since attributes are read dynamically, the model instance would only contain attributes matching columns visible to the logged in user. However, keep in mind that attribute information is cached in production mode for performance reasons, yet another optimization that makes pass-through authentication a performance problem.

For security at the table level, implemented using grants, you can assume that Oracle will generate exceptions from the driver when SQL execution fails due to permission issues. You might catch those and handle them appropriately, but the problem with that approach is not knowing if a given action is valid when an attempt is made. If, for example, you want to disable a "create" button based on the user's insert privileges on a table, you cannot know his privileges until the insert is performed.

A potential workaround is to load all grants and privileges information for that user into the session once the connection is established for the first time. The grant and privileges information could then be used to provide security features in the view (hiding/disabling buttons based on permissions) or adding security at the model (using callbacks like before_save, before_update to perform the permission check before doing the operation). Of course, this is all theoretical and the difficulties involved in reading privileged information are unknown, especially if implemented via stored procedures.

Using More Than One Database

Setting up a Rails application to talk to more than one database is simple. Just specify the extra database connection parameters in the database.yml file. For example, suppose you needed to access a different Oracle database for inventory data:

inventory:
 adapter: oci
 host: 
 
 xe
 username: 
 
 inventory
 password: 
 
 password

Then define a Model (called Inventory) used to access inventory data:

class Forum < ActiveRecord::Base
self.connection = "inventory"
end

Rails applications are deployed as pools of independent server processes, and one database connection is maintained and reused per process for every rails request. Connections are usually created through ActiveRecord::Base.establish_connection and retrieved by ActiveRecord::Base.connection. All classes inheriting from ActiveRecord::Base will use this connection. But you can also set a class-specific connection for specific Models (as shown in the previous section) after which it and all its subclasses will use the explicitly-specified connection instead.

ActiveRecord keeps a connection pool in ActiveRecord::Base as a Hash indexed by the name of the model class. When a connection is requested, the retrieve_connection method will go up the class-hierarchy until a connection is found in the connection pool.

Optimizations

Rails doesn't currently support the binding of variables in SQL queries with Oracle, which impacts performance negatively. The current version of Rails (1.2) sets Oracle connections option cursor_sharing to similar, which results in significant performance improvements, by automatically turning all literals into bind variables at the database level. It also sets the prefetch rows setting to 100.

Neither the cursor sharing or prefetch rows settings are ideal solutions. Both have the potential of putting additional load on the server, but most developers are reporting substantial improvements in the performance of their Oracle-connected Rails applications, which leads us to believe they are sufficient for most cases. Your mileage may vary.

If you need additional performance optimizations, the Ruby OCI driver supports tuning of the database connection using a variety of other attributes, except that at the moment, Rails does not provide a standard way to set them.

Obie Fernandez is a Senior Consultant Technologist for ThoughtWorks; he specializes in the design and implementation of Web-based applications and has extensive enterprise experience integrating large-scale, distributed applications and legacy systems. Obie has been focusing lately on pioneering enterprise-level adoption of Ruby-based technology such as Ruby on Rails.