sql server 中对表函数视图存储过程配置权限

我会带着你远行 2023-02-18 15:27 9阅读 0赞

新建查询

查询全部的表

select * from sysobjects where xtype='U'

存储过程标量

select * from sysobjects where xtype='P '

函数

select * from sysobjects where xtype='FN'

视图

select * from sysobjects where xtype='V'

1、创建用户

CREATE LOGIN developer   
    
       WITH PASSWORD = '强密码',  
    
    default_database= test;

2、加入用户角色，登录名映射数据库用户

create user developer for login developer
    
    use test;
    
    go
    
    EXEC sp_change_users_login 'Update_One', 'developer', 'developer';
    
    go

3、分配权限，创建存储过程，利用grant给全部的权限，利用deny限制权限

4、创建存储过程例子

create procedure viewProce
    
    as 
    
    begin 
    
    SELECT
    
    'deny INSERT,VIEW DEFINITION,ALTER,UPDATE, TAKE OWNERSHIP,CONTROL,DELETE,SELECT,REFERENCES on test.dbo.'+
    
    Name+' to developer;' as t_sql 
    
    from sysobjects where xtype='V'
    
    end
    
    
    
    execute viewProce

或者执行查询拼接sql复制执行

分配权限，创建存储过程，利用grant给全部的权限，利用deny限制赋予权限，revoke回收权限

4、创建存储过程例子

2、单独分配权限例子

其中INSERT是插入,

VIEW DEFINITION是查看定义,

VIEW CHANGE TRACKING 跟踪更新,

ALTER更改,

UPDATE更新,

TAKE OWNERSHIP接管所有权,

CONTROL控制，

DELETE删除，

SELECT选择查询,

REFERENCES引用

表和视图的权限有INSERT  、VIEW DEFINITION  、VIEW CHANGE TRACKING  、ALTER  、UPDATE  、TAKE OWNERSHIP  、CONTROL 、 DELETE 、SELECT  REFERENCES

函数和存储过程的权限有  VIEW DEFINITION    、ALTER    、TAKE OWNERSHIP    、CONTROL  、REFERENCES    、EXECUTE

表

deny INSERT,VIEW DEFINITION,VIEW CHANGE TRACKING ,ALTER,UPDATE, TAKE OWNERSHIP,CONTROL,DELETE,SELECT,REFERENCES on dbo.GZ\_PATSETDATA0013 to developer;

表

deny INSERT,VIEW DEFINITION,VIEW CHANGE TRACKING ,ALTER,UPDATE, TAKE OWNERSHIP,CONTROL,DELETE,SELECT,REFERENCES on dbo.GZ_PATSETDATA0013 to developer;

视图

deny INSERT,VIEW DEFINITION ,ALTER,UPDATE, TAKE OWNERSHIP,CONTROL,DELETE,SELECT,REFERENCES on dbo.GZ_PATSETDATA0013 to developer;

标量函数

deny VIEW DEFINITION ,ALTER,EXECUTE,REFERENCES,TAKE OWNERSHIP,CONTROL on dbo.d to developer;

存储过程

deny execute ,TAKE OWNERSHIP,ALTER on test.dbo.st_pd_TakeSubsidyDetail to developer;

3、master权限到数据库的权限配置用户权限

一、创建maintainer

1、打开ssms连接sa，打开安全性->登录名右键新建

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70][]

填写常规的登录名、强密码、默认数据库。

选择合适的服务器角色和用户映射，这里选择public 架构dbo。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 1][]

安全对象如图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 2][]

2、打开数据库->安全性 右键选择用户->属性![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 3][]

配置两个函数的拒绝权限

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 4][]

3、选择对应的数据右键打开属性

配置权限![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 5][]

4、不分配各个顶层的用户权限，否则不能修改部分下层权限

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 6][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200619104148235.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200619104214658.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200619104223692.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200619104232366.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200619104239789.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200619104248821.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200618175744112.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3Njc0ODU4,size_16,color_FFFFFF,t_70