2005年08月11日

SQL Server 7.0 以降で削除されたログイン ID

probe
repl_publisher
repl_subscriber
posted by w@ko at 19:02|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

Security Manager

・SQL Server 6.5 における Windows 認証グループ管理ツール

posted by w@ko at 18:57|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

Active Directory との統合

  SQL Server のサービス(インスタンス、データベース、レプリケーションパブリケーション等)を Active Directory に公開
 
インスタンス(Active Directory 上:MS-SQL-SQLServer)の登録
  Enterprise Manager → サーバーのプロパティ → [Active Directory]タブ
  sp_ActiveDirectory_Obj ストアドプロシージャ
 
データベース(Active Directory 上:MS-SQL-SQLDatabase)の登録
  Enterprise Manager → データベースのプロパティ → [オプション]タブ
  sp_ActiveDirectry_SCP ストアドプロシージャ
 
【参照】 SQL Server 2000 Books Online 「Active Directory との統合」
                                                           「Active Directory サービス」
posted by w@ko at 18:56|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

アプリケーションセキュリティの管理

ビューおよびストアドプロシージャによるセキュリティ管理
  ユーザーにテーブルに対する直接の参照、変更権限を与えたくない場合に作成
  権限設定もビュー、ストアドプロシージャのみ管理するだけで済む
 
アプリケーションロールによるクライアントアプリケーションのセキュリティ管理
posted by w@ko at 18:56|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

OS コマンドおよび ActiveX スクリプトジョブステップとアクセス権限

ジョブにスクリプト、コンパイル済みアプリケーション、バッチファイルを実行するステップを設定可
 

 
sysadmin 権限を持つユーザーのみがジョブステップを実行できるように SQL Server エージェントサービスを構成する必要あり
  → ジョブの実行は SQL Server エージェントサービスアカウント権限で実行されるため
posted by w@ko at 18:55|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ロール (SQL Server 7.0 以降)

○ サーバーロール
  … サーバー(インスタンス)単位で設定、サーバー全体に設定適用
 
固定サーバーロール
対象:ログインアカウント
サーバーレベルのログイン権限管理
ロール情報は master..sysxlogins システムテーブル内に格納
  検索時は「syslogins」で実行?
Enterprise Manager または sp_addsrvrolemember システムストアドプロシージャでログインを割り当て
sp_addsrvrolemember 'ログイン名', 'ロール名'
  固定サーバーロールメンバのみが実行可
  ユーザー定義トランザクション内では実行不可
  sp_dropsrvrolemember 'ログイン名', 'ロール名' で削除
固定サーバーロール自体の追加、変更、削除は不可
固定サーバーロールのメンバーはそのロールに他のログインアカウントを追加可

○ データベースロール
  … データベース単位で設定
  ※ SQL Server 6.5 でのグループ/別名
 
固定データベースロール
対象:データベースユーザーアカウント
データベースレベルの接続権限管理、オブジェクトの操作権限管理
ロール情報は sysusers システムテーブル内に格納
Enterprise Manager または sp_addrolemember システムストアドプロシージャでユーザーを割り当て
sp_addrolemember 'ロール名', 'アカウント名'
  sysadmin または db_owner メンバーのみ実行可
  ユーザー定義トランザクション内では実行不可
  sp_droprolemember 'ロール名', 'アカウント名' で削除
固定データベースロールの追加、変更、削除は不可
特殊ロール「public」
  全データベースユーザーが所属
  PRINT ステートメントなど権限を必要としないステートメントを実行可
  メタデータを取得できる特殊システムストアドプロシージャを実行可
  guest アカウントでデータベースにアクセス可
 
ユーザー定義データベースロール
対象:データベースユーザーアカウント
オブジェクト権限の付与簡素化、データベースユーザーのグループ化目的
データベースレベルの接続権限をユーザーが定義
設定はデータベースごとに行う
  データベースユーザーは複数のロールに入れる
作成したロールの情報はカレントデータベースの sysusers システムテーブル内に格納
Enterprise Manager または sp_addrole システムストアドプロシージャでロールを作成
sp_addrole 'ロール名'
  sysadmin、db_owner、db_securityadmin メンバーのみ実行可
  sp_droprole 'ロール名' でロール削除
Enterprise Manager または sp_addrolemember システムストアドプロシージャでユーザーを割り当て
 
 
sp_addrolemember はsysadmin、db_owner、db_securityadmin メンバー、ロールの所有者のみ実行可
循環してしまうロール設定は不可
 
アプリケーションロール
対象:アプリケーション
データベース接続許可権限をユーザーが定義
  特定のアプリケーションからのみの間接的アクセスを許可
ロールでアクセス権を与えられたアプリケーションを実行した全ユーザーがアクセス可
  詳細なアクセス権はアプリケーション側で設定、ユーザー直接権限を設定する必要なし
作成したロールの情報はカレントデータベースの sysusers システムテーブル内に格納
カレントデータベースでユーザーアカウント/ロールが既に持っている権限を一時喪失
  publicロール適用権限を除く
カレントデータベースですべてのアプリケーションロール権限は継承
完全修飾オブジェクト名を使用すると、guest アカウントが有効なデータベースにアクセス可
Enterprise Manager または sp_addapprole システムストアドプロシージャでロールを作成
sp_addapprole 'ロール名', 'パスワード'
  sysadmin、db_owner、db_securityadmin メンバーのみ実行可
  sp_dropapprole 'ロール名' で削除
  sp_approlepassword 'ロール名', '新パスワード' でロールのパスワードを変更
Enterprise Manager または GRANT、DENY、REVOKE ステートメントでロール権限を管理

 
クライアントは SQL Server 接続後、以下のストアドプロシージャを実行してロールをアクティブ化する必要あり(これ以外の方法なし)
    sp_setapprole 'ロール名', 'パスワード'
  ロールの有効範囲はカレントデータベースのみ
    → 別データベース切り替え後はそのデータベースの guest アカウント権限が許可
  sp_setapprole でアクティブ化後はユーザーが SQL Server から切断するまでロールを無効にできない
  標準データベースロールに優先 ← アプリケーションロールは排他的
アプリケーションロールをアクティブにするにはパスワードが必要
  オプションとして、ODBC ENCYPT 機能を使用してパスワード暗号化可
  暗号化関数を使用する場合、パスワードの先頭に「N」を付加して Unicode 文字列に変換
ODBC 接続プールが働く以下の環境では正しく動作せず
  アプリケーションがプールされた接続を使用、使用のたびに接続の再確立は不要
  接続のプロパティに依存
  Web アプリケーション環境(ASP、ASP.NET)
  COM+ を利用した多階層環境
posted by w@ko at 18:54|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

2005年08月09日

暗号化

SQL Server 内部の暗号化
  ログインパスワード → 必ず暗号化される
  Transact-SQL 定義
   
 
ストアドプロシージャ、トリガ、ビューの定義を syscomments システムテーブルに保存するときに暗号化可
    ステートメントオプションとして WITH ENCRYPTION をつける
 
ネットワークの暗号化
  SQL Server ネットワークユーティリティを使用して SSL 暗号化を有効にできる
    有効なサーバープロトコル全体に対して有効化/無効化設定
posted by w@ko at 18:35|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

偽装と委任

セキュリティアカウントの偽装
 
 
SQL Server がクライアントの代わりにファイルシステムなどのリソースにアクセスする際にクライアントのセキュリティコンテキストを偽装
    → リソースへのアクセス権としてクライアントの持つ権限が適用
 
セキュリティアカウントの委任
  SQL Server が別の SQL Server に接続する場合、元のクライアントの資格情報を保持
  Windows 2000 以降で Kerberos サポートが必要
  Active Directory アカウントオプションで以下のオプションを有効にする
     「アカウントは委任に対して信頼されている」
     「コンピュータを委任に対して信頼する」
  SQL Server サービスアカウントはドメインアカウント
  TCP/IP を使用
posted by w@ko at 18:35|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

権限設定

○ 権限の種類
ステートメント権限
  データベースまたはデータベース内での操作に対する権限
  Enterprise Manager → データベースのプロパティ → [権限]タブ
  sysadmin、db_owner、db_securityadmin メンバーのみ追加可
 
オブジェクト権限
  オブジェクト使用、操作に対するアクセス権
   
テーブル、ビュー、ユーザー定義関数権限 SELECT、INSERT、UPDATE、DELETE、REFERENCES
  ※ UPDATE ステートメントで WHERE 句を使用したい場合は SELECT ステートメント実行権限が必要
※ REFERENCES → 外部参照キー Enterprise Manager 表記では DRI
列権限 SELECT、UPDATE、REFERENCES
ストアドプロシージャ権限 EXECUTE
 
 
Enterprise Manager → データベース → オブジェクト右クリック → [権限の管理]
(または[プロパティ] → [権限])
 
既定(暗黙)の権限
  固定ロール権限:暗黙の管理権限 例)sysadmin ロールメンバー等
  オブジェクト所有者権限

○ 権限設定
権限設定はカレントデータベース内の権限のみ有効
権限情報は sysprotects および syspermissions システムテーブル内に格納

 
権限追加の権限は、sysadmin、db_owner、db_securityadmin メンバー、オブジェクトの所有者に与えられる
 
GRANT (許可)
  Enterprise Manager または GRANT ステートメントで付与
  GRANT 権限 TO {データベースユーザー|データベースロール} (ステートメント権限の場合)
    GRANT 権限 ON オブジェクト名 TO {データベースユーザー|データベースロール}
  (オブジェクト権限の場合)
 
 
ALL 引数を使用 → (ステートメント権限)すべての権限が追加
                         (オブジェクト権限)オブジェクトに適用可能な全権限が許可される
      ※ システム管理者およびデータベース所有者のみ使用可
 
DENY (拒否)
  Enterprise Manager または DENY ステートメントで付与
  DENY 権限 TO {データベースユーザー|データベースロール} (ステートメント権限の場合)
    DENY 権限 ON オブジェクト名 TO {データベースユーザー|データベースロール}
  (オブジェクト権限の場合)
 
REVOKE (取り消し)
  Enterprise Manager または REVOKE ステートメントで付与
  REVOKE 権限 FROM {データベースユーザー|データベースロール} (ステートメント権限の場合)
    REVOKE 権限 ON オブジェクト名 FROM {データベースユーザー|データベースロール}
  (オブジェクト権限の場合)
posted by w@ko at 18:35|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

オブジェクトの所有者と権限

依存関係を持つデータベースオブジェクトの所有者が異なる場合、元のオブジェクト権限のチェックを行う
 
オブジェクト作成時はなるべく dbo ユーザーを使用
  他ユーザー使用時もなるべく dbo を所有者に指定
 
オブジェクトの所有者の変更方法
    sp_changeobjectowner 'オブジェクト名', '所有者名'
  sysadmin、db_owner、db_ddladmin、db_securityadmin メンバーのみ実行可
  所有者変更時はそのオブジェクトを参照するスクリプトやバッチに新しい設定を手動で反映させる必要あり
    → 自動的に行うことは不可
posted by w@ko at 18:34|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

データベースユーザーアカウントの追加

Enterprise Manager
sp_grantdbaccess 'ログイン名' [, 'アカウント名']
  データベース所有者またはデータベースアクセス管理者のみが実行可
  sp_revokedbaccess 'アカウント名' で削除
 
 
sp_change_users_login '実行操作名'[, 'アカウント名'][, 'ログイン名'] で SQL Server ログイン名とデータベースアカウント名とのマッピングを変更
posted by w@ko at 18:33|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ログインアカウントの追加

○ Windows ログインアカウントの追加
Enterprise Manager
sp_grantlogin 'ログイン名'
  ログイン名は ドメイン名¥ユーザー名 で指定 (UPN は未サポート)
  128 文字以内 (Unicode)
  システムまたはセキュリティ管理者のみが実行可
  sp_revokelogin 'ログイン名' で削除
  sp_denylogin 'ログイン名' でアクセス拒否
孤立オブジェクトの防止
  Windows グループのメンバーが変更されても SQL Server に変更を加える必要なし
  Windows グループ/ユーザーが削除されてもそのグループ/ユーザーは SQL Server から削除されない
    → ユーザー/グループの削除は Windows → SQL Server の順に行う

 
ユーザーが Windows グループのメンバーとして SQL Server にログインした場合でも、SQL Server はユーザー ID を認識
  SUSER_SNAME() 関数がユーザーのドメイン名とログインアカウント名を返す

○ SQL Serverログインアカウントの追加
Enterprise Manager
sp_addlogin 'ログイン名'[, 'パスワード']
  128 文字以内 (「¥」、「sa」など予約済みアカウント、NULL または空白文字列は使用不可)
  システムまたはセキュリティ管理者のみが実行可
  sp_password ['旧パスワード'][, '新パスワード'][, 'ログイン名'] でパスワード変更
  sp_droplogin 'ログイン名' で削除
posted by w@ko at 18:33|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

認証モードの選択

Windows 認証モードの利点
  パスワードの検証、暗号化、監査他の管理機能を持つ
  ユーザーグループを1つのログインアカウントとして SQL Server に追加可能
  SQL Server へのすばやいアクセスを実現

混合モードの利点
  非 Windows NT 系クライアントからのアクセスが可能
posted by w@ko at 18:32|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

セキュリティモデル

(1) SQL Server へのログイン ← ログインアカウントの作成
  Enterprise Manager → [セキュリティ] → [ログイン]で操作
  固定サーバーロールでサーバー管理権限を制御。「sysadmin」他
  特殊アカウント「sa」「BUILTIN¥Administrator」(Windows ローカル管理者アカウント)
  ログインアカウント/サーバーロール情報は master..sysxlogins システムテーブル内に格納

(2) データベースへの接続 ← データベースユーザーアカウントの登録
  Enterprise Manager → [セキュリティ] → [ログイン]のプロパティ → [データベースアクセス]タブ
    または
    Enterprise Manager → データベース → データベースユーザーのプロパティで操作
  固定データベースロールでデータベース管理権限を制御。「public」「db_owner」他
  特殊データベースユーザー「dbo」「guest」
  データベースユーザーアカウント/データベースロール情報は sysusers システムテーブル内に格納

(3) オブジェクト操作 ← オブジェクト権限の付与
  Enterprise Manager → データベース → オブジェクト右クリック → [すべてのタスク] → [権限の管理]
    (またはオブジェクトのプロパティ → [権限])で管理
posted by w@ko at 18:32|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

容量の計画

データベースサイズを見積もる際の考慮事項
  拡張計画を含めた model データベースおよびシステムテーブルのサイズ
  拡張計画を含めたテーブル内のデータ量
  インデックスの数とサイズ(特にキー値のサイズ、行数、FILLFACTOR)
  変更アクティビティ量および頻度の影響を受けるトランザクションログのサイズ
  各トランザクションのサイズ
  トランザクションログのバックアップまたはダンプを行う頻度
  ユーザー、オブジェクトの数などシステムテーブルのサイズ
 
テーブル内のデータ量の見積もり
  1. 各列のバイト数の合計を計算して行のバイト数を算出 (可変長列は平均をとる)
  2. 各データページに含まれる行数を決定 (8060 を1行のバイト数で割る)
  3. テーブル内の行数を各データページに含まれる行数で割る → テーブルの保存に必要なページ数
posted by w@ko at 18:31|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

複数ファイル使用時のデータ書き込み手順

データファイル ファイルグループ内でファイルの空き容量に応じて均等に同時並行書き込み
※ ファイルグループへディスク追加直後は追加されたディスクに集中書き込み
トランザクション
ログファイル
順次書き込み
posted by w@ko at 18:30|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ファイルグループによるパフォーマンス最適化

データ負荷分散 (ファイルグループ内のファイルに対し並列書き込み)
 
並列スレッドによるデータアクセスの改善
 
データベース保守への活用
  データベース全体ではなく、ファイルやファイルグループごとにバックアップ/復元
    大規模環境でのバックアップ/復元を効率的に実行可能
  類似した保守要件のテーブル/インデックスを同じファイルグループに含める
    パフォーマンス要件ではなく保守要件を優先
    RAID (ストライピング)とユーザー定義ファイルグループは同等のパフォーマンス
  保守頻度の高いテーブルを独自のファイルグループに割り当てる
 
ユーザー定義ファイルグループをデフォルトに設定
  予期しない拡張によってシステムテーブルが制約を受けることを防ぐ
 
ファイルグループはフォールトトレランスの向上にはならない → ミラーリング
 
更新系データファイルグループと検索系データファイルグループの使い分け
  検索系ファイルグループを読み取り専用にすることでトランザクションログ復旧とは無関係にバックアップ
posted by w@ko at 17:07|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

データベースファイルの配置

パフォーマンスを考慮したファイルの実装と配置
データファイルを複数作成し I/O を分散
NTFS フォーマットディスクを使用 圧縮ボリュームは使用しない
システムテーブルとユーザー定義テーブルのデータのアクセスと書き込みを分散
○ ハードウェア RAID の考慮
RAID1(ミラーリング) トランザクションログの冗長化
RAID5 データファイル(主に検索系)パフォーマンス向上およびトランザクションログの冗長化
RAID1+0
(ストライプ付きミラーリング)
データファイル(主に更新系)パフォーマンス向上
ソフトウェア (OS) RAID よりも高パフォーマンス
  CPU に負荷をかけない
  障害発生時にシステムの再起動が不要
パフォーマンスを優先するならユーザー定義ファイルグループよりもストライピングを選択
○ データファイルを異なるディスク上に配置
データファイル (可能であればシステムカタログファイルとユーザーデータファイルも分割)
トランザクションログファイル
tempdb データベースファイル → 特にバッチ系処理が多い場合は考慮する
更新系ファイルと検索系ファイルの分割
○ テーブルとインデックスの分散配置
異なるディスク上に配置することでパフォーマンス向上 ← 1つのクエリでの同時アクセスが多い
テーブル本体と分けて配置できるのは非クラスタ化インデックスのみ
分散配置した場合、テーブルとインデックスは同時にバックアップを取る必要あり
RAID が使えればそちらを使ったほうがよい
○ ユーザー定義ファイルグループの使用
posted by w@ko at 17:06|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

ファイルの削除

特定のファイルを削除するときにはファイル内が空(使用領域がない状態)でないと削除不可
  DBCC SHRINKFILE EMPTYFILE オプションで使用領域を別ファイルへ移動
posted by w@ko at 17:06|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

データベースファイルの管理

ログファイルの空き容量が少なくなるとトランザクションが失敗する可能性あり
  使用可能領域がなくなるとデータベースの変更、更新等が行えなくなる
  データベースへの十分な領域の割り当てを行う
 
ファイルサイズの確認方法
  Enterprise Manager → データベースのプロパティ
  sp_helpfile

○ ファイルの拡張
既定で自動拡張、無制限に拡張、拡張増分値:10%

 
Enterprise Manager → データベースのプロパティまたは ALTER DATABASE ステートメントで設定変更可
○ ファイルの圧縮
データベースの自動圧縮(既定:無効、ただし Desktop Edition を除く)
  Enterprise Manager → データベースのプロパティ → [オプション]タブ → [自動圧縮]
  ALTER DATABASE AUTO_SHRINK ステートメント
  exec sp_dboption 'データベース名 ','autoshrink','TRUE'
  圧縮サイズの指定は不可
  ファイル未使用領域が 25% (変更不可)を超えるとデータおよびログファイルを圧縮
  ログの圧縮はアクティブな領域が含まれていない場合に限り実行
  Transact-SQL による設定の確認方法
    SELECT DATABASEPROPERTYEX ('データベース名', 'ISAUTOSHRINK')
        0 → 無効 1 → 有効
  圧縮はバックグラウンドで実行 → ユーザーアクティビティに影響を与えない
 
データベースの手動圧縮
  Enterprise Manager → データベース右クリック → すべてのタスク → [データベースの圧縮]
    SQL Server 7.0 にも存在するが、あまり圧縮されない?
  DBCC SHRINKDATABASE
    初期作成サイズまたは model データベースのサイズより小さくすることは不可
      → DBCC SHRINKFILE で圧縮
    圧縮後のデータベースファイルに残す領域のパーセンテージを指定
 
ファイルの手動圧縮
 
 
Enterprise Manager → データベース右クリック → すべてのタスク → [データベースの圧縮] → [ファイルの圧縮]
  DBCC SHRINKFILE
    初期作成サイズ以下に圧縮可能
    圧縮後のファイルサイズを指定(MB 単位) → 圧縮後は初期ファイルサイズとしてリセット
    指定したファイルのデータを同ファイルグループの別ファイルに移動可能 (EMPTYFILE オプション)
  トランザクションログファイルの圧縮は非アクティブ領域を圧縮
  ログファイルを指定サイズまで圧縮できないときはメッセージが返され、実施事項を表示
    アクティブなログの部分が指定のサイズを超えている
    アクティブなログをトランザクションログファイルの末尾から移動するための操作を表示
      → 指示内容実行後、再度 DBCC SHRINKDATABASE|SHRINKFILE ステートメントを再実行

★ 圧縮時にはパフォーマンスが低下するため、実行タイミングには注意!!

posted by w@ko at 17:04|  ・SQL Server ノート | このブログの読者になる | 更新情報をチェックする

広告


この広告は60日以上更新がないブログに表示がされております。

以下のいずれかの方法で非表示にすることが可能です。

・記事の投稿、編集をおこなう
・マイブログの【設定】 > 【広告設定】 より、「60日間更新が無い場合」 の 「広告を表示しない」にチェックを入れて保存する。


×

この広告は180日以上新しい記事の投稿がないブログに表示されております。