宇宙仮面の
C# Programming

 
SQL 2008

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

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

1.目次

1.目次
2.システム情報
 2.1 システムデータベース
 2.2 カタログビュー
 2.3 プロセスの表示
3.ロール
4.権限
5.照合順序

2.システム情報

 

2.1 システムデータベース

 

名称 情報 説明
master SQL Server のインスタンスのシステムレベルの情報をすべて記録。 Master再構築ユーティリティ(\Tools\binn\rebuildm.exe)で修復することができるが、すべてのデータが削除されてしまうので、かならず再構築前に、データファイルとログファイルのバックアップをする。
model SQL Server のインスタンスで作成されたすべてのデータベースのテンプレートとして使用される。 SQL起動時に必要。
tempdb 一時テーブル、一時ストアドプロシージャ、作業テーブルなどを保管する。 サーバをシャットダウンするとすべてのデータが消去されます。
msdb 警告やジョブのスケジュールを設定するために SQL Server エージェントにより使用されます。 SQL Server Agentと連携する。
Resource SQL Server 2005 に装備されているシステム オブジェクトを格納する読み取り専用のデータベース。 システム オブジェクトは、物理的には Resource データベースに保存されていますが、すべてのデータベースの sys スキーマに論理的に表示される。

 

2.2 カタログビュー

SQL Server 2005では、システムテーブルは互換性ビューという形で提供され、旧バージョンとの互換性のためにのみ用意されています。一部の列ではオーバーフローが発生する可能性があります。これを避けるためには新しいカタログビューを使用するべきです。

 

カタログ ビュー 説明
CLR アセンブリ sys.assemblies
sys.assembly_files
sys.assembly_references
データベースとファイル sys.backup_devices
sys.databases
sys.database_files
sys.master_files
データベース ミラーリング監視サーバー sys.database_mirroring
sys.database_mirroring_endpoints
sys.database_mirroring_witnesses
データ領域とフルテキスト sys.data_spaces
sys.destination_data_spaces
sys.database_files
sys.filegroups
sys.fulltext_document_types
sys.fulltext_index_catalog_usages
sys.partition_schemes
エンドポイント sys.database_mirroring_endpoints
sys.endpoints
sys.endpoint_webmethods
sys.http_endpoints
sys.service_broker_endpoints
sys.soap_endpoints
sys.tcp_endpoints
sys.via_endpoints
拡張プロパティ sys.extended_properties
リンク サーバー sys.linked_logins
sys.remote_logins
メッセージ (エラー用) sys.messages
オブジェクト sys.allocation_units
sys.assembly_modules
sys.check_constraints
sys.columns
sys.computed_columns
sys.default_constraints
sys.events
sys.event_notifications
sys.extended_procedures
sys.foreign_keys
sys.fulltext_indexes
sys.fulltext_index_columns
sys.identity_columns
sys.indexes
sys.index_columns
sys.key_constraints
sys.numbered_procedures
sys.numbered_procedure_parameters
sys.objects
sys.parameters
sys.partitions
sys.procedures
sys.service_queues
sys.sql_dependencies
sys.stats
sys.stats_columns
sys.sql_modules
sys.synonyms
sys.tables
sys.triggers
sys.trigger_events
sys.traces (Transact-SQL)
パーティション関数 sys.partition_functions
sys.partition_parameters
sys.partition_range_values
スカラ型 sys.assembly_types
sys.types
スキーマ sys.schemas
セキュリティレベルのビュー sys.database_permissions
sys.database_principals
sys.database_role_members
sys.master_key_passwords
サーバーレベルのビュー sys.server_permissions
sys.server_principals
sys.server_role_members
sys.sql_logins
sys.system_components_surface_area_configuration
暗号化ビュー sys.assymmetric_keys
sys.certificates
sys.credentials
sys.crypt_properties
sys.key_encryptions
sys.symmetric_keys
Service Broker sys.conversation_endpoints
sys.conversation_groups
sys.remote_service_bindings
sys.routes
sys.service_contracts
sys.service_contract_message_usages
sys.service_contract_usages
sys.service_message_types
sys.service_queue_usages
sys.services
sys.transmission_queue
サーバー全体の構成 sys.configurations
sys.fulltext_languages (Transact SQL)
sys.traces(Transact SQL)
sys.trace_categories(Transact SQL)
sys.trace_columns(Transact SQL)
sys.trace_events(Transact SQL)
sys.trace_event_bindings(Transact SQL)
sys.trace_subclass_values (Transact SQL)
XML スキーマ (XML 型システム) sys.xml_schema_attributes
sys.xml_schema_component_placements
sys.xml_schema_components
sys.xml_schema_elements
sys.xml_schema_facets
sys.xml_indexes
sys.xml_model_groups_
sys.xml_schema_collections
sys.xml_schema_namespaces
sys.xml_schema_types
sys.xml_schema_wildcard_namespaces
sys.xml_schema_wildcards

実行例

Image

.3 プロセスの表示

 
現在のユーザー プロセスのセッション ID
SELECT @@SPID

SQL Server 2000では、@@SPIDはサーバープロセスIDと呼ばれていた。

Image

プロセス一覧

sp_who [[@login_name =] 'login']

Image

1 〜 50 の SPID 値は、内部で使用するために予約されていて、51 以上の SPID 値はユーザー セッションを表す。

.ロール

 

固定サーバー ロール 権限
sysadmin SQL Server ですべての操作。

既定では、ローカルの管理者グループである Windows の BUILTIN\Administrators グループのメンバはすべて、sysadmin 固定サーバー ロールのメンバになります。

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

 

固定データベース ロール 権限
db_owner データベースでのすべての構成作業とメンテナンス作業。

※ SQL Server 2005 では、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 ユーザー テーブルの変更ができない。

 

.権限

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

権限 説明
オブジェクト権限 テーブル全体、ビュー、テーブルまたはビューの各列、ユーザー定義の関数、ストアド プロシージャおよび関数に適用されます。
ステートメント権限 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

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

Image

実行結果

Image

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

Image

実行結果

Image

 

.照合順序

照合順序の種類

SQL Server 2005 の照合順序には、Windows 照合順序と SQL 照合順序という 2 種類のグループがある。

Windows 照合順序

SQL Server 2005 ではできる限り Windows 照合順序を使用する。特に、データベースに Unicode の列と Unicode 以外の列が混在している場合は、Windows 照合順序を使用する。

Windows 照合順序: http://msdn2.microsoft.com/ja-jp/library/ms143515.aspx

SQL 照合順序

SQL 照合順序は以前のバージョンの既存の SQL Server インスタンスとの互換性を保つ場合、または以前のバージョンの SQL Server で SQL 照合順序を使用して開発したアプリケーションの互換性を保つ場合に限定すること。

SQL 照合順序: http://msdn2.microsoft.com/ja-JP/library/ms144250.aspx

データ型の選択

複数の言語を使用するユーザーをサポートする場合は、すべての文字データに対して Unicode データ型の nchar、nvarchar および ntext を使用すること。