Programmatically change a database to use an existing mdw file

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I have a VB app that uses an Access dB. It has already been shipping
and hundreds of customers have it. Now, I wish to secure it. On my
development machine, I've already tinkered with setting user level
security to where I want it and made a new mdw file. Now, I am faced
with the challenge of updating my customers' existing databases to use
this new mdw file.

I'm thinking I need to do the following:
Write an "update" program that will ...
1) Copy the new mdw file to my app's folder on the customer's machine
2) Use GRANT, DENY, and REVOKE commands on the existing dB on the
customer's machine (to set permissions equivalent to what I've already
done on my development computer)
3) Change my app to use the new mdw file when opening the connection.

Am I on the right trach here? Or is there a simpler way?
 
Tim said:
I have a VB app that uses an Access dB. It has already been shipping
and hundreds of customers have it. Now, I wish to secure it. On my
development machine, I've already tinkered with setting user level
security to where I want it and made a new mdw file. Now, I am faced
with the challenge of updating my customers' existing databases to use
this new mdw file.

I'm thinking I need to do the following:
Write an "update" program that will ...
1) Copy the new mdw file to my app's folder on the customer's machine
2) Use GRANT, DENY, and REVOKE commands on the existing dB on the
customer's machine (to set permissions equivalent to what I've already
done on my development computer)
3) Change my app to use the new mdw file when opening the connection.

Am I on the right trach here? Or is there a simpler way?
Click to expand...

Unless you can programmatically change the owner of the database the default
user "Admin" will still have administer permissions to the database object. You
might be better including a properly secured NEW mdb and importing all of the
data from the old one.
 
Not to be rude, but, if you have only "tinkered with" workgroup based
security, you have no chance of getting it right. It is just too
complicated & hard to learn, for that.

You should start by studying the Access Security FAQ, often referenced
in this newsgroup. I think that it covers some VB scenarious, not just
Access ones.

HTH,
TC
 
Rick, good point. I think I recall seeing some method for changing the
owner. I'll have to look that up to make sure. That's exactly the
kind of thing I was hoping someone would point out that I'd overlooked.

TC, no offense taken - indeed Access security is far from trivial, and
you are correct to point out the dangers. I wish there was a simpler
way to do what I need. However, while I have never released a product
with a secure dB, I have researched, read the faq, studied MSDN
extensively, and experimented with dB security on and off for the past
two years. What I meant by "tinkered" is that I was just
experimenting, trying different settings to see how they worked with my
VB app and still provide the required security. I'm certainly no
expert on Access security, but I'm confident I know enough to do this
project right. After all, I've been writing dB apps for six years now
and programming for twenty years.
 
I call your 20, and raise you 15!

(Hollerith cards, drum storage, paper tape readers .... ahhh ...)

Good luck with the app! Post back here if you have any problems.

Cheers,
TC
 
I wonder how many people here know what you're talking about. My first
programming experience was on an Apple II, but I have a stack of cards
and a roll of paper tape stashed away to show my kids some day - and an
old magnetic core memory board too! Thank goodness that stuff was long
gone before I ever got started ;-)

By the way, I decided to take Rick's advice and distribute an empty,
secured dB with my update and copy the records from the old unsecured
dB in to the new secured dB. So, i won't need to set permissions, etc
through my program afterall.

Question for you - do you know if there are any loopholes (documented
or undocumented) in using one mdw file for two or more mdb databases?
It seems to work OK, but I'm not through testing yet.
 
One issue comes to mind...if a user is assigned to a group for the purpose
of doing work in one database, they'll have permissions in the another
database that uses the same mdw. For example, say I have a database for
accounting purposes, and another for human resources purposes. And I have a
group called administrators that has full permissions on everything in both
database. It could be that I really don't want an accounting admin user
looking at human resources stuff (or vice versa). From that perspective,
and having a desire to avoid potential issues, I tend to think that an mdw
should be specific to an application rather than shared among applications.
 
Um, not really. If the two groups with the same name have a different
PID, they will then be as different as chalk & cheese, as far as
Access/Jet is concerned. By that means, you can explicitly decide
whether you /do/ or /do not/ want multiple groups with the same name,
to be considered as identical, or not.

OP, I'm not aware of any holes with using multiple workgroups.

Personally, I feel that microsoft got it "the wrong way round" with
workgroup files. From a /corporate/ viewpoint, you have a big
department of people, & several databases, & it makes sense to have one
workgroup file for the department, & to control access to the various
databases, using that one workgroup file. But for most of us, as
individual developers, the different databases /do not/ have the same
set (or similar subsets) of users. So for most of us, I suspect that
having one workgroup file /per database/, makes more sense.

Luckily they have designed the workgroup file scheme well enough, that
we can all do it whichever way we want to. The use of PIDs, is central
to that.

Cheers,
TC
 
You completely missed the point. I was talking about (as was the OP) using
the same workgroup file to secure multiple databases...of course the groups
will have the same PID.
 
Yes, that is one way to handle the issue, but it could get to be a PITA to
keep straight after awhile as more and more groups/people are added.
Developers have to be very mindful of the people assigned to various groups
and the potential for issues. For example, you could never assign members
of the Users group permissions for anything on any database...not even read
only, because that group may include many people that were added for other
databases, but shouldn't necessarily be allowed in another specific
database.

Using the same workgroup file among many databases is asking for trouble,
IMO. It might work at a departmental level with a limited number of
applications, but once you go beyond that, there are potential problems. And
because of this risk, there really is no way that user administration could
ever be delegated to anyone other than the developer(s) of the applications.
 
Back
Top