对指定的表设置只读属性

通常,要让一个表只读,我们可以:

  1. 创建Insert/Update/Delete触发器
  2. 创建检查约束来禁用Insert/Update,创建触发器禁用Delete
  3. 设置表所在的数据库只读
  4. 把表放到只读文件组中
  5. 拒绝指定的用户有Insert/Update/Delete的权限
  6. 通过视图

但是上述方案中,有些可能会给我们带来一些不便:

  1. 对大表使用触发器会带来性能影响
  2. 对dbo角色不能移除DML权限
  3. 对整个数据库设置只读时常不现实
  4. 放在只读文件组中的表仍然可以添加和删除列

下面是采用方案中的第2种来实现,实现的存储过程如下:

 

USE [TESTDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_Util_SetTableReadOnly]    Script Date: 2013/7/31 16:49:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================================================
--  对任何指定的表设置是否只读
-- =================================================================================================
ALTER PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
  @tableName varchar(50)
 ,@readOnly tinyint=1
 ,@schemaName varchar(50)='dbo'
 ,@debug tinyint=0
AS
BEGIN

if (@readOnly NOT IN (0, 1)) return 10

declare @error int, @rowsCount int, @now datetime2

select 
  @tableName=ltrim(rtrim(@tableName))
 ,@schemaName=ltrim(rtrim(@schemaName)) 
 ,@tablename=t.name
 from sys.schemas s inner join sys.tables t 
   on s.schema_id=t.schema_id 
 where s.name=@schemaName 
 and t.name=@tableName

select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @now=SYSDATETIME()

if (@error<>0) RETURN 20

if (@rowsCount<>1) RETURN 30

declare @createCheckConstraint varchar(8000), 
        @dropCheckConstraint varchar(8000), 
        @createDeleteTrigger varchar(8000), 
        @dropDeleteTrigger varchar(8000)

if (@readOnly=1)
 begin
  select 
   @createCheckConstraint='IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
   +''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'
   +''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)+
   +'ALTER TABLE ['+@schemaName+'].['+@tableName+']  WITH NOCHECK ADD  CONSTRAINT [CK_'+@tableName+'_ReadOnly] CHECK  (1=0)'
   ,@createDeleteTrigger='IF  NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
   +'EXEC dbo.sp_executesql @statement =N'+
   +''''+'CREATE TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete] ON '+'['+@schemaName+'].['+@tableName+']'+char(13)
   +'INSTEAD OF DELETE'+char(13)
   +'AS'+char(13)
   +'BEGIN'+char(13)
   +'RAISERROR( '+''''+''''+'Deletion of table '+@tableName+' not allowed.'+''''+''''+', 16, 1 )'+char(13)
   +'ROLLBACK TRANSACTION'+char(13)
   +'END'+''''

  if (@debug=1)
   begin
   
     print convert(varchar, @now, 126)+ ' @createCheckConstraint='+@createCheckConstraint+' @createDeleteTrigger='+@createDeleteTrigger
   end

  exec (@createCheckConstraint)

  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to create check constraint. will return.'
    return 110
   end

  exec (@createDeleteTrigger) 

  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to create trigger for delete. will return.'
    return 120
   end

 end
else
 begin
  select 
    @dropCheckConstraint='IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
    +''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)
    +'ALTER TABLE ['+@schemaName+'].['+@tableName+'] DROP CONSTRAINT [CK_'+@tableName+'_ReadOnly]'
    ,@dropDeleteTrigger='IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
    +'DROP TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'  
  
  if (@debug=1)
   begin
    print convert(varchar, @now, 126)+ ' @dropCheckConstraint='+@dropCheckConstraint+' @dropDeleteTrigger='+@dropDeleteTrigger
   end

  exec (@dropCheckConstraint)
 
  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to drop check constraint. will return.'
    return 130
   end

  exec (@dropDeleteTrigger)
 
  select @error=@@ERROR, @now=SYSDATETIME()

  if (@error<>0) 
   begin
    print convert(varchar, @now, 126)+ ' failed to drop trigger for delete. will return.'
    return 140
   end
 end

return 0

END


 

参数说明:

@tableName varchar(50)    --表名
@readOnly tinyint=1       --1表示启用只读,0表示取消只读属性
@schemaName varchar(50)='dbo'   --Schema
@debug tinyint=0                --使用动态查询,方便我们能够看到将要执行什么T-SQL字符串

 

测试效果:

有如下表PERSON及数据:

 

对表PERSON设置只读属性(EXEC usp_Util_SetTableReadOnly 'PERSON', 1),然后执行相关Insert/Update/Delete操作,你将得到错误提示:

 

 

如果要取消只读属性,只需执行如下命令:

EXEC usp_Util_SetTableReadOnly 'PERSON', 0

 


 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值