権限を付与

Transact-SQL による SQL Server へのユーザーの作成と権限の付与の確認

  1. ユーザーの作成

    2. テスト用のユーザーをSQLサーバーユーザーとして追加する

    1. SQL サーバーログインユーザー作成構文

      2. 構文 内容
      sp_addlogin [@loginame =]'login'
          [,[@passwd =]'password']
          [,[@defdb =]'database']
          [,[@deflanguage =]'language']
          [,[@sid =]sid]
          [,[@encryptopt=]'encryption_option']       		新しいSQL Server ログインを作成し、 ユーザーが SQL Server 認証を使用してSQL Server のインスタンスに接続できるようにします

    2. SQL サーバーへのログインユーザーの作成

      3. C:\MSSQL7\Binn>isql -S cafeaulait -U sa -P -d lesson
1> exec sp_addlogin @loginame='test_user',@passwd=test
2> go
新しいログインが作成されました。
1>

    3. データベースへログオン

      4. C:\MSSQL7\Binn>isql -S cafeaulait -U test_user -P test -d lesson
Msg 916, Level 14, State 1, Server CAFEAULAIT, Line 1
サーバー ユーザー 'test_user' は、データベース 'lesson' では有効なユーザーではあ
りません。

      データベースのユーザーとして作成されていない(上記で作成したのはSQLサーバーへのログイン)ので、 データベースへログオンできない

  2. データベースユーザーの作成

    1. データベースユーザー作成構文

      2. 構文 内容
      sp_grantdbaccess [@loginame=]'login'
                [,[@name_in_db=]'name_in_db'[OUTPUT]]       		現在のデータベースに Microsoft SQL Server ログイン、 Microsoft Windows NT ユーザー、または Microsoft Windows NT グループ用のセキュリティ アカウントを追加し、 これにデータベースで操作する権限を与える

    2. データベースユーザーを作成

      3. C:\MSSQL7\Binn>isql -S cafeaulait -U sa -P -d lesson
      
1> exec sp_grantdbaccess 'test_user','test_user'
      
2> go
      
'test_user' にデータベースのアクセス権が許可されました。

    3. ログイン成功

      4. ただ、テーブルを閲覧することはできない

      C:\MSSQL7\Binn>isql -S cafeaulait -U test_user -P test -d lesson
1> select * from Customers
2> go
Msg 229, Level 14, State 5, Server CAFEAULAIT, Line 1
SELECT 権限は、オブジェクト 'Customers'、データベース 'lesson'、所有者 'dbo' に
ありません。
1>

    4. Enterprise Manager から 権限を確認すると何もないことがわかる

  3. 権限の付与

    1. GRANT句を使用して、SELECT権限をあたえる

      2. C:\MSSQL7\Binn>isql -S cafeaulait -U sa -P  -d lesson
1> grant select on Customers to test_user
2> go
1>

    2. SELECT 部分にチェックがついたことがわかる

    3. ログインしなおしてSELECT成功

    4. UPDATEは失敗する

  4. 固定データベースロール

    • 固定データベースロールはデータベースレベルで定義され、各データベースに存在する。
    • 有効なユーザーアカウント(Windows NT ユーザーやグループ、SQL Server のユーザーやロール)は固定データベースロールのメンバとして追加可能。
    • データベース内で有効なロールであり、他のデータベースには反映されない。

    固定データベースロール 権限
    db_owner 他のすべての固定データベースロールにまたがって適用され、すべての権限を所有する データべース内のその他の保守作業および設定作業を行う権限も持つ
    db_accessadmin データベース内のWindows NTグループ、WIndows NTユーザーおよびSQL Serverユーザーの追加や削除を行う
    db_datareader データベース内のすべてのユーザーテーブルで、データを読むことができる
    db_datawriter データベース内のすべてのユーザーテーブルで、データの追加、変更、または削除を行うことができる
    db_ddladmin データベース内のオブジェクトの追加、変更、または削除を行うことができる
    db_securityadmin データベースロールのロールおよびメンバを管理し、 データベース内のステートメント権限およびオブジェクト権限を管理することができる
    db_backupoperator データベースをバックアップおよびリストアできる
    db_denydatareader データベース内のすべてのテーブルで読み取りができないようにする
    db_denydatawriter データベース内の任意のデータを変更できないようにする
    public
    • すべてのデータベースユーザーが属している特別なデータベースロール。
    • データベース内のユーザー規定権限をキャプチャする。
    • ユーザー、グループ、またはロールは特に指定しない限り割り当てることはできない。

  5. ロールに追加

    1. ロール追加構文

      2. 構文 内容
      sp_addrolemember [ @rolename = ] 'role' ,
          [ @membername= ] 'security_account'       		既存の SQL Server データベースのメンバとしてセキュリティ アカウントを現在のデータベースに追加します

    2. ロールの追加

      3. C:\MSSQL7\Binn>isql -S cafeaulait -U sa -P -d lesson
1> exec sp_addrolemember 'db_datareader','test_user'
2> go
'test_user' がロール 'db_datareader' に追加されました。
1> exec sp_addrolemember 'db_datawriter','test_user'
2> go
'test_user' がロール 'db_datawriter' に追加されました。

    3. ロールの追加によりUPDATE発行が可能になった