Possible to create user-level security in Access 2007 format w/VBA

[Barry]

There is no doubt that "user-level security" is not supported any longer,
with the Access 2007 file format (.accdb). See
http://office.microsoft.com/en-us/access/HA012301871033.aspx?pid=CH100621891033.
So, what I am looking for is if anyone has created, or knows of, a guide to
replicating "user-level security" via VBA within the new Access 2007 file
format, or if it is even possible. Can anybody help me?

 

[David C. Holley]

I went with a solution that uses 3 tables to manage what person does and
does not have access to.

tblMenuFunctions - List of menu fuctions
tblUserProfiles - List of 'groups' to which a user can belong with relevant
descriptions (Regular Access for Warehouse)
tblUserProfileDetails - List of child records to tblUserProfiles that
defines which Menu Functions belong to a group
tblUsers - List of users that captures the identifies which group their
Windows User ID belongs to

I grabbed some code from mvps.org that gets the Windows User Id of the
person currently logged on to the machine. The value is then used to get the
group that the person belongs to and subsequently the Menu Functions that
they have access to.

In short, I'm using their Windows User ID and several tables to fill a
listbox on the main form that the user has access to.

tblMenuFunctions
ShortName LongName OpensForm
FormName SortOrder ExposeFunction AccessFullVersion
Only
AddShow Show Information - Add Show False
Null 500 False
False
QuitApp Quit & Exit Access False
Null 9999 True
False
TrailerActivity Trailer Activity
True frmTrailerActivity 1000 True
False

OpensForm & FormName allows the code behind the Main Form to be streamlined
using the .Column(n) property of the list box to grab the neccessary
information to Open the Form, all other code resides in individual modules
that are called by a SELECT CASE. The double click on the list box runs it
all. SortOrder allows for a custom sort. ExposeFunction indicates if the
function should be displayed on the main form, there are several points in
the application where the user can Add a Show, however the idea was to
expose the functionality on those forms as opposed to the main form.
AccessFullVersionOnly restricts the function if the user is not using the
full version of Access, like showing the DB window. It serves the dual
function of keeping unauthorized users out of the DB window, since they only
have the Runtime only and it prevents an error from being thrown if the
function happens NOT to be available at all.

tblUserProfiles
ShortName LongName Description
Admin Administration
LTSmith User Profile - LT Smith User specific
profile for LT Smith

tblUserProfileDetails
ID UserProfileShortName
MenuFunctionShortName
(autonumber) Admin
PartMaintenance
Admin
QuitApp

I've been wanting to write it up and publish it, but just haven't had the
time.

 

[Banana]

There is no doubt that "user-level security" is not supported any longer,
with the Access 2007 file format (.accdb). See
http://office.microsoft.com/en-us/access/HA012301871033.aspx?pid=CH100621891033.
So, what I am looking for is if anyone has created, or knows of, a guide to
replicating "user-level security" via VBA within the new Access 2007 file
format, or if it is even possible. Can anybody help me?

If you can use Active Directory, have a look at:

http://accesssecurityblog.com

If you don't have AD, I'm sure you can come up with something similar
(e.g. combination of user name + computer name for instance).

Be aware while this is very effective at providing object-level security
and navigation access, it doesn't actually protect data within the file
itself. If you need the data to be protected, then you would need to
move data into a server-based RDBMS, of which there are free editions
such as MySQL, PostgreSQL, SQL Server Express, etc.

If you are after something more "corral" than "security", then maybe
this may be interesting to you:

http://www.utteraccess.com/forum/index.php?showtopic=1917534

HTH.

 

[David C. Holley]

Crap, I posted a length reply and its not here.

I went with a solution that defined Menu Functions in one table, Users,
UserProfiles and UserProfileDetails in other tables. The Users table lists
out users and contains their Windows User Id to tie it to a specific user.
UserProfiles describes user groups such as 'Admin', 'Warehouse', etc.
UserProfileDetails defines the MenuFunctions that a specific group is
authorized to use. There's code on mvps.org that returns the User Id of the
person currently logged in to Windows. I use that to pull the UserProfile
that the person belongs to and subsequently the MenuFunctions they they're
authorized to use and then populate a List Box on the main form with the
result. If somebody does open the .mdb file from the lan, they're simply
presented with a blank list box and a QUIT button. If a user is set up,
they're automatically presented with the functions that they have access
to.To protect the underlying tables from being viewed the have the prefix
USys which is an old trick telling Access that they are System Tables and
should be hidden. An alternative would be to connect to the tables to grab
the menu functions the person has access to and then detach so that no one
can actually see them without digging into the code, impossible if its a
MDE.

 

[Banana]

To protect the underlying tables from being viewed the have the prefix
USys which is an old trick telling Access that they are System Tables and
should be hidden. An alternative would be to connect to the tables to grab
the menu functions the person has access to and then detach so that no one
can actually see them without digging into the code, impossible if its a
MDE.

Nice trick, David.

So Barry & other readers are aware - USys prefixing works well for
hiding from UI, but there is an option to 'view system tables' which it
will then show up and it does not actually deny access to the table, so
this is just security through obscurity. May be good enough for casual
users, though.

David - I'm not following how connecting to tables to grab menu
functions hides the function? Seem to me that it would have to be
completely inside the VBA code to be non-diggable? Also, note that all
literals and constants are plaintext and thus can be readable even in a
MDE when opened in a hex editor.

 

[Barry]

Thanks guys...do you know where I may find guidance or examples of creating
the permissions (such as pointing to the permission tables David explained)
in VBA?

 

[David C. Holley]

The idea is that the Usys linked tables are linked on the fly, queried, and
then detached. That way if a user happens to expose the database window and
changes the view of the system objects, they don't see them at all because
they simply aren't there. You'd use code to attach to them just long enough
to get the information and then detach. By placing the code within an MDE,
the user would have no way of knowing that the tables are used at all.

 

[David C. Holley]

Send me an email at David C Holley at Hotmail with periods for the spaces.
I'll send a sample mdb file.

 

[David W. Fenton]

If you can use Active Directory, have a look at:

http://accesssecurityblog.com

If you don't have AD, I'm sure you can come up with something
similar (e.g. combination of user name + computer name for
instance).

Be aware while this is very effective at providing object-level
security and navigation access,

How, exactly, does AD provide "object-level security?" You're not
doing anything to protect the object from being opened or edited
with AD, all you can do is control access within your code. That may
be controlling "object availability" in terms of who can open
particular forms or reports via your coded user interface, but it
doesn't do anything regarding the security on how they are stored
and who has access to them outside the flow of your application.

I would not use the term "security" at all for anything you're doing
with AD, as it really isn't security by any definition of the term.
You're just controlling program flow and who has access via the
program to which parts of the app.

That's simply not security.

 

[Stuart McCall]

The idea is that the Usys linked tables are linked on the fly, queried,

and then detached.

Cool idea. Duly noted for future use. Thanks.

 

[Marshall Barton]

The idea is that the Usys linked tables are linked on the fly, queried, and
then detached. That way if a user happens to expose the database window and
changes the view of the system objects, they don't see them at all because
they simply aren't there. You'd use code to attach to them just long enough
to get the information and then detach. By placing the code within an MDE,
the user would have no way of knowing that the tables are used at all.

Instead of actually creating a linked Usys table, how about
just using a query with IN in the FROM clause?

 

[Banana]

How, exactly, does AD provide "object-level security?" You're not
doing anything to protect the object from being opened or edited
with AD, all you can do is control access within your code.

We discussed this before, I think. By objects, I'm referring to
forms/reports and other general functionality. I had neglected to
mention that this also requires a MDE/ACCDE but the idea of using this
in conjecture with AD is to guarantee that a user won't get to the form
that would otherwise make it easy for them to update stuff that they
shouldn't be. MDE may prevent someone from editing Access objects but it
does nothing in regards of access control, hence the need for AD to
provide access control.

My previous reply also makes clear that it does nothing whatsoever to
actually protect the data itself, however, and moving it off to a server
based RDBMS is necessary if they are after the security against the data.

 

[David W. Fenton]

Instead of actually creating a linked Usys table, how about
just using a query with IN in the FROM clause?

Or writing the recordsources on the fly with that.