Rick B. wrote:
This works, and the synonym person is only visible to user b
Our DBAs set up a development schema, a production schema, and N testing
schemas (1 for each developer, one for nightly demo builds, one for
continuous integration builds). I’m not a DBA so perhaps I’m missing a
salient detail, but after removing the troublesome synonyms we haven’t
had any problems in this configuration.
I’m not saying it doesn’t work when it’s all set up properly.
In my case, I needed to write a new Rails script using script/runner and
didn’t
want to have the DBA make changes for the app, as the less work he did,
the
better for me and getting Rails used.
For the new app, they set up a new user ‘svn2its’, which didn’t have
‘its’ as
its default schema and is also not the owner.
So anything in Rails to make this case easier to work with (no synonyms,
no
default schema, no ownership) would be great.
Maybe that’s asking for too much 
But using the
class Issue < ActiveRecord::Base
connection.execute(‘alter session set current_schema=its’)
end
trick did work. Although, this may not work if you have Rails working
with more
than one schema. In this case, I think the easiest way to do this is to
introduce an intermediate class and have the final model subclass it:
class OracleSchemaA < ActiveRecord::Base
establish_connection
connection.execute(‘alter session set current_schema=SCHEMAA’)
end
class OracleSchemaB < ActiveRecord::Base
establish_connection
connection.execute(‘alter session set current_schema=SCHEMAB’)
end
class Post < OracleSchemaA
set_table_name ‘post’
end
class Author < OracleSchemaB
set_table_name ‘author’
end
There are two classes that explicitly create separate connections to the
database, each with its own current_schema.
The set_table_names are required, otherwise AR seems to want to use
oracleschemaX as the table name.
Regards,
Blair