Replication and security

[Guest]

I'm not sure if this belongs here on in the security newsgroup, so please let
me know if this is the wrong place.

We have a replicated database where the master resides on my desktop and a
replica on the network is the "production" application/database. We now have
a need to implement user-level security. Here is what I "think" I want to do,
but I want to do a sanity check here.

1. Synchronize replica with master.
2. (After warning users) Temporarily remove replica from network to prevent
users from changing data.
3. "Unreplicate" the database.
4. Split database (create back-end and front-end).
5. Reimplement replication on back-end. Place replica on network in a
subdirectory of the original network location.
6. Run security wizard on back-end.
7. Synchronize back-ends.
8. Run security wizard on front-end.
9. Reattach front-end to replica on network.
10. Copy front-end to network to original location.

This is the "simplified" version. Actually I plan to kind of do this twice
so I can do a "trial" run to test that all functions work with the new
architecture. If everything worked (ha ha), I would keep the front-end from
the first run to minimize down-time for users.

So, folks, can some of you make suggestions to help bring some sanity to my
approach?

Thanks,
Maredith

 

[Joan Wild]

We have a replicated database where the master resides on my desktop
and a replica on the network is the "production"
application/database. We now have a need to implement user-level
security. Here is what I "think" I want to do, but I want to do a
sanity check here.

1. Synchronize replica with master.
2. (After warning users) Temporarily remove replica from network to
prevent users from changing data.

I would think you'd want to permanently remove this replica.

3. "Unreplicate" the database.

There's more to this than meets the eye. Be sure to peruse the
articles/utilities at www.trigeminal.com.

4. Split database (create back-end and front-end).

You should have done this before you ever replicated. Replication is only
meant for data (tables). You are perhaps lucky that you haven't had
problems prior to this.

5. Reimplement replication on back-end. Place replica on network in a
subdirectory of the original network location.
6. Run security wizard on back-end.
7. Synchronize back-ends.

I would split, secure the frontend/backend, and then replicate the backend.
The security will replicate to the replicas.

8. Run security wizard on front-end.

You'll want to be sure you use the same workgroup file to secure both the
frontend and backend. I would also advise you to study up on security -
there's more to it than 'running the wizard'.
Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

Although the whitepaper is old, it contains information to help you
understand security.

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm

9. Reattach front-end to replica on network.
10. Copy front-end to network to original location.

Each user should have a copy of the frontend on their PC. After reading
through this again, I'm wondering why you have replicated the database at
all? It isn't necessary if all users are on the same LAN. Just put the
backend on the server, and give each user a copy of the frontend on their PC
with links to the backend tables.

 

[David W. Fenton]

I'm not sure if this belongs here on in the security newsgroup, so
please let me know if this is the wrong place.

We have a replicated database where the master resides on my
desktop and a replica on the network is the "production"
application/database. We now have a need to implement user-level
security. Here is what I "think" I want to do, but I want to do a
sanity check here.

1. Synchronize replica with master.
2. (After warning users) Temporarily remove replica from network
to prevent users from changing data.
3. "Unreplicate" the database.
4. Split database (create back-end and front-end).

I'd do the security here, *before* replicating.

5. Reimplement replication on back-end. Place replica on network
in a subdirectory of the original network location.
6. Run security wizard on back-end.

I'd reverse 5 & 6.

7. Synchronize back-ends.

That would be "create new replicas" as the old replicas are no
longer part of the new replica set.

8. Run security wizard on front-end.

This can be done any time, as it is not replicated.

9. Reattach front-end to replica on network.
10. Copy front-end to network to original location.

???

You don't have a copy of the front end for each user? You should.

This is the "simplified" version. Actually I plan to kind of do
this twice so I can do a "trial" run to test that all functions
work with the new architecture. If everything worked (ha ha), I
would keep the front-end from the first run to minimize down-time
for users.

You're also going to need to put the system.mdw on the server and
distribute a shortcut to the end users that will use that to log on
with.

One easy way to do that is to make the Jet security user logon be
the same as the Windows logon and assign no password. Then the
shortcut can use an environment variable, %USERNAME%, as the /user
argument for the shortcut.

 

[Guest]

:

.... stuff deleted ...

I'd do the security here, *before* replicating.


I'd reverse 5 & 6.

.... thanks this is exactly what I was hoping for.

That would be "create new replicas" as the old replicas are no
longer part of the new replica set. Yes, that is what I meant.


This can be done any time, as it is not replicated.


???

You don't have a copy of the front end for each user? You should.

I haven't gotten that much into how you upgrade "versions". By that I mean I
won't always be the user account manager. A non-developer may be adding user
accounts. As it is, nothing will prevent users from copying the front-end
from the network. How you ensure users are using the latest copy of the
front-end? Everything I've tried in the past is a little "clunky" (home-grown
and not without draw-backs). Have you seen any good papers on how to control
versions if it is up to the users to "upgrade"?

.... stuff deleted ...

You're also going to need to put the system.mdw on the server and
distribute a shortcut to the end users that will use that to log on
with.

Understood - thanks for the reminder.

One easy way to do that is to make the Jet security user logon be
the same as the Windows logon and assign no password. Then the
shortcut can use an environment variable, %USERNAME%, as the /user
argument for the shortcut.

WONDERFUL IDEA. Thanks for the tip. This is SO SO useful. I love it!!!

 

[Guest]

:

.... stuff deleted ...

I would think you'd want to permanently remove this replica.

You're right - absolutely. What I should have said is temporary down-time
for the application.

There's more to this than meets the eye. Be sure to peruse the
articles/utilities at www.trigeminal.com.

Thanks for the tip. I was actually planning on just a new database with
importing objects from the master after the "sync" but I'll check that out.
Thanks!

You should have done this before you ever replicated. Replication is only
meant for data (tables). You are perhaps lucky that you haven't had
problems prior to this.


I would split, secure the frontend/backend, and then replicate the backend.
The security will replicate to the replicas.

Thanks. This is the kind of "sanity check" I REALLY needed.

You'll want to be sure you use the same workgroup file to secure both the
frontend and backend. I would also advise you to study up on security -
there's more to it than 'running the wizard'. .... stuff deleted ...

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm

Thanks, I'll check it out. I have developed several Access applications from
"scratch" (over several years) where I designed with a front-end/back-end and
user-level security. But a newbie where replication is part of the mix.

Each user should have a copy of the frontend on their PC. After reading
through this again, I'm wondering why you have replicated the database at
all? It isn't necessary if all users are on the same LAN. Just put the
backend on the server, and give each user a copy of the frontend on their PC
with links to the backend tables.

Actually, I inherited the current architecture. Using "sync" to "deploy"
fixes has been fairly easy but I agree - the main advantages all relate to
the tables/data.

 

[Joan Wild]

Actually, I inherited the current architecture. Using "sync" to
"deploy" fixes has been fairly easy but I agree - the main advantages
all relate to the tables/data.

I think this is a poor reason to use replication. There are ways to
automate updating the frontend on the users' computers.

It could be simply copying a fresh FE from the server every time they log
in. Or use a version control system, to check if there is a new FE on the
server and copy it down.

Tony Toews has a utility you can use. See
http://www.granite.ab.ca/access/autofe.htm -
there's also links at the bottom of that page for other methods.

 

[Granny Spitz via AccessMonster.com]

WONDERFUL IDEA. Thanks for the tip. This is SO SO useful. I love it!!!

Careful, hon. Would you still love it if a hacker copied the shortcut to his
desktop and changed %USERNAME% to *your* login ID and signed into the secure
database as *you*?

 

[David W. Fenton]

:
[]

You don't have a copy of the front end for each user? You should.

I haven't gotten that much into how you upgrade "versions". By
that I mean I won't always be the user account manager. A
non-developer may be adding user accounts. As it is, nothing will
prevent users from copying the front-end from the network. How you
ensure users are using the latest copy of the front-end?
Everything I've tried in the past is a little "clunky" (home-grown
and not without draw-backs). Have you seen any good papers on how
to control versions if it is up to the users to "upgrade"?

Tony Toews Updater utility takes care of this, since the shortcut
they launch the app with checks for new versions and copies it to
their workstation if there's a new version.

http://www.granite.ab.ca/access/autofe.htm

There is just no way that a shared front end can remain stable over
time, no matter what version of Access you're using.

[]

WONDERFUL IDEA. Thanks for the tip. This is SO SO useful. I love
it!!!

It only works if you're OK with Jet security not having a password.
But since there has to be a valid Windows username for it to work,
your validation is handled by Windows.

 

[David W. Fenton]

Careful, hon. Would you still love it if a hacker copied the
shortcut to his desktop and changed %USERNAME% to *your* login ID
and signed into the secure database as *you*?

Who said it was a secure database?

You could also program your app's startup to verify that the user
logon and Windows logon were the same, and if not, shut them down.
That would mean that only someone who had a valid Windows logon that
was in the list of valid Access users could log on.

 

[Granny Spitz via AccessMonster.com]

Who said it was a secure database?

If the shortcut needs the username to open the database, User level security
has been applied to it.

You could also program your app's startup to verify that the user
logon and Windows logon were the same, and if not, shut them down.
That would mean that only someone who had a valid Windows logon that
was in the list of valid Access users could log on.

Very true. I was just pointing out something else to consider with this
wonderful idea.

 

[David W. Fenton]

If the shortcut needs the username to open the database, User
level security has been applied to it.

No, you don't need to apply any user-level security to the objects
in your database to require an Access logon. You just need to set
the Admin password for there to be a logon prompt, and you won't be
able to open the database until you've provided a valid
username/password pair. What I'm suggesting is using the logon name
to identify the user, and not applying a password. There is no need
for any security to be applied to any of the objects in the database
unless you want to.

Very true. I was just pointing out something else to consider
with this wonderful idea.

I use it all the time. You can do the same thing without a password
set and it would prompt for the password.

 

[Guest]

Hi Folks,

Thanks to all of you - you have been VERY gracious and helpful. Given me
much to think about. For now, the Access security is to enable "role-based"
views/processing. I believe the Windows-level security to be very good here
but you have given me good insight to bring to managers regarding purpose of
implementing Access security and where they want to go with it in the future.
That will drive whether to allow blank passwords.

FYI - I'm listening to all suggestions. It's just this is a government
environment. The hoops I would have to go through for permission to install
one of the suggested utilities on a government server I believe to be worse
than a "grow my own" version control so I will have to "bite the bullet" on
that one. (I have split the database. Thanks to Joan Wild for the pointer to
paper, I changed the architecture to be "version aware". But I'm not there
yet.)

Again - a BIG and WARM - THANKS MUCH. Great ideas and help.