SQL Server 帐号权限管理及C#编程应用（图解）

昨晚在群里讲解这部分内容，因为好久没操作过了，差点翻车。。。今天把它整理一下发出来，方便没听明白的小伙伴学习和理解。
我们平时学习数据库时，要么使用sa帐号，要么用windows默认帐号登录，总之都拥有最高权限，而实际的生产环境中，不可能这样的，帐号权限要严格划分，才能保证基本的安全，那么问题来了，如何为帐号分配权限呢？应用程序需要更高的操作权限，该怎么办呢？下面我们就来解决这两个问题！

任务目标

1. 为帐号分配权限，实现对不同表授予不同的增删改查等权限
2. 同一帐号，在不同场景下拥有不同权限

任务1：实现

任务描述：创建一个帐号AppUser，密码123，它只能访问test2数据库，对数据表授予不同权限，如下表所示

表名	SELECT	INSERT	UPDATE	DELETE
Student	有	有	有	有
Course	有	有	有	无
Score	有	有	无	无
Class	无	有	无	无
Exam	无	无	无	无

实现步骤：

1、创建【登录名】：安全性——登录名——新建登录名，如图所示

2、【常规】页面 → 输入登录名AppUser → 输入密码123 → 不勾选“强制实施密码策略” → 默认数据库test2

3、【用户映射】页面 → 在test2数据库前面勾选，用户处自动出现AppUser → “确定”按钮，创建的登录名将出现在列表的最下面。

4、【数据库】 → test2 → 安全性 → 角色 → 数据库角色 → 右键 → 新建数据库角色，

5、【常规】页面 → 输入角色名称MyRole → 添加 → 浏览 → 勾选AppUser ，在“角色成员”列表中出现AppUser

6、【安全对象】页面 → 【搜索】 → 特定对象 → 对象类型 → 勾选“表”，确定 → 浏览 → 勾选Student等5个表，确定，确定 → 分别选择安全对象，并对其授权

注意：【更新】指的是UPDATE语句，【更改】是ALTER语句

表名	SELECT（选择）	INSERT（插入）	UPDATE（更新）	DELETE（删除）
Student	有	有	有	有
Course	有	有	有	无
Score	有	有	无	无
Class	无	有	无	无
Exam	无	无	无	无

任务1：验证

1、用刚刚创建的登陆名AppUser登录，然后试图查看其他数据库，均提示无权访问，只能访问test2，证明我们的授权有效。

2、对几张表进行增删改查的验证，SQL语句及结果如下：

SELECT * FROM dbo.Class
--结果：拒绝了对对象 'Class' (数据库 'test2'，架构 'dbo')的 SELECT 权限。
UPDATE dbo.Score SET Score=90 WHERE Id=1
--结果：拒绝了对对象 'Score' (数据库 'test2'，架构 'dbo')的 UPDATE 权限。
DELETE dbo.Course WHERE Id=1
--结果：拒绝了对对象 'Course' (数据库 'test2'，架构 'dbo')的 DELETE 权限。

而对Student表的操作不受限制，如下图所示，证明我们的授权是有效的。

任务2：实现

任务描述：我们还是使用AppUser帐号，在应用程序中具有不同的权限（一般来说在App中应该有更高的权限），如：目前我们只能对Class表进行插入操作，随后我们让它具备增删改查的权限。

实现步骤：

1、切换sa帐号（否则无权做后面的事） → 【test2】数据库 → 安全性 → 角色应用程序角色 → 右键“新建应用程序角色”

2、【常规】页面 → 角色名称Approle，密码456

3、【安全对象】页面 → 【搜索】 → 特定对象，确定 → 对象类型 → 勾选“表”，确定 → 浏览 → 勾选Class等5个表，确定 → 对5个表全部授予“增删改查”权限

任务2：验证

总体思路：我们使用C#语言创建一个控制台应用程序，依然使用之前创建的AppUser帐号和密码进行登录，然后授予刚刚创建的Approle角色，来达到临时提升AppUser帐号权限的效果。

关键步骤：

1、设置连接字符串，使用的是AppUser帐号和密码

private static string ConnectionString = "Data Source=127.0.0.1,3300;User Id=AppUser;Password=123;Initial Catalog=test2;Pooling=true;Min Pool Size=1";

2、通过执行以下SQL语句临时授予Approle角色权限

EXEC sys.sp_setapprole @rolename='Approle',@password='456'

执行效果：

主要代码：

static void Main(string[] args)
{
	var db = Db.Instance;
	Console.WriteLine("-------- 授予Approle角色前 -------");
	try
	{
		db.Select<Class>().ToList();
	}
	catch (Exception ex)
	{
		Console.WriteLine($"无权查看，错误信息：{ex.Message}");
	}
	db.Ado.ExecuteNonQuery("EXEC sys.sp_setapprole @rolename='Approle',@password='456'");
	Console.WriteLine("-------- 授予Approle角色后 -------");
	Console.WriteLine($"Class表有{db.Select<Class>().Count()}条记录");
	Console.WriteLine("-------- 修改前 -------");
	db.Select<Class>().Take(2).ToList().ForEach(x => Console.WriteLine($"Id:{x.Id}    Name:{x.Name}"));
	Console.WriteLine("-------- 修改第1条数据 -------");
	db.Update<Class>(1).Set(a => a.Name , "测试Update").ExecuteAffrows();
	Console.WriteLine("-------- 修改后 -------");
	db.Select<Class>().Take(2).ToList().ForEach(x => Console.WriteLine($"Id:{x.Id}    Name:{x.Name}"));

	Console.Read();
}