Database Security

[shamas21]

Hi All

I understand the functions behind access security in terms of users and which groups they belong to.

My only problem is this - Lets say when I log on as an Administrator then I would want to see all Tables, Queries. But lets say a user with only read only access logs on then I dont want them to see the Tables, Queries.

Can this be done? to say that when a user logs on with Admin rights so he can see all tables and queries but a user with read only NOT to see tables and quesries?

Thanks

 

[Matoco1]

Im still new to this aswell but I would create a .MDE file for basic users (in the tools menu) the file will work in the same manner but it locks all of the bits that really need locking. Give it a try and see if its what your looking for. For me it's generally enough but it depends on your user base.

 

[shamas21]

Im still new to this aswell but I would create a .MDE file for basic users (in the tools menu) the file will work in the same manner but it locks all of the bits that really need locking. Give it a try and see if its what your looking for. For me it's generally enough but it depends on your user base.

Thanks for the suggestion. But can you explain the concept behind and MDE file? How can i restrict users from seeing tables and quesries?

Thanks again

 

[Matoco1]

the MDE will compile all of your VBA code so that it can't be read or edited, it will prohibit creating forms or reports or modifying existing ones. But to lock it fully make use of the Startup option from the tools menu assign a form to open on load like a menu. at the bottom uncheck all of the boxes even the advanced ones.

make sure you do this to a copy of your DB. then when done create your MDE and it will have locked all of the menu items.

 

[Pat Hartman]

Versions of Access prior to A2007 contain what is called User Level Security (ULS). It allows you to lock down objects within the database to restrict their use. Generally you would create groups with different permissions and assign individuals to groups. Assigning permissions directly to individuals becomes unmanageable so it is not recommended.

With A2007, you don't have this option although you can accomplish some of it by manipulating the navigation pane.

How far you have to go with security really depends on how inquisitive your users are. I rarely do anything besides hide the database window at startup. I don't tell them how to use the shift-bypass key so they can't get out of the form structure I create for them. If your users know about the shift-bypass key, you can disable it but just remember that anyone can re-enable it from a different Access application.

The bottom line is that there is no really secure Access application. If you are worried about data security, you need to switch from a Jet back end to a SQL server back end. That will allow you to control access to the tables.

 

[Niroth]

Versions of Access prior to A2007 contain what is called User Level Security (ULS). It allows you to lock down objects within the database to restrict their use. Generally you would create groups with different permissions and assign individuals to groups. Assigning permissions directly to individuals becomes unmanageable so it is not recommended.

With A2007, you don't have this option although you can accomplish some of it by manipulating the navigation pane.

How far you have to go with security really depends on how inquisitive your users are. I rarely do anything besides hide the database window at startup. I don't tell them how to use the shift-bypass key so they can't get out of the form structure I create for them. If your users know about the shift-bypass key, you can disable it but just remember that anyone can re-enable it from a different Access application.

The bottom line is that there is no really secure Access application. If you are worried about data security, you need to switch from a Jet back end to a SQL server back end. That will allow you to control access to the tables.

How do you hide the database window? Right now I simply use the navigation pane to hide all the tables, which is kind of a pain when I have to go in and change some data.

 

[odin1701]

I use an MDE file and disable viewing the database window that shows the objects. I use a startup form which shows application name, etc. Then I use a custom menu bar at the top. The menu loads based on an access level assigned to users in a table. If they don't have high enough access, say only the data entry form is available to open from the menu bar, etc. If they are not in the access table, the database is closed. You have to use an MDE and disable the bypass (shift) keys. VBA is password protected as well.

The access table uses the windows username and also has their name, initials and access level in it, and whether or not they show up as a user so that records can be assigned to them for work (i.e. admins won't show up in a drop down to assign a person to a record to work).

I grab the windows username using Environ('UserName') and compare that to the data table. Note this should be used in a domain environment otherwise it would be possible for someone to make their own username. An alternative would be to require a username/password to be entered when opening.

There are no data tables in the MDE files - they only store queries, forms and VBA code and link to a password-protected backend MDB file, or SQL server data tables. This ensures that there is less chance for data corruption in a multi-user environment. The backend databases are compacted/repaired daily. Also there is an auto-updater which checks the current installed version of the MDE file to the MDE file on the updates section of the server, and if it's older it copies the new MDE to the client computer before opening it.

That's how I do it.

 

[The_Doc_Man]

The start of hiding anything from anybody is a startup form that you can use to block a user's access to the database window. If your users are savvy enough and persistent enough, that won't work. They'll find a way around it. However, if you allow the users to directly open the database window in the first place, you have no hope of stopping anyone from doing a lot that you said you didn't want done.