宇宙仮面の
C# Programming

 
Image

システム情報・セキュリティ

ロール、権限、セキュリティは複雑ですが、とても重要です。

 

1.目次

1.目次
2.システム情報
 2.1 システムデータベース
 2.2 システムテーブル
 2.3 プロセスの表示
3.認証モード
 3.1 SQL Server 認証
 3.2 Windows 認証
 3.3 sa アカウント
 3.4 guest アカウント
 3.5 dbo について
4.ロール
5.権限
6.照合順序
 

2.システム情報

 

2.1 システムデータベース

 

名称 情報 説明
master システムレベルのすべての情報が格納される。 Master再構築ユーティリティ(\Tools\binn\rebuildm.exe)で修復することができるが、すべてのデータが削除されてしまうので、かならず再構築前に、データファイルとログファイルのバックアップをする。
model 新たに作成されるユーザデータベースのテンプレート SQL起動時に必要。
tempdb 一時テーブル、一時ストアドプロシージャ、作業テーブルなどを保管する。 サーバをシャットダウンするとすべてのデータが消去されます。
msdb スケジュール、ジョブ、警告、オペレータに関する情報が格納される。 SQL Server Agentと連携する。
distributuion レプリケーション用 ヒストリおよびトランザクションデータが格納される。
pubs 出版社を想定したテスト用データベース \Install\instpubs.sql で再構築。
Northwind 商社を想定したテスト用データベース \Install\instnwnd.sql で再構築。

 

2.2 システムテーブル

 
システムテーブル 説明
syslogins ログイン情報
sysmessages システム エラーまたは警告
sysdatabases データベース
sysusers ユーザ
sysobjects オブジェクト

2.3 プロセスの表示

 
項目 コマンド例
自分のプロセスID SELECT @@spid
プロセス一覧 EXEC sp_who
 

3.認証モード

SQL Server 2000 には Windows 認証と SQL Server 認証という2つの認証方式があります。SQL Server認証は、古いバージョンのSQL Serverとの互換性のためであり、基本的には Windows 認証を使用します。

認証モードには、Windows 認証と SQL Server 認証の2つをサポートする混合モードと、Windows 認証から選択することになります。98/Me では、SQL Server 認証しかサポートされていません。

Image

ここで、認証にはデータベースのセキュリティログインと、データベースのユーザがあり、認証方法によりストアドが違うので注意が必要です。

Image

セキュリティログイン

操作 SQL 認証 Windows 認証
セキュリティ ログインの追加 sp_addlogin sp_grantlogin
セキュリティ ログインの拒否 - sp_denylogin
セキュリティ ログインの削除 sp_droplogin sp_revokelogin

※ sp_adduser, sp_dropuser は互換性のためのもので、sp_addlogin, sp_droplogin を使うこと。

また、sp_grantdbaccess により、セキュリティ アカウントの追加とデータベースで操作する権限を同時に付与することができます。

操作 SQL 認証 Windows 認証
セキュリティユーザの追加と権限の付与 sp_grantdbaccess sp_grantdbaccess
セキュリティユーザの削除 sp_revokedbaccess sp_revokedbaccess

3.1 SQL Server 認証

SQL Server 認証のユーザを T-SQL で追加するには、sp_addlogin を使用します。

Image

実行結果

Image

この段階で、Northwind にアクセスしてみると、データベースのユーザにセットしていないにもかかわらず、次のようにデータベースにアクセスができるようになります。

Image

これは Northwind に guest ユーザーが許可されているため、user1 アカウントが guest ユーザーとしてみなされているためです。

試しに、sp_droplogin で、guest ユーザを削除して、

Image

user1 からアクセスしてみると、user1 には権限がないため、SELECT が拒否されます。

Image

そこで、sp_grantdbaccess により、明示的にデータベースに user1 のアクセスを許可すると、再びアクセス可能になります。

Image

実行結果

Image

3.2 Windows 認証

Windows 認証では、Windows のユーザ情報を使用してアカウント名とパスワードを確認します。

ntuser1 というWindows ユーザを作ってデータベースにアクセスしようとすると、当然ながらSQL サーバーにログインできないため、アクセスできません。

Image

アクセスを許可するには、sp_grantdbaccess でアクセス権を許可します。

Image

これにより、次のようにSELECTを実行することができるようになります。

Image

Windows 認証のログインの許可、拒否については、sp_denylogin, sp_grantlogin, sp_revokelogin を使用します。

●拒否の例

Image

サーバーアクセスが拒否になる。

Image

Image

●許可の例

Image

サーバーアクセスが許可になる。

Image

●取り消しの例 

Image

アカウントが削除される。

Image

3.3 sa アカウント

システム管理者 の sa アカウント は、旧バージョンとの互換性を維持するために提供される特別なログインです。sa は通常は使用してはいけない。その代わりに、データベースのシステム管理者を sysadmin のメンバにします。sa はセキュリティホールになりやすいので、ウィンドウズ認証を使うべきである。

3.4 guest アカウント

次の2つの条件を満たす場合、guest ユーザーであるとみなし、ユーザー アカウントなしでデータベースにログインできます。

  • SQL Serverのインスタンスへのアクセス権があるが、自分のユーザー アカウントでデータベースにアクセスできない。
  • データベースに guest ユーザー アカウントが含まれている。

guest ユーザーがデータベースに登録されていると、データベースに明示的に登録していないユーザーであっても、データベースに対して public ロールの権限でアクセスすることができてしまいます。不必要なアクセスを防ぐため、guest ユーザーの使用は避けることが推奨されています。ただし、データベース master, msdb, tempdb は除く。

3.5 dbo について

dbo はデータベース内ですべての動作を実行できる権限が暗黙に与えられたユーザー。sysadmin のメンバは dbo にマッピングされる。sysadmin のメンバによって作成されたオブジェクトは自動的にdbo に所属する仕様になっている。

4.ロール

 

固定サーバー ロール 権限
sysadmin SQL Server ですべての操作。
serveradmin 環境設定オプションの設定、サーバーのシャットダウン。
setupadmin リンク サーバーと起動プロシージャの管理。
securityadmin ログインと CREATE DATABASE 権限の管理、エラー ログの読み取り、パスワードの変更。
processadmin SQL Serverのプロセスを管理。
dbcreator データベースの作成、変更、削除。
diskadmin ディスク ファイルの管理。
bulkadmin BULK INSERT ステートメントの実行。

 

固定データベース ロール 権限
db_owner すべての権限。
db_accessadmin ユーザー ID の追加と削除。
db_securityadmin すべての権限、オブジェクト所有権、ロール、ロール メンバシップの管理。
db_ddladmin DDL の実行。
GRANT、REVOKE、DENY の各ステートメントは実行できない。
db_backupoperator DBCC、CHECKPOINT、BACKUP の実行。
db_datareader ユーザー テーブルの読み込み。
db_datawriter ユーザー テーブルの変更。
db_denydatareader ユーザー テーブルの読み込みができない。
db_denydatawriter ユーザー テーブルの変更ができない。

 

5.権限

ユーザができる操作は、次の権限により決定されます。

権限 説明
オブジェクト権限 テーブル全体、ビュー、テーブルまたはビューの各列、ユーザー定義の関数、ストアド プロシージャおよび関数に適用されます。
ステートメント権限 CREATE DATABASE などのステートメント権限は、データベースで定義された特定のオブジェクトではなくステートメントそのものに適用されます。ステートメント権限は次のとおりです。
  • BACKUP DATABASE
  • BACKUP LOG
  • CREATE DATABASE
  • CREATE DEFAULT
  • CREATE FUNCTION
  • CREATE PROCEDURE
  • CREATE RULE
  • CREATE TABLE
  • CREATE VIEW
暗黙の権限 定義済みシステム ロールのメンバまたはデータベース オブジェクトの所有者のみが実行できる操作を制御します。たとえば、固定サーバー ロール sysadmin の権限。

オブジェクト権限の例

  • テーブル全体とビュー SELECT, INSERT, UPDATE, DELETE ステートメント権限

Image

  • テーブルまたはビューの各列 SELECT, UPDATE ステートメント権

Image

 

GRANT 文で権限を設定することができる。

Image

実行結果

Image

同様にDENY 文で権限を拒否することができる。

Image

実行結果

Image

 

6.照合順序

SQL Server 2000 は2 つのカテゴリの文字データ型をサポート。
  • Unicode データ型の nchar、nvarchar および ntext。
    コード ページは適用されません。
  • 非 Unicode 文字データ型の char、varchar および text。
    1 バイト コード ページまたはダブルバイト コード ページで定義される。

並べ替え順

並べ替え順は SQL Server が文字データの解釈、照合、比較、表示に使用するルールを指定します。

"a" が "b" より大きいか、小さいか、"a" と "b" が等しいかを定義する。

並べ替え順は、たとえば、"m" と "M'" が等しいかどうかなど、照合順序が大文字小文字を区別するかどうかを定義します。また、"á" と "ä" が等しいかどうかなど、照合順序がアクセント符号の有無を区別するかどうかも定義します。

SQL Server 2000 では、1 つの照合順序につき 2 つの並べ替え順を使用します。1 つは Unicode データ用で、もう 1 つは文字コード ページ用です。

多くの SQL Server 照合順序は同じコード ページを使用しますが、コード ページの並べ替え順は異なります。そのため、サイトでは次の項目を選択できます。

  • 単にビット パターンに相当する数値に基づいて文字を並べ替えるかどうか。バイナリ並べ替えでは SQL Server が調整を行う必要がなく、高速で単純な並べ替えアルゴリズムを使用できるため、バイナリ並べ替えは最も高速です。バイナリ並べ替え順では、常に大文字小文字が区別されます。コード ページ内のビット パターンは、特定の言語の辞書の規則で定義されている順序に並んでいないことがあるため、バイナリ並べ替えでは、その言語を使用するユーザーが期待する順序に文字が並べ替えられないことがあります。
  • 大文字小文字を区別するかどうか。
  • アクセント符号の有無を区別するかどうか。
     

照合順序の問題を最小限にする

複数の言語を反映する文字データを格納する必要がある場合は、char、varchar、および text の各データ型の代わりに、常に nchar、nvarchar および ntext の各 Unicode データ型を使用することによって、照合順序の互換性の問題を最小限に抑えることができます。Unicode データ型を使用することによって、コード ページ変換の問題を解消できます。