User Level Security in Access 2007

[Kraut59]

In Access 2000, we have a single database with multiple tables. We allow some
users to update some tables (like user1 updates table1 only, user2 updates
table2 only) and some users cannot update any tables; and we allow some users
to view the entire database and some users can not open the database. We
also have security on queries in this database.

In Access 2007, I (and I am brand new at 2007) see database password only.
Either you get in or not.

Is there any method in 2007 to set up securities like we have in 2000?

Please point me to where I can start setting this up.

Thanks,
Kraut59

 

[Tom van Stiphout]

On Tue, 7 Oct 2008 11:56:07 -0700, Kraut59

You may want to read up on "what's new in A2007" articles. Workgroup
security is one of them and is no longer supported in .accdb-style
databases.
So you come up with a different method. Personally I prefer checking
Active Directory: the logged in user is a member of certain groups and
because of that has certain rights.

-Tom.
Microsoft Access MVP

 

[Kraut59]

"Workgroup Security", in my opinion, should be called db security. It
appears that Access 2007 has NO WAY of securing tables and/or parts of data
within a database. Within one database with Access 2000, I could give user1
update rights to the name-address table and nothing else; user2 update rights
to financial table and nothing else; and user3 update rights to medical table
and nothing else. Is the ONLY WAY to do this in Acess 2007 -MAKE EACH TABLE
from the access 2000 database A SEPARATE DATABASE in Access 2007 and then
link them?

 

[Tom van Stiphout]

On Wed, 8 Oct 2008 06:06:04 -0700, Kraut59

That would be silly and counter-productive. For example no RI across
databases.
If you are sold to Workgroup Security you can continue to use it in
A2007 as long as it was established and is maintained in a previous
version of Access.
But you see the writing on the wall, so you consider other options.
First you realize that Workgroup Security isn't as strong as you might
think. It keeps honest people honest. Dishonest ones can find the
password crackers. My AD suggestion is of the same level: keeping
honest people honest. If you want real security, SQL Server or another
server-based DBMS is your best choice. SQL Server Express Edition is
free.

-Tom.
Microsoft Access MVP

 

[Joan Wild]

If you use the mdb format in 2007 you can still use Access security.

If you want to use the accdb format, you must roll your own security.
Here's a link you might find useful:
http://www.pdtltd.co.uk/pdtl/Access2007/Access 2007 vPPC.pdf
and
http://www.utteraccess.com/forums/s...&page=1&view=collapsed&sb=5&o=&fpart=1I--Joan WildMicrosoft Access MVP"Kraut59" <[email protected]> wrote in message"Workgroup Security", in my opinion, should be called db security. It> appears that Access 2007 has NO WAY of securing tables and/or parts ofdata> within a database. Within one database with Access 2000, I could giveuser1> update rights to the name-address table and nothing else; user2 updaterights> to financial table and nothing else; and user3 update rights to medicaltable> and nothing else. Is the ONLY WAY to do this in Acess 2007 -MAKE EACHTABLE> from the access 2000 database A SEPARATE DATABASE in Access 2007 and then> link them?>> "Tom van Stiphout" wrote:>>> On Tue, 7 Oct 2008 11:56:07 -0700, Kraut59>> <[email protected]> wrote:>>>> You may want to read up on "what's new in A2007" articles. Workgroup>> security is one of them and is no longer supported in .accdb-style>> databases.>> So you come up with a different method. Personally I prefer checking>> Active Directory: the logged in user is a member of certain groups and>> because of that has certain rights.>>>> -Tom.>> Microsoft Access MVP>>>>>> >In Access 2000, we have a single database with multiple tables. We allowsome>> >users to update some tables (like user1 updates table1 only, user2updates>> >table2 only) and some users cannot update any tables; and we allow someusers>> >to view the entire database and some users can not open the database.We>> >also have security on queries in this database.>> >>> >In Access 2007, I (and I am brand new at 2007) see database passwordonly.>> >Either you get in or not.>> >>> >Is there any method in 2007 to set up securities like we have in 2000?>> >>> >Please point me to where I can start setting this up.>> >>> >Thanks,>> >Kraut59>>

 

[TDataGator]

Tom, can you point me in the direction of an example implementation showing
how one can check active directory?

I think what I am struggling with here is not the fact that user-level
security is gone from the Access 2007 format, but rather the *philosophy*
underlying what Microsoft expects us to do in its stead.

For those of us who are not programmers, but who must work with reams of
medium-security institutional data, the Access 2003 user security permissions
were a way to do meaningful work even though we weren't really entrusted to
take any bold measures inside the firewall.

I understand that Sharepoint Services is the superior solution, but even
though our institution has that server, only the executives are allowed to
use it.

You mention that SQL Server Express, a free product, would have
functionality equivalent to the user-level security. It's not a matter of
cost, we "knowledge workers" just don't have the authority to implement that.

In the meantime, all I know is I want to share my medium-security data with
co-workers, but in such a way that they can only see the parts that are
appropriate to their jobs.

And I prefer to use Access 2007 because I'm guessing it has features that
will facilitate migration to Sharepoint services, in case the IT folks one
day decide that's "safe" for general usage.

How might I do this with the active directory look-up?

Thanks,

T

 

[TDataGator]

Chris,

Thanks for your response. It sounds like you are aware of the capability of
Access 2007 to read Access 2003 databases.

Microsoft states:

"User-level security is not available for databases created in Office Access
2007 (.accdb files)."

- http://office.microsoft.com/en-us/access/HA101662271033.aspx


As I understand it, what they mean by this is that Access 2007 does not
implement user-level security in its native format, which are .accdb and
..accde files.

However, Access 2007 does recognize user-level security when it opens Access
2003 files of the .mdb type.

At this time I no longer have a natural need to work with datasets my
organization originally produced in the .mdb format.

I would like to explore the new Access 2007 format (.accdb) for future
projects, and am wondering if there is an appropriate way to restrict who can
view and update tables. There may not be.

Again, thanks for your response. All suggestions are welcome.


T

 

[David W. Fenton]

Thanks for your response. It sounds like you are aware of the
capability of Access 2007 to read Access 2003 databases.

MDB is a native format for Access 2003, whether version 2000, 2002
or 2003.

Microsoft states:

"User-level security is not available for databases created in
Office Access 2007 (.accdb files)."

- http://office.microsoft.com/en-us/access/HA101662271033.aspx


As I understand it, what they mean by this is that Access 2007
does not implement user-level security in its native format,

MDB is a native format for Access 2007.

which are .accdb and
.accde files.

That is the *new* format, not *the* native format. Both MDB and
ACCDB are native formats for Access 2007.

However, Access 2007 does recognize user-level security when it
opens Access 2003 files of the .mdb type.

At this time I no longer have a natural need to work with datasets
my organization originally produced in the .mdb format.

Why? What is there that ACCDB provides that you need that an MDB
lacks?

I would like to explore the new Access 2007 format (.accdb) for
future projects, and am wondering if there is an appropriate way
to restrict who can view and update tables. There may not be.

There is not if using ACCDB as your data store.

If you use MDB, you can still use user-level security.

Or, if you don't want to use an MDB, you can use a different back
end database engine that provides its own security.

 

[David W. Fenton]

Access 2007 mdbs, adps and accdbs are all native formats to Access
2007. xls, csv, wdb, dbf, xml and html aren't native formats but
Access can work with them. I think you want to put the mdb and
adp formats in the second category because you want to use the ACE
engine, not Jet. But Jet 4 fully supports Access 2007 mdbs.

Quibble: ACE is *Jet*, just a later version than Jet 4 (which is the
engine for A2K through A2K3).

[]

The only security available from Access for accdb files is the
database password.

This is not security at all, in my opinion. And it certainly isn't
by any means a replacement for Jet ULS.