【SQL server数据库基础】数据库的安全管理

1. 登录管理

有了登陆账户，只能连接到数据库服务器；而要使用数据库服务器中的数据库，一定要有用户，用户必须跟登录名有关联（映射关系），该登录名才能使用数据库；有了用户，还要对用户进行授权，才能对数据库对象进行操作。每个数据库用户只能对应一个登录名

-- 将Windows 账户中的用户 "Teacher" 添加到SQL Server 登录中，默认数据库是“master”
create login [机器名字\Teacher] -- 创建登录账户名，从Windows账户中映射的用户名要用 [] 括起来
from windows -- 从windows 账户中添加
with default_database = [master] -- 关键字加中括号

-- 创建名为 “teachers” 的SQL登录账户，密码为“sql@123%”，默认数据库为“master”，强制实施密码策略
create login teachers  -- 创建登陆账户
with password = "sql@123%",  -- 账户需要一个密码
	 default_database = [master],
	 check_expiration = on, -- 强制密码过期（密码有效日期，操作系统密码存在有效日期）
	 check_policy = on  -- 强制实施密码策略
	 
-- 修改SQL登录账户teachers的密码为 “Sql10o123”
alter login teachers
with password = 'Sql10o123'
old_password = 'sql@123%'  -- SQL身份验证的登录账户需要输入原密码

-- 禁用名为 “teachers” 登录账户
-- 禁用
alter login teachers disable
-- 启用
alter login teachers enable

-- 删除名为 “teachers” 登录账户
drop login teachers

2. 用户权限管理

每个数据库用户只能对应一个登录账号；一个登录名可以被授权访问多个数据库，但一个登录名在每个数据库中只能映射一次。即一个登录可对应多个用户，一个用户也可以被多个登录使用。

每个架构只能赋给一个用户。

-- 用户管理
-- 创建名为 “teachUser”的登录名，在StuSelDB 数据库中，创建用户 “teach1” 与 “teachUser” 登录名对应
create login teachUser 
with password = '123'
	 default_database = [master]
go
use StuSelDB
go
create user teach1
for login teachUser -- 用户映射到登录账户

-- 修改在 StuSelDB 数据库中创建的用户teach1，将其名称修改为teach2
alter user teach1
with name = teach2

-- 删除 StuSelDB 数据库用户 teach2
drop user teach2

-- 权限管理
-- 在 StuSelDB 中，创建用户 teacher，其对应登录名为 teachers，并将表 TeachInfo 的 select 权限授予 teacher
create login teachers -- 第一步，创建登陆账户
with password = '123',  -- 账户密码
	 default_database = [master],
	 check_expiration = on,
	 check_policy = on
go
use StuSelDB
go
create user teacher -- 第二步 创建用户
for login teachers -- 映射登录账户
go
grant select on TeachInfo to teacher -- 第三步，给用户授权

-- 在 StuSelDB 数据库中，拒绝用户 teacher 查看 AdminUser 表的权限
deny select on AdminUser to teacher

-- 在 StuSelDB 数据库中，撤销用户 teacher 对表 TeachInfo 的 select 权限
revoke select on TeachInfo to teacher

3. 角色管理

固定服务器角色：对整个数据库服务器进行控制， sp_addsrvrolemember ‘登录账户’,‘角色’

固定数据库角色：对单个数据库中的文件授权控制， sp_addrolemember ‘角色’,‘用户’

-- 将 SQL Server 登录名 “teachers” 添加到 sysadmin 固定服务器角色中，使其可以在数据库服务器上执行任何操作
exec sp_addsrvrolemember 'teachers','sysadmin'

-- 将 SQL Server 登录名 “teachers” 从 sysadmin 固定服务器角色中删除
exec sp_dropsrvrolemember 'teachers','sysadmin'

-- 创建一个新的登录 test,使其映射到 StuSelDB 数据库中的角色 test，设置为 db_datareader 数据库角色的成员，使之对该数据库中的数据只有只读权限
create login test
with password = '123',
	 dafault_database = [master]
go
use StuSelDB
go
create user test
for login test
go
exec sp_addrolemember 'db_datareader','test'

-- 将 StuSelDB 数据库中的用户 test 从 db_datareader 角色中删除
exec sp_droprolemember 'db_datareader','test'

-- 将  StuSelDB 数据库中添加角色 MyTeacher,并将用户 teacher1（对应登录名teach1）和 teacher2（对应登录名teach2）添加到该角色中，赋予该角色在教师信息表 TeachInfo 上有插入、修改和删除的权限，拒绝该角色创建表的权限
create login teach1
with password = '123',
	 dafault_database = [master]
go
create login teach2
with password = '123',
	 dafault_database = [master]
go
use StuSelDB
go
create user teacher1 for login teach1
go
create user teacher2 for login teach2
go
create role MyTeacher
go
exec sp_addrolemember 'MyTeacher','teacher1'
go
exec sp_addrolemember 'MyTeacher','teacher2'
go
grant insert,update,delete on TeachInfo to MyTeacher
with grant option -- 可以给其他角色或用户授权
go
deny create table to Myteacher

主文章：备考计算机三级数据库——SQL 案例