Solved

Microsoft Access user-level security, get list of user permissions

Posted on 2014-09-09
6
775 Views
Last Modified: 2014-09-24
I want to upgrade an Access 2003 Database to Access 2010. The database has many tables and forms where permissions are limited to certain users. I want to revise that and will have to build in some user permission control in the upgraded database.
I want a list of all files and forms in the database with all their permissions.
I can get a list of user and group accounts which gives me a list of all users and their associated groups and also the other way round, all the groups with all their users. I get that from User and Group Accounts in Users and Permissions in Database Tools in the 2010 ribbon.
I think I have seen a list like what I want previously.
Does anybody know how I can get such a list please.
0
Comment
Question by:Fritz Paul
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I can't verify this since I am using A2013 but you should be able to find the collection that contains the data you are seeking and by using VBA, loop through and extract what you need.  There might even be a useful MSys table which would make it easier.  Unhide the systems tables and start there.
0
 

Author Comment

by:Fritz Paul
Comment Utility
Hi Pat,

The security file associated with this database is Secured.mdw. From there I can get lists of user and group names, but not how they relate to the tables and forms.

However one of the system files in the main database itself seems to have the information, but it is in "Long binary data". See the picture below. Do you know how to see that in readable format?

Through Database Tools > Database documenter I could get a report consisting of many pages that among other things contains the information if I export in txt or rtf format. I believe I will be able to open those up in Excel and eventually filter the required information out tediously. (The info can be exported in Excel, but then the names of tables and forms are not included.

Image of System file with definitions
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I know the logins are in the .mdw but I think the details regarding objects are in the database itself.  I don't have any old applications with security and I don't have anything older than A2010 to work with so I can't really investigate this for you but the answer lies with the collections.  Having a MSys table would have been too easy.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Assisted Solution

by:LukeChung-FMS
LukeChung-FMS earned 250 total points
Comment Utility
Our commercial Total Access Analyzer addin product performs detailed documentation and cross-referencing of your database objects and detects 300+ types of errors, suggestions, and performance tips.

Among its documentation are several workgroup security reports that include lists of users, groups, groups and users, and user/group permissions at the object type and individual object level. Examples of the reports it generates are shown here: Microsoft Access Workgroup Security Permissions for Users and Groups

This pretty helpful information if you're trying to understand the existing security and managing it over time. Leaving a hole in the security scheme could be devastating and Microsoft doesn't make it easy to have an overview of the settings.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
If you want a roll-your-own sort of thing, you'll have to loop through the Containers and Documents collections, and determine the permission levels for each User associated with the object. This website has some information on the process:

http://www.casadebender.com/reference/other/bldapps/chapters/ba14_2.htm
0
 

Author Closing Comment

by:Fritz Paul
Comment Utility
Thanks I appreciate.
Regards,
Fritz
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now