sql-server - SQL Server:拒绝对重新创建的表进行选择
问题描述
在 SQL Server 2017 中,我有一个具有 ddladmin 权限的用户定期删除和重新创建一个表。我们需要在该表上拒绝选择。
只有当表被删除时,它才会失去拒绝。我不想授予用户 securityadmin。
如何确保每次重新创建此表时都保留此表上的拒绝选择?
解决方案
...调整
revert
go
-- drop trigger deny_select_on_frequentlydroppedtable on database
create or alter trigger deny_select_on_frequentlydroppedtable
on database
with execute as 'dbo'
for CREATE_TABLE
as
begin
set nocount on;
--when table name = frequentlydroppedtable
if EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname') = 'frequentlydroppedtable'
begin
declare @tbl nvarchar(500) = concat(quotename(EVENTDATA().value('(EVENT_INSTANCE/SchemaName)[1]', 'sysname')), '.', quotename(EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname')));
declare @user nvarchar(150) = EVENTDATA().value('(EVENT_INSTANCE/UserName)[1]', 'sysname');
if @user <> 'dbo' and IS_ROLEMEMBER('db_owner', @user) = 0 and IS_ROLEMEMBER('db_ddladmin', @user) = 1 --... any ddl admin gets deny when they create the table :)
begin
declare @sql nvarchar(500) = 'deny select on ' + @tbl + ' to ' + quotename(@user);
exec(@sql)
end
end
end
go
--dbo or ....
drop table if exists frequentlydroppedtable;
go
create table frequentlydroppedtable(id int);
go
--works
select * from frequentlydroppedtable
go
--create a test db_ddladmin
create user test_db_ddladmin without login;
go
alter role db_ddladmin add member test_db_ddladmin;
go
execute as user = 'test_db_ddladmin'
go
drop table if exists frequentlydroppedtable;
go
create table frequentlydroppedtable(id int);
go
--permission violation, deny select
select * from frequentlydroppedtable
go
revert
go
--cleanup
drop table if exists frequentlydroppedtable;
go
drop user test_db_ddladmin;
go
drop trigger deny_select_on_frequentlydroppedtable on database
go
推荐阅读
- javascript - 使用 php 代码将引导轮播链接到数据库
- python-3.x - 如何创建滑动窗口附加最高阈值为 70 的字典?
- typescript - 无法使用“this”指针将方法用作回调
- html - 如何创建一个仅在单击/切换时才显示某些输入的按钮?
- python - 在 Python 中从 JWT 令牌中检索一个值
- python - 模型不训练和输出相同的类
- java - 未找到重新运行文件以在黄瓜中执行失败的功能场景
- image - MATLAB中图像的双数据类型
- ios - 如何在swiftui中对齐来自不同堆栈/容器的元素?
- java - 无法使用 setsmallIcon 在 android studio 中更改图标