A97 - Modify Structure of Secure mdb

[Kahuna]

Hi Folks

I have a FE/BE app, both parts are secured. I'd like to use a routine in the
FE to update the structure of the BE (adding tables / fields etc).

This works great with the non-secured BE, but I am struggling to determine
if this is possible with a secured BE. I use a table of tables, fields,
attributes etc in the FE to make the update. As the FE is updated and
deployed it has with it this table, essentially, describing the BE structure
that's required to match the new FE.

One wrinkle is that the end user will run the 'Updater' routine when they
first open the App, so they wont have permissions to access the secured BE
other than through queries (RWOP).

Any comments on the feasibility of doing this, and how I could access the BE
and make the structural changes (perhaps by creating the link using the
developers username/password in code)?

TIA

 

[TC]

That's a nice idea to have the changes described in a table. I've used
the same approach, but using custom procedures like AddField,
DeleteField, AddTable, and so on, which I call in sequence (from code)
to perform the changes.

To do what you need, you need to temporarily elevate the current user's
priviliges, under program controls. Here's how you do it.

(untested)

dim ws as workspace, db as database
set ws = dbengine.createworkspace("", "Developer", "s3cr3t")
set db = ws.opendatabase("full path to BE database")
... do stuff ...
db.close
set db = nothing
set ws = nothing

In the above example, user "Developer", password "s3cr3t" is the
username/password of a user who has sufficient priviliges to perform
the operations in question. The current user, by executing that code,
will temporarily gain the other user's priviliges, for operations that
he performs /via that workspace variable/. Obviously you should
obfuscate the priviliged user's name & password, in the code, so it
can't be found by a simple "strings" search of the database file.

HTH,
TC

 

[TC]

So, for example:

set ws = ...
set db = ...

db.tabledefs.append ...

HTH,
TC

 

[Kahuna]

That's a nice idea to have the changes described in a table. I've used
the same approach, but using custom procedures like AddField,
DeleteField, AddTable, and so on, which I call in sequence (from code)
to perform the changes.

To do what you need, you need to temporarily elevate the current user's
priviliges, under program controls. Here's how you do it.

(untested)

dim ws as workspace, db as database
set ws = dbengine.createworkspace("", "Developer", "s3cr3t")
set db = ws.opendatabase("full path to BE database")
... do stuff ...
db.close
set db = nothing
set ws = nothing

In the above example, user "Developer", password "s3cr3t" is the
username/password of a user who has sufficient priviliges to perform
the operations in question. The current user, by executing that code,
will temporarily gain the other user's priviliges, for operations that
he performs /via that workspace variable/. Obviously you should
obfuscate the priviliged user's name & password, in the code, so it
can't be found by a simple "strings" search of the database file.

HTH,
TC

Thanks TC - the CreateWorkspace() was what I was looking for I think.

I'll trial that code this week. Appreciate your help and feedback.

 

[TC]

No probs, glad I could help

TC

 

[Tom Stoddard]

In the above example, user "Developer", password "s3cr3t" is the
username/password of a user who has sufficient priviliges to perform
the operations in question. The current user, by executing that code,
will temporarily gain the other user's priviliges, for operations that
he performs /via that workspace variable/. Obviously you should
obfuscate the priviliged user's name & password, in the code, so it
can't be found by a simple "strings" search of the database file.

HTH,
TC

How would you go about "obfuscating" the user's name and password?

 

[Kahuna]

Tom, I have looked at using an encryption routine so the username / password
is never held 'in the clear' in the code.and I would use a common expression
as the seed for the encryption.

Not sure how obfuscation works in Access VBA - never heard of it before.
Thought it was just a VB thing!

 

[TC]

It's massive overkill to /encrypt/ those values. Just code them so that
they can't be seen by a single "strings" dump of the database file.

For example:

dim sUsername as string
sUsername = chr$(41) & chr$(42) & chr$(32)

instead of:

const sUsername = "ABC"

(PS. The chr$ codes above are probably wrong, ie. not the codes for
"A", "B" and "C". I don't have anything suitable here to check. But
it's clear what I am getting at, yes?)

HTH,
TC

 

[Kahuna]

It's massive overkill to /encrypt/ those values. Just code them so that
they can't be seen by a single "strings" dump of the database file.

For example:

dim sUsername as string
sUsername = chr$(41) & chr$(42) & chr$(32)

instead of:

const sUsername = "ABC"

(PS. The chr$ codes above are probably wrong, ie. not the codes for
"A", "B" and "C". I don't have anything suitable here to check. But
it's clear what I am getting at, yes?)

Yes I get your point TC

But its really not a problem running a simple encryption routine. I have it
in my app anyway, so just call it with the encrypted string as an argument
to populate the connection.

Cheers

 

[Tom Stoddard]

Thanks, TC! I was really only asking because I was wondering if VBA has some
built in capability I wasn't aware of. Your solution is easy enough and it
seems that it would be fairly effective.

 

[TC]

There's nothing built-in, but it's quite easy to code strong encryption
in VBA. For example, you can do RC4 in a few dozen lines. I've even
seen implementations of the US Advanced Encryption Standard (AES) in
javascript!

Cheers,
TC