sql-server - 用于访问 sys.dm_db_index_usage_stats 的公共(public)存储过程

Question

我正在尝试向 SQL Server 中的所有用户提供 sys.dm_db_index_usage_stats View 的某些部分。目标是在不破坏 SQL Server 安全性的情况下提供该信息。

我不是数据库专家，但我准备了以下代码:

CREATE PROCEDURE dbo.[LastTableUpdate]   
    @Table nvarchar(50)   
WITH EXECUTE AS OWNER 
AS   
SELECT 
    DB_NAME(database_id) as 'Database', 
    OBJECT_NAME(object_id) As TableName, 
    max(last_user_update) as user_update,
    max(last_system_update) as system_update
 FROM sys.dm_db_index_usage_stats WHERe database_ID=DB_ID() AND object_id=OBJECT_ID(@Table)
 GROUP BY database_id, object_id

接下来我授予权限

GRANT EXEC ON dbo.[LastTableUpdate] TO PUBLIC

但是当我打电话时它得到了

Msg 15562, Level 16, State 1, Procedure LastTableUpdate, Line 5
The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

你能帮我吗，我该怎么办？正如我所提到的 - 我的目标是尽可能保证数据库的安全。

Answer 1

模块签名(与 pimpin 不同)很容易。本质上，你:

1. 创建证书
2. 根据该证书创建登录
3. 授予该登录权限
4. 签署存储过程
5. 利润

<小时/>

use master;
go
create login [foobar] with password = 'f00bar!23';
go
CREATE PROCEDURE dbo.[LastTableUpdate]   
    @Table nvarchar(50)   
AS   
SELECT 
    DB_NAME(database_id) as 'Database', 
    OBJECT_NAME(object_id) As TableName, 
    max(last_user_update) as user_update,
    max(last_system_update) as system_update
 FROM sys.dm_db_index_usage_stats WHERe database_ID=DB_ID() AND object_id=OBJECT_ID(@Table)
 GROUP BY database_id, object_id
 go
 grant execute on dbo.LastTableUpdate to public
 go
EXEC sp_ms_marksystemobject 'LastTableUpdate'
go
 create certificate [CodeSigningCertificate]
    encryption by password = 'Sooper$ecretp@ssword123'
    with expiry_date = '2099-01-01',
    subject = 'Code Signing Cert'
go
create login [CodeSigningLogin] from certificate [CodeSigningCertificate]
grant view server state to [CodeSigningLogin];
go
-- nothing up my sleeve
execute as login = 'foobar';
exec dbo.LastTableUpdate 'n'
revert

-- doesn't work - login [foobar] doesn't have permissions
go
-- here's the magic
add signature to dbo.[LastTableUpdate]
    by certificate [CodeSigningCertificate]
    with password = 'Sooper$ecretp@ssword123';
go

execute as login = 'foobar';
exec dbo.LastTableUpdate 'n'
revert

-- works now!

<小时/>

注意:我从您的过程中取出了 EXECUTE AS OWNER 子句；您现在不需要它，因为调用者将仅获得此存储过程运行上下文的VIEW SERVER STATE权限。