User Level Security in Access 2007

K

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
 
T

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
 
K

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?
 
T

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
 
J

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

Chris O'C via AccessMonster.com

Don't you think requiring establishment and maintenance in an earlier version
of Access silly and counter-productive when you can do that in Access 2007?
;)

If you don't add the buttons to the QAT, you can use the immediate window to
bring up the guis.

runcommand acCmdWorkgroupAdministrator
runcommand acCmdUserAndGroupPermissions
runcommand acCmdUserAndGroupAccounts

The first brings up workgroup administrator, the 2nd user and group
permissions, the 3rd user and group accounts. Everything the earlier
versions have as long as the db is in mdb format, not accdb.

Chris
Microsoft MVP
 
T

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
 
C

Chris O'C via AccessMonster.com

Please don't spread misinformation. It's not gone. Access 2007 supports
user level security in all mdbs, including the dbs you create in Access 2007
mdb format.

Chris
 
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
 
C

Chris O'C via AccessMonster.com

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.

You can create and maintain an Access 2007 mdb secured with user level
security *in* Access 2007. You don't need to create the mdb and secure it in
earlier versions of Access. Access 2007 also supports user level security in
mdbs developed in earlier versions of Access as said earlier.

The only security available from Access for accdb files is the database
password. If you need more individualized security than that you can convert
it to mdb or migrate the data to SQL Server or the like and use their
security features.

Chris
Microsoft MVP
 
D

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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top