Web サイト共有サービスでの DB 機能提供の実装


以前のブログの記事で公開しました IIS7.x での共有 Web ホスティングのためのサンプルアプリケーション について、以下の機能を追加する予定でおります。

1. ディスククォータの制御
2. ファイルスクリーン機能の実装
3. データベースとデータベースの管理ユーザー作成

1 と 2  の機能については、すでにこのブログでサンプルとしてコードをご紹介しましたが、今回は 3 の "データベースとデータベースの管理ユーザー作成" 機能について書きます。

 

この機能の目的

"IIS の共有 Web ホスティングサービスと、データベースが何の関係が?" と、思われるかもしれませんが、はい、実は機能的にはまったく何の関係もございません。

この機能を実装する意図は、共有サービスを利用する管理ユーザーに、ある程度自由に使用できるデータベースの機能を提供することです。

たとえば、Web サイトには静的なコンテンツだけでなく、アプリケーションも配置されることが多々ありますが、それらアプリケーションの多くはデータベースを使用します。

それらデータベースの作成を、セキュリティとリソースの配分をコントローラブルに行えるようにすることが今回の実装の目的です。

ということで、今回のサンプルコードは以下の要件で作成しています。

  1. ターゲットとなるのは SQL Server (MySQL はよく知らないので)
  2. ユーザーアカウントを作成し、指定されたデータベースのみアクセス権(db_ower)を与える
  3. データベースはユーザーが DL 可能とするためファイル形式で作成する
  4. データベースのサイズ(初期/増分/最大)を指定可能とする

以上の要件を踏まえて書いてみたのが以下の SQL 文です。

–データベースを作成
CREATE DATABASE contoso
ON
(NAME = contoso_dat,
  FILENAME = ‘E:\webSite\contosoroot\App_Data\contoso.mdf’,
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 5 )
LOG ON
(NAME = contoso_log,
  FILENAME = ‘E:\webSite\contosoroot\App_Data\contoso.ldf’,
  SIZE = 5MB,
  MAXSIZE = 25MB,
  FILEGROWTH = 5MB);
GO

USE master

— ログインアカウントを作成
CREATE LOGIN contosoAdmin 
   WITH PASSWORD = ‘P@ssword’,
   DEFAULT_DATABASE = contoso;
GO

— 作成したデータベースに切り替え
USE contoso

— ユーザーアカウントを作成
CREATE USER contosoAdmin FOR LOGIN contosoAdmin;

— 作成したアカウントにロールを設定
EXEC sp_addrolemember N’db_owner’, N’contosoAdmin’;
GO

この SQL 文が行っている処理は以下の通りです。

  1. "contoso" という名前のデータベースを作成
  2. サイズ制限をかけたデータファイル(*.mdf)を作成
  3. サイズ制限をかけたログファイル(*.ldf)を作成
  4. SQL Server へのログインアカウントを "contosoAdmin" を作成
  5. データベース"contoso" のユーザーアカウント "contosoAdmin" を作成
  6. ユーザーアカウント "contosoAdmin" にロール "db_owner" を割り付け

これでユーザーは、作成したアカウント "contosoAdmin" を使用して SQL Server にログインし、データベース "contoso" を自由に操作することができます。

mdf/mdl ファイルにはサイズ制限をしてあるので、わんぱくなユーザーにやんちゃな使い方をされてディスク容量が足りなくなる、という状況も回避できるでしょう。また保存先を FTP サイトで公開している位置に作成すれば、ユーザーはデータベースをダウンロードして、ローカルで使用することができます。

さて、SQL 文はけっこう簡単にできたのですが、ストアドプロシージャがどうにも….。

コンパイルエラーを避けつつ、四苦八苦しながら作成したら、以下のように EXEC 文ばかりの非常にイケてないものが出来上がりました。

CREATE PROCEDURE [dbo].[sp_createUserAndDBForWebsite]
(
   @dbName nvarchar(50),
   @mdfPath nvarchar(500),
   @ldfPath nvarchar(500),
   @startSize nvarchar(3),
   @maxSize nvarchar(3),
   @fileGrowth nvarchar(3),
   @userID  nvarchar(20),
  @passWord nvarchar(20)
)
AS
BEGIN TRY
    — データベースの作成
EXEC (‘CREATE DATABASE ‘ +  @dbName +
   ‘ ON (NAME=’ +  @dbName + ‘_dat, FILENAME=”’ +
   @mdfPath + ‘\’ + @dbName + ‘.mdf”, SIZE=’ + @startSize +
   ‘, MAXSIZE=’ + @maxSize + ‘, FILEGROWTH=’ + @fileGrowth + ‘ ) ‘ +
   ‘LOG ON (NAME =’ + @dbName + ‘_log, FILENAME=”’ +
   @ldfPath + ‘\’ + @dbName + ‘.ldf”, SIZE=’ + @startSize + ‘MB, MAXSIZE=’ + @maxSize +
   ‘MB, FILEGROWTH=’ + @fileGrowth + ‘MB);’);
END TRY

— エラー処理
BEGIN CATCH
— エラーナンバーを返して処理を抜ける
RETURN ERROR_NUMBER();
END CATCH

— ログインアカウントの作成
BEGIN TRY
    EXEC(‘CREATE LOGIN ‘ + @userID + ‘ WITH PASSWORD=”’ + @passWord +
      ”’,DEFAULT_DATABASE=’ + @dbName + ‘;’);
END TRY

— エラー処理
BEGIN CATCH
   — 作成したデータベースを削除
   EXEC (‘DORP DATABESE ‘ + @dbName);
   — エラーナンバーを返して処理を抜ける
   RETURN ERROR_NUMBER();
END CATCH

— データベースにユーザーアカウントを作成してロールを設定
BEGIN TRY
    EXEC(‘USE [‘ + @dbName + ‘] CREATE USER ‘ + @userID + ‘ FOR LOGIN ‘ + @userID + ‘;’ +
  ‘EXEC sp_addrolemember N”db_owner”,N”’ +  @userID + ”’;’);
END TRY

— エラー処理
BEGIN CATCH
  — 作成したデータベースを削除
  EXEC (‘DORP DATABESE ‘ + @dbName);
  — 作成したログインを削除
  EXEC (‘DORP LOGIN ‘ + @userID);
  — エラーナンバーを返して処理を抜ける
  RETURN ERROR_NUMBER();
END CATCH

RETURN 0;
 

とくに sp_addrolemember を使用するところで、EXEC 文の中で さらにEXEC 文を使用しているところがなんとも……_| ̄|〇

どなたかうまい書き方があれば教えてください。

ちなみに .NET コードでの呼び出しはこんな感じです。

SqlConnection sqlConn = new SqlConnection(接続文字列);
SqlCommand sqlCmd = new SqlCommand("sp_createUserAndDBForWebsite", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add("returnValue", System.Data.SqlDbType.Int, 4);
sqlCmd.Parameters["returnValue"].Direction = System.Data.ParameterDirection.ReturnValue;

sqlCmd.Parameters.Add("@dbName", SqlDbType.NChar).Value = "contoso";
sqlCmd.Parameters.Add("@mdfPath", SqlDbType.NChar).Value =@"C:\inetpub\contosoroot\App_Data";
sqlCmd.Parameters.Add("@ldfPath", SqlDbType.NChar).Value = @"C:\inetpub\contosoroot\App_Data";
sqlCmd.Parameters.Add("@startSize", SqlDbType.NChar).Value = "5";
sqlCmd.Parameters.Add("@maxSize", SqlDbType.NChar).Value = "50";
sqlCmd.Parameters.Add("@fileGrowth", SqlDbType.NChar).Value = "5";
sqlCmd.Parameters.Add("@userID", SqlDbType.NChar).Value = "contosoAdmin" ;
sqlCmd.Parameters.Add("@passWord", SqlDbType.NChar).Value = "P@ssword";

sqlCmd.Connection.Open();
sqlCmd.ExecuteNonQuery();
string returnValue = sqlCmd.Parameters["returnValue"].Value.ToString();
sqlCmd.Connection.Close();

見てくれの悪いコードですがちゃんと動作しますので、興味のある方は実行してみてください。

 

データベース管理機能の提供について

さて、データベースを作成した後、どのようにユーザーに管理させるか、というところなのですが、Web UI を持ったデータベース管理ツールとしては、MySQL 向けには PHP MyAdmin 、SQL Server 向けにはASP.NET Enterprise Manager(※2003 年で更新が停止)、phpMSAdmin(※2006 年で更新が停止) というオープンソースのアプリケーションがあります。

いずれも 痒いところに手が届きそうな 高機能なツールではあるのですが、今回はなるべく設置に工数をかけず、かつ、管理ユーザーの操作の自由をデータベース内に限定させたいという意図から、データベースの管理は、IIS 管理ツールからデータベースを操作するためのモジュール DataBase Manager で行うことを前提としています。

IIS 管理ツール用のモジュールは、サーバーにインストールすれば、クライアントとなる IIS 管理ツールに自動配布されるのでクライアントごと/Web サイトごとに配置や設定を行う必要はありません。

 

DataBase Manager の使い方

IIS 管理ツールを使用してデータベースの操作を行えるようにするには、IIS をホストするサーバーにあらかじめ DataBase Manager をインストールしておきます。

クライアントは IIS 管理ツールを起動し、IIS サーバーがホストしている Web サイトに接続します。(サーバーローカルで行う場合は必要ありません)

Web サイトへの接続処理の際に DataBase Manager をインストールするか否かの質問ボックスが表示されるので [OK] ボタンをクリックしてモジュールをインストールします。(サーバーローカルの場合は表示されません)

Web サイトの [機能ビュー] を見ると DataBase Manager のアイコンが作成されているので、ダブルクリックします。

データベースマネージャーの画面が表示されるので、データベースの接続アイコンをクリックします。

[接続の追加] ダイアログボックスが表示されるので、必要事項を入力します。

ちなみに [Server] の欄に入力するのはサーバーからみた SQL Server のインスタンス名です。たとえば、IIS と同じサーバーに SQL Express がインストールされているのであれば、クライアントの IIS 管理ツールであっても ".\sqlExpress" となります。

接続が完了すると、ツリーにデータベ��スが追加され、SQL Server の管理ツールと同じ要領で操作を行うことができます。

もちろんクエリーも発行できます。

さて、ツリーを見て "LocalMySqlServer" という文字に目が止まった方もいるかと思います。

じつは、MySQL の oleDb プロバイダーをインストールすると、IIS DataBase Manager から MySQL も操作できる……らしいです。

mysql-connector-net-6.2.2.zip

http://dev.mysql.com/downloads/mirror.php?id=379362

 

と、まあ、こんなふうにデータベースを適切に作成さえしてあげれば、低いコストと工数でユーザーにデータベースのサービスも提供可能です。

すでに IIS の管理共有サービスを提供している方は、非常に簡単にサービスのネタを増やせますので、ぜひお試しください。

では。

Real Time Analytics

Clicky

Comments (0)

Skip to main content