sql-server – 授予具有有限访问权限的DB用户执行权限的后果是什么?

如果我的用户只有权限有限 – 只有db_datareader和db_datawriter,它应该只允许用户查询数据并插入/编辑/删除数据,而不允许用户在数据库中添加/修改/删除表.

可能需要用户能够执行存储过程.如果给予用户执行权限(通过以下sql:“GRANT EXECUTE T​​O UserName”),是否仍会对用户尝试通过存储过程执行的操作强制执行先前的限制(datareader和datawriter)?或者执行权限真的打开了潘多拉的其他安全漏洞(如果是这样的话)?

如果存储过程的所有者有权对表进行选择,插入,更新或删除,则只要调用者对存储过程具有执行权限,就会执行存储过程内的选择,插入,更新和删除语句,甚至如果调用者没有权限直接对表执行select,insert,update或delete.

但是,存储过程无法执行DDL,除非调用者有权执行DDL,即使存储过程的所有者具有DDL权限也是如此.请注意,这也适用于truncate table.

答:在您的情况下,向用户授予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

现在创建另一个用户并授予所有存储过程的执行权限.不授予任何其他权利. (假设公共没有额外的权限和混合模式身份验证.不建议使用混合模式身份验证,但会测试如何更轻松地处理权限.)

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

它们起作用,因为SP的所有者拥有正确的权利,即使SoUser没有.

尝试截断或删除存储过程:

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.
相关文章
相关标签/搜索