Read-only access to the database


#1

Is there to set up the ActiveRecord connection to the database to that
you only have read-only access to the database?

For example, you are accessing a legacy database to create reports, you
are not too sure what you are doing and what to make sure that the Rails
application can not (accidentally) overwrite any data?

Thanks for any ideas


#2

wfisk wrote:

Is there to set up the ActiveRecord connection to the database to that
you only have read-only access to the database?

For example, you are accessing a legacy database to create reports, you
are not too sure what you are doing and what to make sure that the Rails
application can not (accidentally) overwrite any data?

Thanks for any ideas

Set up user security in the database so that only SELECT statements are
allowed.

See http://dev.mysql.com/doc/refman/5.0/en/grant.html

See also

http://www.ruby-forum.com/topic/83697#149313


#3

Its an SQL Server database. I guess I could create a user with readonly
access and then connect as that user. Yes good idea.


#4

database to that you only have read-only access to the database?

For example, you are accessing a legacy database to create
reports, you are not too sure what you are doing and what to
make sure that the Rails application can not (accidentally)
overwrite any data?

Here’s what I’ve do:

class Foo < ActiveRecord::Base
def write_attribute(name, value)
raise NotImplementedError, ‘read only table’
end
end

This approach won’t even allow you to assign values to a Foo instance.
If you want to defer until the moment a user tries to save a record,
then redefine ‘save’ and ‘save!’ instead.

Regards,

Dan

This communication is the property of Qwest and may contain confidential
or
privileged information. Unauthorized use of this communication is
strictly
prohibited and may be unlawful. If you have received this communication
in error, please immediately notify the sender by reply e-mail and
destroy
all copies of the communication and any attachments.


#5

“This approach won’t even allow you to assign values to a Foo instance.
If you want to defer until the moment a user tries to save a record,
then redefine ‘save’ and ‘save!’ instead.”

I thought about something like this, too, but I have a database of over
a million records, so I didn’t even want to take a chance. Not granting
any abilities beyond SELECT guarantees that no possible error in your
coding, not even the briefest lapse, will impact the DB because the DB
won’t let it happen.


#6

Dan,

Thanks for that - I am going to do that too!

I must admit I thought that ‘readonly’ might have been a parameter of
the database connection and I looked for that, but apparently not.

I like the idea of redefining ‘write_attribute’ and save, and I think
that it is worth doing, because you will catch some cases where a write
has been attempted
but you still cannot be sure that a write (or a restructure?) will not
happen some other way.

The best route still seems to be to define a user that only has read
access to the
database.

Thanks again for the ideas.

William


#7

Add this to your model

class User < ActiveRecord::Base

Ensure that this record can not be saved or modified in any way

If save is called, this will throw an exception.

def readonly?() true end

end

That will prevent anything from saving. I use that a lot. :slight_smile:


#8

Thanks for that - I am going to do that too!

I must admit I thought that ‘readonly’ might have been a parameter of
the database connection and I looked for that, but apparently not.

I know some vendors (such as Oracle) support a restricted mode, but it
requires connecting to the database first, then immediately issuing an
“alter session” command. How you would implement that in Rails I’m not
sure. Perhaps some sort of “post_connect” method?

database.
Oh, definitely. This was just the way to do it through Rails, not at the
DB layer. And, like you said, you can always write it in such a way as
to track anyone who attempts to make a write attempt (presumably through
a backend interface, such as xml-rpc).

Thanks again for the ideas.

You’re welcome.

Regards,

Dan

This communication is the property of Qwest and may contain confidential
or
privileged information. Unauthorized use of this communication is
strictly
prohibited and may be unlawful. If you have received this communication
in error, please immediately notify the sender by reply e-mail and
destroy
all copies of the communication and any attachments.