设置Sql server用户权限---对表,视图,存储过程,架构的增删改查权限

周大超

已于 2022-04-15 15:44:26 修改

阅读量1.2w

点赞数 4

分类专栏： SQLserver 文章标签：数据库 golang sqlserver

于 2022-03-18 10:42:40 首次发布

原文链接：https://www.cnblogs.com/OpenCoder/p/8087210.html

版权

SQLserver 专栏收录该内容

47 篇文章

订阅专栏

本文详细介绍了如何通过SQL语句在数据库Schema中对用户权限进行精细化管理，包括授予和回收定义权限、执行存储过程权限、增删改查操作，以及针对具体表和视图的权限设置。重点展示了如何设置数据库角色和列级别的权限控制，以实现数据保密和访问权限的定制化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

根据数据库Schema限制用户对数据库的操作行为

授予Shema dbo下对象的定义权限给某个用户（也就是说该用户可以修改架构dbo下所有表/视图/存储过程/函数的结构）

use [Your DB NAME]
GRANT VIEW DEFINITION ON SCHEMA :: dbo to [THE USER NAME]

回收某个用户对Shema dbo下对象的定义权限（也就是说该用户不可以修改架构dbo下所有表/视图/存储过程/函数的结构）

use [Your DB NAME]
DENY VIEW DEFINITION ON SCHEMA :: dbo to [THE USER NAME]

允许某个用户执行Shema dbo下定义的存储过程

 GRANT EXEC  ON SCHEMA :: dbo TO  [your_DB_account]

不允许某个用户执行Shema dbo下定义的存储过程

 DENY EXEC  ON SCHEMA :: dbo TO  [your_DB_account]

允许某个用户可以对Schema dbo下对象进行增删改查操作

GRANT SELECT  ON SCHEMA :: dbo TO [your_DB_account]  -- 允许查询数据权限
GRANT UPDATE  ON SCHEMA :: dbo TO [your_DB_account]  -- 允许更新数据权限
GRANT INSERT  ON SCHEMA :: dbo TO [your_DB_account]  -- 允许插入数据权限
GRANT DELETE  ON SCHEMA :: dbo TO [your_DB_account]  -- 允许删除数据权限

不允许某个用户对Schema dbo下对象进行增删改查操作

DENY SELECT  ON SCHEMA :: dbo TO [your_DB_account]  -- 不允许查询数据权限
DENY UPDATE  ON SCHEMA :: dbo TO [your_DB_account]  -- 不允许更新数据权限
DENY INSERT  ON SCHEMA :: dbo TO [your_DB_account]  -- 不允许插入数据权限
DENY DELETE  ON SCHEMA :: dbo TO [your_DB_account]  -- 不允许删除数据权限

限制用户对数据库对象（比如表/视图/存储过程等）的操作行为

允许用户修改数据库表T_Testing的结构

GRANT VIEW DEFINITION ON [dbo].[T_Testing] to [THE USER NAME]

不允许用户修改数据库表T_Testing的结构

DENY VIEW DEFINITION ON [dbo].[T_Testing] to [Customer]

允许用户对数据库表T_Testing进行增删改查操作

GRANT SELECT  ON [dbo].[T_Testing] TO [your_DB_account]  -- 允许查询数据权限
GRANT UPDATE  ON [dbo].[T_Testing] TO [your_DB_account]  -- 允许更新数据权限
GRANT INSERT  ON [dbo].[T_Testing] TO [your_DB_account]  -- 允许插入数据权限
GRANT DELETE  ON [dbo].[T_Testing] TO [your_DB_account]  -- 允许删除数据权限

不允许用户对数据库表T_Testing进行增删改查操作

DENY SELECT  ON [dbo].[T_Testing] TO [your_DB_account]  -- 不允许查询数据权限
DENY UPDATE  ON [dbo].[T_Testing] TO [your_DB_account]  -- 不允许更新数据权限
DENY INSERT  ON [dbo].[T_Testing] TO [your_DB_account]  -- 不允许插入数据权限
DENY DELETE  ON [dbo].[T_Testing] TO [your_DB_account]  -- 不允许删除数据权限

上面这些对数据库表的语句同样适用于其它数据库对象，例如视图/存储过程等，如下所示：

GRANT VIEW DEFINITION ON [dbo].[V_Testing] to [your_DB_account] --允许用户修改视图V_Testing的定义
DENY VIEW DEFINITION ON [dbo].[V_Testing] to [your_DB_account]--不允许用户修改视图V_Testing的定义

GRANT VIEW DEFINITION ON [dbo].[P_Testing] to [your_DB_account] --允许用户修改存储过程P_Testing的定义
DENY VIEW DEFINITION ON [dbo].[P_Testing] to [your_DB_account]--不允许用户修改存储过程P_Testing的定义

但是注意SELECT/UPDATE/DELETE/INSERT这几个增删改查的权限不适用于存储过程

此外对数据库对象（比如表/试图/存储过程等）的上述操作行为，还可以直接设置在数据库角色（注意是数据库角色，不是数据库Instance角色）上，例如：

允许数据库MyDataBase的角色MyRole对表T_Testing拥有增删改查权限

USE [MyDataBase]

GRANT SELECT  ON [dbo].[T_Testing] TO [MyRole]  -- 允许查询数据权限
GRANT UPDATE  ON [dbo].[T_Testing] TO [MyRole]  -- 允许更新数据权限
GRANT INSERT  ON [dbo].[T_Testing] TO [MyRole]  -- 允许插入数据权限
GRANT DELETE  ON [dbo].[T_Testing] TO [MyRole]  -- 允许删除数据权限

不允许数据库MyDataBase的角色MyRole对表T_Testing进行增删改查操作

USE [MyDataBase]

DENY SELECT  ON [dbo].[T_Testing] TO [MyRole]  -- 不允许查询数据权限
DENY UPDATE  ON [dbo].[T_Testing] TO [MyRole]  -- 不允许更新数据权限
DENY INSERT  ON [dbo].[T_Testing] TO [MyRole]  -- 不允许插入数据权限
DENY DELETE  ON [dbo].[T_Testing] TO [MyRole]  -- 不允许删除数据权限

此外对于SELECT/UPDATE这两个改查的权限还可以直接设置到表/试图的列上，例如下面语句我们设置数据库MyDataBase的角色MyRole拥有表T_Tesing和视图V_Testing中列Name的改查权限

USE [MyDataBase]

GRANT SELECT  ON [dbo].[T_Testing]([Name]) TO [MyRole]  -- 允许查询表T_Testing的Name列数据
GRANT UPDATE  ON [dbo].[T_Testing]([Name])  TO [MyRole]  -- 允许更新表T_Testing的Name列数据

GRANT SELECT  ON [dbo].[V_Testing]([Name]) TO [MyRole]  -- 允许查询视图V_Testing的Name列数据
GRANT UPDATE  ON [dbo].[V_Testing]([Name])  TO [MyRole]  -- 允许更新视图V_Testing的Name列数据

使用数据库权限控制用户的访问行为

如果希望某个用户只拥有某个数据库的只读权限，最简单的办法就是只将该数据库的角色db_datareader赋予用户即可：

exec sp_addrolemember' db_datareader','用户名'

这里最后列出所有数据库Instance角色和（用户映射）数据库角色的含义：

数据库Instance角色：

sysadmin 可以在SQLServer中执行任何活动。
serveradmin 可以设置服务器范围的配置选项，关闭服务器。
setupadmin 可以管理链接服务器和启动过程。
securityadmin 可以管理登录和CREATEDATABASE权限，还可以读取错误日志和更改密码。
processadmin 可以管理在SQLServer中运行的进程。
dbcreator 可以创建、更改和除去数据库。
diskadmin 可以管理磁盘文件。
bulkadmin 可以执行BULKINSERT语句。

（用户映射）数据库角色：

db_owner执行数据库中的所有维护和配置活动。
db_accessadmin添加或删除Windows用户、组和SQLServer登录的访问权限。
db_datareader读取所有用户表中的所有数据。
db_datawriter添加、删除或更改所有用户表中的数据。
db_ddladmin在数据库中运行任何数据定义语言(DDL)命令。
db_securityadmin修改角色成员身份并管理权限。
db_backupoperator备份数据库。
db_denydatareader无法读取数据库用户表中的任何数据。
db_denydatawriter无法添加、修改或删除任何用户表或视图中的数据。

第二种配置方法:

转载地址:https://jingyan.baidu.com/article/7c6fb428af56e5c0642c90f8.html

本经验介绍如何只想让某个用户访问数据库特定的表或视图，达到保密的效果。现有数据库CC下有3张数据表，本例子将设定一个用户，让其只能访问其中的SFC_ZC表。跟着下面的步骤开始做吧。

工具/原料

SQL server 2014

方法/步骤

登录SSMS之后选择安全性，右键点击登录名。在弹出的右键菜单中选择【新建登录名】选项。
在新建登录名界面中的，常规页面输入新的登录名（本例为user_test）并设定密码。可以取消勾选用户下次登录时必须更改密码选项。最后设定默认数据库为CC。
接下来在用户映射页面中，勾选映射CC数据。因为我们这次要分配的是查询权限的用户，所以下方的数据库角色成员身份只勾一个public即可。点击确定按钮完成新用户创建。
在CC数据库下点击新建查询，在IDE中输入 grant select on sfc_zc to user_test语句并执行。该语句的作用是分配sfc_zc表的查询权限给user_test用户。
权限分配完成后可以测试一下效果如何。断开数据库连接，用我们刚才新建的user_test用户重新登录数据库。
这时我们可以看到CC数据库下面只能看到SFC_ZC这张数据表了，另外两张表都不见了，到此大功告成。