Securing objects using VB

[bosun]

I am trying to find a way to secure a database in access 97 to a greater extent than the built in security features allow.

My basic thought is to make a table with the following fields: [UserName],[Password],[UserLevel]

The idea is that the user would enter their username and password into a form when the database is opened, the user level would then be used to make certain objects or controls available or unavailable to the appropriate users.

I have a couple of questions...

1.) Could this work to secure forms down to individual controls?

2.) How would I store that variable until the application was closed?

3.) Is there anywhere I can look for related source code?

4.) What is the air speed of an unladen swallow?… either African or European

Thank you in advance for you help

 

[jimmythegeek]

No problem. I do this all the time. In fact I NEVER mess with Access security, I make my own. Access security is a pain in the hiney.

Create a user table like you said, with a UserID (autonumber), and the three fields you mentioned (and anything else pertaining to the user).

In answer to your questions:

1) Yes, you can lock controls, hide stuff, do whatever you want.

2) I use a global variable. The only problem with them, is if you get an Access error, it wipes out your global variables, and you have to re-login. So just write code that doesn't create errors . Globals are fine. The only thing you have to store, is the user ID. Everything else you can get from that.

For instance, if I create a function called UserLevel(), I can use this on open of forms to verify user levels, and conditionally lock controls or whatever.

Let's say I have a public variable called gintUserID that was set on login.

Here is a sample function:

Public Function UserLevel() As Integer
UserLevel = ("SecLevel", "tblUser", "UserID=" & gintUserID)
End Function

So to use it on a form, On Open you simply do this:

If UserLevel = 4 Then .... OR
If UserLevel > 2 Then ....

Another example would be:

UserName() (returns the string of the logged in user's name)

3) Don't know that there are many resources available for this, but it's fairly simple to master.

4) uh, uh .... I dun't know that ........ Ahhhhhhhhhhh.
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[bosun]

Thanks for the help. Frankly I was confused by Access's built in security and this seems simpler and far superior.

 

[jebry]

Hi!

A couple of points to add to Jim's excellent post. I would open a hidden form and store the user id in a text box on the form. This is less volatile then the global variable. Make your user table a hidden object, that way a user outside the database cant import the table from another database without knowing the name of the table(which adds another point, make the table name unusual or at least unrelated to user ids).

hth
Jeff Bridgham
bridgham@purdue.edu

 

[bosun]

My original thought had been to create a login form that would remain hidden once a valid username and password had been entered. I am glad that will work.

Thank you for your help.

 

[jimmythegeek]

One other option of securing the user table is to use a "Usys" prefix, along with making it hidden. For example:

UsysUST (which I use for "Usys User Security Table&quot

Access will now see this as a User (developer) system table. Just like Access has Msys tables.

Now for a user to see the user table, they have to check both "Hidden Objects", AND "System Objects" on the options screen, and most users don't think of, or are not brave enough to click "system objects". Then they would have to open the Usys table, even though they would not know by looking at the name, that it was a user table.

It is just one more way to make it harder for the nosy folks to get to the data, but it won't stop them if they dig hard enough. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[bosun]

Thanks, most folks I have deal with would not be nosy enough, but that is a great.