SQLServer复习笔记:数据库编程、数据库安全、事务管理与并发控制、数据库存储管理与数据恢复

考前的一些复习整理，虽然考试考的会很浅，但难得有机会认真的复习一下数据库内容……

游标 cursor

DECLARE cursor_name CURSOR FOR SELECT ...
OPEN cursor_name
FETCH FROM cursor_name INTO @v_1,@v_2...
WHILE @@FETCH_STATUS=0

失败返回-1
没有找到记录返回100

CLOSE cursor_name
DEALLOCATE cursor_name

存储过程

CREATE PROCEDURE proc_name 
@input ='default',@input...
AS 
SELECT ...
RETURN @output ...

DECLARE @in INT ...
EXECUTE @in = proc_name '','',...
EXECUTE @in = proc_name @input = '',...

如果是OUTPUT参数的话调用PROC时需要传入数据对象

DECLARE @in INT, @avg INT
EXECUTE @in = proc_name @input = ' ',@avg = @avg OUTPUT

返回游标

CREATE PROCDURE proc_name 
@cursor_out CURSOR OUTPUT
AS
SET @cursor_out = CURSOR FOR 
sql_statement
OPEN cursor_out 

触发器

CREATE TRIGGER trigger_name ON
table_name
FOR/INSTEAD OF
INSERT/UPDATE/DELETE
sql_statement

动态SQL

DECLARE cursor_name DYNAMIC CURSOR FOR SQLSA
PREPARE SQLSA FROM @sql_statement  --通常为字符串变量
OPEN DYNAMIC cursor_name USING @parameter

数据库安全
访问控制

• 用户管理
• 角色管理
• 权限管理
• 数据加密

存取控制

• 定义用户权限 被称为安全规则或授权规则
• 合法权限检查 用户发出请求后，查找数据字典通过安全规则进行检查，如果超出权限则拒绝

用户角色权限

• 角色成为用户的成员
• 权限可以授权给用户，也可以授权给角色

自主存取控制

• 用户对于不同的数据对象（表）有不同的操作权限
• 不同的用户对于同一对象也有不同的操作权限，用户可以将其存取权限转授

强制存取控制
每一个数据对象被标以密级，每个用户被许可相应的级别

建立登录用户

CREATE LOGIN zhang WITH PASSWORD = '....' {[ MUST_CHANGE ]
[,DEFAULT_DATABASE = database ]
[, CHECK_EXPIRATION = { ON | OFF}]}

尽管指定了默认数据库，但是在成为该数据库用户前还是无法USE该数据库。

修改用户
ALTER LOGIN zhang DISABLE/ENABLE

• 修改口令
  管理员：ALTER LOGIN zhang WITH PASSWORD='bistu'
  用户自己：ALTER LOGIN zhang WITH PASSWORD='mis' OLD_PASSWORD='bistu'

删除登录用户

DROP LOGIN login_name

数据库用户

CREATE USER user_name [LOGIN login_name]
[ WITH DEFAULT_SCHEMA = schema_name ]

默认架构即访问数据库对象时可以省略架构名

USE 仓储订货
CREATE USER zhang WITH DEFAULT_SCHEMA =仓储

没有指定login则自动映射到同名login
在创建前USE数据库，指定默认架构，此时zhang成为了数据库用户，但在没有得到操作授权前不能进行查询和操作。

游标 cursor

DECLARE cursor_name CURSOR FOR SELECT ...
OPEN cursor_name
FETCH FROM cursor_name INTO @v_1,@v_2...
WHILE @@FETCH_STATUS=0
--失败返回-1
--没有找到记录返回100
CLOSE cursor_name
DEALLOCATE cursor_name

存储过程

CREATE PROCEDURE proc_name 
@input ='default',@input...
AS 
SELECT ...
RETURN @output ...

DECLARE @in INT ...
EXECUTE @in = proc_name '','',...
EXECUTE @in = proc_name @input = '',...

如果是OUTPUT参数的话调用PROC时需要传入数据对象

DECLARE @in INT, @avg INT
EXECUTE @in = proc_name @input = ' ',@avg = @avg OUTPUT

返回游标

CREATE PROCDURE proc_name 
@cursor_out CURSOR OUTPUT
AS
SET @cursor_out = CURSOR FOR 
sql_statement
OPEN cursor_out 

触发器

CREATE TRIGGER trigger_name ON
table_name
FOR/INSTEAD OF
INSERT/UPDATE/DELETE
sql_statement

动态SQL

DECLARE cursor_name DYNAMIC CURSOR FOR SQLSA
PREPARE SQLSA FROM @sql_statement  --通常为字符串变量
OPEN DYNAMIC cursor_name USING @parameter

数据库安全
访问控制

• 用户管理
• 角色管理
• 权限管理

数据加密

存取控制

• 定义用户权限 被称为安全规则或授权规则
• 合法权限检查 用户发出请求后，查找数据字典通过安全规则进行检查，如果超出权限则拒绝

用户角色权限

• 角色成为用户的成员
• 权限可以授权给用户，也可以授权给角色

自主存取控制

• 用户对于不同的数据对象（表）有不同的操作权限
• 不同的用户对于同一对象也有不同的操作权限，用户可以将其存取权限转授

强制存取控制
每一个数据对象被标以密级，每个用户被许可相应的级别

建立登录用户

CREATE LOGIN zhang WITH PASSWORD = '....' {[ MUST_CHANGE ]
[,DEFAULT_DATABASE = database ]
[, CHECK_EXPIRATION = { ON | OFF}]}

尽管指定了默认数据库，但是在成为该数据库用户前还是无法USE该数据库。

修改用户
ALTER LOGIN zhang DISABLE/ENABLE

修改口令

• 管理员：ALTER LOGIN zhang WITH PASSWORD='bistu'
• 用户自己：ALTER LOGIN zhang WITH PASSWORD='mis' OLD_PASSWORD='bistu'

删除登录用户
DROP LOGIN login_name

数据库用户

CREATE USER user_name [LOGIN login_name]
[ WITH DEFAULT_SCHEMA = schema_name ]

默认架构即访问数据库对象时可以省略架构名

USE 仓储订货
CREATE USER zhang WITH DEFAULT_SCHEMA =仓储

没有指定login则自动映射到同名login
在创建前USE数据库，指定默认架构，此时zhang成为了数据库用户，但在没有得到操作授权前不能进行查询和操作。

数据库用户分类

• 系统管理员
• 数据库管理员
• 数据库对象用户 创建表、视图等
• 数据库访问用户 可以操作被授权的数据库对象（表、视图等）

public角色

• 每个数据库（包括所有系统数据库和所有用户数据库）都有public角色
  每个数据库用户都自动是public角色的成员
  public角色的权限是数据库中所有用户的默认权限
  或者说每个用户都拥有的权限应该来自public角色
  系统初始时public角色几乎没有任何默认的权限
  管理员应该将所有用户拥有的权限授予public角色

创建角色
CREATE ROLE role_name [ AUTHORIZATION owner_name ]
以sa登录后直接执行如下命令：

USE 仓储订货
	CREATE ROLE manager

指定角色
sp_addrolemember @rolename='manager',@membername='huang'
或
sp_addrolemember 'manager','huang'
取消角色
sp_droprolemember @rolename='manager',@membername='huang'

修改角色名称命令
ALTER ROLE role_name WITH NAME = new_name
例：将角色manager的名称修改为orders_manager 。
ALTER ROLE manager WITH NAME = orders_manager

授予权限-对象权限

GRANT SELECT ON 基础.职工(职工号,仓库号,姓名,班组长) TO public 
GRANT INSERT,UPDATE(经手人,供货方,订购日期,金额),DELETE 
ON 订货.订购单 TO order_man 
WITH GRANT OPTION
GRANT ... ON A.B TO ... AS ...
REVOKE GRANT OPTION FOR INSERT ON table FROM role
REVOKE INSERT ON table FROM role

架构权限

GRANT INSERT/CONTROL ON SCHEMEA::name TO user
ALTER AUTHORIZATION ON SCHEMEA::name/table_name TO user/SCHEMA OWNER 
ALTER schema_to TRANSFER schema_from.table 

语句权限
CREATE TABLE/RULE
BACKUP 等

禁止权限
DENY X TO A

事务
在关系数据库中，一个事务可以是一条SQL语句，一个SQL语句序列或整个程序。
事务的开始与结束可以由用户显式控制。如果用户没有显式地定义事务则由DBMS按缺省规定自动划分事务(隐式事务)。
在SQL语言中，定义事务的语句有：
BEGIN TRANSACTION 定义事务开始
COMMIT 提交事务
ROLLBACK 回滚事务

事务的特性

• 原子性（Atomicity）：事务是数据库工作的基本逻辑单位
• 事务中包括的各操作要么全做，要么全不做；
• 一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态。
• 隔离性（Isolation）：一个事务 的执行不能被其它事务干扰。
• 持续性（Durability）：一个事务一旦提交，它对数据库数据的改变就应是持久的，即使系统发生故障也不影响其结果。

以上称为事务的ACID特性。

可能导致事务ACID特性被破坏的因素

• 多个事务并发运行，不同事务的操作交叉执行；
• 事务在运行过程中被强行停止。

数据恢复机制和并发控制机制的任务

• 保证多个事务的交叉运行不影响这些事务的原子性；
• 保证被强行终止的事务不影响其它事务的执行。

设置隐含事务方式的命令
SET IMPLICIT_TRANSACTIONS ON

取消隐含事务方式的命令
SET IMPLICIT_TRANSACTIONS OFF

基本的封锁类型

• 排它锁(Exclusive Locks，简称X锁)，又称为写锁；
  若事务T对数据对象A加X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。
  这保证了其他事务在T释放A的锁之前不能再读取和修改A。

• 共享锁(Share Locks，简称S锁)，又称为读锁。
  若事务T对数据对象A加S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A的S锁。
  这就保证了其他事务可以读A，但在T释放A的S锁之前不能对A做任何修改。

SQL Server的封锁操作是在相关语句的“WITH (<table_hint>)”子句中完成的，该短语可以在SELECT、INSERT、UPDATE和DELETE等语句中指定表级锁定的方式和范围。

TABLOCK：对表施行共享封锁，在读完数据后立刻释放封锁，此类封锁可以避免读“脏”数据，但不具有可重复读的特性。
HOLDLOCK：与TABLOCK一起使用，可将共享锁保留到事务完成，而不是在读完数据后立即释放锁，这样可以保证数据的可重复读。
NOLOCK：不进行封锁，此关键词仅应用于SELECT语句，这样可能会读取未提交事务的数据，即有可能发生“脏”读。
TABLOCKX：对表实施独占封锁。
UPDLOCK：对表中的指定元组实施更新封锁；这时其它事务可以对同一表中的其它元组也实施更新封锁，但是不允许对表实施共享封锁和独占封锁。

建立冗余数据最常用的技术是数据转储和登录日志文件。