SQL Server 帐号权限管理及编程应用(图解)

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

任务目标

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

任务1:实现

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

表名 SELECT INSERT UPDATE DELETE
Student
Course
Score
Class
Exam
SQL Server 帐号权限管理及编程应用(图解)

实现步骤

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

SQL Server 帐号权限管理及编程应用(图解)

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

SQL Server 帐号权限管理及编程应用(图解)

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

SQL Server 帐号权限管理及编程应用(图解)

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

SQL Server 帐号权限管理及编程应用(图解)

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

SQL Server 帐号权限管理及编程应用(图解)

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

注意:【更新】指的是UPDATE语句,【更改】是ALTER语句

SQL Server 帐号权限管理及编程应用(图解)
表名 SELECT(选择) INSERT(插入) UPDATE(更新) DELETE(删除)
Student
Course
Score
Class
Exam

任务1:验证

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

SQL Server 帐号权限管理及编程应用(图解)SQL Server 帐号权限管理及编程应用(图解)

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表的操作不受限制,如下图所示,证明我们的授权是有效的。

SQL Server 帐号权限管理及编程应用(图解)

任务2:实现

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

实现步骤

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

SQL Server 帐号权限管理及编程应用(图解)

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

SQL Server 帐号权限管理及编程应用(图解)

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

SQL Server 帐号权限管理及编程应用(图解)

任务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‘

执行效果:

SQL Server 帐号权限管理及编程应用(图解)

主要代码:

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();
}

SQL Server 帐号权限管理及编程应用(图解)

上一篇:使用 TiUP cluster 在单机上安装TiDB


下一篇:数据分析,数组转化,对象转化,代码总结统计