最近更新したページ
2013-10-20
2013-09-29
2013-09-23
2012-01-07
2011-11-09
2011-10-23
2011-10-09
2011-10-01
2011-09-29
2011-09-03
2011-08-07
2011-08-02
2011-07-29
2011-07-10
2011-05-05
2011-05-04
2011-04-24
2011-04-13
2011-04-05
2011-03-26
2011-02-18
2011-02-15
2010-12-26
2010-12-07
2010-12-05
2010-11-23
2010-09-28
2010-09-23
2010-08-26
2010-08-22
2010-07-16
2010-01-17
2010-01-11
2009-10-04
2009-08-21
2009-08-13
2009-06-18
2009-06-01
2009-04-29
2009-02-16
2009-02-11
2009-02-03
2008-07-22
2008-07-21
2008-07-15
2008-07-14
2008-07-13
2008-07-12
2008-07-08
2008-07-05
2008-06-28
2008-06-17
2008-06-05
2008-06-02
2008-06-01
2008-05-29
2008-05-26
2008-05-21
2008-05-19
2008-05-18
2007-10-31
2007-10-27
2007-09-28
2007-09-23
2007-09-17
2007-09-16
2007-09-14
2007-09-11
2007-06-18
2007-04-15
2006-12-21
2006-11-30
2006-11-22
2006-08-17
2006-03-29
2006-03-28
2006-03-27

SQL文:MS SQL Server(セキュリティ)


 Microsoft SQL Server の SQL文の最も簡単な文例を、SQL Server 2005 Books Online より抜粋引用し、見やすくしただけの項である。


DBオブジェクトに対する権限設定


Windows アカウントの作成


  • [ファイル名を指定して実行]→「%SystemRoot%\system32\compmgmt.msc /s」
  • [ローカル ユーザーとグループ]→[ユーザー]で[新しいユーザー]→「Mary」を作成。
  • [パスワード] を設定し[作成] で、新しいローカルの Windows ユーザーを作成。

※当然PCの新規ユーザーが作成されるので理解してない場合やめるが吉。
※SQL Server 云々の操作では無く、SQL Server の学習向けに新規ユーザーを作成する作業である。

ログインを作成する

 computer_name = 自PC名に置き換え。
 FROM WINDOWS は Windows がユーザーを認証することを示す。
 オプションの DEFAULT_DATABASE 引数は、接続文字列が Mary なら、 Mary を TestData データベースに接続。
 このステートメントでは、セミコロンが Transact-SQL ステートメントのオプションの終了文字として使用される。

CREATE LOGIN [computer_name\Mary]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO

 これでユーザー名 Mary が承認され、SQL Server のこのインスタンスへのアクセスが認証さる。
 インスタンスが複数ある場合、Mary がアクセスする各インスタンスでログインを作成する必要がある。
 Mary はドメインのアカウントではないため、このユーザー名はこのコンピュータでしか認証できない。

 ※一度 CREATE LOGIN [computer_name\Mary] ・・・ を実行することで、サーバーの[セキュリティ][ログイン]内にログイン情報が登録されるため再実行するとエラーとなる。

 ※Mary は SQL Server のこのインスタンスにアクセスできるが、データベースにアクセスする権限は無く、データベースのユーザーとして承認されるまで TestData にもまだアクセスできない。

データベースへのアクセス権付与


 Mary にアクセス権を与えるには、TestData データベースに切り替えてから CREATE USER ステートメントを使用し、Mary という名のユーザーにそのログインをマップする。

USE [TestData];
GO

CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO

 これで、Mary は、SQL Server と TestData の両方にアクセスできる。

 これも重複実行するとエラーが発生するようである。

ビューとストアド プロシージャの作成


 「ビュー」や「ストアド プロシージャ」のような「データベース オブジェクト」を作成し、Mary にこれらのオブジェクトへのアクセスが可能である。

  • 「ビュー」は、格納された SELECT ステートメント。
    • ビューに対しては、テーブル同様クエリを実行できる。・・・が、パラメータは使用できない。
  • 「ストアド プロシージャ」は、バッチとして実行される 1 つ以上の Transact-SQL ステートメント。
    • ストアド プロシージャは、ビューよりも複雑である。
    • 入力と出力のパラメータを指定でき、IF ステートメントや WHILE ステートメントなど、コードの流れが制御可能。データベース内の繰り返し操作にはストアド プロシージャが一般的に使用される。


ビューを作成する


この例では、CREATE VIEW を使用して、Products テーブル内の 2 つの列だけを選択するビューを作成

CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO

 ※これも TestData の ビューフォルダ内に vw_Names というフィールドを作成するので、重複実行するとエラーを返す。

ビューのテスト


ビューはテーブルと同じように処理される。ビューにアクセスするには SELECT ステートメントを使用します。

SELECT * FROM vw_Names;
GO

ストアド プロシージャを作成する

  • 次のステートメントでは・・・
    • pr_Names という名前のストアド プロシージャを作成
    • @VarPrice という名前の、money データ型の入力パラメータを受け入れます。
    • このストアド プロシージャによって、money データ型から varchar(10) 文字データ型に変更される入力パラメータと連結されるステートメント Products less than が出力されます。
    • 次に、ビューに対して SELECT ステートメントが実行され、WHERE 句の一部として入力パラメータが渡されます。
    • これによって、入力パラメータ値よりも価格が安い製品がすべて返されます。

 CREATE PROCEDURE を使用し、価格のパラメータを受け入れ、指定されたパラメータ値よりも価格が安い製品のみを返すストアド プロシージャを作成している。

CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @varPrice;
   END
GO


ストアド プロシージャのテスト

 このプロシージャにより、レッスン 1 で Products テーブルに入力した、価格が 10.00 より安い 2 つの製品の名前が返される。

EXECUTE pr_Names 10.00;
GO

 ※早い話が、サブルーチンコールだね。(多分)

 とにかく、CREATE 関係のステートメントは、一時的なコード生成ではなく、TestData 内のデーブル、プログラミング、ビュー、内の各所にそれらを保存するので、同一の CREATE コマンドを実行するとすでに存在するためエラーとなる。

 また、記述した SQL文をファイルとして保存できるが、それとはまた別の次元の動作なので注意。


データベース オブジェクトへのアクセス権の付与


 管理者は Products テーブルおよび vw_Names ビューから SELECT を実行し、pr_Names プロシージャを実行できますが、ユーザー Mary は実行できません。Mary に必要な権限を付与するには、GRANT ステートメントを使用します。

手順

 次のステートメントを実行して、pr_Names ストアド プロシージャの EXECUTE 権限を Mary に与えます。

GRANT EXECUTE ON pr_Names TO Mary;
GO

 ※こいつは、重複実行してもエラーは出ない。

 Mary はストアド プロシージャを使用して Products テーブルにのみアクセスできます。
 Mary がビューに対して SELECT ステートメントを実行できるようにする場合は、GRANT SELECT ON vw_Names TO Mary も実行する必要があります。データベース オブジェクトへのアクセス権を削除するには、REVOKE ステートメントを使用します。

 ※だんだんわかんなくなってきた・・・。

 要するに、DB の管理者(ユーザー)としてログインしてれば、何でもできるが、Mary でログインして実行権の無いストアドプロシージャを実行させることは出来ないので、実行権限を付与しなさい。ってことと思うが・・・。

GRANT について

 ストアド プロシージャを実行するには、EXECUTE 権限が必要です。
 データにアクセスしたり、データを変更するには、SELECT、INSERT、UPDATE、および DELETE 権限が必要です。
 GRANT ステートメントは、テーブルを作成する権限など、他の権限にも使用されます。

 ・・・?

データベース オブジェクトに対する権限の構成

 ※ここまでのまとめかなっ?

 ユーザーはログインすることで、SQL Server に接続する権限を与えられます。
 このログインで、ユーザーは特定のデータベースにアクセスできます。

 データの読み取り、アクセス、および変更を行う権限をユーザーに与えるには、GRANT ステートメントを使用します。

 ビューとは、1 つの SELECT ステートメントであり、ユーザーにはテーブルのように表示されます。
 ストアド プロシージャは、バッチとして実行される 1 つ以上の Transact-SQL ステートメントです。


[ 編集 ] ▲上へ


まとめ


Windows の新規アカウント作成


  • Windows のアカウント作成は、SQL 文では出来ないので、もし削除し再度作り直すなら「手動」ということになる。
    1. [ファイル名を指定して実行]→「%SystemRoot%\system32\compmgmt.msc /s」
    2. [ローカル ユーザーとグループ]→[ユーザー]で[新しいユーザー]→「Mary」を作成。
    3. [パスワード] を設定し[作成] で、新しいローカルの Windows ユーザーを作成。

ログイン・アクセス権・ビュー・ストアド プロシージャ


  • 1) アクセス権の設定とビューの作成

/*
   TestData のログイン・アクセス権の作成
   ※computer_name = 自PC名へ要置換
*/
CREATE LOGIN [computer_name\Mary]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO
-- Mary へアクセス権付与
USE [TestData];
GO
CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO
-- ビューの作成と動作確認
CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO
SELECT * FROM vw_Names;
GO

 ※computer_name を各自環境に必ず合わせる。


2) ストアド プロシージャの作成とテスト

/*
   プロシージャの作成
*/
CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @varPrice;
   END
GO
/*
    動作チェック
*/
EXECUTE pr_Names 10.00;
GO


データベース オブジェクトへのアクセス権の付与


GRANT EXECUTE ON pr_Names TO Mary;
GO

 ※ユーザー Mary で動作確認してない・・・。TestData のプロパティを確認するとデータベースを作成したログオンユーザー名 "computer_name\Owner" とかが「管理者」となっている。OS の管理者とは分けて考えると、Mary で TestData へアクセスした場合、アクセス権の付与が無いと拒否されることを予想できる。


[ 編集 ] ▲上へ


データベース・SQL文・SQLクエリー(query)関連リンク


参考になる書籍など


 ・SQL Serverデータベース・プログラミング ・ASP.NET ・SQL全般

  • ASP.NET 関連良書・書籍





wiki内関連ページ



[ 編集 ] ▲上へ
2009年08月21日(金) 19:29:57 Modified by sakura_momo2




スマートフォン版で見る