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