Is UserLevel Security Really Necessary?

[Earl Anderson]

First, I feel somewhat embarrassed and apologetic that this post is lengthy,
but in an effort to furnish sufficient information (as opposed to too little
information) to you, I wanted to supply all of the relevant facts. Second,
despite the fact that I think the obvious 'quick' answer to my question
would be 'Yes', I am seeking your wisdom and recommendation to the question
as to this particular set of circumstances.

I have 20 Security Guard posts scattered over an entire state (US). I am in
the final process of building an electronic Guard Activity Log (GAL) in
Access XP for the guards to document their security activity rather than
utilize the "old" method of handwriting their entries into a paper log book
(a couple of recent incidents convinced me that handwritten log book entries
are too easily altered or manipulated). The GAL is a single multi-field
data entry form which opens 'on top' whenever Windows is loaded. The
entered data is linked, locked and captured to a networked backend file
permitting security managers to view the activity at any given site for any
given time period. In addition to the company-required IT Window Logon
protocols, I've asked IT-Security to ensure no other programs are available
to the guards including IE, email and Games. Other relevant facts are:
1. The guards operate in three (3) 8-hour shifts (24/7 operation).
2. The computer at a particular site is tied by IT to that site only.
3. The GAL form has a default facility name value of that Facility's name.
4. The GAL has a mandatory 'Shift Sign-In' and 'Shift Sign-Out' fields with
system date/time entered automatically based upon their name and
that particular Facility.
5. As there are 3 guards needed to cover the shifts for a single day, there
are 6 guards authorized to work Security at each particular site (only
they have received site-specific training for that particular site).
6. The site operations manager has a weekly guard duty roster indicating
the name of the guard who is supposed to be on duty for a
particular day/shift as well as a list of the 6 guards authorized at his/her
site.

As I envision the set-up I want, since Guard 'B' is relieving Guard 'A', and
Guard 'C' relieves Guard 'B', there will be no need to ever 'shut down' GAL
(just as there is never a need to close the cover to a paper log book and
then re-open it). For security's sake, there are certain physical security
protocols a relieving guard must go through before being granted admittance
inside to get to his post.

So my question is, based on the foregoing, would there be any compelling
database security reason(s) to have the Security Officers 'logon' each shift
change, with a password., to GAL? This would require each outgoing Officer
to 'shut-down' and the incoming Officer to 'Logon' both to Windows and then
again to GAL (Access). Given the stated security protocols in place, I
personally feel that it would be unnecessary, but am trying to determine if
their is something I'm not considering.

Thanks so much...
Earl Anderson

['69 Camaro]

Hi, Earl.

So my question is, based on the foregoing, would there be any compelling
database security reason(s) to have the Security Officers 'logon' each
shift change, with a password., to GAL? This would require each outgoing
Officer to 'shut-down' and the incoming Officer to 'Logon' both to Windows
and then again to GAL (Access). Given the stated security protocols in
place, I personally feel that it would be unnecessary

So if I'm Guard B and I screw up, I can edit Guard A's database entry to
make it look like he did it instead. Since there's no separate Windows
login or Access database login, no one can track my strategic edit job.
Awesome! This is soooo much better than a log book, where people can tell
my handwriting looks different than Guard A's handwriting.

And if I'm late to work, I can edit the time the previous guard signed out
and the time I signed in, and no one can track those changes either! I'll
just press <CTRL><Gand enter the following two commands in the Immediate
Window:

CurrentDB().Exe cute "UPDATE tblShifts SET SignOut = #6/17/2007 8:00:16 AM#
WHERE Facility = 'Fort Knox' AND WeekNum = 25 AND Shift = 1;"

CurrentDB().Exe cute "UPDATE tblShifts SET SignIn = #6/17/2007 8:00:48 AM#
WHERE Facility = 'Fort Knox' AND WeekNum = 25 AND Shift = 2;"

Since the database application recorded the system time when we signed in
and out (as required), the previous guard must have been mistaken about when
I arrived, because we can all see that it's "8:00 A.M." in the database for
today's date, just like it should be.

I _love_ this database application! I can do no wrong! (At least no one
can prove it.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[Earl Anderson]

Gunny,

I'm glad you brought up that circumstance of being able to edit another
guard's entry(ies). Inasmuch as my post was long enough, I didn't state
that once the entry is written and THEN added to the log (permanently as a
record), it can not be edited. That is to say, if Guard 'C' makes an entry
that, say, 'none of the alarms worked' and THEN adds that entry to the log
(again a permanent record) and afterwards sees that he made a mistake when
in fact 'all 'of the lights worked, he then must make another entry
referencing and correcting his original entry. As an mde, there is no
ability for the guards to tamper or edit any code and all 'Edit' and most
other menu bar options have been disabled.

Hope I presented that clearly enough...
Earl
"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAMwrote in
message news:13******** *****@corp.supe rnews.com...

Hi, Earl.

>So my question is, based on the foregoing, would there be any compelling
database security reason(s) to have the Security Officers 'logon' each
shift change, with a password., to GAL? This would require each outgoing
Officer to 'shut-down' and the incoming Officer to 'Logon' both to
Windows and then again to GAL (Access). Given the stated security
protocols in place, I personally feel that it would be unnecessary

So if I'm Guard B and I screw up, I can edit Guard A's database entry to
make it look like he did it instead. Since there's no separate Windows
login or Access database login, no one can track my strategic edit job.
Awesome! This is soooo much better than a log book, where people can tell
my handwriting looks different than Guard A's handwriting.

And if I'm late to work, I can edit the time the previous guard signed out
and the time I signed in, and no one can track those changes either! I'll
just press <CTRL><Gand enter the following two commands in the Immediate
Window:

CurrentDB().Exe cute "UPDATE tblShifts SET SignOut = #6/17/2007 8:00:16 AM#
WHERE Facility = 'Fort Knox' AND WeekNum = 25 AND Shift = 1;"

CurrentDB().Exe cute "UPDATE tblShifts SET SignIn = #6/17/2007 8:00:48 AM#
WHERE Facility = 'Fort Knox' AND WeekNum = 25 AND Shift = 2;"

Since the database application recorded the system time when we signed in
and out (as required), the previous guard must have been mistaken about
when I arrived, because we can all see that it's "8:00 A.M." in the
database for today's date, just like it should be.

I _love_ this database application! I can do no wrong! (At least no one
can prove it.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

['69 Camaro]

Hi, Earl.

I didn't state that once the entry is written and THEN added to the log
(permanently as a record), it can not be edited.

How are you preventing users from editing the records without implementing
security? (I can think of several ways to detect a user's edits, but I want
to hear it from you as to the steps you've taken to prevent any hanky-panky
from knowledgeable database users, because even if those security guards
don't know anything about databases today, they can find someone who does
who can then advise them about what to do to get around the standard
precautions.)

From the example I gave, you can see that a user can update records in a
table and leave no trace by using the Immediate Window for the commands. An
MDE doesn't prevent the user from using the Immediate Window, nor from
creating and deleting queries, tables, and macros, so the inability to use
menus or edit or add any VBA code in the current database application isn't
that much of a hurdle when it comes to data manipulation. And without
User-Level Security, the built-in menus can easily be re-enabled, so the
missing "Edit" menu can be put back in and removed again without your
knowledge.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[Rick Brandt]

'69 Camaro wrote:

Hi, Earl.

I didn't state that once the entry is written and THEN added to the
log (permanently as a record), it can not be edited.

How are you preventing users from editing the records without
implementing security? (I can think of several ways to detect a
user's edits, but I want to hear it from you as to the steps you've
taken to prevent any hanky-panky from knowledgeable database users,
because even if those security guards don't know anything about
databases today, they can find someone who does who can then advise
them about what to do to get around the standard precautions.)

[snip]

And that last statement is why I would say impementing ULS is a waste of time.
If users can seek advice how to manipulate your data in an app without ULS they
can just as easily obtain advice on how to circumvent ULS.

If you need to protect data from users then it shoul not be stored in an
Access/Jet database.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Larry Linson]

"Earl Anderson" <is*****@optonl ine.netwrote

I have 20 Security Guard posts scattered
over an entire state (US).

How do you plan to implement this? A split Access-Jet database is
unsuitable for use over a WAN. If you are planning an Access client
application to a server DB at a central site over the WAN, or access via
Windows Terminal Services, security considerations will be different.

Access User/Group Level Security is breakable, and you should not _count on_
the users' current inexperience preventing them from managing to break it.

If the users (guards) have to log on to Windows or the network with their
own unique ID, you can use that to identify the log entries (see
http://www.mvps.org/access/api/api0008.htm). If you are using a Server DB as
the data store, then the Server DB's security is likely to be stronger than
Access'.

We've heard reports, but I cannot vouch for their accuracy, that someone has
created a successful dis-compiler for MDE "compiled" databases... which
only, by the way, prevents opening code modules, and associated objects, in
design view.

If you can provide more detailed information and you'll likely get more
detailed answers or comments.

Larry Linson
Microsoft Access MVP

[Earl Anderson]

Gunny,

This is very helpful...exact ly what I was looking for. In short, you're
saying if I don't employ user-level security, someone who knew what they
were doing could get into the GAL and change data. I believe with the file
as an mde file would prevent access to the forms, reports and modules, it
still permits access to the queries and tables. However, if user-level
security is in place with the guards needing and having 'Read' and 'Write'
permissions, and without 'Read Design', 'Modify Design', 'Update', 'Delete'
and 'Insert Data' permissions, they would still have access to the Database
Window (F-11 is one way) and, as you point out, the Immediate Window
(Ctrl-G), as well. Now, with user-level security invoked, even if they
could get to the Immediate Window, I don't believe they would be able to
Save any changes in an mde file. But on the 'flipside', with the 'Write'
permissions they have with user-level security, by getting into the database
window, they could 'write' data to a table with their user-level 'write'
permission. So, I guess I'm saying/asking is that if they can get to the
tables to 'write' data no matter if its an mde file or if there is
user-level security employed, what is the solution to prevent any
modification of data?

Also, I did not mention previously, that a guard cannot enter and save data
to the log without being signed-in on shift first.

Thx...
Earl
"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAMwrote in
message news:13******** *****@corp.supe rnews.com...

Hi, Earl.

>I didn't state that once the entry is written and THEN added to the log
(permanently as a record), it can not be edited.

How are you preventing users from editing the records without implementing
security? (I can think of several ways to detect a user's edits, but I
want to hear it from you as to the steps you've taken to prevent any
hanky-panky from knowledgeable database users, because even if those
security guards don't know anything about databases today, they can find
someone who does who can then advise them about what to do to get around
the standard precautions.)

From the example I gave, you can see that a user can update records in a
table and leave no trace by using the Immediate Window for the commands.
An MDE doesn't prevent the user from using the Immediate Window, nor from
creating and deleting queries, tables, and macros, so the inability to use
menus or edit or add any VBA code in the current database application
isn't that much of a hurdle when it comes to data manipulation. And
without User-Level Security, the built-in menus can easily be re-enabled,
so the missing "Edit" menu can be put back in and removed again without
your knowledge.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[Neil]

With user-level security, you would give the user no permissions to write to
the table, but you would only give the owner of the append query permission
to write to the table. Thus, the user, whoever's logged in, could only use
the append query to write to the table; but they would not be able to go
into the tables themselves and edit data.

Another approach would be to use SQL Server as a back end, and call a stored
procedure to append the data to the back end. That way, the tables aren't
linked to the front end at all. Of course, if the user determined the
location of the SQL database they could work around it. But you could
restrict edits to the SQL Server database to a specific account, and then,
in your code that calls the stored procedure, you would provide that account
name and password. Your code to call the stored procedure would reside in a
code module, not in a query, so it would be compiled into the MDE and would
be unobtainable. If the user were able to decompile the MDE, they might be
able to get the password to write to the SQL database; but that would be a
stretch, IMO.

Bottom line: it depends on how "secure" it needs to be. Are you trying to
prevent casual tampering? Then you're probably fine. Are you trying to
prevent someone who has more experience in tampering (or has a friend who
does) from being able to tamper with it? Then you probably need to do more?
Are you trying to make it bullet-proof, no possible way in the world it can
be tampered with? Probably not possible.

Neil
"Earl Anderson" <is*****@optonl ine.netwrote in message
news:1g******** *******@newsfe1 2.lga...

Gunny,

This is very helpful...exact ly what I was looking for. In short, you're
saying if I don't employ user-level security, someone who knew what they
were doing could get into the GAL and change data. I believe with the
file as an mde file would prevent access to the forms, reports and
modules, it still permits access to the queries and tables. However, if
user-level security is in place with the guards needing and having 'Read'
and 'Write' permissions, and without 'Read Design', 'Modify Design',
'Update', 'Delete' and 'Insert Data' permissions, they would still have
access to the Database Window (F-11 is one way) and, as you point out, the
Immediate Window (Ctrl-G), as well. Now, with user-level security
invoked, even if they could get to the Immediate Window, I don't believe
they would be able to Save any changes in an mde file. But on the
'flipside', with the 'Write' permissions they have with user-level
security, by getting into the database window, they could 'write' data to
a table with their user-level 'write' permission. So, I guess I'm
saying/asking is that if they can get to the tables to 'write' data no
matter if its an mde file or if there is user-level security employed,
what is the solution to prevent any modification of data?

Also, I did not mention previously, that a guard cannot enter and save
data to the log without being signed-in on shift first.

Thx...
Earl
"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAMwrote
in message news:13******** *****@corp.supe rnews.com...

>Hi, Earl.

>>I didn't state that once the entry is written and THEN added to the log
(permanentl y as a record), it can not be edited.

How are you preventing users from editing the records without
implementing security? (I can think of several ways to detect a user's
edits, but I want to hear it from you as to the steps you've taken to
prevent any hanky-panky from knowledgeable database users, because even
if those security guards don't know anything about databases today, they
can find someone who does who can then advise them about what to do to
get around the standard precautions.)

From the example I gave, you can see that a user can update records in a
table and leave no trace by using the Immediate Window for the commands.
An MDE doesn't prevent the user from using the Immediate Window, nor from
creating and deleting queries, tables, and macros, so the inability to
use menus or edit or add any VBA code in the current database application
isn't that much of a hurdle when it comes to data manipulation. And
without User-Level Security, the built-in menus can easily be re-enabled,
so the missing "Edit" menu can be put back in and removed again without
your knowledge.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[Earl Anderson]

"Larry Linson" <bo*****@localh ost.notwrote in message
news:eHldi.6550 $Fk7.6291@trndd c01...

A split Access-Jet database is unsuitable for use over a WAN. If you
are planning an Access client application to a server DB at a central site
over the WAN, or access via Windows Terminal Services, security
considerations will be different.

Larry, not to be a pain-in-the ass, but I am/was planning to employ this
over a WAN. Is there either a reference you can point me to or else tell me
WHY Access is "unsuitable for use over a WAN"?

As far as more details, the path that points to the backend is a secure
network path that only I can authorize IT-Security to permit an individual
access to it. Although I'm not concerned about the guards getting to other
elements of the program (they only have the GAL data immediately available
to them), it appears that with some Access know-how, it would be possible to
change data of the activitiy log.

From what I'm gathering from you, Gunny and Rick, Access Security Sucks. It
appears that the most I can do if I want to use this form of documenting log
entries is to employ network security as well as ULS for this endeavor and
still, that's no guarantee that data can not be manipulated once entered.

I think the larger question/issue is if it (data manipulation) can be done
here in my Access app, can it not be done in any Access app?

Thx...
Earl

[Larry Linson]

"Earl Anderson" <is*****@optonl ine.netwrote

> A split Access-Jet database is unsuitable for
use over a WAN. If you are planning an Access
client application to a server DB at a central site
over the WAN, or access via Windows Terminal
Services, security considerations will be different.

Larry, not to be a pain-in-the ass, but I am/was
planning to employ this over a WAN.

That much was clear. My question was "what configuration do you hope to
employ over the WAN?

Is there either a reference you can point me to
or else tell me WHY Access is "unsuitable for
use over a WAN"?

That's not what I said... I said, and quote from the paragraph above "A
split Access-Jet database is unsuitable for use over a WAN." A bit of
Googling in this newsgroup, or in microsoft.publi c.access.multiu ser, would
explain, or a visit to MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.

But, just to summarize... the Jet database engine is not a _server
database_, but a _file-server database_. All the actual data retrieval,
storage, and manipulation is done on the user's machine. The back-end
datastore on the server or remote shared drive is used just as it would be
if on the local drive... it's not "the whole database has to be brought
across the network" but all the data that would be retrieved from the local
hard drive has to be brought across. For example, if you have a table that
is indexed on MemberID, and your query selects a particular MemberID, the
MemberID index will be brought across (or at least enough of it to locate
the particular Member ID) and then the specific Record will be brought
across. If your table is not indexed and you Query on a specific value in a
specific field, it is possible that the entire table will be brought across
the network. WAN speed is just not sufficiently fast for that to be
practical.

However, if your Access front-end is a _client_ application to a true server
DB such as Microsoft SQL Server, where the request is sent over the network,
but the processing of the index, and extraction of the data is done remotely
by the server DB, a WAN will generally be OK. Or, if you have the necessary
license for accessing the server via Windows Terminal Services, and you have
sufficient computer power on the server, each user's database work will
actually be run on the server itself.

We are pointing out what _can_ happen, not what necessarily _will_ happen.
But, yes, you are correct -- I have long said, "If your data is worth $150
to anyone, better not rely on Access/Jet security to protect it." That's
what some "password recovery software" sells for (you or I might call it
"security crack software").

On the other hand, the server DBs have better security. MS SQL Server is,
IMNSHO, about the easiest to learn and administer, but has an initial cost
plus a license is required for each user. There are open-source databases,
e.g., MySQL and PostgreSQL, which do not have the initial or per-user
charge, but for which you may need to contract "services" for helping you
solve problems, etc., and they are not as easy to learn and administer.

It's not, necessarily, that you implemented something erroneously and left
security holes. It is that Access' and Jet's security leaves a great deal to
be desired if you really want close control over your data. "Sucks" is
pretty strong, because it is adequate to keep user's from stumbling over
their own feet and ending up somewhere in the database that is none of their
business.

But, if you use Access to front-end a server DB as the datastore, you have a
lot more security. But no one's security model is _perfect_.

Larry Linson
Microsoft Access MVP

As far as more details, the path that points to the backend is a secure
network path that only I can authorize IT-Security to permit an individual
access to it. Although I'm not concerned about the guards getting to
other elements of the program (they only have the GAL data immediately
available to them), it appears that with some Access know-how, it would be
possible to change data of the activitiy log.

From what I'm gathering from you, Gunny and Rick, Access Security Sucks.
It appears that the most I can do if I want to use this form of
documenting log entries is to employ network security as well as ULS for
this endeavor and still, that's no guarantee that data can not be
manipulated once entered.

I think the larger question/issue is if it (data manipulation) can be done
here in my Access app, can it not be done in any Access app?

Thx...
Earl