Doug: I was thinking about whether to use something with IP Address. I won't
bother with "USER" because that looks at the CFG file, and I only have one CFG
file so everyone is "none". Tests show that UserID is the same as NetUser, but
what the heck, it's worth a try using those 2 other identifiers!
Karen
-----Original Message-----
From: Doug Hamilton <[email protected]>
To: [email protected]
Sent: Tue, Mar 15, 2022 1:23 pm
Subject: Re: [RBASE-L] - Delete Trigger mystery?
I set up a DBActivityLogger table to track various events when/where needed.
I set variables for form name, component ID, etc and then RUN SELECT a little
routine that adds a row of data to the table.
To get user info that goes into a column in the table; I use:
SET VAR +
pDBLogUser = ( +
'NetUser:' + (CVAL('NETUSER')) + +
';WinUser:' + (CVAL('USERID')) + +
';RBUser:' + (CVAL('USER')) ), +
pDBLogLocation = ('IP Addr:' & (GETVAL('GetIPAddress','1')) )
Maybe one of those will help you track the errant deleter.
Doug
On 3/15/2022 11:48 AM, 'Karen Tellef' via RBASE-L wrote:
Yes I'm set on the archive table holding the records that were deleted, easy
to restore. The only thing missing is the Before Delete Trigger recording the
name of the user who did the deleting so we can figure out how it's getting
done. No clue why the user comes up Null
One thing I added to my trigger is a Pause statement in red and white telling
the user to get ahold of me or the office manager right away and tell them what
menu option you're running, etc. The person/program who just deleted 200
records is going to be pressing [enter] a bunch of times (most likely will
reboot their computer, yikes)
I guess I'll have to look at GRANT. A quick read of the help screens (I've
never used Grant in all my decades of RBase work) shows that I need to first
set up a bunch of SET USER commands. Dialed into the company, and the USER
name is set to "NONE". I use CVAL("NetUser") anytime I need to identify a
user, I never refer to the cfg file for anything. So I'm guessing I could set
USER equal to NetUser, and then Grant Delete privileges only to the one person
that I sorta trust? She of course claims she's not the one deleting, but I
can't say I'm 100% on board with that.
Karen
-----Original Message-----
From: [email protected] <[email protected]>
To: RBASE-L <[email protected]>
Sent: Tue, Mar 15, 2022 11:32 am
Subject: Re: [RBASE-L] - Delete Trigger mystery?
At this juncture, I would go straight to the GRANT on permissions to lock down
the tables to specific uses, based on users. If there is no Damage occurring,
then this would eliminate any deletions without identifying the user first.
On Tuesday, March 15, 2022 at 12:24:25 PM UTC-4 [email protected] wrote:
And I missed the deletions from the Archive table as well. Ok then. The
Archive table is just that, without any triggers period? If that is the case,
then the ONLY way is through active persons doing it.
On Tuesday, March 15, 2022 at 12:19:13 PM UTC-4 [email protected] wrote:
Alrighty then.. I guess I missed that and why I earlier suggested as much,
using either of "Delete Flag) (in lieu of ACTUAL DELETION) and / or a separate
table " Add a second table, as you have already done to contain just the
records marked as deleted. This would be to determine if there are any actual
deletions occurring in either table and if there are any deletions, is there
any commonality to the deletions. " So at least you have protection! And from
my above comment, I assume there are NO deletions in your archive table, so it
still points to the two possibilities outline previously.
On Tuesday, March 15, 2022 at 10:55:21 AM UTC-4 Karen Tellef wrote:
They are stored nicely in the table that the Before Delete Trigger saves to,
so it's an easy "append". Much easier than RScope!
Karen
-----Original Message-----
From: [email protected] <[email protected]>
To: RBASE-L <[email protected]>
Sent: Tue, Mar 15, 2022 9:41 am
Subject: Re: [RBASE-L] - Delete Trigger mystery?
Also, If you did NOT know, since the DB is not damaged, those deleted rows
are recovered fairly easily with R:Scope. From R:Scope
5.4.2.2 Manually Fixing the Data File
The Manual option allows you more control over the data file than the
Automatic option. By using the
Manual option, you can:
· Rebuild pointers in the data file
· Find and undelete rows of deleted data
5.4.2.2.7 Restoring and Deleting Row s
You can restore deleted rows to a table or delete existing rows from a table
by using R:Scope. If you
restore or delete rows, you will need to rebuild the indexes for the table.
Restoring Rows
R:BASE marks a row as deleted by making its row size a negative number.
To restore a deleted row:
1. Move the block cursor to the row you want to undelete.
2. Select the red button with the plus (+) or press the [Insert] key to
undelete the row. R:Scope
restores the row and moves to the next wow in the table. The row size will now
be a positive
number.
On Tuesday, March 15, 2022 at 10:10:45 AM UTC-4 Karen Tellef wrote:
So some kind of login / logout table to show which users are in and out of
the database? And then match up the list with the datetime of the deletions?
Yeah, that could provide a clue. I'll ask my contact if that might work, not
sure if everyone leaves the app up all day or they go in / out (only 4 users)
Karen
-----Original Message-----
From: [email protected] <[email protected]>
To: RBASE-L <[email protected]>
Sent: Tue, Mar 15, 2022 9:06 am
Subject: Re: [RBASE-L] - Delete Trigger mystery?
But you also know that when the deletion is limited to EXACT rows and not
random gaps in the data file, that the deletions are occurring from one of two
sources. Programmatically or direct User. That was why one of my suggestions
was to add a table with a datetime (and any other info you might feel germane
) and in a delete trigger have that table updated with the timestamp so it
might point to a time period when certain users were in the db, or at some time
when no one should be.
On Monday, March 14, 2022 at 5:58:29 PM UTC-4 Karen Tellef wrote:
But we have NO CLUE where/when these deletions are happening. There is
nowhere in code that I can add the insertion of a delete flag, because there
honestly should be nowhere that a user (nor a program) is deleting these rows.
This data has to do with lawsuits, and the data has to stay there until the
lawsuit is settled and then the record is archived at monthend. We know
someone isn't running this monthend routine when they shouldn't, because a
bunch of other stuff would also happen at the same time, and none of that is
happening
Karen
-----Original Message-----
From: [email protected] <[email protected]>
To: RBASE-L <[email protected]>
Sent: Mon, Mar 14, 2022 3:23 pm
Subject: Re: [RBASE-L] - Delete Trigger mystery?
A "Delete Flag" is just a single character field that excludes the record
from any results. It is in lieu of "Actual" deletion. Internally, I believe
R:Base does the same thing with deleted records and they stay "so marked" until
PACK or RELOAD occurs.
By doing this, you can then restrict to a single event that would occur ONLY
when one person, so authorized, has access to do so.
The requirement for a password in advance of any deletions is a good stopgap
too, so long as the password is not widely known, but you know a secret is only
a secret when kept among 3 people and two of them are dead.
On Monday, March 14, 2022 at 2:34:53 PM UTC-4 Karen Tellef wrote:
Do you mean using GRANT to restrict deletion? You know, I have never once
used GRANT in all the decades I've used RBase!!! I'd have to read up on how to
use it. Do you assign permission to delete to only specific users? And what
"name" is it using to assign permissions? Stuff like that that I don't have
the answers to, would have to read up on.
Also, I have nowhere in my code where the user is allowed to delete a record,
so there wouldn't be anywhere to set some kind of "delete flag". No program
deletes a batch of records other than the one monthend program which works fine
to archive/delete records, we know that one works on the last day of the month
as intended. Only one user has the password to run that routine and the
monthend date is never the one that is erroneously deleting records.
Karen
-----Original Message-----
From: [email protected] <[email protected]>
To: RBASE-L <[email protected]>
Sent: Sun, Mar 13, 2022 9:04 am
Subject: Re: [RBASE-L] - Delete Trigger mystery?
Well it might take a little work, but this is what I would do.
#1 - I would lock the table down with access control as tight as practically
possible.
Add a single character field to the table to use as a Delete Flag (hangover
from the old DBase days, using an asterisk (0x2A) as the marker or something
else as your choosing). Having done that, you would have to change your code
to exclude those marked as deleted.
To the above, you might also add another table with just a datetime column
that is added by a trigger from the main table that is updated ANY TIME the
table is accessed (this will give you a timeframe to point to user activity
involvement at the time of ANY access or deletions)
Add a second table, as you have already done to contain just the records
marked as deleted. This would be to determine if there are any actual
deletions occurring in either table and if there are any deletions, is there
any commonality to the deletions.
It seems as the detective work is pointing to user access being the culprit,
but maybe by taking extraordinary steps, it can be conclusive one way or
another.
On Saturday, March 12, 2022 at 2:45:33 PM UTC-5 Karen Tellef wrote:
Adrian: I am assuming they were actually deleted. Since the trigger is
"before delete" and operates as it should other than giving me that null
DeletedBy, and I can't find them anywhere in the original table, I have to
think they were deleted. If there was an issue with indexes or a database
problem itself, I would think it would show up on an Autochk. This database
has never had a bad Autochk.
Karen
-----Original Message-----
From: Adrian Huessy <[email protected]>
To: [email protected] <[email protected]>
Sent: Sat, Mar 12, 2022 11:14 am
Subject: RE: [RBASE-L] - Delete Trigger mystery?
Karen, Quick question: do you feel that records are/were effectively
deleted or do you have NULL entries even though nothing was deleted? BR, Adrian
Von: 'Karen Tellef' via RBASE-L [mailto:[email protected]]
Gesendet: Samstag, 12. März 2022 01:02
An: [email protected]
Betreff: [RBASE-L] - Delete Trigger mystery? Why I needed the
before-delete trigger: no one should ever be deleting data from this table.
And although they have the full version of RBase, no one knows how to "get to
the R> prompt". There is a monthend routine that the senior user runs that
will delete a batch of data, but that's all (we remove the trigger before this
monthend routine, then put it back on) Yet every now and then we find
data mysteriously disappeared. So I created a before-delete trigger. It
takes the record to be deleted and appends it to an archive table. 3 columns
in the archive table will hold the deleted date / time and the user who deleted
it. Works perfectly at my development environment and when I test it at the
place where the database is installed. Records the date, time and the user.
However, twice now we have found records in that archive table that had
been deleted (hundreds at a time). The records had the deleted date/time but
had NO user name. Here's the code I use in my stored procedure. The
DeletedDate and DeletedTime works fine, it gets updated every time. There is
no login to this app, so I grab the NetUser (used many times in the application
for other things, successfully). The first time that the archive had no user
name, I modified the stored procedure to grab the ComputerName. But still,
nothing..... SET VAR vText TEXT = NULL
SET VAR vText = (CVAL("NetUser"))
IF vText IS NULL THEN
SET VAR vText = (CVAL("ComputerName"))
ENDIF
UPDATE ClaimsDeleted SET DeletedDate = .#DATE, DeletedTime = .#TIME, +
DeletedBy = .vText WHERE claim = .spClaim Can anyone
think of anything else I can trap that would help me figure out how/when the
records got deleted? Or does anyone know how records could get deleted
when there actually is no NetUser or ComputerName? Karen
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/1654695576.980450.1647043308102%40mail.yahoo.com.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/dbba74c3572c4f6aa054b424fd42d70b%40huessy.com.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/ff8ce79d-ce09-43b8-a3fc-48a36de02ca2n%40googlegroups.com
.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/bc0980d9-c3a2-47ee-8d40-b1364fb952c7n%40googlegroups.com
.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/f7c59574-ff16-48d1-86d0-43df117ecbf5n%40googlegroups.com
.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/b244ee2d-88c7-484a-a8a8-cf9fe7018f6an%40googlegroups.com.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/85889f58-1a4c-4970-8a59-c5ef6270bf0fn%40googlegroups.com.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/317792764.1803447.1647362884723%40mail.yahoo.com.
| | This email has been checked for viruses by Avast antivirus software.
www.avast.com |
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/4e0f5a57-6072-33bd-9fee-ed88de41335a%40wi.rr.com.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/1406503420.1851738.1647372295127%40mail.yahoo.com.
