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?

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.

"Barry" <Barry@discussions.microsoft.com> wrote in message 
news:8D4394AD-D55B-4B62-9BC8-1C809045C373@microsoft.com...
> 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? 

Barry wrote:
> 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.

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.

"Barry" <Barry@discussions.microsoft.com> wrote in message 
news:8D4394AD-D55B-4B62-9BC8-1C809045C373@microsoft.com...
> 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 wrote:
> 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.

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?

"Banana" wrote:

> David C. Holley wrote:
> > 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.
> .
> 

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.

"Banana" <Banana@Republic> wrote in message 
news:4B8D19A9.7070505@Republic...
> David C. Holley wrote:
>> 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. 

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

"Barry" <Barry@discussions.microsoft.com> wrote in message 
news:83C07CC5-C269-4431-B861-4A20B4A504D7@microsoft.com...
> 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?
>
> "Banana" wrote:
>
>> David C. Holley wrote:
>> > 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.
>> .
>> 

Banana <Banana@Republic> wrote in news:4B8C5B64.5030001@Republic:

> 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.

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/

> 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.

David C. Holley wrote:
>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?

-- 
Marsh
MVP [MS Access]

David W. Fenton wrote:
> 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.

Marshall Barton <marshbarton@wowway.com> wrote in
news:95pro51r97k6k1ou5mr55ffsdidepe7ems@4ax.com: 

> 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.

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/