A97 - Modify Structure of Secure mdb

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

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
 
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 said:
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
Click to expand...
Thanks TC - the CreateWorkspace() was what I was looking for I think.

I'll trial that code this week. Appreciate your help and feedback.
 
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
Click to expand...

How would you go about "obfuscating" the user's name and password?
 
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!
 
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
 
TC said:
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?)
Click to expand...



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
 
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.
 
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
 
Back
Top