Word 2007 Missing User Level Securitty - ARRRGGGGHHHH What were they thinking?

[Steve House]

Just discovered Access 2007 no longer supports user level security. What in
the world are they doing here? Serious real-world applications very
frequently need to restrict user's access to some but not all of the data in
a system. For example, in a human resources application it is not at all
unusual for clerical staff to need to view and update all the various
elements of an employee's record EXCEPT for salary information while
managers and only the managers should have the ability to view and edit the
salary fields. And there can't be any backdoors allowed so that someone who
who is allowed to only open a form that contains some of the fields in a
data table would be able to view information they're not supposed to be
privy to by opening a datasheet view of the same table. Removing the
ability to control exactly what users of the system are able to view and
change seriously cripples Access usability as a line-of-business database
application development platform. What am I missing here? Are there any
workarounds to establish object level, table level, and field level
priviledges in Access 2007 (other than sticking with Access 2003 or earlier
file format) or are we stuck with an all or nothing scheme where anyone who
is allowed to open the database at all has free rein to do anything in it
they want to? I confess I am completely gob-smacked that MS could have done
something so incredibly counter-productive!

Steve House
A 20-year veteran of database development in panic mode

 

[Rick Brandt]

Just discovered Access 2007 no longer supports user level security. What
in the world are they doing here? Serious real-world
applications very frequently need to restrict user's access to some
but not all of the data in a system. For example, in a human
resources application it is not at all unusual for clerical staff to
need to view and update all the various elements of an employee's
record EXCEPT for salary information while managers and only the
managers should have the ability to view and edit the salary fields.
And there can't be any backdoors allowed so that someone who who is
allowed to only open a form that contains some of the fields in a
data table would be able to view information they're not supposed to
be privy to by opening a datasheet view of the same table. Removing
the ability to control exactly what users of the system are able to
view and change seriously cripples Access usability as a
line-of-business database application development platform. What am
I missing here? Are there any workarounds to establish object level,
table level, and field level priviledges in Access 2007 (other than
sticking with Access 2003 or earlier file format) or are we stuck
with an all or nothing scheme where anyone who is allowed to open the
database at all has free rein to do anything in it they want to? I
confess I am completely gob-smacked that MS could have done something
so incredibly counter-productive!
Steve House
A 20-year veteran of database development in panic mode

Access ULS security was never very secure and the prevailing theory is that
since a file based system can never be made secure that there was no point
continuing the charade. I assume the lawyers had a hand in the decision.

You are aware that there are utilities one can find on the internet that
will defeat ULS aren't you? They have been around for several years now.

 

[Keith Wilby]

Just discovered Access 2007 no longer supports user level security. What
in the world are they doing here? Serious real-world applications very
frequently need to restrict user's access to some but not all of the data
in a system. For example,

There's not a lot of point in trying to teach grandma how to suck eggs ;-)

If you keep your files in the mdb format then you can carry on using ULS in
A2007.

Keith.
www.keithwilby.com

 

[Steve House]

....in panic mode

Access ULS security was never very secure and the prevailing theory is
that since a file based system can never be made secure that there was no
point continuing the charade. I assume the lawyers had a hand in the
decision.

You are aware that there are utilities one can find on the internet that
will defeat ULS aren't you? They have been around for several years now.

Thanks - Yep, I'm aware that it's not foolproof but many of my clients don't
need to protect their data so much from hackers as they need to protect it
from snoopy file clerks and sales-droids who are not Access literate. Oh
well, the good Lord giveth and taketh away <grin>.

 

[Steve House]

LOL - just realized I had typed "Word" instead of "Access" in the orginal
message header/subject line ... yesterday was NOT a good day! ROFL.

 

[Guest]

the prevailing theory is that since a file based system

can never be made secure that there was no point

Barf... File servers aren't secure? Word and Excel files on
a file server aren't secure? SMB servers aren't secure, but
SQL servers are secure? Multics wasn't a secure OS?
Global Policy Objects and User Profiles stored and
distributed by the same mechanism aren't secure?

Access predates Windows security, which is why it had
it's own security model. It builds on a database model
that pre-dates both relational databases and SQL. It has
never been updated or kept current. The underlying OS
database system has never been updated or kept current.

I can imagine good business reasons not to continue with
the OS-based database server product, and good business
reasons not to SQL enable the OS-based database server
product, but the words "can never be" don't figure in any
of those reasons.

Regarding the OP specific question, you maintain object
-level security by putting the objects in different file objects,
because the OS database primitives don't support record
based security. The underlying OS database system predates
Windows security, and has never been updated or kept
current. Putting the objects in different file objects breaks
Declarative Referential Integrity, because the underlying OS
database system predates relational databases, and has never
been updated or kept current.

Microsoft has announced improved database primitives in
the file system, and/or improved file systems in the database,
but all of these products have died or been killed. AFAIK,
none of these products were ever killed because of problems
in the security model. Building a server that responds to
SQL requests is not inherently more secure than building a
server that responds to physical record requests. The SQL
service object is not inherently more secure by not being
included in the base Windows distribution.

/end rant/

(david)

 

[Rick Brandt]

the prevailing theory is that since a file based system
can never be made secure that there was no point

Barf... File servers aren't secure? Word and Excel files on
a file server aren't secure? [snip rest]

Please, do you really not understand what I wrote (in the context which it
was written) are you just trolling?

To make the snippage of that one phrase a bit more to your liking...

....the prevailing theory is that since a file based database system cannot
be made secure from people who have physical access to the file, that there
was no point...

I am perfectly aware that files can be secured by prohibiting access to them
and that is exactly the security model that server databases use. Users
have access to data served to them from the engine, but have no access to
the physical files where the data is actually stored.

Notice I also removed the "never" but most people realize a bit of
rhetorical license when they see it used in the manner I did.

The fact of the matter is that for the last few years the word "security" in
"Access User Level Security" (the topic being discussed) has been a complete
falsehood and I suspect that MS was getting increasingly uncomfortable with
the term.

Some feel they could have just changed the name to reflect more of a "user
identification/customization system", but for whatever reasons, they chose
to just drop the model and suggest that developers who need "security" move
to one of the SQL Server alternatives. I think that is a perfectly
reasonable decision.

 

[Guest]

Please, ... are you just trolling?

People who actually need object-level security should
use the method I proposed.

...do you really not understand what I wrote (in the context
which it was written) ..

It is common to see here a fairly basic misunderstanding
of the way the Jet engine works "you have to download the file",
based on a fairly basic misunderstanding of the way Windows
handles database records "It's file based".

Many people don't even realise that Windows has an integral
database system, which is record based, and has been since
DOS 3, and it is used by Jet.

I am perfectly aware that files can be secured by prohibiting
access to them and that is exactly the security model that server
databases use. Users have access to data served to them from
the engine, but have no access to the physical files where the data
is actually stored.

By contrast, you seem to think that the File Server serves files to Jet?

This leads to mistakes/ non-sequitur's like

a file based database system cannot be made secure

Which is just as false for SQL Server as it is for Jet and just
as irrelevant for Jet as it is for SQL Server.

.. the prevailing theory is that since a file .. system
cannot be made secure ..

I'm not barfing at the idea that Jet is insecure: only at the
idea that file systems can't be made secure, and at the
matching idea that it has any theoretical relevance to a
record-based network database system.

Since you make a point of "physical access to the file" let me
add that "physical access to the file" is no more a theoretical
limitation of the Windows database system than it is of SQL
Server/MSDE. In neither case is "physical access to the file"
a requirement for the database system to work.

Since I'm sure you'll admit that point, let me rephrase it:
In no way is "logical file permission" ever a theoretical limit
to security in the Jet/Windows database system.

"...the prevailing theory is that since a file based database
" system cannot be made secure from people who have
" physical access to the file..."

That "prevailing theory" is both irrelevant and irrelevant. Group
Policy objects are secure enough to be useful, secured at
both object and user level. And there is no theoretical reason
why Jet/Access users need file permissions any more than
SQL Server users need file permissions.

" ... cannot be made secure ... "

Object-level and user-level security in the native Windows
database primitives is entirely an argument about implementation,
marketing, development resources, support, maintenance,
and incremental functionality,
not at all an argument about what is possible.

Regards,
(david)

the prevailing theory is that since a file based system
can never be made secure that there was no point

Barf... File servers aren't secure? Word and Excel files on
a file server aren't secure? [snip rest]

Please, do you really not understand what I wrote (in the context which it
was written) are you just trolling?

To make the snippage of that one phrase a bit more to your liking...

...the prevailing theory is that since a file based database system cannot
be made secure from people who have physical access to the file, that there
was no point...

I am perfectly aware that files can be secured by prohibiting access to them
and that is exactly the security model that server databases use. Users
have access to data served to them from the engine, but have no access to
the physical files where the data is actually stored.

Notice I also removed the "never" but most people realize a bit of
rhetorical license when they see it used in the manner I did.

The fact of the matter is that for the last few years the word "security" in
"Access User Level Security" (the topic being discussed) has been a complete
falsehood and I suspect that MS was getting increasingly uncomfortable with
the term.

Some feel they could have just changed the name to reflect more of a "user
identification/customization system", but for whatever reasons, they chose
to just drop the model and suggest that developers who need "security" move
to one of the SQL Server alternatives. I think that is a perfectly
reasonable decision.

 

[Rick Brandt]

That "prevailing theory" is both irrelevant and irrelevant. Group
Policy objects are secure enough to be useful, secured at
both object and user level. And there is no theoretical reason
why Jet/Access users need file permissions any more than
SQL Server users need file permissions.

Please describe an Access/Jet application where users of the database do not
have access to the file containing the data. By "access to the file" I mean
they are free to open the file, copy the file, take that copy with them,
etc..

 

[Guest]

I don't have to argue about the parts where I agree with you :~)

Regards
(david)

 

[Glen Almcrantz]

I'm new to Access 2007, but I've done a lot of business grade systems development using more robust database systems. Here's my take:

Previous MS Access user permission security gave the appearance of security but was not really secure at all. Access 2007 preserves the older version's user security capabilities if you don't upgrade the file format of existing databases.

Today the Microsoft party line is that if you want user security, use SQL Server because it is truly secure. I believe it's easy to use Access 2007 to create an application with the databases hosted on SQL Server instead of just sitting open in the file system (mdb/accdb files) where everyone can access it. SQL Server Express 2005 is a free solution and while yes it does require more a lot more knowledge to install and operate than Access, it does provide real security. Be sure to also install the SQL Server Management Studio Express (SSMSE) component as the free SQL Server Express DB is intended only as a standalone database engine to be embedded and redistributed by software vendors without any tools to manage it. SQL Server scales much better than Access and it's all free.

The real benefit here is that you can build powerful applications with Access and also make them secure without having to migrate to a more comprehensive/complex application development toolkit for the user front-end. That's why I'm looking at it now.

There's a new 2008 version of SQL Server Express but I haven't explored it yet. Otherwise the SQL Express 2005 version is still available to my knowledge.

Good Luck.