none
SQLのアカウントの権限の制限について RRS feed

  • 質問

  • 利用OS:widnows server 2012 R2

    利用SQL:MS SQL  server 2012

    上記環境におきまして、利用しているアカウントに下記のような制限をかけたいのですが、

    方法についてご存じでしたらご教示いただきたいと考えています。

    ・全権削除ができなくなるよう制限をかけたい。

    ・上記以外のほか、特定コマンドのみ実行不可のような制限をかけたい。

    2021年4月1日 6:24

回答

  • もっとよい方法はあるかもしれませんが、トリガーで削除された件数に応じて RAISERROR で例外を発生させ、ロールバックするという実装はできました。

    なお、トリガーを作りこみ、実行されているクエリ内容を取得して、内容に応じて強制的にロールバックさせるということも出来るかもしれませんが、トリガーで複雑な処理を実行すると、本処理がボトルネックとなることが危惧されます。そのため、該当テーブルへのDELETEを制限し、例えば、データの削除はストアドプロシージャからのみ可能にするなどの対策を考慮したほうが無難かもしれません。

    create table deltriggertbl (c1 int)
    go
    insert into deltriggertbl values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    go
    create trigger deletechk on deltriggertbl
    for delete as
    if @@ROWCOUNT > 5 
    begin
    	raiserror('Five or more rows of records have been deleted.',16,1)
    	rollback
    end
    go
    
    delete deltriggertbl
    go
    メッセージ 50000、レベル 16、状態 1、プロシージャ deletechk、行 5 [バッチ開始行 16]
    Five or more rows of records have been deleted.
    メッセージ 3609、レベル 16、状態 1、行 17
    トランザクションはトリガーで終了しました。バッチは中止されました。

    • 回答としてマーク abataro 2021年5月17日 9:36
    2021年4月2日 1:12

すべての返信

  • >全権削除ができなくなるよう制限をかけたい。

    こちらは、全件削除ではなく数件の削除の場合は削除を許可したいという感じでしょうか? 削除のみを制限掛けたいのであれば、該当のログインに対して、DELETE 権限をはく奪することで最も簡単に実現することができるかと思います。

    仮に、全件削除のみ出来ないように制限したいというのであれば、実現性の確認はしていませんが、トリガーの中で指定した件数以上のデータが削除された場合、例外を発生され、強制的にロールバックされるという実装はできるかもしれません。

    また、特定のコマンドのみというのが、どのようなコマンドを想定されているのかにもよりますが、権限やトリガーなどを使用することで実現することができるかもしれません。

    https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-trigger-transact-sql?WT.mc_id=DP-MVP-5004154&view=sql-server-ver15

    2021年4月1日 7:46
  • 全件削除のみ出来ないように制限したい

    →イメージとしてはこちらになります。

        すべてのテーブルをdeleteしようとするコマンドを制限したいようなイメージです。。

    2021年4月1日 11:22
  • もっとよい方法はあるかもしれませんが、トリガーで削除された件数に応じて RAISERROR で例外を発生させ、ロールバックするという実装はできました。

    なお、トリガーを作りこみ、実行されているクエリ内容を取得して、内容に応じて強制的にロールバックさせるということも出来るかもしれませんが、トリガーで複雑な処理を実行すると、本処理がボトルネックとなることが危惧されます。そのため、該当テーブルへのDELETEを制限し、例えば、データの削除はストアドプロシージャからのみ可能にするなどの対策を考慮したほうが無難かもしれません。

    create table deltriggertbl (c1 int)
    go
    insert into deltriggertbl values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    go
    create trigger deletechk on deltriggertbl
    for delete as
    if @@ROWCOUNT > 5 
    begin
    	raiserror('Five or more rows of records have been deleted.',16,1)
    	rollback
    end
    go
    
    delete deltriggertbl
    go
    メッセージ 50000、レベル 16、状態 1、プロシージャ deletechk、行 5 [バッチ開始行 16]
    Five or more rows of records have been deleted.
    メッセージ 3609、レベル 16、状態 1、行 17
    トランザクションはトリガーで終了しました。バッチは中止されました。

    • 回答としてマーク abataro 2021年5月17日 9:36
    2021年4月2日 1:12
  • abataroさん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    使用するアカウントにかけたい制限について、より具体的な例を挙げていただけますでしょうか。

    どうぞよろしくお願いいたします。

    MSDN/ TechNet Community Support Haruka
    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2021年4月6日 7:28
    モデレータ
  • abataroさん、こんにちは。フォーラムオペレーターのHarukaです。

    ご質問いただいた件ですが、その後いかがでしょうか。
    追加でご確認いただいたことなどあれば、追記いただくことで回答がつきやすくなります。
    また、参考になった投稿があれば、[回答としてマーク] を設定いただけますと幸いです。

    どうぞよろしくお願いいたします。

    MSDN/ TechNet Community Support Haruka
    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2021年4月19日 1:19
    モデレータ