制限付きアクセス権を持つDBユーザーに実行権限を与えることの影響は何ですか?

database security sql-server stored-procedures

権限が制限されているユーザーしかいない場合は、db_datareaderとdb_datawriterだけを使用します。これにより、ユーザーはデータベース内のテーブルの追加/変更/削除を許可されずに、データの照会とデータの挿入/編集/削除が可能になります。

ユーザーがストアドプロシージャを実行できるようにする必要があるかもしれません。 ユーザーに実行権限が与えられている場合(SQL: “GRANT EXECUTE T​​O UserName”)、ユーザーがストアドプロシージャを介して実行しようとしていることに対して、以前の制限(datareaderとdatawriter)は依然として適用されますか? それとも実行特権は本当に他のセキュリティホールのパンドラの箱を開けますか(そしてもしそうなら、何)

  3  0


ベストアンサー

ストアドプロシージャの所有者がテーブルに対して選択、挿入、更新または削除する権限を持っている場合、呼び出し元がストアドプロシージャに対する実行権限を持っている限り、ストアドプロシージャ内のselect、insert、update、およびdeleteステートメントが実行されます。呼び出し元に、テーブルに対して直接選択、挿入、更新、または削除を実行する権限がない場合。

ただし、ストアドプロシージャの所有者がDDL権限を持っていても、呼び出し側がDDLを実行する権限を持っていない限り、ストアドプロシージャはDDLを実行できません。 これはテーブルの切り捨てにも当てはまります。

*回答:*ユーザーに `+ db_datareader `と ` db_datawriter +`を付与すると、すべてのテーブルで完全なDMLが既にユーザーに付与されます。 ストアドプロシージャの実行を許可しても、追加の権限は与えられません。

ストアドプロシージャを使用すると、すべての外部プログラムが通過しなければならないゲートを提供することによってデータの整合性を向上させることができます。 挿入、削除、または更新を許可しないで、作業を実行してデータに関する適切な規則を適用するSPを作成します。 Joe Kuemerle氏が指摘しているように、ストアドプロシージャを使用してセキュリティを強化することができます。

SQL Server 2000でアプリケーションを開発している間にこの動作を観察しましたが、これはSQL Server 2008で再テストしても同じ動作が見つかりました。 この動作に関するドキュメントを見つけることができませんでした。

DBOおよびSAとしてログインしてテーブルを作成します。

create table dbo.SO (PK int identity constraint SO_PK primary key
    , SomeData varchar(1000)
)

次に、基本DML用のストアドプロシージャをいくつか作成します。

create procedure dbo.InsertSO (@SomeData varchar(1000)) as
    begin
    insert into dbo.SO (SomeData) values (@SomeData)
    return SCOPE_IDENTITY()
    end
go

create procedure dbo.SelectSO (@PK int=null) as
    begin
    if @PK is not null
        select PK, SomeData from dbo.SO where PK = @PK
    else
        select PK, SomeData from dbo.SO
    end
go

create procedure dbo.CountSO as
    begin
    select COUNT(*) as CountSO from SO
    end
go

create procedure dbo.DeleteSO (@PK int=null ) as
    begin
    if @PK is not null
        delete dbo.SO where PK = @PK
    else
        delete dbo.SO
    end
go

create procedure dbo.UpdateSO (@PK int, @NewSomeData varchar(1000)) as
    begin`
    update dbo.SO
    set SomeData =  @NewSomeData
    where PK = @PK
    end
go

create procedure dbo.TruncateSO as
    begin
    truncate table dbo.SO
    end
go

dboとして、次のSQL文を実行できます。

declare @PK_to_update int
insert into dbo.SO (SomeData) values ('Hello world!')
set @PK_to_update = SCOPE_IDENTITY()

declare @PK_to_delete int
insert into dbo.SO (SomeData) values ('Goodbye cruel world!')
set @PK_to_delete = SCOPE_IDENTITY()

insert into dbo.SO (SomeData) values ('Four score and seven years ago...')

select PK, SomeData
from dbo.SO

delete dbo.so
where PK = @PK_to_delete

update dbo.SO
set SomeData = 'Hello Milky Way!'
where PK = @PK_to_update

select PK, SomeData
from dbo.SO

truncate table dbo.SO

select COUNT(*) as CountSO from dbo.SO

またはストアドプロシージャを介して同等のことをする

go
declare @PK_to_update int
exec @PK_to_update = dbo.InsertSO 'Hello world!'

declare @PK_to_delete int
exec @PK_to_delete = dbo.InsertSO 'Goodbye cruel world!'

exec dbo.InsertSO 'Four score and seven years ago...'

exec dbo.SelectSO

exec dbo.DeleteSO @PK_to_delete

exec dbo.UpdateSO @PK_to_update, 'Hello Milky Way!'

exec dbo.SelectSO

exec dbo.TruncateSO

exec dbo.CountSO

次に、DDLストアドプロシージャを作成してテストします。

create procedure dbo.DropSO as
    begin
    drop table dbo.SO
    end
go
begin transaction
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'SO'
exec dbo.DropSO
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'SO'
rollback transaction

そして今度は別のユーザーを作成し、すべてのストアード・プロシージャーに対する実行権限を付与します。 他の権利を与えないでください。 (publicには追加の権限と混在モード認証がないと仮定します。 混在モード認証はお勧めできませんが、権利がどのように処理されるかをテストするのに役立ちます。

exec sp_addlogin @loginame =  'SoLogin' , @passwd = 'notsecure', @defdb = 'Scratch'

exec sp_adduser @loginame = 'SoLogin', @name_in_db = 'SoUser'
go
grant execute on dbo.InsertSo to SoUser
grant execute on dbo.InsertSO to SoUser
grant execute on dbo.SelectSO to SoUser
grant execute on dbo.CountSO to SoUser
grant execute on dbo.DeleteSO to SoUser
grant execute on dbo.UpdateSO to SoUser
grant execute on dbo.TruncateSO to SoUser
grant execute on dbo.DropSO to SoUser

SoLoginとしてログインします。 DMLを試してください。

declare @PK_to_update int
insert into dbo.SO (SomeData) values ('Hello world!')
set @PK_to_update = SCOPE_IDENTITY()

declare @PK_to_delete int
insert into dbo.SO (SomeData) values ('Goodbye cruel world!')
set @PK_to_delete = SCOPE_IDENTITY()

insert into dbo.SO (SomeData) values ('Four score and seven years ago...')

select PK, SomeData
from dbo.SO

delete dbo.so
where PK = @PK_to_delete

update dbo.SO
set SomeData = 'Hello Milky Way!'
where PK = @PK_to_update

select PK, SomeData
from dbo.SO

truncate table dbo.SO
go
select COUNT(*) as CountSO from dbo.SO
go

drop table dbo.so

エラー以外何もない:

Msg 229, Level 14, State 5, Line 2
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 6
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 9
The INSERT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 14
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 14
The DELETE permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 17
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 17
The UPDATE permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 229, Level 14, State 5, Line 21
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 1088, Level 16, State 7, Line 24
Cannot find the object "SO" because it does not exist or you do not have permissions.
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'SO', database 'Scratch', schema 'dbo'.
Msg 3701, Level 14, State 20, Line 2
Cannot drop the table 'SO', because it does not exist or you do not have permission.

基本的なDMLストアドプロシージャを試してください。

declare @PK_to_update int
exec @PK_to_update = dbo.InsertSO 'Hello world!'

declare @PK_to_delete int
exec @PK_to_delete = dbo.InsertSO 'Goodbye cruel world!'

exec dbo.InsertSO 'Four score and seven years ago...'

exec dbo.SelectSO

exec dbo.DeleteSO @PK_to_delete

exec dbo.UpdateSO @PK_to_update, 'Hello Milky Way!'

exec dbo.SelectSO

SoUserが持っていなくても、SPの所有者が正しい権利を持っているので、それらはうまくいきます。

切り捨てまたは削除ストアドプロシージャを試してください。

exec dbo.TruncateSO
go
exec dbo.DropSO

もう一度エラー:

Msg 1088, Level 16, State 7, Procedure TruncateSO, Line 4
Cannot find the object "SO" because it does not exist or you do not have permissions.
Msg 3701, Level 14, State 20, Procedure DropSO, Line 4
Cannot drop the table 'SO', because it does not exist or you do not have permission.

4


実行権限によって余分なセキュリティホールが開かれることはありません。 私の意見では、もっと大きな穴は、ユーザーがテーブルへの直接の読み取り/書き込みアクセス権を持っているという事実です。

SQL Serverは所有権連鎖を実装しているため、データリーダー/データライターの権限を取り消し、ユーザーに実行権限しかないストアドプロシージャを介してすべてのデータアクセスを提供することで、制御可能で監査可能なデータアクセスを提供できます。 これにより、誰かがテーブルから任意に挿入、更新、削除することができなくなります。 また、データベースを使用するアプリケーションがSQLインジェクション攻撃に対して脆弱であるという場合のように、攻撃者が望むテーブルに対して読み書きを行うことができない場合など、多層防御戦略において別の層を提供します。

これを行う場合の唯一の注意点は、ORMを使用している場合、ORMに動的にSQLを生成させるのではなく、sprocsを使用することでさらに開発が必要になる可能性があることです。

2


あなたが望む概念は “http://msdn.microsoft.com/en-us/library/ms188676.aspx [所有権連鎖]”です。

基本的に、ストアドプロシージャで使用されているのと同じスキーマ(たとえばdbo)内のオブジェクトに対する権限はチェックされません。 例外:拒否は常にチェックされています。

そのため、ストアドプロシージャdbo.uspDoStuffがテーブルdbo.Parentとdbo.Childを使用している場合、テーブルに対する権限は不要で、動作します。 「 + DENY SELECT ON dbo.Parent to MyUser +」を実行していない限り。

注:通常は「CREATE ROLE MyRole」を実行し、ユーザーをロールに追加して、そのロールに対する権限を付与します。 たとえば、db_datareaderは特別な予約済みの役割です。

1


実行パーミッションを付与することで、そのストアドプロシージャがそのストアドプロシージャのコンテキスト内で行うことをその人が実行できるようになります(したがって、sprocがテーブルを削除した場合、ユーザーはsprocを実行してテーブルを削除できます)。

編集して、確認したところ、間違っていました。 アクセスを拒否しても、ストアドプロシージャでアクションを実行する機能が無効になることはありません。

これは、アクセスを拒否してもストアドプロシージャには影響しないことを明記したMSDNの記事です。

更新:あなたがすることができるかもしれないことはストアドプロシージャの中でsp_executeSQLを通してテーブル削除コマンドを実行して、そしてユーザにテーブルを削除する能力を否定することです。 sp_executesqlを使用するには、ストアドプロシージャへのアクセスだけでなく、sqlアクションを実行するためのアクセス許可が必要なので、ストアドプロシージャがコマンドを正常に実行できないようにする必要があります(ユーザーに許可がない場合)。

0


タイトルとURLをコピーしました