08.txt

包含了几个由sql server2005开发的典型程序

第8章 安全与权限
8.2 管理用户
8.2.1 管理对SQL Server实例的访问 
3．授权SQL Server登录名 

	CREATE LOGIN Marylogin
	WITH PASSWORD = '674an7$52',
	DEFAULT_DATABASE =实例数据库;

获取有关SQL Server登录名的信息：
 
	SELECT *
	FROM sys.sql_logins;

4．实施密码策略

打开密码实施策略：
 
	CREATE LOGIN Marylogin
	WITH PASSWORD = '674an7$52' MUST_CHANGE,
	CHECK_EXPIRATION = ON,
	CHECK_POLICY = ON;

5．拒绝用户访问

启用或者禁用登录名Marylogin： 

	ALTER LOGIN Marylogin DISABLE;
	ALTER LOGIN Marylogin ENABLE;

检查被禁用的登录名：

	SELECT * FROM sys.sql_logins
	WHERE is_disabled=1;

删除登录名Marylogin： 

	DROP LOGIN Marylogin; 

8.2.2 管理对SQL Server数据库的访问 
1．创建数据库用户

创建数据库用户

	CREATE LOGIN Peterlogin WITH PASSWORD='234$7hf8';
	USE 实例数据库;
	GO
	CREATE USER Peteruse FOR LOGIN Peterlogin;

2．管理数据库用户

检查当前的登录名：

	SELECT HAS_DBACCESS('实例数据库');

撤消用户Peteruse的CONNECT授权：

	USE 实例数据库;
	GO
	REVOKE CONNECT TO Peteruse;

3．管理孤立用户

获取孤立用户的信息：

	USE 实例数据库;
	GO
	EXECUTE sp_change_users_login @Action='Report';

创建一个没有映射到登录名的用户Peteruse：

	USE 实例数据库;
	GO
	CREATE USER Peteruse WITHOUT LOGIN;

4．启用Guest用户

	USE 实例数据库;
	GO
	GRANT CONNECT TO Guest;


8.3 管理角色
8.3.1 管理实例角色

查询当前用户是否属于一个服务器角色：
 
	SELECT IS_SRVROLEMEMBER ('sysadmin'); 

为现有的服务器角色添加一个登录名： 

	EXECUTE sp_addsrvrolemember 'Marylogin', 'sysadmin'; 

删除sysadmin服务器角色中的登录名Marylogin： 

	EXECUTE sp_dropsrvrolemember 'Marylogin', 'sysadmin';  

8.3.2 管理数据库角色
1．创建数据库角色

创建了一个名为Auditors的数据库角色，并在这个新角色中添加数据库用户Peter：

	USE 实例数据库;
	GO
	CREATE ROLE Auditorsrole;
	GO
	EXECUTE sp_addrolemember 'Auditorsrole', 'Peteruse';

2．管理数据库角色

判断当前用户是否属于角色db_owner：

	USE 实例数据库;
	GO
	SELECT IS_MEMBER ('db_owner');

将数据库用户Peteruse从数据库角色Auditorsrole中移除：

	USE 实例数据库;
	GO
	EXECUTE sp_droprolemember 'Auditorsrole', 'Peteruse';
	DROP ROLE Auditorsrole;

8.3.3 管理应用程序角色 
1．创建应用程序角色

创建一个应用程序角色。

	USE 实例数据库
	GO
	CREATE APPLICATION ROLE FinancialRole
	WITH PASSWORD = '09$9ik985';

2．使用应用程序角色

激活应用程序角色FinancialRole并解除这个操作

	USE 实例数据库;
	GO
	DECLARE @context varbinary (8000);
	EXECUTE sp_setapprole 'FinancialRole',
	'09$9ik985',
	@fCreateCookie = true,
	@cookie = @context OUTPUT;
	SELECT CURRENT_USER;
	EXECUTE sp_unsetapprole @context;
	GO
	SELECT CURRENT_USER;
	GO

3．删除应用程序角色

删除应用程序角色

	USE 实例数据库;
	GO
	DROP APPLICATION ROLE FinancialRole;

8.4 管理架构
8.4.1  认识架构

创建一个名为Adminschema的架构，指定所有者,创建表,授予select权限：

	USE 实例数据库;
	GO
	CREATE SCHEMA Adminschema
	AUTHORIZATION Peteruse;
	GO
	CREATE TABLE Adminschema.Student (
	StudentID int,
	StudentDate smalldatetime,
	ClientID int);
	GO
	GRANT SELECT ON Adminschema.Student
	TO public;
	GO

获取架构的信息：

	SELECT *
	FROM sys.schemas; 

删除架构： 

	USE 实例数据库
	GO
	SELECT s.name AS 'Schema',
	o.name AS 'Object'
	FROM sys.schemas s
	INNER JOIN sys.objects o
	ON s.schema_id=o.schema_id
	WHERE s.name='Adminschema';
	GO
	DROP TABLE Adminschema.Student;
	GO
	DROP SCHEMA Adminschema; 

8.4.2  使用默认架构

创建一个架构并将其指定为一个数据库用户的默认架构： 

	CREATE LOGIN Marylogin
	WITH PASSWORD=' 674an7$52 ';
	GO
	USE 实例数据库;
	GO
	CREATE USER Peteruse
	FOR LOGIN Marylogin;
	GO
	CREATE SCHEMA Adminschema
	AUTHORIZATION Roseuse;
	GO
	CREATE TABLE Adminschema.Student (
	StudentID int,
	StudentDate smalldatetime,
	ClientID int);
	GO
	GRANT SELECT ON Adminschema.Student TO Peteruse;
	GO
	ALTER USER Peteruse
	WITH DEFAULT_SCHEMA= Adminschema;

8.5 管理权限
8.5.1  管理对SQL Server实例和数据库的访问
1．控制登录操作

为登录名Marylogin授予创建和执行SQL Server Profiler跟踪的权限：
 
	GRANT ALTER TRACE TO Marylogin; 

以下语句将显示用户的权限。 
	SELECT * FROM fn_my_permissions (NULL, 'SERVER');

2．为数据库角色授予权限

数据库用户Peteruse授予BACKUP DATABASE(备份数据库)权限： 

	USE 实例数据库;
	GO
	GRANT BACKUP DATABASE TO Peteruse;

8.5.2  管理对表和列的访问 
1．更改对表的访问

授予用户Peteruse对表Sales.Customer的SELECT，INSERT和UPDATE权限: 

	USE 实例数据库;
	GO
	GRANT SELECT,INSERT,UPDATE
	ON Adminschema.Student
	TO Peteruse;

使用REVOKE语句清除以前授予的权限： 

	USE 实例数据库;
	GO
	Adminschema.Student table
	REVOKE SELECT
	ON Adminschema.Student
	TO Peteruse;

使用DENY语句来拒绝这个用户的访问： 

	USE 实例数据库;
	GO
	DENY DELETE
	ON Adminschema.Student
	TO Peteruse;

2．提供对列的单独访问

为Peteruse用户在表Adminschema.Student的StudentDate 及ClientID列上授予了SELECT 和UPDATE权限。 

	USE 实例数据库;
	GO
	GRANT SELECT,UPDATE (
	StudentDate,
	ClientID)
	ON Adminschema.Student
	TO Peteruse;

取消对列的访问授权,需要使用DENY语句。 

	USE 实例数据库;
	GO
	REVOKE UPDATE (StudentDate)
	ON Adminschema.Student
	TO Peteruse;

8.5.2  管理对可编程对象的访问 
1．管理存储过程的安全性

为数据库用户Peteruse 授予存储过程dbo.uspGetBillOfMaterials的EXECUTE权限： 

	USE 实例数据库;
	GO
	GRANT EXECUTE On dbo.uspGetBillOfMaterials
	TO Peteruse;

2．管理用户定义函数的安全性

以下示例将授予数据库用户Peteruse对用户定义函数dbo.ufnGetContactInformation的SELECT权限：

	USE 实例数据库;
	GO
	GRANT SELECT ON dbo.ufnGetContactInformation
	TO Peteruse;

授予数据库用户Peteruse对函数dbo.ufnGetContactInformation的EXECUTE权限： 

	USE 实例数据库;
	GO
	GRANT EXECUTE ON dbo.ufnGetStock
	TO Peteruse;

3．管理程序集的安全性
(2)  执行一个程序集

授予数据库用户Peteruse对程序集的EXECUTE权限： 

	USE 实例数据库;
	GO
	GRANT EXECUTE ON <AssemblyName>
	TO Peteruse; 

5．管理执行上下文

（1） 运行EXECUTE AS

使用EXECUTE AS语句为数据库用户Peteruse更改执行上下文：

	USE 实例数据库;
	GO
	EXECUTE AS USER=' Peteruse ';
	TRUNCATE TABLE dbo.ErrorLog;

能成功执行的代码：

	REVERT;
	TRUNCATE TABLE dbo.ErrorLog;

(2)  管理上下文切换

切换上下文到数据库用户dbo的上下文以创建一个存储过程：

	CREATE PROCEDURE dbo.usp_TruncateErrorLog
    	WITH EXECUTE AS 'dbo' 
    	AS
    	TRUNCATE TABLE dbo.ErrorLog;
	GO
	GRANT EXECUTE ON dbo.usp_TruncateErrorLog TO Peteruse
	EXECUTE AS [USER=]'Peteruse'
	EXECUTE dbo.usp_TruncateErrorLog