Access 2007, 2010, 2013 - Security (1 Viewer)

Microsoft has removed User Level Security in Access 2007, 2010 and 2013, when using a .accdb. You can still use ULS if you stick to .mdb.

If you want to use only .accdb, for FE and BE, are there any ideas as to how to implement ULS. I can think of a few ways, but would appreciate it if someone invented the wheel before I attempt to invent it!!

John

Even though there is no built-in ULS in Access 2007, 2010 & 2013, there must be at least one user defined. Is this user Admin and where can I find it in Access 2013?
John

This may not quite answer your question, but I do have a way for distributing access solutions securely and take these steps.

Typically I have 4 dbs.

1. An accde which contains modules only (not password protected)
2. an accdb which contains customer data tables only and is password protected
3. an accdb which contains system/control tables plus related system forms and is password protected (different password)
4. an accdb (the user db) which contains all forms and queries and might or might not be password protected. I also set navigation off and hide the access window so that the user cannot use the shift key to get into the system. In terms of tables, the only ones linked are the ones in db 2. The ones in db 3 are accessed by DAO via db 1.

The reason for having 2 & 3 is for ease of maintenance and to hide visibility of the user table in 3 which has the access rights

In the code for 4, 1 is referenced as a library. Incidentally, I put all sensitive code in 1 so forms in 4 typically simply have a call to the function or sub in 1

Forms and tables in 3 are accessed only by a call through a function or sub in 1 which in turn is controlled by a function in 1 which determines the access level of the user based on the windows logon and machine id's

I don't change 4 to an ACCDE because a number of the routines need to create temporary forms to display as a subform - and you can't create new forms in an ACCDE.

I don't combine 3 and 1 together, although in theory there is no change because I would then need to password protect it and you can't have a password protected ACCDE as a library.

All four db's need to reside in the same directory since I use currentproject.path to determine where everything is. The library file (1) could be located elsewhere, but often users don't have the access rights to place it there.

Finally, the benefit of using a combination of windows logon and machineID means if the system is copied to a different location (e.g. someone wants to take it home) it won't work at all - in fact I have a sneaky routine which will, after other identity checks will lock everything up and ask for an email to be sent to me asking for the unlock code and will delete the contents of db's 2-4 if necessary.


It's a bit complex and I'm sure someone could break in with enough time but it does for me!

JohnPapa said:

Microsoft has removed User Level Security in Access 2007, 2010 and 2013, when using a .accdb. You can still use ULS if you stick to .mdb.

Click to expand...

That is true for 2007 and 2010 but, from what I saw, ULS has been completely removed in 2013.

Even though there is no built-in ULS in Access 2007, 2010 & 2013, there must be at least one user defined. Is this user Admin and where can I find it in Access 2013?
John

Click to expand...

Not sure what that would do for you even if you could. But the user ADMIN is a system user and is not available to modify at all for anyone. Admin is what everyone is UNLESS ULS was in place and the Admin user removed from the Admins Group.

CJ and Bob many thanks for your replies,

I will have a closer look at CJ's setup, although you could get away with using .accde as FE and .accdb for BE.

Bob I am using A2013 (or trying to) so when it comes to ULS I would need to make my own security system? Is this something that MS is proud of or is Microsoft trying to push people to use Sharepoint, Trust Center, SQl Server?

John

you could get away with using .accde as FE

Click to expand...

Not if you need to create temporary forms!

JohnPapa said:

or is Microsoft trying to push people to use Sharepoint, Trust Center, SQl Server?

Click to expand...

Yep, that is the reason. SharePoint and/or SQL Server.

Going back to my original question, for A2013 do you have to invent your own ULS, assuming you do not want to keep on using mdb? Any ideas?

JohnPapa said:

Going back to my original question, for A2013 do you have to invent your own ULS, assuming you do not want to keep on using mdb? Any ideas?

Click to expand...

And the answer to that would be YES.

Many thanks Bob,
I will give a try and if I can come up with something interesting I will post.
Still I cannot believe that there is no ULS (talking to myself)
John

it isn't so hard

table for groups
table for users
tables for user groups membership


now you need to determine what options to allow for each object. I suspect the only critical one is forms and reports (I find it easier not to prevent access to queries) - so in the form/report open code, you can test the group membership criteria, and proceed from there.

you just need a standard module you develop yourself to manage this stuff. bit of a pain, but relatively easy.

once you have this you can do other stuff, like forcing passwords to expire, and so on, so in some ways it is more powerful (and more transparent) the ULS.

Thanks gemma-the-husky, will try out.