1.問題の声明
したがって、最初に出力で取得するものを定式化しましょう。
- システムは、理解、実装、および運用において可能な限りシンプルでなければなりません。
- データベース内で完全に動作するはずです-攻撃者がアプリケーションに侵入し、データベースからアプリケーションアカウントにアクセスした場合、システムは引き続き動作し、変更をコミットする必要があります
- linq2sqlに対して透過的であり、コンテキストのlinq2sql定義外のコードを変更せずにアプリケーションに埋め込まれている必要があります。
- 標準のデータベースメカニズムに基づいており、外部アプリケーションやタスクを必要としません
- インスタントコピー-遅延なし
- データベース全体または単一のレコードの任意の状態に即座にロールバックする機能
- （オプション）ロギングのためにアプリケーションから追加情報を提供する機能
2.一般的なシンプルなソリューション
検索の説明は退屈せず、結果のみを説明します。
次のアプローチを使用します。アプリケーションを閉じてテーブルに直接アクセスし、特別に準備されたビューのみを表示します。 データを更新、削除、挿入するには、特別にトレーニングされたトリガーを使用します。トリガーは変更の履歴を個別のテーブルに保存し、さらにマークによる削除を使用します。 さあ始めましょう！
3.シンプルなソリューションの詳細
.NETを使用せずに、1つのSQLサーバーのみを使用して計画を実装できます。 コメントを保存するためのテーブルを考えます：
CREATE TABLE [dbo].[comments] (
[commentID] uniqueidentifier NOT NULL ,
[text] varchar (5000) NULL ,
[dt] datetime NULL ,
[userID] uniqueidentifier NULL ,
[topicID] uniqueidentifier NOT NULL ,
[enabled] bit NOT NULL DEFAULT ((1)) ,
[version] timestamp NOT NULL
)
* This source code was highlighted with Source Code Highlighter .
フィールドの詳細：
有効-コメント利用可能フラグ（マークによる削除を使用することを忘れないでください）
version-文字列のバージョンを格納するタイムスタンプ型のフィールド。そのようなフィールドがある場合、Linq2sqlはデータを更新するときにより簡潔なクエリを作成します（読み取り後に書き換え可能な文字列が変更されていないことを確認するために、すべてのフィールドをデータベースに転送する必要はありません、バージョンだけです）。
残りはすでに明確です-コメントフィールド。
彼女が変更履歴を保存するためのテーブルを作成しましょう。 私の意見では、変更ログを別のテーブルに保存する方が良いのですが、なぜメインログが乱雑になるのでしょうか？ たとえば、将来的には、ログを含むテーブルを別のファイルに「排除」し（メインのmdfおよびmdsとは別に）、データベースの弾頭をSSDディスクに転送できます。これにより、データベースからの読み取り速度が向上します。 同じ理由で、私は意識的に情報の冗長性を求めています。 「変更フィールド-古い値-新しい値」という形式のテーブルを使用する場合、特定の日付まで行をロールバックするには、この行のすべての変更を元に戻す必要があります。 まあ、または各セルの日付の後に一番最初を選択します。 それと別のものの両方は、明らかに既製のラインを取ることよりも明らかに困難です。 そして、基地が成長するという事実はそれほど怖くない。 具体的には、そのようなモデルが適合しない場合、変更ストレージモデルのクエリを調整することで、トピックの一般的な考え方を適用できます。
CREATE TABLE [dbo].[history_comments] (
[HistoryItemID] uniqueidentifier NOT NULL ,
[HistorySavedDate] datetime NOT NULL ,
[HistorySiteUser] uniqueidentifier NULL ,
[commentID] uniqueidentifier NOT NULL ,
[text] varchar (5000) NULL ,
[dt] datetime NULL ,
[userID] uniqueidentifier NULL ,
[topicID] uniqueidentifier NOT NULL ,
[enabled] bit NOT NULL ,
[version] timestamp NOT NULL
)
* This source code was highlighted with Source Code Highlighter .
フィールドの詳細：
HistoryItemID-アーカイブレコード識別子
HistorySavedDate-アーカイブに保存した日付
HistorySiteUser-今後、このフィールドは「複雑な」ソリューションで必要になります。 まだ彼に注意を払ってはいけません。
魔法の次の要素はビューです：
CREATE VIEW [applicationLevel].[comments_view] AS
SELECT
dbo.comments.commentID,
dbo.comments.text,
dbo.comments.dt,
dbo.comments.userID,
dbo.comments.topicID,
dbo.comments.version
FROM
dbo.comments
WHERE
dbo.comments.enabled = 1
* This source code was highlighted with Source Code Highlighter .
最後のジャークは残りました-3つのトリガーを作成します。
CREATE TRIGGER [applicationLevel].[onCommentUpdate]
ON [applicationLevel].[comments_view]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[comments]
SET
[comments].[text] = [inserted].[text],
[comments].[dt] = [inserted].[dt],
[comments].[userID] = [inserted].[userID],
[comments].[topicID] = [inserted].[topicID]
OUTPUT
NEWID(),
GETDATE(),
'00000000-0000-0000-0000-000000000000' ,
[inserted].[commentID],
[inserted].[text],
[inserted].[dt],
[inserted].[userID],
[inserted].[topicID],
[inserted].[enabled]
INTO [dbo].[history_comments]
(
[HistoryItemID],
[HistorySavedDate],
[HistorySiteUser],
[commentID],
[text],
[dt],
[userID],
[topicID],
[enabled]
)
FROM
[inserted]
WHERE
[comments].[commentID] = [inserted].[commentID]
AND [comments].[version] = [inserted].[version]
END
* This source code was highlighted with Source Code Highlighter .
Googleドックでさらに2つのINSERTとDELETE 。
4.小計
かなりうまくいきました。 C＃の1行のコード（および、一般に、SQL Serverで実行するアプリケーション）を変更せずに、すべての変更が、最後を除く記事の冒頭で設定されたすべての要件を満たす別のテーブルに安全に保存されるようにしました。
彼の何が問題なのですか？ 説明させてください。データベースはアプリケーションで何が起こっているかについて何も知らず、入ってくるSQLクエリのみを見ます。 データベースは、どのシステムユーザーがこのリクエストを行ったかを判断できません-すべてのユーザーが同じ人であり、同じアカウントの下にある-アプリケーションのSQLアカウント。 ただし、変更の日付と本質に関する情報に加えて、少なくともユーザーIDを保存したいと思います。 覚えているなら、このためのフィールドHistorySiteUserを作成しました。 この実装では、これを行うことに成功しないことを認めることを残念に思います。 さて、私たちは元気になり、さらに読みに行きます。
5.アイデアを複雑にする
最後の段落では、トリガーだけではできないと論理的に結論付けました。 新しいソリューションは次のようになります：ビューのUPDATE、INSERT、DELETE権限をアプリケーションから選択し（記事の最初の部分のテーブルから完全に分離します）、C＃アプリケーションから呼び出す対応するストアドプロシージャを記述し、パラメーターのすべての追加情報を渡しますこれを変更の履歴に保存します。 このプロシージャは、テーブルを更新し、特定の変更操作に関連付ける変更可能なデータとすべての追加データを保存します。
5.計画を実現します
最初に、4番目の段落で作成されたトリガーを削除し、アプリケーションからビューのUPDATE、INSERT、DELETEアクションを削除します。
次に、3つのストアドプロシージャを作成します。
CREATE PROCEDURE [applicationLevel].[comment_update]
@commentID AS uniqueidentifier ,
@version AS timestamp ,
@text AS varchar (5000) ,
@dt AS datetime ,
@userID AS uniqueidentifier ,
@topicID AS uniqueidentifier ,
@SiteUserID AS uniqueidentifier = '00000000-0000-0000-0000-000000000000'
AS
BEGIN
UPDATE [dbo].[comments]
SET
[comments].[text] = @text,
[comments].[dt] = @dt,
[comments].[userID] = @userID,
[comments].[topicID]= @topicID
OUTPUT
NEWID(),
GETDATE(),
@SiteUserID,
@commentID,
[inserted].[text],
[inserted].[dt],
[inserted].[userID],
[inserted].[topicID],
[inserted].[enabled]
INTO [dbo].[history_comments]
(
[HistoryItemID],
[HistorySavedDate],
[HistorySiteUser],
[commentID],
[text],
[dt],
[userID],
[topicID],
[enabled]
)
WHERE
[comments].[commentID] = @commentID
AND version = @version
END
* This source code was highlighted with Source Code Highlighter .
Google Doxでさらに2つのINSERTおよびDELETEを実行します。
ここで、C＃アプリケーションをわずかに修正する必要があります。 dbmlファイルに移動し、サーバーエクスプローラーから関数をマウスでワークスペースにドラッグします。 ワークスペースで、テーブル（コメント）を右クリックし、[動作の構成]項目を選択します。 そこで適切なアクションを選択し、それに対してインポートされたプロシージャを選択します。
削除と貼り付けについても同様です。
少しだけ残っていますが、siteUserId関数のパラメーターを設定する方法は？ 非常に簡単：DataContextaの継承者を作成し、その中の関数を再定義します。 これを行うには、関数のプロパティでInheritance modifier：virtualを選択します（Access：Protected thereを設定すると便利です）
public class SafetyDatabaseDataContext : DatabaseDataContext
{
#region constructors
public SafetyDatabaseDataContext () : base () { }
public SafetyDatabaseDataContext ( string connection) : base (connection) { }
public SafetyDatabaseDataContext (System.Data.IDbConnection connection) : base (connection) { }
public SafetyDatabaseDataContext ( string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base (connection, mappingSource) { }
public SafetyDatabaseDataContext (System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base (connection, mappingSource) { }
#endregion
protected override int comment_update( Guid ? commentID, System.Data.Linq.Binary version, string text, DateTime ? dt, Guid ? userID, Guid ? topicID, Guid ? siteUserID)
{
return base .comment_update(commentID, version, text, dt, userID, topicID, siteUserID ?? HSession.UserIdOrEmpty);
}
protected override int comment_delete( Guid ? commentID, System.Data.Linq.Binary version, Guid ? siteUserID)
{
return base .comment_delete(commentID, version, siteUserID ?? HSession.UserIdOrEmpty);
}
protected override int comment_insert( Guid ? commentID, string text, DateTime ? dt, Guid ? userID, Guid ? topicID, Guid ? siteUserID)
{
return base .comment_insert(commentID, text, dt, userID, topicID, siteUserID ?? HSession.UserIdOrEmpty);
}
}
* This source code was highlighted with Source Code Highlighter .
すべての標準コンストラクターを定義し（コンストラクターは継承されないことに注意してください）、空のパラメーター（この場合はsiteUserID）を補足するように、更新、変更、削除の機能を再定義します。 手動で設定するオプションを残しました（siteUserID == nullの場合のみ再定義されます）独自の動作ロジックを選択できます。
以上で、SafetyDatabaseDataContextデータベース（linq2sqlウィザードが作成するクラスと完全に互換性がある）を操作するために、あらゆる場所で使用する必要があります。 データコンテキストを取得するHDataBase.GetDataContext（）関数があり、それを修正しました
にpublic static DatabaseDataContext GetDataContext()
{
return new DatabaseDataContext();
}
* This source code was highlighted with Source Code Highlighter .
public static DatabaseDataContext GetDataContext()
{
return new SafetyDatabaseDataContext();
}
* This source code was highlighted with Source Code Highlighter .
6.結論
このソリューションは、記事の冒頭で説明したすべての要件を完全に満たしています。 実際にはアプリケーションの変更は必要なく、アプリケーションレベルで変更に関連する変更履歴とデータを保存できます。 さらに、履歴を実行するためのすべての操作はデータベースで実行され、アプリケーションへのアクセスを取得した潜在的な攻撃者が変更に関連するデータ（この場合はsiteUserID）である場合、履歴をバイパスする何かを変更することはできません。