トランザクションとセキュリティ

(2005.11 更新)

（ＳＱＬＳｅｒｖｅｒを直接利用する実習は今回で最後の予定です。しかし念のため次回以降もパスワードは忘れないようにしてください。）

トランザクション

トランザクションとは、データベースでデータを処理する為の一連の処理手続きのことです。
例えば、これまでSQLサーバ上で実行したSQL文、

insert into SHIILE values ( '2003/12', 'ABC', 10)
update ZAIKO set KAZU=KAZU+10 where NAME = 'ABC'
select * from ZAIKO

などもトランザクションといえます
ただし、これまでの演習では、実は、

１つのSQL文　＝　１つの処理　＝　１つのトランザクション

として処理されていました。
つまり、上記のSQLの例では、 insert と　update と select の各処理は、
　一つの命令の処理が完了　→　データベースが更新される　→　次の命令を処理
というように動作します。
　しかし、これでは問題が生じる場合があります。例えば、insert で品物の入庫数を記録後にデータベースや通信回線に異常が生じて update が実行時エラーになったとします。
　すると、入庫数は記録されているのに、在庫数の値が増えていないという矛盾が生じてしまいます。

そこで、上記の処理の様に、複数のSQL文を、

複数のSQL文　＝　1連の処理　＝　１つのトランザクション

としてデータベースで一括して処理する方法が必要になります。
要点：
■トランザクションの実行中にエラーが生じると、トランザクションの処理全体が失敗したことになり、データベースはトランザクションの実行前の状態の元に戻ります。
■トランザクションの実行中は、他のトランザクションが割り込んで処理されることはありません。
■トランザクションの実行中に、エラー処理をしてデータベースを処理前の状態に戻す（ロールバック）ことができます。

トランザクション処理とROLLBACK・COMMIT

マルチユーザ環境でのデータベースの問題点とその対処法について説明する。

• 排他制御：
  リソース（データ：情報、デバイス：装置）を同時に複数の利用者で利用しないように制御すること。
  （リソースの共有における問題点の確認）
• 復旧処理
  コミット：　データベースに送ったトランザクションの処理を完了させる。
  ロールバック：　トランザクション実行時に障害が発生してトランザクションが完了しなかった場合などに、以前にコミットした時点か、以前にトランザクションをセーブした時点までデータベースの状態を戻すこと
  ロールフォワード：　処理をコミットした時点からもう一度トランザクションを実行しなおしてデータベースを復旧する。
  トランザクションログ：データベース復旧用の記録。ロールバックに必要。
• トランザクション処理の原子性（atomicity）：　データベースに対する更新などの処理が、”完了”か”失敗”かのどちらかになることが保障されること。
  例）　テーブルのレコードを変更する際に、
  
  update ITEM set cost = 10 where cost<10
  
  として処理する場合、
  　”全ての１０未満のcostのデータが変更される”
  もしくは
  　”一つもデータが変更されないか”（エラーが起きた場合など）
  のどちらかになることが保障される。　万が一、更新処理の途中でエラーが起きた場合でも、一部の１０未満のcostのレコードだけが変更されるという事態が発生しないようにする。

ＳＱＬＳｅｒｖｅｒでトランザクション処理を行う為のコマンド

• BEGIN　TRANSACTION
  トランザクション処理の開始宣言
• ROLLBACK
  トランザクション処理を破棄し、データベースを処理前（トランザクション開始前）の状態に戻す
• COMMIT
  トランザクション処理を完了し、データベースに結果を反映させる
• SAVE　TRANSACTION
  ROLLBACKで復帰する為のセーブポイントを設定する。
  セーブポイントを設定した状態でROLLBACKすると、トランザクション処理の先頭ではなく、セーブポイントの時点までデータベースの状態が元に戻る

例）
BEGIN TRANSACTION
SQL文
SQL文
SQL文
COMMIT

例）
BEGIN TRANSACTION
SQL文
SQL文
SQL文
エラーが発生したら、ROLLBACK
SQL文
SQL文
COMMIT

演習課題１：　トランザクションとロールバック処理

SQLサーバに接続し、以下を行う。

SQLサーバーの利用３

演習室PCとは別の場所にあるSQLサーバにネットワークで接続し、サーバー上のデータベースで作業を行う。

• ＳＱＬサーバー接続用のアカウントとパスワードの用紙を受け取る。
  なお、この用紙についてを紛失・落とす・忘れてくるなどの管理不行き届きがある場合は、データベース演習の単位は「不可」とする。なお、管理期間はデータベースの成績が発表されるまでとする。
• ログイン用パスワードで、ＳＱＬサーバーに接続する。
  
  1. スタート→ プログラム→ コマンド・プロンプト
  2. SQLサーバー（cyteen）に接続します。
     osql -S cyteen -U アカウント
  3. パスワードを入力する
• トランザクション処理の確認：
  
  1. use db???????
     ???????は自分の学籍番号を指定。
  2. go
  3. CLIENT表を作成し、IDフィールドを主キーに設定する。
SQLServerでは、constraintキーワードで制約名（CLIENT_ID、他の制約名と重複しない名前）を指定する必要がある。
(注意：下線の部分、char()ではカッコとｒの間に空白を入れてはならない)

create table CLIENT (ID integer not null constraint CLIENT_ID primary key, NAME char(12) not null, TEL char(11))
  4. go
     上記が正常に処理されれば、以下のようなテーブルがＤＢＭＳに作成される。
     各フィールドの設定内容をよく確認すること。
     

     ＩＤ	NAME	TEL
     登録番号 主キー	名前 12文字までの'文字'データ　入力必須	電話番号 11文字までの'文字'データ

  5. 名前の部分は12文字以内、電話番号の部分は11文字以内で各自で適当にデータを考えて指定すること. IDの値は変更しないように。
insert into CLIENT values ( 1, 'Test1', '0901231111')
go
  6. select * from CLIENT
go
  7. データを２つ追加する。わざと、主キーを衝突させてエラーを起こしてみる。
insert into CLIENT values ( 2, 'Test2', '0901232222')
insert into CLIENT values ( 1, 'Test3', '0901231234')
insert into CLIENT values ( 3, 'Test3', '0901233333')
go
  8. ２つ目のデータの追加でエラーが起きたので、CLIENT表はどうなったかを確認
select * from CLIENT
go
     SQLが一行単位で処理されているので、最初と最後に追加したレコード（ID=2と3）は追加されている。
     つまり、エラーは、エラーの発生したコマンドだけがエラーで実行されず、
     エラーが起きる前のコマンドは処理されるのはもちろん、エラー発生以降のコマンドも、1行単位で独立して実行されている。
  9. 以下のトランザクション処理で、ロールバックした場合、データベースが復旧することを確認
  10. begin transaction
  11. insert into CLIENT values ( 4, 'Test4', '0901234444')
  12. rollback　（トランザクション開始前の状態に復帰させる）
  13. insert into CLIENT values ( 5, 'Test5', '0901235555')
  14. go
  15. rollbackにより、11行目のデータ（ID番号4）は登録されていないことを確認（一方のID番号5は登録されている）
select * from CLIENT
go
  16. 以下のトランザクションで、複数データの追加の際に、
      IF文を利用してエラーが起きた回数を数え、エラーが1回でも起きたらロールパックするように制御する。
      なお、@@ERROR関数は、直前のSQLの実行の状態を示す。値が０なら正常終了、それ以外はエラー（エラーの種類に応じたエラー番号）となる。
      
      ●2つの番号を同時に登録し、両方同時に登録できなければ、どちらも登録しないようなトランザクション処理の例）
  17. begin transaction
  18. declare @a as int
  19. set @a = 0
  20. insert into CLIENT values ( 4, 'Test4', '0901234444')
  21. if @@ERROR<>0
      begin
      set @a = 1
      end
  22. insert into CLIENT values ( 5, 'Test5', '0901235555')
  23. if @@ERROR<>0
      begin
      set @a = 1
      end
  24. if @a<>0
      begin
      rollback
      end
  25. commit
  26. go
  27. 22行目の実行時にエラーが起きるため、24行目でロールバックが発生し、
      20行目のデータも22行目のデータもどちらとも登録されていないことを確認
      select * from CLIENT
      go
  28. 今度は、　上の17行から26行までのプログラムを参考に、　２つのデータが同時に登録できるように
      
      20行目のinsert文で、　登録番号を　6　に変更
      22行目のinsert文で、　登録番号を　7　に変更
      
      し、トランザクション処理を行ってみよ。
      （ヒント：　上記の処理を支持されたように値を変更して実行しなおすだけ）

セキュリティ

データベースのユーザに、データベースの利用権限を設定するコマンドを紹介する。

• GRANT　権限　ON　テーブル名　TO　ユーザー
  ユーザにデータベースの利用権限を認める
• REVOKE　権限　ON　テーブル名　FROM　ユーザー
  ユーザーからデータベースの利用権限を取り上げる

　権限に指定可能なキーワード：
　　　ALL（全て）・SELECT・INSERT・UPDATE・DELETE・REFERENCESなど

　ユーザー：
　　　データベースを利用する際のユーザー名で権限を設定するユーザを指定する。
　　　PUBLIC　というキーワードでユーザを指定すると、全データベースユーザに対して権限を設定できる。

演習課題２：

SQLサーバに接続し、以下を行う。

SQLサーバーの利用２（つづき）

以下の演習は、２名以上が組み（チーム）になって行うこと。組かたは各自に任せる。
相手が見つけられない人は、こちらで指示するので申し出るように。
コンマとドット（ピリオド）が見わけにくいのでFONTを拡大してよく確認すること。

• 権限の設定（上の演習のつづき）
  以下で、' [ ]などの記号はそのまま入力すること
  1. 自分のデータベースを他人が利用できるようにアカウントを発行する。
     EXEC sp_grantdbaccess @loginame = '他の演習者のアカウント'
  2. go
  3. 発行したアカウントから、CLIENT表の閲覧権限を取上げる（禁止する）。
     revoke select on CLIENT from [他の演習者のアカウント]
  4. go
  5. CLIENT表から、名前だけを取り出して表示する為のViewを定義
     create view CLIENT_NAME as select NAME from CLIENT
  6. go
  7. 名前を表示するViewの閲覧権限を与える。
     grant select on CLIENT_NAME to [他の演習者のアカウント]
  8. go
  9. 他人のデータベースを利用してみる。
     ここで、他人のデータベースに接続する
     接続使用とするデータベースにアカウントを作成してもらって（上記1のコマンドで設定してもらう）から接続すること
     use db??????? 　（??????? はユーザ登録してもらった相手の番号）
  10. select * from CLIENT
  11. go
  12. テーブルCLIENTの閲覧は禁止されているので表示できない。（権限に関するエラーが起きる）
  13. select * from CLIENT_NAME
  14. go
  15. ビューCLIENT_NAMEは、閲覧を許可されているので名前だけが表示される
  16. exit

ここまでで、今回の演習は終了です。