Adv/disadv of using Access built-in security tools vs build in db

[Guest]

This is a high level question.

I can think of three ways, in general, to implement Access user-level security
* Use the built in security tools to create workgroups and users
(have never done, but have read it can be tricky to implement)
* Obtain the Windows XP ID to control access to forms and database
changes
* Store user IDs and associated privilege codes in the database

In a book written by John Viecas, MVP (Building Access Applications), the
last method is used in at least one of his applications.

Is there a reason to use an outside user level security method versus the
built-in tools, or vice versa?

Thanks.

 

[Scott McDaniel]

This is a high level question.

I can think of three ways, in general, to implement Access user-level security
* Use the built in security tools to create workgroups and users
(have never done, but have read it can be tricky to implement)
* Obtain the Windows XP ID to control access to forms and database
changes
* Store user IDs and associated privilege codes in the database

In a book written by John Viecas, MVP (Building Access Applications), the
last method is used in at least one of his applications.

Is there a reason to use an outside user level security method versus the
built-in tools, or vice versa?

Depends on what you're doing and why you're doing it.

If you're looking to protect the data in your Access database, then you'd want to implement Access User Level Security.
You'd also want to look into restricting Windows user permissions, and you'd of course do this on a secure file system
like NTFS.

Your second and third methods would somewhat mimic ULS in that you could control access to specific interfaces in your
program, but they offer absolutely no data security. Any user with a copy of Access, Excel, etc can link to the data
tables and have at your data. Granted most users don't really know how to do this, but the scariest user I know is the
guy who finished work early on Friday and can't leave until 5:00, so he fires up Excel and starts browsing thorugh the
menus and finds that "Link to External Data" command ... hoo-boy, that can be a fun call on Monday morning

Personally I've never understood re-inventing the wheel, since ULS does everything needed and it's builtin, but some
developers - even very experienced developers - have an intense dislike of ULS ... to each his (or her) own, I guess ...

Just my .02 worth, of course ... other thoughts will vary ...

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

 

[Chris Mills]

This is a high level question.

I can think of three ways, in general, to implement Access user-level security
* Use the built in security tools to create workgroups and users
(have never done, but have read it can be tricky to implement)
* Obtain the Windows XP ID to control access to forms and database
changes
* Store user IDs and associated privilege codes in the database

By all means use in-built tools, so far as reasonable, to implement something.
There are several problems
a) "MS wizards" are often spurned because they DO NOT a1) always work as you'd
expect if not outright limitations in them a2) wizards "by design" insulate
you from a full understanding of, in this case, ULS.
b) ULS is very complex to implement and I don't think anyone disagrees. There
is NO shortcut to understanding it. It is documented better than many other
aspects of Access.
c) in many respects, and whatever you do, ULS is insecure anyway. In regards
to security, and however complex ULS is to implement, it's arguably the least
of your security worries or at least cannot be relied on alone

Obtaining a Windows logon ID to logon to Access, is not inherently available
and in any case may be seen as a user-convenience but does NOTHING for or
against improved security.

Storing ID's (or anything besides what Access Security already stores) does
not sound to me like anything which would improve Access Security.

Access ULS is what it is (at the same time, both complex and yet easy to break
in some quarters). I read your requirements as conflicting, in that to
implement ULS there is no choice but to learn it.

If nothing else, it may well be GOOD that ULS is tricky to implement. If
everyone could do it, so it seems to me, then it would hardly be security.
(this is the principle of obfuscation, hardly ever practised in this
newsgroup, which is nevertheless fundamental to all security)

Is there a reason to use an outside user level security method versus the

built-in tools, or vice versa?

No, in respect of ULS. Yes, in respect of further methods beyond ULS. It is
often said here that "home-grown" methods can do no better than ULS, and
perhaps that is true. Nevertheless, "homegrown methods", not necessarily
merely replacing ULS logic, are a recommended addition (source: QBuilt).

Access Security is a can of worms. ULS is only part of it. Naturally, it
depends equally on what level of security you are looking for.

I hope I've been fair. In regards to ULS, there is no shortcut but to
understand it to whatever level suits your purpose. The same can (perhaps) be
said of all security even SQLServer.

I wouldn't even think of using in-built security "wizards", and I believe I
have some high-level support for that view. (even though the SecFaq may
suggest some)
Chris

 

[Chris Mills]

NTFS may not be much security help, since generally Access requires all
permissions to the entire folder?

Just a thought...

 

[Scott McDaniel]

NTFS may not be much security help, since generally Access requires all
permissions to the entire folder?

You're correct in that it would have nothing to do with acutal Data security, but it would allow you to, for example,
deny delete permissions to the actual .mdb file. Users could still delete data once logged in, but at least they
couldn't delete the file through Windows Explorer or My Computer.

My point with NTFS would be that you could use it in addition to ULS to make the data and datafile as secure as could
be.

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

 

[Keith Wilby]

You're correct in that it would have nothing to do with acutal Data
security, but it would allow you to, for example,
deny delete permissions to the actual .mdb file. Users could still delete
data once logged in, but at least they
couldn't delete the file through Windows Explorer or My Computer.

My point with NTFS would be that you could use it in addition to ULS to
make the data and datafile as secure as could
be.

You can also use NTFS permissions to deny access to individual users and/or
groups. You also have to remember to remove the delete permissions to the
file after each compact/repair operation since permissions for new files are
inherited from the host folder.

Keith.

 

[onedaywhen]

By all means use in-built tools, so far as reasonable, to implement something.
There are several problems
a) "MS wizards" are often spurned because they DO NOT a1) always work as you'd
expect if not outright limitations in them a2) wizards "by design" insulate
you from a full understanding of, in this case, ULS.

I too am wary of wizards (read: the whole Access UI <g>).

Note, then, that 'ULS' is implemented in the Jet engine rather than at
the Access level. This means that one can (as I do) ignore the Access
wizards and use SQL DDL to directly create/drop users, groups and
permissions e.g.

CREATE GROUP Billing
;
CREATE USER Tim pwd
;
ADD USER Tim TO Billing
;
REVOKE ALL PRIVILEGES
ON Invoices
FROM Billing
;
GRANT SELECT, INSERT, UPDATE, DELETE
ON Invoices
TO Billing
;

For details, see:

Advanced Microsoft Jet SQL
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp

Personally, I even use the OLE DB provider to create the associated
workgroup file in the first place <g>.

Jamie.

--

 

['69 Camaro]

Hi, Chris.

Nevertheless, "homegrown methods", not necessarily
merely replacing ULS logic, are a recommended addition (source: QBuilt).

I'm not sure where you got this idea. Please cite your source with a link
to the statement that homegrown security is recommended.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi, Jamie.

This means that one can (as I do) ignore the Access
wizards and use SQL DDL to directly create/drop users, groups and
permissions

The downside to this is that if the workgroup information file needs to be
recreated with duplicates of the original users and groups, since no PID was
used to create the user or the group, all the users and groups will be
different from the original ones (even if the same names are used in the new
workgroup information file), and therefore not recognized by the database as
members or groups in the workgroup used to secure the database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[onedaywhen]

The downside to this is that if the workgroup information file needs to be
recreated with duplicates of the original users and groups, since no PID was
used to create the user or the group, all the users and groups will be
different from the original ones (even if the same names are used in the new
workgroup information file), and therefore not recognized by the database as
members or groups in the workgroup used to secure the database.

One *can* supply the PID values, though. From the above article:

"Although you can create the group accounts with just a name, you
should also include the optional argument known as a personal
identifier, or PID. The PID is an extra string value that you can pass
to the CREATE GROUP and CREATE USER statements. Jet will then combine
the PID with the user or group name into a unique key value known as a
security identifier, or SID. The SID is the value that Jet uses
internally to identify and work with the corresponding user or group
account. Specifying a PID when creating a user or group account ensures
that the account is unique. Specifying a PID also allows you to
re-create an identical account if the workgroup file becomes damaged,
or if you need to move the account into another workgroup file."

Does that cover your point or was their something subtle I missed (PIDs
aren't my field of expertise <g>).

Thanks,
Jamie.

--

 

['69 Camaro]

Hi, Jamie.

Does that cover your point

I was referring to your example SQL statements, but yes, the article covers
the point. Sorry I didn't clarify that.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[onedaywhen]

I was referring to your example SQL statements, but yes, the article covers
the point.

I lifted my examples straight from the article and chose the ones that
looked pretty i.e. without those ugly PIDs <g>.

Cheers,
Jamie.

--

 

['69 Camaro]

Shoulda chosen the ugly ones. ;-)

The pretty ones won't help you when disaster strikes and you need to
recreate your workgroup information file without paying an Access expert to
do it for you -- if you can find one.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[onedaywhen]

Shoulda chosen the ugly ones. ;-)

The pretty ones won't help you when disaster strikes

I demand PIDs that are both pretty and difficult to guess <g>!

Jamie.

--

 

[Chris Mills]

Those were my words. I regard "homegrown" to mean any method which is not
in-built Access security, such as "obfuscation". So I dont consider the term
to be disparaging. You may consider that to be too broad.

Cheers
Chris

 

[Guest]

Hi '69 Camaro,

It wasn't recommended. It was an inference I made from scanning warnings
about the built-in ULS and, more directly, from the fact that John Viescas'
book Building Access Applications uses a home grown ULS method in at least
one of its applications. Just thought there might be a reason why that I
should know about.

By the bye, my security needs for this project are low. It will be
replacing a spreadsheet with no security, but that exists in a protected
directory.

 

[Guest]

Thanks everyone!!!

This is exactly what I was looking for. With the resources in this list,
and elsewhere, I will be teaching myself how to implement the built-in ULS
without the wizard. Wish me luck.

Thanks again.

 

['69 Camaro]

Hi, Chris.

Those were my words.

Then you need to post a retraction, because you wrote that the source of the
recommendation for "homegrown methods" for security was Q-Built, not you. I
don't know of any statement that Q-Built's consultants have ever made that
homebuilt security is a recommended practice. Q-Built's recommendation is
that if you need a secure database, then use a client/server database, such
as Oracle or SQL Server, not Access. These aren't "homegrown methods," even
if you do use a "broad definition."

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi.

Sorry for any confusion. I was referring to Chris Mills's post stating that
Q-Built recommends "homegrown methods" for Access security, which is not
correct.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Chris Mills]

A source of "homegrown methods" of security is this:
www.access.qbuilt.com/html/security.html#PreventImportFrSecDB
"How to be sneakier than the snoops peeking at data they shouldn't see. "

With the exception of point 1(RWOP), the rest are "homegrown" methods, and
very good they are too.

As I have pointed out (twice in my original sentence), these methods (such as
the ones above) do not replace, but are additional to, in-built Access
security methods. As I hope I have pointed out, the above is a reliable source
for some ideas on the concept of "additional security". If anyone has an issue
with the word "homegrown", then they should get over it.

Glossary:
Homegrown: similar meaning to "in-house", in Access security this generally
means any method other than the designed in-built Access security features.

HTH
Chris