some security concerns

We have a TON of access databases on our servers.

after 10 yrs a person went in and wiped about 50 files in one.
ALL hell broke loose.

A few questions if I may.
one person said ok we can link tables and put the data on another
server..WELL you can still remove records..

we can log every person who logs into the database, and we can log
times..but if we store this info in the table they are working on how
will we know what they did if they delete the record.

Put logs into another table...is there a way to say ok bob logged in
on 9/25 he added records 201,202,203 and deleted record ....###
adding would be easy but editing and deleting sound like a pain to
track....correct ?

they do not want locked down databases....one of the people even said
they wanted to know who deleted a database. I told them to call IT
maybe they knew how to track that...can they?

overall it still comes down to training of the data entry people as
well as the statistics people. AND TRAINING is degrading to a stat
phd. Training data entry people cost money, and they should not be a
problem with them....ok forget this last paragraph its a no win
arguement around here.

thanks for any comments on this or anything else about securing from
this type of thing.

Re: some security concerns

"sparks" wrote in message
news:cjn3j3lin1eh9a4tqp1jhgo7fbtl1tdbgs@4ax.com...
> We have a TON of access databases on our servers.
>
> after 10 yrs a person went in and wiped about 50 files in one.
> ALL hell broke loose.
>
> A few questions if I may.
> one person said ok we can link tables and put the data on another
> server..WELL you can still remove records..
>
> we can log every person who logs into the database, and we can log
> times..but if we store this info in the table they are working on how
> will we know what they did if they delete the record.
>
> Put logs into another table...is there a way to say ok bob logged in
> on 9/25 he added records 201,202,203 and deleted record ....###
> adding would be easy but editing and deleting sound like a pain to
> track....correct ?
>
> they do not want locked down databases....one of the people even said
> they wanted to know who deleted a database. I told them to call IT
> maybe they knew how to track that...can they?
>
> overall it still comes down to training of the data entry people as
> well as the statistics people. AND TRAINING is degrading to a stat
> phd. Training data entry people cost money, and they should not be a
> problem with them....ok forget this last paragraph its a no win
> arguement around here.
>
> thanks for any comments on this or anything else about securing from
> this type of thing.
>
>

Sounds like you need to upsize to a SQL server with all the security and
logging options engaged. I don't think a Jet database can be secured to the
level you indicated needing.

Re: some security concerns

The biggest thing is they don't want to make it hard for ANYONE to log
in and enter whatever, add, delete or edit a record.

BUT they want to know who what when and where. BUT only if there is a
problem.


On Wed, 7 Nov 2007 11:34:11 -0600, "paii, Ron" wrote:

>
>"sparks" wrote in message
>news:cjn3j3lin1eh9a4tqp1jhgo7fbtl1tdbgs@4ax.com...
>> We have a TON of access databases on our servers.
>>
>> after 10 yrs a person went in and wiped about 50 files in one.
>> ALL hell broke loose.
>>
>> A few questions if I may.
>> one person said ok we can link tables and put the data on another
>> server..WELL you can still remove records..
>>
>> we can log every person who logs into the database, and we can log
>> times..but if we store this info in the table they are working on how
>> will we know what they did if they delete the record.
>>
>> Put logs into another table...is there a way to say ok bob logged in
>> on 9/25 he added records 201,202,203 and deleted record ....###
>> adding would be easy but editing and deleting sound like a pain to
>> track....correct ?
>>
>> they do not want locked down databases....one of the people even said
>> they wanted to know who deleted a database. I told them to call IT
>> maybe they knew how to track that...can they?
>>
>> overall it still comes down to training of the data entry people as
>> well as the statistics people. AND TRAINING is degrading to a stat
>> phd. Training data entry people cost money, and they should not be a
>> problem with them....ok forget this last paragraph its a no win
>> arguement around here.
>>
>> thanks for any comments on this or anything else about securing from
>> this type of thing.
>>
>>
>
>Sounds like you need to upsize to a SQL server with all the security and
>logging options engaged. I don't think a Jet database can be secured to the
>level you indicated needing.
>

Re: some security concerns

"sparks" wrote in message
news:oa24j3h7me2e9pi8b32nt83t1n024khiav@4ax.com...
> The biggest thing is they don't want to make it hard for ANYONE to log
> in and enter whatever, add, delete or edit a record.
>

I'm not an expert on MS SQL Server, but if you are using Active Directory; I
think the serve can use the AD login. Access security will require a
password for the database which cannot be linked to Active Directory.

> BUT they want to know who what when and where. BUT only if there is a
> problem.
>

There are example of logging code for Jet but nothing that could be used to
rebuild your database after a mass delete of records. As for deleting MDBs,
Windows server has Shadow Copy that will generate backups that can be
restored. Also your IT department should be doing regular backups.

>
> On Wed, 7 Nov 2007 11:34:11 -0600, "paii, Ron" wrote:
>
> >
> >"sparks" wrote in message
> >news:cjn3j3lin1eh9a4tqp1jhgo7fbtl1tdbgs@4ax.com...
> >> We have a TON of access databases on our servers.
> >>
> >> after 10 yrs a person went in and wiped about 50 files in one.
> >> ALL hell broke loose.
> >>
> >> A few questions if I may.
> >> one person said ok we can link tables and put the data on another
> >> server..WELL you can still remove records..
> >>
> >> we can log every person who logs into the database, and we can log
> >> times..but if we store this info in the table they are working on how
> >> will we know what they did if they delete the record.
> >>
> >> Put logs into another table...is there a way to say ok bob logged in
> >> on 9/25 he added records 201,202,203 and deleted record ....###
> >> adding would be easy but editing and deleting sound like a pain to
> >> track....correct ?
> >>
> >> they do not want locked down databases....one of the people even said
> >> they wanted to know who deleted a database. I told them to call IT
> >> maybe they knew how to track that...can they?
> >>
> >> overall it still comes down to training of the data entry people as
> >> well as the statistics people. AND TRAINING is degrading to a stat
> >> phd. Training data entry people cost money, and they should not be a
> >> problem with them....ok forget this last paragraph its a no win
> >> arguement around here.
> >>
> >> thanks for any comments on this or anything else about securing from
> >> this type of thing.
> >>
> >>
> >
> >Sounds like you need to upsize to a SQL server with all the security and
> >logging options engaged. I don't think a Jet database can be secured to
the
> >level you indicated needing.
> >
>

Re: some security concerns

On Nov 7, 7:56 am, sparks wrote:
> We have a TON of access databases on our servers.
>
> after 10 yrs a person went in and wiped about 50 files in one.
> ALL hell broke loose.
>
> A few questions if I may.
> one person said ok we can link tables and put the data on another
> server..WELL you can still remove records..
>
> we can log every person who logs into the database, and we can log
> times..but if we store this info in the table they are working on how
> will we know what they did if they delete the record.
>
> Put logs into another table...is there a way to say ok bob logged in
> on 9/25 he added records 201,202,203 and deleted record ....###
> adding would be easy but editing and deleting sound like a pain to
> track....correct ?
>
> they do not want locked down databases....one of the people even said
> they wanted to know who deleted a database. I told them to call IT
> maybe they knew how to track that...can they?
>
> overall it still comes down to training of the data entry people as
> well as the statistics people. AND TRAINING is degrading to a stat
> phd. Training data entry people cost money, and they should not be a
> problem with them....ok forget this last paragraph its a no win
> arguement around here.
>
> thanks for any comments on this or anything else about securing from
> this type of thing.

Another method is to create three fields for each of your tables:

UserID Text 25
ModifyDate Date/Time
Deleted Yes/No Default = No

Every time a user adds or modifies a record, the user's logon id can
be placed into the UserID field with date and time into the ModifyDate
field. If a record is to be deleted, it will simply be marked for
deletion instead of actually deleting it. To mark a record for
deletion, the Deleted field should be set to 'Yes' along with who
(UserID) and when (ModifyDate).

Now, I'm not saying this is the best solution. It's just an idea to
consider. It might not be because marking records for deletion will
never shrink the size of the database, unless a process is created to
archive this data on a periodic basis.

I hope this helps!

Re: some security concerns

On Nov 7, 7:56 am, sparks wrote:
> We have a TON of access databases on our servers.
>
> after 10 yrs a person went in and wiped about 50 files in one.
> ALL hell broke loose.
>
> A few questions if I may.
> one person said ok we can link tables and put the data on another
> server..WELL you can still remove records..
>
> we can log every person who logs into the database, and we can log
> times..but if we store this info in the table they are working on how
> will we know what they did if they delete the record.
>
> Put logs into another table...is there a way to say ok bob logged in
> on 9/25 he added records 201,202,203 and deleted record ....###
> adding would be easy but editing and deleting sound like a pain to
> track....correct ?
>
> they do not want locked down databases....one of the people even said
> they wanted to know who deleted a database. I told them to call IT
> maybe they knew how to track that...can they?
>
> overall it still comes down to training of the data entry people as
> well as the statistics people. AND TRAINING is degrading to a stat
> phd. Training data entry people cost money, and they should not be a
> problem with them....ok forget this last paragraph its a no win
> arguement around here.
>
> thanks for any comments on this or anything else about securing from
> this type of thing.

Sparks,
I have done this exact thing. On any database it's tough, on an
access database it is monsterous.
In access all your activity would have to be form driven and then you
would have to have code events on the form that wrote the changes both
before and after to another table. What if 3 people changed a record,
you would need to know who changed what from what. As far as deletes,
you also have to worry about related tables. What if I delete a client
and all their related invoices should get deleted to. If I want to
see who deleted that invoice how do I know that it was from a client
deletion. All of that would have to be managed by code and written to
a table. Really ugly.

If you upgrade to sql server like Ron suggested - sql express is free,
you can set up triggers on the tables so that any changes you make can
automatically written to a log table. Still a lot of work, but only
on the database side as opposed to on the client side. Much easier to
manage and maintain.
Good luck
P

Re: some security concerns

yes its a big mess trying to track this stuff. Like you said did
someone delete a record out of a subtable or did they delete the
primary key and auto delete all the linked table data.

Basically they rely on IT for backups...2 weeks on site and 1 yr
offsite but as they mentioned...HOW will they tell when or what
happened. AS WELL AS we didn't notice if for a year.
I told them that is a main problem.
Reports that can be run weekly for record counts....if it goes down
OUCH ??????? that is the only thing I could think of.
but I think they need to have someone watching this kind of thing.

one other point can IT tell you who made a file?
I mean if you go in and copy a database and call it data1BACKUP the
owner of the backup is you but is the owner the creater of the file?





On 7 Nov 2007 17:07:33 -0800, Pachydermitis
wrote:

>On Nov 7, 7:56 am, sparks wrote:
>> We have a TON of access databases on our servers.
>>
>> after 10 yrs a person went in and wiped about 50 files in one.
>> ALL hell broke loose.
>>
>> A few questions if I may.
>> one person said ok we can link tables and put the data on another
>> server..WELL you can still remove records..
>>
>> we can log every person who logs into the database, and we can log
>> times..but if we store this info in the table they are working on how
>> will we know what they did if they delete the record.
>>
>> Put logs into another table...is there a way to say ok bob logged in
>> on 9/25 he added records 201,202,203 and deleted record ....###
>> adding would be easy but editing and deleting sound like a pain to
>> track....correct ?
>>
>> they do not want locked down databases....one of the people even said
>> they wanted to know who deleted a database. I told them to call IT
>> maybe they knew how to track that...can they?
>>
>> overall it still comes down to training of the data entry people as
>> well as the statistics people. AND TRAINING is degrading to a stat
>> phd. Training data entry people cost money, and they should not be a
>> problem with them....ok forget this last paragraph its a no win
>> arguement around here.
>>
>> thanks for any comments on this or anything else about securing from
>> this type of thing.
>
>Sparks,
>I have done this exact thing. On any database it's tough, on an
>access database it is monsterous.
>In access all your activity would have to be form driven and then you
>would have to have code events on the form that wrote the changes both
>before and after to another table. What if 3 people changed a record,
>you would need to know who changed what from what. As far as deletes,
>you also have to worry about related tables. What if I delete a client
>and all their related invoices should get deleted to. If I want to
>see who deleted that invoice how do I know that it was from a client
>deletion. All of that would have to be managed by code and written to
>a table. Really ugly.
>
>If you upgrade to sql server like Ron suggested - sql express is free,
>you can set up triggers on the tables so that any changes you make can
>automatically written to a log table. Still a lot of work, but only
>on the database side as opposed to on the client side. Much easier to
>manage and maintain.
>Good luck
>P