Security and lock down a form

Experts,

I need to know if a form can be locked down to only allow one particular user modify the
 data?

Thank you  
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danishaniCommented:
Yes, try this example in below KB article:
http://support.microsoft.com/kb/209871

Hope this helps,
Daniel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric FlammSenior ConsultantCommented:
Actually, it depends on what you mean by "user", and what version of Access you are using. Microsoft dropped the whole security model from Access with Access 2007, so there are no Access "users" anymore (although i think there is a default Admin user for authenticating access to the database from other applications). If you're using Access 2007 or 2010, you can get the Windows user information from the OS and use it to filter access to the form in question.
0
pdvsaProject financeAuthor Commented:
Hi... Thanks for the tips.  I am using 2007.  I was reading some articles about The "Security" feature but from what was said abouy 2007, i wonder if this same security is in 2007?  

I am not at my computer now.

Thank you
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
An approach I have used is to define a global variable for user ID, and set it through the startup form as soon as the database opens.  We also have group names associated with users in a Personnel table.  These groups determine permissions to different parts of the database.

How that variable is set is dependent on how you are managing your users (CAC Card or Windows Login Credentials, Access User Level Security in Access 2003 and earlier, or some variation on that which you have defined your self).

We have a main menu with buttons that open up different forms in the dabase, and code to this effect to disable(or hide) buttons in the open event of that menu:

If DLookup("UserGroup", "tblPersonnel","UserID = " & gUserID) <> Admin then
     Me.cmdAdminForm.Enabled = False
     Me.cmdMgmtForm.Enabled = False

'etc

Open in new window


0
mbizupCommented:
Another approach is directly blocking users from opening certain forms.

You would have authentication code in the open event of a form:


Private Sub Form_Open(Cancel as integer)
      If DLookup("UserGroup", "tblPersonnel","UserID = " & gUserID) <> Admin then Cancel = True
End Sub

Open in new window


Canceling the Open Event prevents the form from opening.  In doing so, it generates an Error in the code that opens the form, which needs to be handled:

     On Error goto EH
     Docmd.OpenForm "SomeForm"
     Exit Sub

EH:

     If err.num = 2501 then 
         msgbox "The form could not be opened -- due to insufficient group permissions or to some othere cause"
          Resume next
    Else
          msgbox "Error " & err.number & ": " & err.description
     end if

end sub

Open in new window

0
pdvsaProject financeAuthor Commented:
Mbizup... That sounds like a good idea and not that difficult.   I am going to have to build thus iver the weekend.  Thank you.  
0
pdvsaProject financeAuthor Commented:
Danishani or someone else:

I am trying to implement the password at the form level as instructed in KB link.
http://support.microsoft.com/kb/209871

Do you know what 2818 is suppose to mean?

I follow the instructions and I can lockdown a form with a pw in the Northwind db but when i try to do the same in my db it does not work.  I am thinking that 2818 is a certain code that would be different in my db.

please see pic

untitled.JPG
0
pdvsaProject financeAuthor Commented:
I am also changing the ObjectName to the name of my table.  (from "Orders" to "name of my table"

I guess that is correct as well.  

attached is a pic of the northwind db table and form northwind dd
0
danishaniCommented:
Keycode 2818 is the data you entered in tblPassword, that means that 2818 is the matching KeyCode for the Form to be opened.

Try in a Button OnClick event this:

MsgBox KeyCode("PASSWORD")

It will return 2818.

So if you want a different Password, then do the same thing as above.
The number returned you save in the tblPassword.

Hope this helps,
Daniel
0
danishaniCommented:
Also make sure you have the frmPassword being created as well.
Make sure you have ObjectName in tblPassword set as PrimaryKey.

If its not working let me know what error you receive, and what exactly is not working.
0
pdvsaProject financeAuthor Commented:
Daniel:  the pw that is entered to open the form is "PASSWORD".  I do not have to enter 2818.  

in Immediate window I put:
MsgBox KeyCode("PASSWORD")

and it returns 2818.  

I dont think 2818 is a password though...it is PASSWORD.

what do you think now?
0
danishaniCommented:
Yes, that's what I mean string "PASSWORD" is being returned as code 2818 by the Function KeyCode placed in your Module.

So you are correct to open the Form, you enter "PASSWORD" as you have 2818 stored in your table with ObjectName the FormName which you want to protect with a password.

Hope that explains.
0
pdvsaProject financeAuthor Commented:
OK so if I change 2818 to 2000 then PASSWORD is no longer associated with 2818 but instead 2000?

I see the objectname is teh form name and not the tablename.  

I will be able to test in a couple hours.   Gotta run at the moment...

thank you
0
pdvsaProject financeAuthor Commented:
Dashani:

OK I have it working but now I want to change the pw.
I dont see how I can change the pw from PASSWORD to "what I want"

from the link I have pasted what I think is where the pw is actually set to "PASSWORD" but I dont know because it says to enter "PASSWORD" as in input mask.  so I am really confused how I can change the pw to what I want.

I know this must be real simple but I dont see it.

http://support.microsoft.com/kb/209871

9. Open the tblPassword table and then enter the following data:

     ObjectName: Orders
       KeyCode: 2818
                              

10. Create a new form in design view and save the form as frmPassword.
11. Add a single textbox to frmPassword called Text0, and a command button called CheckPassword.
12. Set the Input Mask property of Text0 to "PASSWORD" (minus the quotation marks).
Add the following code to the OnClick Event of the CheckPassword button and then save the form:

If IsNull(Forms!frmPassword!Text0.Value) Then
                 MsgBox "You cannot enter a blank Password. Try again."
                 Me!Text0.SetFocus
            Else
                 MyPassword = Me!Text0.Value
                 DoCmd.Close acForm, "frmPassword"
            End If
                              
0
danishaniCommented:
Try this MsgBox Keycode("what I want") and see what number will return in message. Replace 2818 with that number.
0
pdvsaProject financeAuthor Commented:
so this seems a little odd to me.  Whatever I put (eg 2818) is a number that corresponds to the word "PASSWORD" in caps?

thank you
0
danishaniCommented:
That's correct 2818 is the keycode for PASSWORD.
0
pdvsaProject financeAuthor Commented:
M bizup:
<another approach is directly blocking users from opening certain forms.

You would have authentication code in the open event of a form:
Private Sub Form_Open(Cancel as integer)
      If DLookup("UserGroup", "tblPersonnel","UserID = " & gUserID) <> Admin then Cancel = True
End Sub

Question:  would it be rather easy to open the tblPersonnel and change the USERID to "Admin"
I mean unless the db is locked down it would seem easy to do this.  

I am thinking a pw might be a sure fire way to stop a user from opening the form even if the db is not locked dow because the password can not be seen if the input mask is set to Password as opposed to a user being able to change the USERID to "Admin".
==>You know what I mean?
Private Sub Form_Open(Cancel as integer)
      If DLookup("UserGroup", "tblPersonnel","UserID = " & gUserID) <> Admin then Cancel = True
End Sub

Open in new window

0
mbizupCommented:
<
would it be rather easy to open the tblPersonnel and change the USERID to "Admin"
I mean unless the db is locked down it would seem easy to do this.
>

That is correct - the approach I was describing *assumes* that your users do not have direct access to the tables.  Only developers should have direct access to the tables, and only specific (administrative) users should have access to your Personel table, especially fields that define user groups.
0
pdvsaProject financeAuthor Commented:
ahhh I see.  thanks for clearing that up.  
0
mbizupCommented:
Just another note about that - I don't think my last post read too well.

Developers should be the only people with direct access to tables or queries - design or data.

Generally all users should access data only through a form interface  - which give you as the developer control over what data they can edit, add or delete (user level security).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.