Developer: Ruby on Rails
   DOWNLOAD
 Oracle Database XE
 Ruby OCI8 Adapter
 Ruby on Rails
 Ruby Gems
   TAGS
xe, rubyonrails, All

Connecting to Oracle from Ruby on Rails


by Obie Fernandez

Learn how to connect to Oracle from a Rails application and related authentication and performance issues.

Published June 2007

In the Java world, there are two common types of drivers for Oracle: the pure-Java (a.k.a. thin) driver and the native OCI driver. At the time of writing, no pure Ruby driver, similar to Java's JDBC-based thin driver, exists.

In order to connect to Oracle, you need to install the Ruby/Oracle Call Interface (OCI8) library, which is a database driver based on Ruby/DBI (Database Interface module). RubyDBI provides a database-independent interface for Ruby to talk to databases similar to JDBC or ODBC. The Ruby OCI8 driver provides connectivity to Oracle versions 8 through 10 via standard Oracle Client software. OCI8 is a Ruby wrapper written around native C code which does the actual interaction.

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:

ruby ruby-oci8-0.1.16-mswin32.rb 

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:

     
set oracle_sid=xe
ruby -r oci8 -e "OCI8.new('hr', 'password').exec('SELECT * FROM jobs ORDER BY 1') {|r| puts  r.join}"  
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. A variety of optimizations, including setting of options such as prefetch-rows are discussed at www.ruby-forum.com/topic/86831.


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.