A simple implementation scheme to prevent the MDF file from being illegally appended and modified

In the last few days at the end of last year, I asked csdn how to prevent illegal attachment of MDF files? Although there is no good solution, I would like to thank you for your enthusiastic suggestions.

I will discuss the following questions again:
The system we are working on is facing such a problem that when the database is released, a specific password is added to the instance specified during installation. Use SQL
The Server2000 client cannot connect to this instance directly, but some users have a little understanding of SQL
Server Technology customers can create an instance and attach the MDF file to the new instance, so that they can easily modify it and BypassProgramIs there any good way to prevent this situation?
Born?

When I was looking for information the other day, I accidentally found that SQL Server 2000 provided a global variable -- @ dbts, which reminds me of the problem that has plagued me some time ago about how to prevent users from illegally modifying databases, I think it is convenient to control this variable.

Of course, it is also a negative method of post-event control, but at least it is also a method, so I made a simple demo, it is really simple, the following is the main steps and pseudoCodeImplementation:

0. @ dbts (from msdn)

Returns the value of the current timestamp data type for the current database. This timestamp value must be unique in the database.
Syntax

@ Dbts
Return type

Varbinary
Note

@ Dbts returns the timestamp value used at the end of the current database. When a row with a timestamp column is inserted or updated, a new timestamp value is generated.
Example

The following example returns the current timestamp value from the pubs database.

Use pubs
Select @ dbts

1. Create two experiment tables:

// Encrypt the current @ dbts variable of the Storage System

Create Table [DBO]. [dbts_log] (
[Dbtslog] [char] (18) Collate chinese_prc_ci_as not null
) On [primary]
Go

// Experiment data table, which must have the timestamp field
Create Table [DBO]. [dbts_test] (
[Testid] [uniqueidentifier] not null,
[Name] [char] (10) Collate chinese_prc_ci_as null,
[Timestamp] [timestamp] Null
) On [primary]

2. Create two global functions:

// A. f_updatedbts updates dbts_log records when changing business data

String ls_dbstamp

String ls_dbtslog

// Retrieve the Database @ dbts
Declare my_cursor Dynamic Cursor for sqlsa;

Prepare sqlsa from "select @ dbts ";

Open dynamic my_cursor;

Fetch my_cursor into: ls_dbstamp;

Close my_cursor;

// Verify that the record value of the dbts_log table matches the value of @ dbts before updating.
Select dbtslog
Into: ls_dbtslog
From dbts_log;

// Todo adopts encrypted storage, which is decrypted here

If ls_dbstamp = ls_dbtslog then
// When updating a record table, you must consider encrypted storage and irreversible encryption. Algorithm
Update dbts_log
Set dbtslog =: ls_dbstamp;

If sqlca. sqlcode <> 0 then
Rollback using sqlca;
Return false
Else
If pb_iscommit then
Commit using sqlca;
End if
End if

Else

Return false

End if

Return true

// B. f_validdbts verify that the dbts_log record is invalid

String ls_dbstamp
String ls_dbtslog

// Retrieve the Database @ dbts
Declare my_cursor Dynamic Cursor for sqlsa;

Prepare sqlsa from "select @ dbts ";

Open dynamic my_cursor;

Fetch my_cursor into: ls_dbstamp;

Close my_cursor;

// Retrieve the dbts logs recorded in the table
Select dbtslog
Into: ls_dbtslog
From dbts_log;

// Todo adopts encrypted storage, which is decrypted here

// Return the comparison result
Return (ls_dbstamp = ls_dbtslog)

3. Simple experiment:

A) // modify the table with the timestamp field through the program

Insert into dbts_test
(Name)
Values ("new ");


// Update the dbts_log table at the same time
F_updatedbts (true)

B) Check whether the record value of the dbts_log table matches the value of @ dbts when the system is turned on.


If not f_validdbts () then

MessageBox ("** System", "The program detects that your current database has been illegally modified. Please contact the system administrator !")

Halt
End if

PS: I use the older language PowerBuilder for pseudocode, which is simple and easy to understand, so I didn't specifically modify it.