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 文では出来ないので、もし削除し再度作り直すなら「手動」ということになる。
- [ファイル名を指定して実行]→「%SystemRoot%\system32\compmgmt.msc /s」
- [ローカル ユーザーとグループ]→[ユーザー]で[新しいユーザー]→「Mary」を作成。
- [パスワード] を設定し[作成] で、新しいローカルの 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内関連ページ
- SQL文:SQLステートメント
- SQLステートメント
- SQLクエリー
- SQL文:ユニオンクエリー
- SQL文:パススルークエリー?
- SQL文:データ定義クエリー
- SQL文:サブクエリー?
- VB2005リファレンス(覚え書き)
- Microsoft SQL Server 関連
[ 編集 ] ▲上へ
2009年08月21日(金) 19:29:57 Modified by sakura_momo2