SQL Server 数据库安全之角色-YES开发框架网

一直以来数据库用户名为了方便，都是直接给与用户db_owner权限，这种做法是依然存在安全隐患，看看db_owner角色的权限说明

db_owner	db_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动，还可以删除 SQL Server中的数据库。（在 SQL Database 和 SQL 数据仓库中，某些维护活动需要服务器级别权限，并且不能由 db_owners执行。）
public

服务器级的固定角色

SQL Server 提供服务器级角色以帮助你管理服务器上的权限。这些角色是可组合其他主体的安全主体。服务器级角色的权限作用域为服务器范围。（“角色”类似于 Windows 操作系统中的“组”。）

提供固定服务器角色是为了方便使用和向后兼容。应尽可能分配更具体的权限。

SQL Server 提供了九种固定服务器角色。无法更改授予固定服务器角色（public 角色除外）的权限。从 SQL Server 2012 (11.x)开始，您可以创建用户定义的服务器角色，并将服务器级权限添加到用户定义的服务器角色。

你可以将服务器级主体（ SQL Server 登录名、Windows 帐户和 Windows 组）添加到服务器级角色。固定服务器角色的每个成员都可以将其他登录名添加到该同一角色。用户定义的服务器角色的成员则无法将其他服务器主体添加到角色。

服务器级的固定角色	描述
sysadmin	sysadmin 固定服务器角色的成员可以在服务器上执行任何活动。
serveradmin	serveradmin 固定服务器角色的成员可以更改服务器范围的配置选项和关闭服务器。
securityadmin	securityadmin 固定服务器角色的成员可以管理登录名及其属性。他们可以 GRANT、DENY 和 REVOKE 服务器级权限。他们还可以 GRANT、DENY 和 REVOKE 数据库级权限（如果他们具有数据库的访问权限）。此外，他们还可以重置 SQL Server 登录名的密码。重要提示：授予数据库引擎的访问权限和配置用户权限的能力使得安全管理员可以分配大多数服务器权限。 securityadmin 角色应视为与 sysadmin 角色等效。
processadmin	processadmin 固定服务器角色的成员可以终止在 SQL Server 实例中运行的进程。
setupadmin	setupadmin 固定服务器角色的成员可以使用 Transact-SQL 语句添加和删除链接服务器。（使用Management Studio 时需要 sysadmin 成员资格。）
bulkadmin	bulkadmin 固定服务器角色的成员可以运行 BULK INSERT 语句。
diskadmin	diskadmin 固定服务器角色用于管理磁盘文件。
dbcreator	dbcreator 固定服务器角色的成员可以创建、更改、删除和还原任何数据库。
public	每个 SQL Server 登录名都属于 public 服务器角色。如果未向某个服务器主体授予或拒绝对某个安全对象的特定权限，该用户将继承授予该对象的 public 角色的权限。只有在希望所有用户都能使用对象时，才在对象上分配 Public 权限。你无法更改具有 Public 角色的成员身份。注意： public 与其他角色的实现方式不同，可通过 public 固定服务器角色授予、拒绝或调用权限。

数据库默认角色列表：

db_owner	db_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动，还可以删除 SQL Server中的数据库。（在 SQL Database 和 SQL 数据仓库中，某些维护活动需要服务器级别权限，并且不能由 db_owners执行。）
db_securityadmin	db_securityadmin 固定数据库角色的成员可以修改角色成员身份和管理权限。向此角色中添加主体可能会导致意外的权限升级。
db_accessadmin	db_accessadmin 固定数据库角色的成员可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限。
db_backupoperator	db_backupoperator 固定数据库角色的成员可以备份数据库。
db_ddladmin	db_ddladmin 固定数据库角色的成员可以在数据库中运行任何数据定义语言 (DDL) 命令。
db_datawriter	db_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。
db_datareader	db_datareader 固定数据库角色的成员可以从所有用户表中读取所有数据。
db_denydatawriter	db_denydatawriter 固定数据库角色的成员不能添加、修改或删除数据库内用户表中的任何数据。
db_denydatareader	db_denydatareader 固定数据库角色的成员不能读取数据库内用户表中的任何数据。

SQL Database 和 SQL 数据仓库的特殊角色

这些数据库角色仅存在于虚拟 master 数据库中。他们的权限仅限于在 master 中执行的操作。只能向这些角色添加 master 中的数据库用户。无法向这些角色添加登录名，但可以基于登录名创建用户，然后向角色添加用户。也可以向这些角色添加 master 中包含的数据库用户。

角色名称	描述
dbmanager	可以创建和删除数据库。创建数据库的 dbmanager 角色的成员将成为该数据库的所有者，从而让该用户可作为 dbo 用户连接到该数据库。 Dbo 用户具有数据库中的所有数据库权限。 Dbmanager 角色的成员不一定具有访问非他们所有的数据库的权限。
loginmanager	可以创建和删除虚拟 master 数据库中的登录名。

参考这个列表，尝试规范一下角色分配

分配这两个角色

db_datawriter	db_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。
db_datareader	db_datareader 固定数据库角色的成员可以从所有用户表中读取所有数据。
public

分配过后，，可以对表数据进行增删该查

按要求无法执行drop

似乎已经达到基本目的，继续测试

发现无法执行存储过程，报错信息如下：

查资料:

    1. ---------------------------------------------------------
    2. -- 创建用户角色，给角色赋存储过程执行权限, 将角色赋预某用户
    3. use [YOUR DB NAME]
    4. -- 建角色[db_YourDBName_Executor]
    5. create role [db_YourDBName_Executor] AUTHORIZATION [dbo];
    6. -- 授予角色执行的权限
    7. grant exec to [db_YourDBName_Executor];
    8. -- 将角色授予用户
    9. exec sp_addrolemember N'db_YourDBName_Executor',N'karl.li'
    10. ----------------------------------------------------------
    11. -- 给用户赋存储过程查看定义权限
    12. use [YOUR DB NAME]
    13. GRANT VIEW DEFINITION ON SCHEMA :: dbo to [Your User Name]
---------------------------------------------------------

GarsonZhang www.yesdotnet.com

来自 <https://blog.csdn.net/keenweiwei/article/details/48173765>

参考此写法，达到效果

执行以后能看到数据库角色中多了一个：

用户映射中，已经赋予了角色

分析代码：第11行给用户赋存储过程查看定义权限

    1. -- 给用户赋存储过程查看定义权限
    2. use [YOUR DB NAME]
    3. GRANT VIEW DEFINITION ON SCHEMA :: dbo to [Your User Name]
---------------------------------------------------------

GarsonZhang www.yesdotnet.com

如果不执行这段代码，最直接的效果就是无法查看存储过程定义

查看配置：

数据库→安全性→架构→dbo→属性