Changing the user of objects in a replica

[Tom Stoddard]

I have a replicated database (back end with tables only) which was created
with no security enabled so "admin" own all the objects in the database. I
now come to learn that allowing "admin" to own everything is not desirable
if I want to be able to secure my data.

What's the best way to change the ownership of the tables in my replicas? I
assume that I would make changes to the design master and those changes
would propagate throughout the replica set.

Anyone have any tips or suggestions for me to consider before implementing
security on an application which is already in use with several remote users
and several users on the LAN? My application is split so I need to enable
security on all front end mdbs and back end mdbs.

 

[TC]

Replication is a complex topic. Replication + security is even worse! I
strongly suggest that you ask this question in a replication newsgroup,
before you proceed.

HTH,
TC

 

[Tom Stoddard]

Replication is a complex topic. Replication + security is even worse! I
strongly suggest that you ask this question in a replication newsgroup,
before you proceed.

I asked the question and learned the answer so I'll share it with this group
because it is relevant.

What I learned is that you can't change the ownership of a replicated
database. In order to secure a back end data file which has already been
replicated while in a default security setup (owned by admin) you would need
to create a completely new replica set. Depending on your application, this
can be a major undertaking. The moral of the story is, set up at least
minimal security on your databases and make sure they are owned by a user
other than "admin" before considering replication.

The reason this is so is that once you've created a database you cannot
change the ownership of the database object itself. The workaround for this
is to create a new database owned by whomever you want to transfer ownership
to and then import all objects from the existing database into the new one.
This does not work for replicated databases because the new database would
not be a member of the existing replica set.

I hope this explanation helps to save someone else some of the work that I
now need to do. My conconclusion is that you should learn about security
before you learn about replication. I did it the other way around and am now
paying the price.

 

[TC]

once you've created a database you cannot change the
ownership of the database object itself.

Not through the Access user interface. But this would probably do it
from code, if you were logged on as a suitably priviliged user:

dbengine(0)(0).containers![databases].documents![msysdb].owner =
"Fred"

Not suggesting you should actually do that, though!

Cheers,
TC