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

Steve House wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

"Steve House" <sjhouse at hotmail dot com> wrote in message
news:(E-Mail Removed)...
> 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

"Rick Brandt" <(E-Mail Removed)> wrote in message
news:SI8Qj.9824$(E-Mail Removed)...
....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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

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 [Project MVP]
MS Project Trainer & Consultant
Visit http://project.mvps.org/faqs.htm for the FAQs

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


"Rick Brandt" <(E-Mail Removed)> wrote in message
news:SI8Qj.9824$(E-Mail Removed)...
> Steve House wrote:
>> 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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

> 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" <(E-Mail Removed)> wrote in message
news:SI8Qj.9824$(E-Mail Removed)...
> Steve House wrote:
> > 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
>
> 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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

david@epsomdotcomdotau wrote:
>> 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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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




"Rick Brandt" <(E-Mail Removed)> wrote in message
news:zK_Qj.11464$(E-Mail Removed)...
> david@epsomdotcomdotau wrote:
> >> 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, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>

david@epsomdotcomdotau wrote:
> 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..

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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

Regards
(david)


"Rick Brandt" <(E-Mail Removed)> wrote in message
news:G%XSj.83$(E-Mail Removed)...
> david@epsomdotcomdotau wrote:
> > 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..
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>