Rails + Oracle + oracle_enchanced adapter: db:create asks for SYSTEM password?

Question

I just updated an older post about Rails + Oracle DB and wondered why my rake db:create db:migrate asks me for the SYS/SYSTEM password of the Oracle DB. The Oracle user I have configured at config/database.yml has these permissions:

GRANT CREATE SESSION TO my-user;
GRANT ALTER SESSION TO my-user;
GRANT CREATE SEQUENCE TO my-user;
GRANT CREATE SYNONYM TO my-user;
GRANT CREATE VIEW TO my-user;
GRANT CREATE TABLE TO my-user;
GRANT GATHER_SYSTEM_STATISTICS TO my-user;
GRANT CREATE PROCEDURE TO my-user;
GRANT CREATE TRIGGER TO my-user;
GRANT CREATE DATABASE LINK TO my-user;
GRANT CREATE TYPE TO my-user;

This is what Rails is prompting me for:

Please provide the SYSTEM password for your Oracle installation (set ORACLE_SYSTEM_PASSWORD to avoid this prompt)

I do not understand why, as my-user should have all permissions needed for its own schema.

Any ideas?

Answer 1

It seems that requiring SYSTEM password for rake db:create is hardcoded in the gem.

def create
  system_password = ENV.fetch('ORACLE_SYSTEM_PASSWORD') {
    print "Please provide the SYSTEM password for your Oracle installation (set ORACLE_SYSTEM_PASSWORD to avoid this prompt)\n>"
    $stdin.gets.strip
  }
  establish_connection(@config.merge('username' => 'SYSTEM', 'password' => system_password))
  begin
    connection.execute "CREATE USER #{@config['username']} IDENTIFIED BY #{@config['password']}"
  rescue => e
    if e.message =~ /ORA-01920/ # user name conflicts with another user or role name
      connection.execute "ALTER USER #{@config['username']} IDENTIFIED BY #{@config['password']}"
    else
      raise e
    end
  end
  connection.execute "GRANT unlimited tablespace TO #{@config['username']}"
  connection.execute "GRANT create session TO #{@config['username']}"
  connection.execute "GRANT create table TO #{@config['username']}"
  connection.execute "GRANT create view TO #{@config['username']}"
  connection.execute "GRANT create sequence TO #{@config['username']}"
end

Source

As you can see, it also actually creates the user which you set in your database.yml and gives some privileges to it.

It's probably the best to skip rake db:create. You can always create database manually once in production environment, and in development/testing environment it's alright to give SYSTEM access to your Ruby code.