I have been developing Access database applications for many years, and
naively relied on user level security extensively. 

I just discovered that Microsoft has removed this capability from Access
2007, saving me many hours of exasperation, and eliminating the possibility
of actually locking myself out of my own database (which I actually did once
or twice, in my younger days.)

Furthermore, if some anal client actually wants to limit the ability of some
users to modify objects or perform some actions, I can charge exorbitant fees
to create my own security!

This is the kind of "improvement" I usually only see in a Dilbert comic strip.

Hi, Bill.

You are mistaken.  Access 2007 supports User-Level Security for MDB database
format files, but not for the new ACCDB database format.  Keep your files
developed in earlier versions of Access as MDB's, and you can use them with
User-Level Security in Access 2007.  And, if necessary, you can even create
an MDB file in Access 2007 and implement User-Level Security on it, although
it's a little more difficult than with earlier versions.  But it's still
there for backwards compatibility.  Microsoft didn't remove it completely.

When your customer finds out you're gouging him, you'll lose the customer
and word-of-mouth advertising will work against you, because that customer
won't be saying kind things about your Access knowledge and pricey, but
needless, "security" expertise.

.. . . where software users fail to read the manual and rely on unfounded
rumors or assumptions?  That happens in real life, too.  ;-)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Thanks for your response, Camaro!

I'm hoping you will be able to point me in the right direction, since I have
apparently been hasty.

Without implementing an obsolete format, how do I prevent users from
modifying object designs?  How do I limit a user's ability to perform certain
actions?  How do I record the identity of the current user?

Without using my pricey, needless expertise, that is...

Show quote

Hide quote

Hi, Bill.

If history is any guide, the MDB database file format won't be obsolete for
at least another 15 years.  Besides, it's the safest Microsoft Office file
format.  Only one virus can successfully infect an MDB file, and it was
discovered in 1998, so if your antivirus software's virus definitions are up
to date as of November 1998, scan any incoming MDB files with it, and you
know whether or not you're safe.  (I suspect ACCDB files are just as safe,
but I don't know that for a fact, yet.)

Convert the ACCDB database to the ACCDE database format (equivalent to an
MDE in earlier versions) and set every form's "Allow Design Changes"
Property to "Design View Only."

That depends upon which actions you want to limit, but there may not be an
equivalent in Access 2007.  What do you have in mind?

Please see the following Web page for the VBA code:

http://www.mvps.org/access/api/api0008.htm

Insert the result of the fOSUserName( ) function into a table for record
keeping purposes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Per Wiktionary:
"To obsolete is often used in technical fields (e.g., computing) to indicate
an effort to remove or replace something. "

Introducing a new file format for Access seems to be consistent with the
definition of obsolete, but I'll use the term deprecated instead for the mdb
format.

If the new format doesn't render the mdb obsolete, then why introduce it?

What about tables and queries?

Some users can add customers, some can only add orders.  Easy with ULS -
read only for one group to the customers table.  Without ULS, I have to
create my own control, and charge more, and then my client will accuse me of
gouging him and will say unkind things about me!

Sure, I can use an API call, I know, but still even though it's easy I
didn't have to do it in earlier versions of Access. 

I just don't understand the logic in discarding ULS.  That was one of the
strengths of Access.  Sure it could be cracked with willful intent, but if
you want real security you go to SQL Server.  It was a good mid-level
measure.

Show quote

Hide quote

Hi, Bill.

The new ACCDB format was introduced mostly to accommodate multivalue fields
and remove replication and User-Level Security, because those are better
handled in Sharepoint Server, according to the Microsoft Marketeers.  If
you're in the habit of buying "new and improved!" products simply because
they're touted by marketeers, then I would advise you not drink the Kool-Aid
when someone hands you a cup.

An Access developer must ask himself, "Does my database application really
need the benefits of Sharepoint Server?"  Most of us will answer "No,"
because:

1.)  We need to enforce referential integrity on our data;
2.)  We can't afford to host an expensive Sharepoint Server for a small
database application; and
3.)  We don't need multivalue fields because we already know how to create
related tables and foreign keys.

Microsoft has an incentive to market Sharepoint Server and Office 2007
because, like any busines trying to make a profit, that's where a large
portion of their new revenues come from and they'd like to maximize that
profit. They won't make much of a profit if every user of older Microsoft
technology stays with that older technology, and that's why they want you to
think the older technology is obsolete.

You can prevent them from editing and adding new rows to the tables, but
it's wide open for the user to alter the design of the tables and queries,
unless you switch to a client/server database for the back end.  If you
really don't want the users altering designs, then upgrade the back end to
one of the free express versions of the major database vendors, such as SQL
Server 2005 Express or Oracle 10g Express.

But if you keep the data in ACE tables, there are two consequences of the
users altering these designs:

1.)  It breaks your database application and either they have to fix it
themselves or they have to hire someone to fix it, and the natural
alternative is to pay you for support, since you designed it in the first
place; or

2.)  It adds functionality to your database application, which makes the
customer happier that he doesn't have to pay an expert to do something he
can do himself.

In both cases, I see upside, and not much downside -- but they've purposely
caused it themselves (and should be smart enough to recognize that, or else
you don't want them to be your customers).

Design it for code reuse, and all you need to do is to plug it into the next
applications you develop that require "can/can't" permissions.  You'll be
saving future customers money that way, because you don't have to reinvent
the wheel for every application.

User-Level Security is intended to be used to guide program flow, not secure
the data or the application's intellectual property.  By calling it
"security," many people are fooled into thinking their data and intellectual
property are secure in an Access database.  They aren't, not even slightly.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

While I agree with your main point, I think the ACCDB is the
beginning of the evolution of the new Jet engine, freed from its
relationship with Windows and legacy technologies in order that it
can evolve to fit the needs of the Access development team alone.
This is a good thing, I believe, even though I'm sad that they chose
to eliminate ULS and replication in the new format.

Can't you use the database password and code it into your VBA so
that when you make an ACCDE and encrypt it, the password won't be
accessible?

If it were really that easy, wouldn't there be a downloadable module
on the Access Web that everybody would be using already?

I haven't used ULS for securing objects in an app that I designed
from scratch for many years now (some of the apps I've inherited
that were developed by others *do* use it for security, but I
basically ignore that aspect of it!). The natural direction for me
to go has been towards using Windows security and Active Directory
in place of Jet ULS for controlling access because it makes more
sense to have all your user groups and permissions in one place.
Also, the sysadmins I've had to work with prefer keeping it all in
one place, rather than having to maintain two security systems.

That said, I haven't actually *implemented* any AD replacements to
Jet ULS -- it's still on the table for several apps!

=?Utf-8?B?YmlsbA==?= <bel***@hotmail.com> wrote in
If the ADP doesn't render the MDB obsolete, then why introduce it?

Hint: ADPs were introduced in A2K and are now deprecated by
Microsoft, whereas MDBs never were deprecated.

Why can't I delete a user?
cannot clear password with 2003 or 2007
folder security nightmare to allow .ldb file deletion
Permission should be permission, right?!?!
Two Simple Questions on Security