access security tutorial & file extensions 1

[y2k1981]

Hi All,

I've got two questions. I'm creating my first proper "multi-user" database (ie, is on a networked drive and can be updated my multiple users at the same time). Firstly, I want to learn about some basic access security. I've found one document, but it's a little complex for me that doesn't know anything about access security. Also, from what I can gather from that document though, access security is "per machine" so to speak. I want to create a database in which users will have to log on, but that it won't affect anything else they do in other access databases, and that I won't have to modify anything on anybody's machine.

Second - file extensions. It looks like there are alot of differnet access file extensions apart from .MDB, there's .MDE, .ADE, and there just the ones I can remember. Where can I find out more about all those differnet file extensions and what they're used for ? Are there any in particular I should pay attention to before setting out on developing this database? Also, what is an access backend file? I can guess that it's a file that resides on a server which maybe contains all the tables etc, but can somebody give me some more technical info on it?

If anybody can recommend any other access developers tutorials or info sites, they would't go astray.

Thanks in advance for all your help

 

[Dalain]

Because you are using a network drive, you can set it up that only the users that require to use the database have permissions to access the drive.

For tracking who is using the Database, (for Me anyway) All the users have to log on thier computer in order to have access to the Network Drives. Which meens the Network Admin person will have a record who was loged on when. I also have a field in each table Time stamped with the Username when ever a record info is changed.

I currently have my Database located on a Network. All the users have been given certin permissions

Network Drive
U:\Data, Has the backend Database. This database has All the Tables, which is access by every user.

U:\NameOfUser1, this has the Front End database. The user can copy this file on to thier desktop. This databse holds only the forms, queries etc.. The tables in here are linked tables comming from the backend with one or two local tables used for default settings.

When the user maps the Network Drive they should see only the two Directories you have given them persission to see.

Example
U:\Data - Backend DB File. For all Personnel using the Database. Permissions: Write, Erase, Modify, Create, Files Scan. Do NOT give them Delete Permission because you do not want them to delete the data file. Making you lose all you info, then having to resetore it from the backup (if a backup was done and is current)

U:\NameOfUser1 - Front End DB for User1. Permissions:Write, Erase, Modify, Create, File Scan, Delete. Here it does not mater if the user deletes the front end database since it does NOT HOLD ANY importent information. If it happends, tell them not to do that and then given them another copy of the file.

You could also defined (setup) Access user-level security, This depends on how many users you are going to have and if you can trust them, but for me I found this unnessary, I have only 20 Users.

For File Extentions;

MDB file is a database that can contain Microsoft Visual Basic code

If your database contains Microsoft Visual Basic code, saving your Microsoft Access database as an MDE compiles all modules, removes all editable source code, and compacts the destination database. Your Visual Basic code will continue to run, but it cannot be viewed or edited, and the size of your Access database will be reduced due to the removal of the code. Additionally, memory usage is optimized, which will improve performance.

Saving your Access database as an MDE file prevents the following actions:

1. Viewing, modifying, or creating forms, reports, or modules in Design view.
2. Adding, deleting, or changing references to object libraries or databases.

3. Changing code using the properties or methods of the Microsoft Access or VBA Object models — an MDE file contains no source code.

4. Importing or exporting forms, reports, or modules. However, tables, queries, data access pages, and macros can be imported from or exported to non-MDE databases. Any tables, queries, data access pages, or macros in an MDE file can be imported into another Access database, but no forms, reports, or modules can be imported into another Access database.

Caution Be sure to save a copy of your original Access database. If you need to modify the design of forms, reports, or modules in an Access database saved as an MDE file, you must open the original Access database, modify it, and then save it as an MDE file again. Saving an Access database containing tables as an MDE file creates complications reconciling different versions of the data if you need to modify the design of the forms, reports, or modules later. For this reason, saving a Access database as an MDE file is most appropriate for the front-end database of a front-end/back-end application.

Caution You won't be able to open, convert, or run code in a Microsoft Access 2000 MDE file in future versions of Microsoft Access. The only way to convert a Microsoft Access 2000 MDE file to a future version will be to open the original Access database the MDE file was created from(the MDB File), convert it, and then save the converted Access database as an MDE file.

Hope This Helps

Dalain

 

[y2k1981]

Thanks for your response Dalain - yes, I do want to setup access level security (because, unlike you, I can't trust that my users as much as you !!). However, like I said, it seems that access security is "per machine" rather than per database. How do I go about setting up security so that no matter what PC you're at, when you open the db, you must supply a valid username and password, and still not affect any other databases you open on the same PC? can you help me out at all

thanks for the file extension info too.

 

[GTLoco99]

The way i got around the problem is I built in secruity options in my database. First you need to create a table called User_Table. This will have User_ID, User_Password, and User_Group Fields. Then I created a log in form, this had two text boxes and two buttons, one that logs them in, the other that exits the database. You will also need to create a new menu bar for your users to user. This will not have all the options that the default bar has. Once that Menu bar is created, make it the default at the start up. Then Last but not least, set up your switchboard to give your users what they need and use the code bellow for your form, this should give you what you want. Good Luck!

Code
___________________________________

Option Compare Database
Option Explicit
________________________________________________
Private Sub Exit_Click()

DoCmd.Quit acQuitSaveNone

End Sub
________________________________________________
Private Sub Ok_Click()

Dim db As Database
Dim rs As RecordSet
Dim SQL As String

Dim User As TextBox
Dim Pass As TextBox


Set User = Me.User_ID
Set Pass = Me.User_Password


Set db = CurrentDb()

SQL = "SELECT User_Table.* FROM User_Table WHERE User_Table.User_ID Like '" & User.Value & "';"

Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

If rs.EOF Then
MsgBox "Username or Password is Incorrect! Try re-entering your Log In Information.", vbInformation, "Login Incorrect"
GoTo Exit_Sub
End If

If rs.Fields(&quot;User_Password&quot <> Pass.Value Then
MsgBox &quot;Username or Password is Incorrect! Try re-entering your Log In Information.&quot;, vbInformation, &quot;Login Incorrect&quot;
GoTo Exit_Sub
End If

Select Case rs.Fields(&quot;User_Group&quot.Value

Case 0

DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , &quot;[ItemNumber] = 0 AND [SwitchboardID] = 1&quot;
DoCmd.Close acForm, &quot;Login_Frm&quot;, acSaveNo
Application.MenuBar = &quot;&quot;

Case 1
DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , &quot;[ItemNumber] = 0 AND [SwitchboardID] = 1&quot;
DoCmd.Close acForm, &quot;Login_Frm&quot;, acSaveNo


Case 2
DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , &quot;[ItemNumber] = 0 AND [SwitchboardID] = 6&quot;
DoCmd.Close acForm, &quot;Login_Frm&quot;, acSaveNo


End Select

Exit_Sub:

End Sub
_____________________________________________

If you have any other questions about this, let me know

Thanks
GTLoco

 

[y2k1981]

GTLoco - thanks so much, I was beginnint to think I'd never get this the way I wanted it !! A few questions:

Firstly, how secure is this (not that I'm doubting yur capabilities or anything)? I'm doing to be disabling the F11 key etc so that the user will have to use the switchboard to navigate through the forms, but I just wanted to make sure that there's no way of the user getting in without a valid UserID and password

Second, I have one form which updates a field with the userid when a new record is added using CurrentUser() (even thought I haven't actually been able to build in any security yet). How would I go about doing this using your code above? I'm guessing I'd have to declare a public variable, but what is two users were logged in at the same time?

Third, how would I go about implementing group level security? Supervisors would need to have a higher security level than ordinary users.

And finally, just in case all of the above isn't enough of a challenge for a sunday afternoon, why is Access Security built that way? Doesn't seem to make much sense to me.

Thanks again for your help, I'm off to try out your code now !!

Martin

 

[GTLoco99]

Ok let me see if i can answer all of these.

Firstly, how secure is this (not that I'm doubting yur capabilities or anything)? I'm doing to be disabling the F11 key etc so that the user will have to use the switchboard to navigate through the forms, but I just wanted to make sure that there's no way of the user getting in without a valid UserID and password

This way is a fairly secure system but not full proof. Disabling the F11 key will help, but if the user still holds down the shift key while opening the database, they will have access to the database window and all of the default menu selections. I have currently not found away around this. On you log in form, make sure you only have an OK button and a Exit button.

Second, I have one form which updates a field with the userid when a new record is added using CurrentUser() (even thought I haven't actually been able to build in any security yet). How would I go about doing this using your code above? I'm guessing I'd have to declare a public variable, but what is two users were logged in at the same time?

This can be done a couple ways. I think the easiets is to added a SQL statement to your Log in form that will create a table with one field and insert the user ID. You can call this table Current_User or something. Then you can pull that data from the table on your form. This would be a SELECT INTO...query, I will let you find out the rest

Third, how would I go about implementing group level security? Supervisors would need to have a higher security level than ordinary users.

This is already in my Code. The Group_ID is how I determine which group the user will have access in. Then I use a Case statement to open the correct forms and menus by the group ID. Here is a code snipit bellow

Code:
___________________________

Select Case rs.Fields(&quot;User_Group&quot.Value

Case 0 'Administartor

DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , &quot;[ItemNumber] = 0 AND [SwitchboardID] = 1&quot;
DoCmd.Close acForm, &quot;Login_Frm&quot;, acSaveNo
Application.MenuBar = &quot;&quot;

Case 1 'User lvl 1 access
DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , &quot;[ItemNumber] = 0 AND [SwitchboardID] = 1&quot;
DoCmd.Close acForm, &quot;Login_Frm&quot;, acSaveNo


Case 2 'User lvl 2 access
DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , &quot;[ItemNumber] = 0 AND [SwitchboardID] = 6&quot;
DoCmd.Close acForm, &quot;Login_Frm&quot;, acSaveNo
____________________________________

Lvl 1 access can open any items on the swithboard. Lvl 2 access can only use reporting functions. I open the switchboard and filter out the Menus i have set up for the different user groups using the code in blue: DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , &quot;[ItemNumber] = 0 AND [SwitchboardID] = 6&quot; This code will only open the swithbord menue with the ID 0, 6. You will need to go into the code on your swithboard into the On_Open statement and remove a line. If you dont, you will not be able to open directly into a menue on your switchboard. The code is bellow and what you need to remove is marked in red.

Code:
_________________________________

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

' Move to the switchboard page that is marked as the default.
Me.Filter = &quot;[ItemNumber] = 0 AND [Argument] = 'Default' &quot;
Me.FilterOn = True

End Sub
_______________________________

And finally, just in case all of the above isn't enough of a challenge for a sunday afternoon, why is Access Security built that way? Doesn't seem to make much sense to me.

I have no idea why microsoft chose to do it this way. When Access was originally designed, people used it for far greater applications than it was intended, so they did not create the security in such a way that it would need to be used on a shared database. Access was really only intended for small, one terminal solutions.

Let me know if you need anything else

Thanks
GTLoco

 

[y2k1981]

me again !!

Your code isn't working properly for me. It doens't recognize Database as a valid variable type. What version of access are you using? this needs to work on 97 and 2000. also, I don't think it recognizes dbOpenShapshot as being valid either, but obviously I can't tell for definite when it won't get past the variable declarations

can you help me out again?

thanks in advance
martin

 

[GTLoco99]

You need to go to references and make sure you have Micorsoft DAO object Library 3.5 or higher enabled. That should fix your problem

Thanks
GTLoco

 

[psemianonymous]

I guess it's already too late to try out the Access security, but to answer your original post--you CAN have all your users connect to the &quot;per-database&quot; security mechanism.

What this requires is that you set up a specific shortcut for your users to get into your database. Mine looks something like this:

&quot;C:\Program Files\Microsoft Office\Office\Msaccess.exe&quot; &quot;J:\trn\trndb.mdb&quot; /wrkgrp &quot;J:\trn\trndev.mdw&quot;



I'd recommend that if you have time, you read the FAQ I wrote on why you should probably use Access security. Especially interesting information is at the bottom, wherein I show how to break into the &quot;table-based&quot; login systems, guaranteed. faq181-3893

 

[y2k1981]

no foolio12, it's not too late, I havent' implemnted anything yet, I was just doing some research before I got into the serious db development. You say that I'd need to create a shortcut to the db to implement the per-database security. what if the user didn't use the shortcut but instead went straight into windows explorer and opened the db from there, would they be bypassing the security then or would they still be required to authenticate themselves?

 

[psemianonymous]

If you implement security properly, they'll get an error message saying something like &quot;Unable to open database window;no permissions.&quot; if they just double-click on the MDB file.


If you're interested in implementing the built-in security, check out Microsoft's excellent manual on the subject. I think it's worth the time investment:

Microsoft Access Security FAQ:

http://support.microsoft.com/support/kb/articles/Q165/0/09.asp

http://download.microsoft.com/download/access97/faq1/1/WIN98/EN-US/Secfaq.exe

(This is the Security FAQ for *ALL* version of Access)

http://support.microsoft.com/support/access/content/secfaq.asp

(On-Line version of the Security FAQ)