Importing A Linked Table

[Mr. Smith]

Hi,

Objective: Prevent users from seeing back-end tables.

I am using MS Access 2000 as a front end to a DB2 backend. I have set the
startup properties of the MS Access database to launch a form automatically.
I have disabled all special keys and disabled the "SHIFT" key from bypassing
automatic startup. The only other glitch that I have found and cannot find
an answer on how to solve is. If a person creates a new database, they can
"IMPORT" my tables from the .mde file into the new database and view the
data within them. However, if they close the database and go back into the
in where the "imported linked tables" are, they are prompted for a password
to DB2. It acts like on the first occurance it is actually passing my
connection string properties?!?! Funny thing is you cannot import the forms
or modules from an .MDE but you can the tables.

How can I prevent them from importing my tables and viewing the data?

James

 

[TC]

Conicidentally I just answered exactly the same question from someone else.
Do a google search for the word: RWOP in this newsgroup, today.

HTH,
TC

 

[unknown]

Can I lock these tables down w/o modifying the Access Security? I have
built my own security model. Is there a application property that I can set
in VBA that would not let anyone import the table(s) from an external
source? I notice that when I revoke all rights to users (using the MS
Access security) on the tables it does not allow them to import the tables
from an external access database. Therefore, I am thinking that Access is
looking comparing something out of that mdw file to something to see if the
user has the right to do something with the database at the time the secured
DB is initialized. So, I am hoping that there is some code in VBA that I
can set to do the same thing.

Totally bogus code but something like:

Public Sub StartupProperties()

Dim CallingSource as Object

set CallingSource = ADODB.Connection

If CallingSource <> Currentdb()
then msgbox("You don't have authority to perform this operation", vbokonly)
terminateconnection()
else
blah blah blah

endsub

Any ideas?

 

[TC]

If you do not use Access security, there is no way (AFAIK) to stop anyone
from linking to and downloading your data. The way to do this is to read &
study the Access security FAQ. Access security is very capable, but
unfortunately it is fairly complicated (initially), with a fairly steep
learning curve, & many traps for the unwary.

As for doing you own custom scheme - it's really not worth it, unless you
mean something really simple with (for example) one fixed password etc. For
example, are you storing any security related information (suuch as
usernames/passwords) in a table anywhere? If yes - and you are not using
Access security - how do you stop anyone from linking to or downloading that
table, thereby learning the usernames/passwords?

HTH,
TC

 

[TC]

Ok, you clearly have a capable setup. I only commented because most
home-grown schemes are hopelessly lame (and therefore, misleading). But you
have got a good scheme going there.

So let's return to your original question!

If a person creates a new database, they can "IMPORT" my tables
from the .mde file into the new database and view the data within them.

AFAIK, if you have not implemented Access user-level security, there is no
way you can stop a user from starting Access, creating a new database, then
importing all the tables from an unsecured database. The reason is, that in
an *unsecured* database, all of the tables are owned by the so-called,
default Admin user. And when you start Access without user-level security,
you are automatically logged-on as that user. So you are now logged-on as
the owner of those tables! So you will always be able to import them into a
new database.

However, if they close the database and go back into the in where the
"imported linked tables" are, they are prompted for a password to DB2.
It acts like on the first occurance it is actually passing my connection string
properties?!?!

I imagine this would be due to the differences between linking & importing.

Funny thing is you cannot import the forms or modules from an .MDE
but you can the tables.

All of the human readable source-code has been stripped within an MDE. So
there is no way you can see the forms in design view, let alone import them
into a new database.

How can I prevent them from importing my tables and viewing the data?

As before, I don't think you will be able to do this unless you fire-up
user-level security.

HTH,
TC

 

[Mr. Smith]

Ok. My security database which is on DB2 contains three tables:
SECURITY_USER (Logins/Passwords/ other information about the user),
SECURITY_ACCESS (Applications that the user has access too and what level of
security they have), SECURITY_APPLICATION (Application information, how to
launch the application). Example:

SECURITY_USER TABLE

Unique ID User Name User Password Other User info (telephone, email,
etc..)
1 John Doe mypass (e-mail address removed)

SECURITY ACCESS TABLE

User ID Application ID Read Write Modify Delete Option
1 Option 2
1 1 Y Y Y
Y
1 2 Y
1 3 Y
1 4 Y Y Y
Y

SECURITY APPLICATION TABLE

APPLICATION ID Application Title
Command String
1 Action Item Resolution System
"I:\BusinessApplications\AIRS.mde"
2 Prior Authorization Screen
"I:\BusinessApplications\PA.mde
3 Correspondence Logs
"I:\BusinessApplications\Corr.mde"
4 Security Module Administration
"I:\BusinessApplications\Security.mde"


All users have access to a form named: APPLICATION_LOGIN.mde which
launches a screen that asks them for a username and password.

LOGIN: JOHNDOE

Password: mypass

After login the form builds another form showing the user what they have
access too, for this user they would see 4 applications available to them.
The user would then click the button to the left of the desired
applications. When they click this button the security module launches the
application using a shell command and passes the users security rights to
the new access application it just loaded via the command string. I parse
the command string out in a module and read all the parameters into public
variables. Depending on the users permissions certain buttons on forms
lower in the hierachy may or may not be available to the user, this is all
done via VBA coding in the forms.

The DB2 tables are locked down via DB2 security requiring a login and
password prior to entry. However, the problem as I have stated before is
that when they open a new database and import the linked tables, it copies
the linked information. I have to link the tables and save the password
with the link because the users do not have access to DB2 via their login
name and password. We have created a generic login name for our
applications and use it to login and link tables. This login has full
rights, but the users are regulated by the security module. I have locked
down special keys and disable the bypass feature for the mde files.

Maybe I can place a password on each of the applications and pass that
somehow from the security module. I am going to try this next.

I was just hoping that I would not have to use MS Access security for each
application that I develop because we are going to have alot of them. Your
right I am not familiar with Access security that well, so I will have to
learn. I am hoping that the security can be applied globally like I am
doing in my examples, otherwise, I am in for alot of work.

Thank you for you time,

Much appreciated!

James