Access 2007

[Diane]

I inherited an Access 2000 database with user level security. Each PC using
this database has a security.mdw file on it. I'm installing new PC's and I
don't know how to apply this security for Access 2007. I'd like to remove
all the security and recreate it in the new version if possible but I'm
scared of screwing it up! Can someone provide a little guidance? Thanks,
Diane

 

[Scott McDaniel]

I inherited an Access 2000 database with user level security. Each PC using
this database has a security.mdw file on it. I'm installing new PC's and I
don't know how to apply this security for Access 2007. I'd like to remove
all the security and recreate it in the new version if possible but I'm
scared of screwing it up! Can someone provide a little guidance? Thanks,
Diane

You can use a 2000-format database, with security enabled, on the 2007 machines IF you maintain the .mdb/.mde format. If
you convert it to 2007, however, all security features are removed. This copy/paste is from the Office website
(http://office.microsoft.com/en-us/access/HA101980471033.aspx):

<start>

Access 2007 does not provide user-level security for databases that are created in the new file format (.accdb and
..accde files). However, if you open a database from an earlier version of Access in Access 2007 and that database has
user-level security applied, those settings will still function.

If you convert a database that has user-level security from an earlier version of Access to the new file format, Access
strips out all security settings automatically, and the rules for securing an .accdb or .accde file apply.

<end>

Joan Wild has a writeup about removing security: www.jmwild.com

Be careful about removing ULS, however, unless you are absolutely sure that doing so won't cause other issues. If ULS is
being used as a navigation aid (i.e. directing a user to a specific form, or allowing/disallowing certain users access
to certain Forms/Reports) or is being used with an "audit trail" setup (i.e. "stamping" a record with the username when
a new record is entered, or when changes are made to existing records) you may find that removing ULS can cause
significant problems.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Diane]

Scott, Thanks for the info. Diane

You can use a 2000-format database, with security enabled, on the 2007
machines IF you maintain the .mdb/.mde format. If
you convert it to 2007, however, all security features are removed. This
copy/paste is from the Office website
(http://office.microsoft.com/en-us/access/HA101980471033.aspx):

<start>

Access 2007 does not provide user-level security for databases that are
created in the new file format (.accdb and
.accde files). However, if you open a database from an earlier version of
Access in Access 2007 and that database has
user-level security applied, those settings will still function.

If you convert a database that has user-level security from an earlier
version of Access to the new file format, Access
strips out all security settings automatically, and the rules for securing
an .accdb or .accde file apply.

<end>

Joan Wild has a writeup about removing security: www.jmwild.com

Be careful about removing ULS, however, unless you are absolutely sure
that doing so won't cause other issues. If ULS is
being used as a navigation aid (i.e. directing a user to a specific form,
or allowing/disallowing certain users access
to certain Forms/Reports) or is being used with an "audit trail" setup
(i.e. "stamping" a record with the username when
a new record is entered, or when changes are made to existing records) you
may find that removing ULS can cause
significant problems.


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Nando]

Hi, I'm confused. I'm still an Access XP user who may be migrating some
projects to Access 2007, but could you please confirm about this security on
2007? Is it true MS dropped the security for the new format?

 

[Douglas J. Steele]

Hi, I'm confused. I'm still an Access XP user who may be migrating some
projects to Access 2007, but could you please confirm about this security
on 2007? Is it true MS dropped the security for the new format?

As Scott says, there's no user-level security if you use the new accdb
format. If you leave your application in mdb format, ULS is still usable.

 

[Nando]

As Scott says, there's no user-level security if you use the new accdb
format. If you leave your application in mdb format, ULS is still usable.

Thanks Douglas! Pardon my ignorance, but isn't that odd! Why did MS remove
such an important feature? So how do they address the security needs of the
Access users on a multi-user environment? I see this as a major change
(isn't it?). Or perhaps they are forcing for client-server scheme (SQL
Server). I'm short circuiting...

 

[Scott McDaniel]

Thanks Douglas! Pardon my ignorance, but isn't that odd! Why did MS remove
such an important feature? So how do they address the security needs of the
Access users on a multi-user environment? I see this as a major change
(isn't it?). Or perhaps they are forcing for client-server scheme (SQL
Server). I'm short circuiting...

MS beefed up the password and encryption features of Access. In the past, the database password feature of Access was
weak and easily broken. Supposedly, in 2007, this is much stronger and therefore would be the replacement for the
security features in ULS (which was also very easily broken, BTW). What's NOT replaced, however, is the login ability,
the ability to use ULS as a navigation aid, the CurrentUser function etc etc ... now, if you want to do this, you'll
need to build your own.

You've probably already seen this page, but here's the entry point for the MS site regarding 2007 security:
http://office.microsoft.com/en-us/access/HA101980471033.aspx

A few links, stolen <g> from a previous post by Joan Wild:
http://www.utteraccess.com/forums/s...=373275&page=1&view=collapsed&sb=5&o=&fpart=1
http://groups.google.ca/group/micro...ea71aeab394?lnk=st&q=&rnum=1#a9856ea71aeab394
Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Nando]

MS beefed up the password and encryption features of Access. In the past,
the database password feature of Access was
weak and easily broken. Supposedly, in 2007, this is much stronger and
therefore would be the replacement for the
security features in ULS (which was also very easily broken, BTW). What's
NOT replaced, however, is the login ability,
the ability to use ULS as a navigation aid, the CurrentUser function etc
etc ... now, if you want to do this, you'll
need to build your own.

Thanks Scott! Prior 2007 I could set permissions to objects through UI by
just indicating the logins. I could also extend security by using the
function CurrentUser() and disable fields on forms for example. So I suppose
Access 2007 will still allow to create logins and passwords, but those
login-accounts cannot no longer be associated with objects to restrict
access to them (if I understood correctly).

But how does Access (or the developer) can deny access to records or objects
then? That seemed to be pretty straight forward before (because it followed
the same concept as setting permissions for folder or files in an OS). So
how is it then? Is it just a database password then? ew! Hopefully I'm
missing something, because it makes no sense.

 

[Rick Brandt]

Thanks Scott! Prior 2007 I could set permissions to objects through UI by just
indicating the logins. I could also extend security by using the function
CurrentUser() and disable fields on forms for example. So I suppose Access
2007 will still allow to create logins and passwords, but those login-accounts
cannot no longer be associated with objects to restrict access to them (if I
understood correctly).

But how does Access (or the developer) can deny access to records or objects
then? That seemed to be pretty straight forward before (because it followed
the same concept as setting permissions for folder or files in an OS). So how
is it then? Is it just a database password then? ew! Hopefully I'm missing
something, because it makes no sense.

If you need to protect the data from non-users then use network security. If
you need to protect the data from users then don't store it in an Access/Jet
file. Use a server database with real security.

This has always been true. Access 2007 is just making it clear that Microsoft
admits to that truth.

 

[Nando]

If you need to protect the data from non-users then use network security.

Makes sense. "Anyone" could delete the MDB file. "Anyone" could read/modify
records by manipulating the MDB file, it is just a file. A not complicated
job for hacker.

If you need to protect the data from users then don't store it in an
Access/Jet file. Use a server database with real security.

Ouch! I know that, but it still hurts!

An MDB on a network folder is like having a file on the street (waiting for
some one to try to destroy it or decode/read/change its content), in
contrast to having the file and its contents policed behind a window/clerk
(SQL Server) the perfect and ideal way. However, I just cannot beleive
Microsoft is retracting themselves now by decomission the ULS design and
pushing SQL Server this way.

At some extend, ULS is not too bad. If current industry encryption
techniques are as good as they claim they are, then using that encryption to
protect data (like an MDB file) in combination with object security with ULS
should be OK. User's passwords could act as an intermediate Access-generated
key that could combine with Access' one to create another unique private
keys that allows to open the file. And since Access will be the only one
opening the file with the last generated key, then it has full control on
the file and the its engine could police ULS access to records and internal
file objects. And whoever want to read/modify the file without the right key
or with hacking will be going though years of cracking. Yes, it still raises
the comparisson between "secured" and "entrusted" data, but some users don't
really need a server to host the data.

Encryption should be a sufficient solution I think. It sounds possible, but
I don't know if my theories are actually practical. I do not know the
performace benchmarks of such solution. Perhaps they are not enough to be
implement as an Access database product. I'm wondering how other database
makers deal with this.

 

[Scott McDaniel]

Makes sense. "Anyone" could delete the MDB file. "Anyone" could read/modify
records by manipulating the MDB file, it is just a file. A not complicated
job for hacker.


Ouch! I know that, but it still hurts!

An MDB on a network folder is like having a file on the street (waiting for
some one to try to destroy it or decode/read/change its content), in
contrast to having the file and its contents policed behind a window/clerk
(SQL Server) the perfect and ideal way. However, I just cannot beleive
Microsoft is retracting themselves now by decomission the ULS design and
pushing SQL Server this way.

There's always SQL Server Express, which is free and freely redistributable:

http://msdn.microsoft.com/vstudio/express/sql/
http://www.microsoft.com/sql/editions/express/default.mspx

Describes some of the limitations of SQL Express vs full SQL Server:
http://help.alentus.com/article.aspx?id=10471&cNode=5Y4P1X

While not a dropin replacement for Jet, it's pretty easy to configure and use, even for non-technical users.

At some extend, ULS is not too bad. If current industry encryption
techniques are as good as they claim they are, then using that encryption to
protect data (like an MDB file) in combination with object security with ULS
should be OK. User's passwords could act as an intermediate Access-generated
key that could combine with Access' one to create another unique private
keys that allows to open the file. And since Access will be the only one
opening the file with the last generated key, then it has full control on
the file and the its engine could police ULS access to records and internal
file objects. And whoever want to read/modify the file without the right key
or with hacking will be going though years of cracking. Yes, it still raises
the comparisson between "secured" and "entrusted" data, but some users don't
really need a server to host the data.

Encryption should be a sufficient solution I think. It sounds possible, but
I don't know if my theories are actually practical. I do not know the
performace benchmarks of such solution. Perhaps they are not enough to be
implement as an Access database product. I'm wondering how other database
makers deal with this.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Nando]

There's always SQL Server Express, which is free and freely
redistributable:

http://msdn.microsoft.com/vstudio/express/sql/
http://www.microsoft.com/sql/editions/express/default.mspx

Describes some of the limitations of SQL Express vs full SQL Server:
http://help.alentus.com/article.aspx?id=10471&cNode=5Y4P1X

While not a dropin replacement for Jet, it's pretty easy to configure and
use, even for non-technical users.

Thank you Scott!!

 

[Joan Wild]

Thanks Scott! Prior 2007 I could set permissions to objects through UI by
just indicating the logins. I could also extend security by using the
function CurrentUser() and disable fields on forms for example. So I suppose
Access 2007 will still allow to create logins and passwords, but those
login-accounts cannot no longer be associated with objects to restrict
access to them (if I understood correctly).

If you stick with the mdb format in 2007 (not the new accdb), then all of that is still there for you.

 

[Nando]

If you stick with the mdb format in 2007 (not the new accdb), then all of
that is still there for you.

Thanks Joan! It's nice to now that at least Microsoft made Access 2007
backward compatible. Question: the command line to pass the MDB and MDW
files remains the same then right?

 

[David W. Fenton]

If you need to protect the data from non-users then use network
security. If you need to protect the data from users then don't
store it in an Access/Jet file. Use a server database with real
security.

This has always been true. Access 2007 is just making it clear
that Microsoft admits to that truth.

Yes, but it ignores a very important use for ULS, which is
conditional access to application functionality based on user group
membership. That's not a security function, and, theoretically, one
could use Active Directory to replicate the same thing, but I don't
know many sysadmins who are going to be too thrilled with putting
your Access user groups into AD, unless the groups you're using
actually already reflect data that's in AD (one of my clients is
exactly in this situation, where the Organizational Units are
replicated in the System.mdw).

 

[David W. Fenton]

If you stick with the mdb format in 2007 (not the new accdb), then
all of that is still there for you.

Is the stronger encryption used by A2K7 for encrypting any MDB?

 

[Joan Wild]

Thanks Joan! It's nice to now that at least Microsoft made Access 2007
backward compatible. Question: the command line to pass the MDB and MDW
files remains the same then right?

Yes it does.

 

[Joan Wild]

Is the stronger encryption used by A2K7 for encrypting any MDB?

I don't know, David. I understand that the database password feature is improved.

 

[Rick Brandt]

If you need to protect the data from non-users then use network
security. If you need to protect the data from users then don't
store it in an Access/Jet file. Use a server database with real
security.

This has always been true. Access 2007 is just making it clear
that Microsoft admits to that truth.

Yes, but it ignores a very important use for ULS, which is
conditional access to application functionality based on user group
membership. [snip]

Agreed, but implementing ULS just to assign conditional access at a group level
is (to me) paying ten dollars for a coke. Rolling your own for that is not only
easier than ULS, but WAY easier to maintain.

 

[Jeff Conrad [MSFT]]

in message:

Is the stronger encryption used by A2K7 for encrypting any MDB?

I believe it's only for the ACCDB file format David.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com