Setting permissions to Windows level user accounts

[stev57]

I am researching the best ways to secure a database using the built in features of Access 2003. When I run the User Level Security Wizard one thing I am puzzled by is the lack of a "Select Users, Groups or Computers" dialog. As you may know this dialog is available in Excel when you apply user-level permissions to specific ranges, and it allows you to refer to the various Security group definitions existing on a Windows network including individual Users, User Groups or even specific Computers if this is appropriate. There is a tree view that lets you browse the entire corporate network and select those entities which you require or you can use a search feature.

In the Access wizard you have to effectively create Workgroups from scratch by creating new users and setting their passwords, which seems incredibly limited in comparison, I would have expected Access to have the enhanced features and Excel to be more limited. What am I missing?

 

[RainLover]

Stev

You can create Security Groups in Access then make the various users as Members of One or more groups.

Does this help?

 

[stev57]

This is true, but its not what I'm after. I want Access to pick up the Windows user groups so I don't have to maintain 2 sets of permissions for each user (for the access database itself and for various network folders and spreadsheets related to the project), or worry about users sharing passwords (as sharing Windows passwords is explicitly against the IT policy). I would rather users didn't have to remember a seperate password or type it everytime they need to get in. I am honestly quite astonished if the program doesn't have this feature.

 

[RainLover]

I am sorry but I do not understand your objective.

Lots of programs have different Passwords compared to the Windows Password.

I am sure I am missing something here.

 

[stev57]

OK let me expalin in more detail.

Our project consists of a database and various related spreadsheets and other documents, which are stored on a set of shared folders on a network. Users have various roles which dictate which folders/documents they can get into, and which ranges on some spreadsheets they can edit. All the necessary permissions for these tasks are linked to their Windows login account and are therefore 'automatic'.

If Access does not allow user level security to be linked in the same way, this means I have to:
1) Create a new set of passwords for each user for database use.
2) Create/restore old passwords when they get forgotten/accidentally revealed etc (with Windows passwords, this would already be handled much more efficiently by our existing IT infrastructure).
3) When a new users join the project/existing users leave or switch role, I will have to update their security credentials linked to both their Windows account AND the database. This to me sounds like unnecessary overhead.

 

[RainLover]

Stev

I am not a network guy, so I don't know a lot about this sort of thing.

Windows can set up the basic Read or Write Permissions.

But I do not know of a circumstance where Windows can allot permission to the lower level function of any program.

Are you trying to tell me that Windows can set a permission for various users as to their ability to read or write to a particular cell or sheet in an Excel Spreadsheet. I think not.

 

[stev57]

Yes, it can certainly do this and is an often used feature that has existed from at least Excel 2003 (Tools Menu->Protection->Allow Users To Edit Ranges).

 

[gemma-the-husky]

i dont think access will do that -

access does vertical control quite easily - enable different classes of users to interact with different sets of forms etc

it doesnt realy do horizontal control - if you can "see" a form - you can see all the data that goes in the form - ir all the data in the table

to circumvent this you have to find a way of limiting data returned by queries - but you wont do that in ULS - you have to write your own functions

i am pretty sure that out of the box access ULS is completely distinct from windows permissions. Access actually applies security all the while - its just that by default everyone is an Admin, enabled to do anything to anything.


Note that Access is far more sophisticated than excel in data handling - effectively what you do in access is limit everybody's ability to write particular types of data into each cell, automatically - so a number cell cannot accept text. a date cell can only accept a date - you can't get anywhere near this in excel. - which is why you get so many probelms when you try to import excel sheets into access, and find they are not properly normalised. - you just cannot think of access using an excel model - you really wil get into a lot of difficulties if you do.

 

[RossWindows]

I think you may have to write your own functions. for instance, in one of my largest databases I use the API "fOSUserName" in combination with three tables. tblGroups and tblUsers and tblUserPermissions. Users are part of one or more groups. The groups table holds default permissions for each group. When a user is created, they "inherit" the highest security of the groups they are a part of, but an individual user can be given more or less permission by adding an entry in tblUserPermissions table. Entries in tblUserPermissions override all. The API function is a gem in that it returns the actual username of the user running the database, therefore no database login is required by the user (this is assuming people do not share their usernames/passwords [If that's a concern, you can also implement Access User/Group security]). I have found this method to be most effective. It should also be noted that users are only allowed to run the .mde via windows file/folder permissions. Also the shift-key is disabled in both the FE and BE.