DB security

willchr

New member
Local time
Today, 05:29
Joined
Jun 20, 2008
Messages
1
I have the following code to determine who the user is. I would like to know how to use the user Id to lock the design view when certain users access the database.

I appreciate any help. Thanks

Function CheckValidUser()

Dim strUser As String
strUser = Environ("UserName")
Select Case DLookup("[userName]", "tbl_security", "[password]='" & strUser & "'") & ""
Case strUser
'Do welcome message
MsgBox "User Authenticated"
Case ""
MsgBox "User Not Authenticated"
Application.CloseCurrentDatabase
End Select
End Function
 
I am a little confused at your question. Your code suggests that you have a table named "tbl_security" and you are doing a lookup to see if user is in this table.

Is your intent to prevent access to the database completely if the user doesn't have an account in your table?

If so then you can do a couple of things. Set the On Open procedure of a new form to check the environ value against the table in a recordset. If the value exists in the table then the form closes and the Main Form is opened. If not then a messagebox would appear saying the the user does not have an account and the whole database closes.

To do this you need a new form that you could call Authentication.

Set the On Open procedure as follows:

Set VarCheck=currentdb.openrecordset("Select tbl_security.* from tbl_security where tbl_security.password=environ("username")",2)
if VarCheck.eof then
msgbox "User Not Authenticated"
Application.CloseCurrentDatabase
else
docmd.openform "[Your Main Menu Form Here]"
docmd.close acform, "Authentication"
end if


This will accomplish you desire to check the username against your security table.

Quote: "I would like to know how to use the user Id to lock the design view when certain users access the database."

You now run into another problem that I had. I am sure that there alot of different users, but they can probably be put into a few different groups: Supervisor, Employees, Database Administrator, etc. The problem is that you could set the on open procedure for each form to check the environ value but then you have to specify for each user. What I did was create Security profiles and put the users into a profile. That way you can say:
if CurrentUser = Employee then
Forms!Invoice.ShortcutMenu = False
end if

The problem is that all of your forms would need to have this code. It may be just easier to reverse this and say only give Design view capabilities to the following users

if environ("username")= Tim OR Jane then
Forms!Invoice.ShortcutMenu = True
end if

You would have to go to each form and set the Shortcutmenu value to No. Then only those people with the selected environ value would be able to right click and go to design view.



Hope this helps,

Tyler
 
Last edited:
What is the Shortcut Menu?

I usually disable all menus and tool bars and use the SwitchBoard form. From there I do call and open procedure and there check the user group. Depending on the group the form may stay the same be the RecordSource may change.

BTW, I use dLookup rather than to set the user group or deny access to the db and close based on the environ variable.
 
Why not use User Level Security?

It'd be far more secure than rolling out your own solution.
 
Why not use User Level Security?

It'd be far more secure than rolling out your own solution.
 
That is where we started, then we heard horror stories from other users here.
In addition to forms that may not open we have controls on forms that may or may not be visible depending on the User Group, diffent sets of data may export, etc...

We always use FE/BE. We have starting migrating the BE to MSSQL which of course has it's own security.

Perphaps we just don't know enough about it and should look into User Level Security again.

I thought we would have to edit and locate the security file on everyones computer. Can you used it on multiple dbs, with FE/BE and some dbs in the field not always connecting to the network?
 
Well, there's few options:

1) You can distribute the secured database with .mdw then teach users to click on shortcut which will automatically and silently join them to the .mdw file . This is best way to implement security without affecting any other databases they may have. If they try to open your FE without the .mdw, they'd just be locked out unless they explicitly opened the shortcut.

2) You can just leave the database unsecured, using MSSQL's permissions to deny access. If you don't want to show a form, you can code it so it tries to read the recordset first (which MSSQL will then deny) and pop up an error message. Of course, this does not stop your users from seeing the form in design view or moving around controls. But no matter what, they can't circumvent the MSSQL's security.

3) If you have something that you want to enforce within forms and do not want to have users tamper, while using MSSQL security, the simplest thing to do is just to compile the databae into MDE, which removes all source code and make forms inaccessible to design. Mind, this will not stop users from creating their own tables or queries, IINM. This shouldn't be a concern as long the MSSQL security is properly implemented.

Regarding the horror stories, IMHO, this is because the process is somehow confusing, but if you follow it to the letter, you'll be fine. This is what I usually recommend: Linky.

Finally, I'm of belief that one should use built-in functionality whenever possible and this is especially true for security. The most ominous thing about security is it's easy to fool oneself into believing it is secured but is actually trivial to break. If it's just a simple UI design to not confuse users, lookup table for usernames is just fine. But to actually secure, it can be circumvented in many different ways.
 
In option 1. Each Access app will have it's own secured db (which it already does) and a seperate .mdw file?

This will be true for all apps and all users?
 
.mdw can be shared among many databases or users as long they use same security model. Otherwise, you need a separate .mdw for each application with its own security model.

But really, I can't think of any good reason why I would want to have two separate models. Just have one security model that encompasses everyones' needs, and follow the instructions to letter and you'll be fine.
 
For my needs I have found that the same users require different levels of security depending on the applications.

i.e. in one app the can view everything, in another they could view everything but only write to jobs assigned to them.
 
I see. That would necessitate a separate .mdw, I'm afraid.

If this is simpler to implement on backend's security, just use an unsecured .MDE and check server's permissions before opening a form or enabling a control.

EDIT: An example: You can use VBA to check the form's recordsource, and create one-row query using the same SQL statement to verify that you can access that row. If the user is not sufficiently privileged, the backend will send back an error with permission denied, and you can then handle this in VBA, never opening a form until a successful verification, so they never see the privileged forms/controls, and because it's in MDE, they can't edit it.

In this case, I wouldn't keep anything about security in the front-end because it can be used against you.

In my case, I make sure my user supply the information at the time they log in to the server themselves, and there is nothing saved for re-connecting or otherwise harvested for malicious use. I then let server deal with permissions, using VBA to handle permission denial errors.
 
Hmm, yes I seem to have a lot of reading ahead...
This doesn't look easy.

Thanks,

I think
 

Users who are viewing this thread

Back
Top Bottom