Rules of thumb

[.Len B]

I have split databases before and I have put passwords
on them. I have never applied user-level security though.

At the moment I have a project that will require user-
level security and I want to split the db.

Is there a better/best time to split and a time to do
the security thing? What is the rule of thumb and are
there any gotchas to avoid?

TIA

 

[(PeteCresswell)]

Per .Len B:

I have split databases before and I have put passwords
on them. I have never applied user-level security though.

At the moment I have a project that will require user-
level security and I want to split the db.

Is there a better/best time to split and a time to do
the security thing? What is the rule of thumb and are
there any gotchas to avoid?

This does not answer your question directly, but my experience is
that MS Access security is tb avoided if possible.

It's not that strong and there's a hassle factor in user
education and password/permission maintenance.

Since you are splitting the DB (and it's probably living on a
file server somewhere) I would check to see if LAN security will
suffice with the users. i.e. Put all the users in a security
group and give the group Add/Change/Delete permission to the
directory where the back end lives.

Others may differ, but I'd say that if the users can live with
LAN security it's the better choice.

 

[.Len B]

| Per .Len B:
| >I have split databases before and I have put passwords
| >on them. I have never applied user-level security though.
| >
| >At the moment I have a project that will require user-
| >level security and I want to split the db.
| >
| >Is there a better/best time to split and a time to do
| >the security thing? What is the rule of thumb and are
| >there any gotchas to avoid?
|
| This does not answer your question directly, but my experience is
| that MS Access security is tb avoided if possible.
|
| It's not that strong and there's a hassle factor in user
| education and password/permission maintenance.
|
| Since you are splitting the DB (and it's probably living on a
| file server somewhere) I would check to see if LAN security will
| suffice with the users. i.e. Put all the users in a security
| group and give the group Add/Change/Delete permission to the
| directory where the back end lives.
|
| Others may differ, but I'd say that if the users can live with
| LAN security it's the better choice.
|
| --
| PeteCresswell


Hi Pete,
That's my usual technique and I guess its why I haven't done it
before now. Access lives on the Terminal Servers and the mdb(s)
will live on the dfs file servers.

This particular db will contain sensitive info which some eyes
with legitimate access to other areas ought not see. It will
also have processes which only some users will be permitted to
invoke.

To further complicate matters, other back end databases will be
called upon. (a couple of linked tables)

The idea has occurred to have multiple front ends and I haven't
thought it through but the maintenance prospect doesn't thrill.

Your reply also prompts another question. I usually have BE & FE
in the same folder. Anything to consider there?

 

[John W. Vinson]

Hi Pete,
That's my usual technique and I guess its why I haven't done it
before now. Access lives on the Terminal Servers and the mdb(s)
will live on the dfs file servers.

This particular db will contain sensitive info which some eyes
with legitimate access to other areas ought not see. It will
also have processes which only some users will be permitted to
invoke.

To further complicate matters, other back end databases will be
called upon. (a couple of linked tables)

If you want to protect the data from anyone skilled and serious, Access
workgroup security isn't your best choice. It can be cracked - a web search
can easily find "services" (some legitimate, some not) to break .mdw file
security. "Locks keep out honest people".

Your best bet may be to move the backend into a properly secured SQL Server
instance (SQL Express can, with some effort, be such).

If you do want to use Workgroup Security, and you're ok with .mdb format and
A2003 to implement it, get the Microsoft Access 2000 Security FAQ:

http://support.microsoft.com/kb/207793/en-us

Print it out. Read it, thoroughly and carefully. Get a good night's sleep.
Read it AGAIN, even more carefully. Follow the steps scrupulously, and be sure
you understand what each step is doing. It's not for the fainthearted!

 

[(PeteCresswell)]

Per .Len B:

This particular db will contain sensitive info which some eyes
with legitimate access to other areas ought not see. It will
also have processes which only some users will be permitted to
invoke.

To further complicate matters, other back end databases will be
called upon. (a couple of linked tables)

I'm no SQL Server expert - although I've written one fairly large
application using it as a back end, complete with multitudinous
stored procedures.

Having said that, those requirements are saying
"S-Q-L--S-e-r-v-e-r" to me. If you can document the inadequacy
of MS Access' security to their satisfaction, maybe the users
will spring for the 35% or so (my experience) additional man
hours to put the back end on SQL Server.

I can't cite, but I'd bet next month's mortgage money that a
determined, skilled user can defeat any MS Access security
scheme. One of our more illustrious contributors said to the
effect of: "Access security is a brown paper bag. OTOH, the
Mafia enforce SQL Server security."

The idea has occurred to have multiple front ends and I haven't
thought it through but the maintenance prospect doesn't thrill.

Your reply also prompts another question. I usually have BE & FE
in the same folder. Anything to consider there?

I deploy a "source" front end in the same directory as the back
end, but nobody ever executes it. Instead, my deployment
consists of copying a shortcut to each users desktop. The
shortcut calls a .BAT file that copies a fresh copy of the front
end down to the user's "temp" directory when needed and executes
it there.

I've never had concurrent users executing the same front end, so
I don't have any real experience - but the reason I haven't is
that it sounds like such a bad idea that I've never even tried
it. Report printer setup conflicts, for one, but there's
plenty more...

 

[John W. Vinson]

I can't cite, but I'd bet next month's mortgage money that a
determined, skilled user can defeat any MS Access security
scheme. One of our more illustrious contributors said to the
effect of: "Access security is a brown paper bag. OTOH, the
Mafia enforce SQL Server security."

LOL!

Microsoft database password security (all that's available in .accdb) is a
$5.99 bicycle lock. Five minutes with the combination, or five seconds with a
good pair of pliers or a bolt cutter and it's gone.

Workgroup security is a good Yale padlock. You can break it with a good
boltcutter.

SQL/Server security - DONE CORRECTLY, which is not a given!!! - is a bank
vault. Yes, it can be broken... but...

 

[Armen Stein]

Hi Len,

I agree with the other posters who are saying that this is starting to
look like a job for SQL Server. Even the free Express version can be
made much more secure than Access if configured properly.

Regarding how to use Access as a front-end to SQL Server:

I've written a PowerPoint presentation on techniques for using Access
as a client-server front-end to SQL Server databases. It's called
"Best of Both Worlds" at www.JStreetTech.com/Downloads. It includes
some thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.

If you do decide to stick with an Access BE, at least have a good
relinking mechanism. You're welcome to use our free J Street Access
Relinker at:
http://www.jstreettech.com/downloads

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[.Len B]

| || Per .Len B:
|| >I have split databases before and I have put passwords
|| >on them. I have never applied user-level security though.
|| >
|| >At the moment I have a project that will require user-
|| >level security and I want to split the db.
|| >
|| >Is there a better/best time to split and a time to do
|| >the security thing? What is the rule of thumb and are
|| >there any gotchas to avoid?
||
|| This does not answer your question directly, but my experience is
|| that MS Access security is tb avoided if possible.
||
|| It's not that strong and there's a hassle factor in user
|| education and password/permission maintenance.
||
|| Since you are splitting the DB (and it's probably living on a
|| file server somewhere) I would check to see if LAN security will
|| suffice with the users. i.e. Put all the users in a security
|| group and give the group Add/Change/Delete permission to the
|| directory where the back end lives.
||
|| Others may differ, but I'd say that if the users can live with
|| LAN security it's the better choice.
||
|| --
|| PeteCresswell
|
|
| Hi Pete,
| That's my usual technique and I guess its why I haven't done it
| before now. Access lives on the Terminal Servers and the mdb(s)
| will live on the dfs file servers.
|
| This particular db will contain sensitive info which some eyes
| with legitimate access to other areas ought not see. It will
| also have processes which only some users will be permitted to
| invoke.
|
| To further complicate matters, other back end databases will be
| called upon. (a couple of linked tables)
|
| The idea has occurred to have multiple front ends and I haven't
| thought it through but the maintenance prospect doesn't thrill.
|
| Your reply also prompts another question. I usually have BE & FE
| in the same folder. Anything to consider there?
| --
| Len
| ______________________________________________________
| remove nothing for valid email address.
|
|

Phew, thanks guys. Are you trying to scare me? It's working.

It looks like I've got a bit of reading to do.

The domain runs on SBS2003 and I think I've seen a SQL server
there; not sure of version but I think it keeps some monitoring
data for SBS itself.

Nevertheless, I'm still inclined to mdb because
* the budget for man hours and
* the fact that, as John said "Locks keep out honest people"
and I think that it is 'honest' people I'm dealing with.

Authorised users wouldn't really want to hack in for curiosity
sake but if the door was open they might peek. OTOH, a hacker
would first have to logon to the domain as a user with access to
the FE folder and also know a password for the db or at least
that the db is there and is juicy enough to be a target.
Am I being naive here?

The jewels being protected are medical records of profoundly
disabled children - not an attractive target I think, at least
not like credit card or financial info. Users are medical or
admin staff. The organization is a 'not for profit' and the
budget is always tight.

Thanks for the advice and the links. I'll take a deep breath and
follow them.

Thanks once again Armen, John and Pete.

 

[Armen Stein]

Phew, thanks guys. Are you trying to scare me? It's working.

I understand. It's like when a police officer looks at your house and
tells you all the ways it's vulnerable. Doesn't mean it will happen,
but it makes you think. We didn't mean to scare you, but we wanted
you to know how easily a burglar could break the locks you're planning
to use.

The domain runs on SBS2003 and I think I've seen a SQL server
there; not sure of version but I think it keeps some monitoring
data for SBS itself.

Depends on the version of SBS. The higher level includes SQL Server.
If you got non-profit pricing you probably got the higher version. But
remember that the Express version is a free download - you just need
to figure out how to install and configure it. More reading.

Nevertheless, I'm still inclined to mdb because
* the budget for man hours and
* the fact that, as John said "Locks keep out honest people"
and I think that it is 'honest' people I'm dealing with.

You know, you can always start with an MDB back-end and upgrade to SQL
Server later. There would be some rework, but you'd be more familiar
with your system by then and could focus on the SQL-specific
requirements.

Authorised users wouldn't really want to hack in for curiosity
sake but if the door was open they might peek. OTOH, a hacker
would first have to logon to the domain as a user with access to
the FE folder and also know a password for the db or at least
that the db is there and is juicy enough to be a target.
Am I being naive here?

The jewels being protected are medical records of profoundly
disabled children - not an attractive target I think, at least
not like credit card or financial info. Users are medical or
admin staff. The organization is a 'not for profit' and the
budget is always tight.

Well, I don't think you're being naive. When you said "sensitive"
information before, we probably imagined salaries and credit cards.
Medical information is still sensitive, but you're probably correct
that it isn't as much of a draw. You do need to use a prudent level
of security so that people can't just see it at will, and also meet
HIPAA requirements.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[(PeteCresswell)]

Per Armen Stein:

Even the free Express version can be
made much more secure than Access if configured properly.

Speaking as one with only limited knowledge of SQL Server:


"Even"?.... I thought the Express version was full-blown SQL
Server, but throttled to limit concurrent users.

Sounds like there are other considerations....

??

 

[(PeteCresswell)]

Per .Len B:

The jewels being protected are medical records of profoundly
disabled children - not an attractive target I think, a

I wonder if HIPPA (SP?) spells out any requirements in that vein.

 

[John W. Vinson]

The jewels being protected are medical records of profoundly
disabled children - not an attractive target I think, at least
not like credit card or financial info. Users are medical or
admin staff. The organization is a 'not for profit' and the
budget is always tight.

Before you do much more, DO read up on HIPAA requirements. These are pretty
stringent, involve very large fines, and (in my opinion) would prohibit
reliance on just MDB security. I'd hate to see your nonprofit end up in court
over this issue!!!

 

[Douglas J. Steele]

Per Armen Stein:

Speaking as one with only limited knowledge of SQL Server:


"Even"?.... I thought the Express version was full-blown SQL
Server, but throttled to limit concurrent users.

Sounds like there are other considerations....

Essentially, any time a user can get direct access to the data file, there's
usually a way to get at the data.

Since Express is installed on the workstation, the .MDF files are
accessible.

 

[David W. Fenton]

i.e. Put all the users in a security
group and give the group Add/Change/Delete permission to the
directory where the back end lives.

Strictly speaking, DELETE permission is not necessary.

 

[David W. Fenton]

If you can document the inadequacy
of MS Access' security to their satisfaction, maybe the users
will spring for the 35% or so (my experience) additional man
hours to put the back end on SQL Server.

That number seems quite high to me, particularly for an app that
appears to be in the planning stages such that it won't need to be
ported from a Jet back end, but can be designed from the ground up
for SQL Server.

 

[David W. Fenton]

I agree with the other posters who are saying that this is
starting to look like a job for SQL Server. Even the free Express
version can be made much more secure than Access if configured
properly.

Your comment implies that SQL Server Express lacks security features
that full SQL Server provides. What would those be?

 

[David W. Fenton]

Essentially, any time a user can get direct access to the data
file, there's usually a way to get at the data.

Since Express is installed on the workstation, the .MDF files are
accessible.

Uh, why would it be installed on the workstation?

 

[David W. Fenton]

Am I being naive here?

I don't think so. In my opinion, a large number of "security"
problems are actually personnel problems in disguise. You have to
provide people access to the data to do their work, and you have to
trust them at a certain level. Then the question becomes how much
you trust them, and if you find yourself building security walls
into your app, you've basically admitted you don't trust your
employees.

Now, in some situations, such as retail point-of-sale apps, that's a
completely valid assumption and you build your app accordingly.

But in most small businesses, you're protected from the professional
hackers but you're always going to be exposed to potentially
non-trustworthy employees. What you do about that depends on how
well-managed the company is, in my opinion -- a well-managed company
won't long continue to employee a worker who can't be trusted.

 

[Douglas J. Steele]

Uh, why would it be installed on the workstation?

I thought when you installed Access, you were given the opportunity to
install Express to be used instead of Jet and that Access installed it on
your local machine.

 

[(PeteCresswell)]

Per David W. Fenton:

That number seems quite high to me, particularly for an app that
appears to be in the planning stages

It might be more a comment on my abilities than anything else.

But I found that, without being able to interactively debug the
code, developing stored procedures took a disproportionately long
time compared to doing the same thing in VB.

Also, with stored procedures, ADO recordsets seem tb the norm and
there's more coding there. Mitigated once one develops a set of
wrapper routines... but more coding nevertheless.