SQL 用户权限（表、函数、存储过程等）

杨叔叔

　　对数据的访问是通过SQL Server 2008的权限层次结构进行管理的。可以通过GRANT、DENY和REVOKE语句来管理这个权限层次结构。
　　●       GRANT：允许一个数据库用户或角色执行所授权限指定的操作。
　　●       DENY：拒绝一个数据库用户或角色的特定权限，并且阻止它们从其他角色中继承这个权限。
　　●       REVOKE：取消先前被授予或拒绝的权限。
5.5.1  管理对SQL Server实例和数据库的访问
1. 控制登录操作
　　SQL Server 2008提供了一个精细的权限结构，能够更出色地控制登录操作。可以通过GRANT、DENY和REVOKE语句来控制权限，通过sys.Server_permissions目录视图来获取有关服务权限的信息。
　　如下语句将为登录名Marylogin授予创建和执行SQL Server Profiler跟踪的权限：

　　GRANT>　　用户可以通过使用fn_my_permissions函数来了解自己的权限。如下语句将显示用户的权限：
　　SELECT * FROM fn_my_permissions (NULL, 'SERVER');
2. 为数据库角色授予权限
　　除了使用固定数据库角色之外，还可以为数据库角色授予小粒度的数据库权限。下面的语句将为数据库用户Peteruse授予BACKUP DATABASE(备份数据库)权限：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Grant permissions to the database user Peteruse
　　-- to backup the database 实例数据库.
　　GRANT BACKUP DATABASE TO Peteruse;
5.5.2  管理对表和列的访问
1. 更改对表的访问
　　用户对表所拥有的有效权限控制着用户对表的访问行为。可以通过管理表的权限来控制数据库用户对表的访问。如表5-4所示的就是可以管理的表的权限。可以对数据库用户或角色指定这些权限。
　　表5-4  表的权限
　　权    限
　　描    述
　　ALTER
　　可以更改表属性
　　CONTROL
　　提供所有权之类的权限
　　DELETE
　　可以从表中删除行
　　INSERT
　　可以向表中插入行
　　REFERENCES
　　可以通过外键引用其他表
　　SELECT
　　可以在表中选择行
　　TAKE OWNERSHIP
　　可以取得表的所有权
　　UPDATE
　　可以在表中更新行
　　VIEW DEFINITION
　　可以访问表的元数据
　　可以使用GRANT语句授权数据库用户或者角色对表的访问。如下语句将授予用户Peteruse对表Adminschema.Student的SELECT、INSERT和UPDATE权限：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Grant some permissions to Peteruse on the Adminschema.Student table.
　　GRANT SELECT,INSERT,UPDATE
　　ON Adminschema.Student
　　TO Peteruse;
　　限制对表的访问，有两种不同的情况。如果已经为用户授予了表的这种权限，则应该使用REVOKE语句清除之前授予的权限。示例如下：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Revoke SELECT permissions from Peteruse on the Adminschema.Student table
　　REVOKE SELECT
　　ON Adminschema.Student
　　TO Peteruse;
　　然而，如果用户隶属于某个具备此权限的角色，则用户可能依然具备通过REVOKE语句取消的权限。在这种情况下，需要使用DENY语句来拒绝该用户的访问。示例如下：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Deny DELETE permission to Peteruse on the Adminschema.Student table,
　　-- regardless of what permissions this user might
　　-- inherit from roles.
　　DENY DELETE
　　ON Adminschema.Student
　　TO Peteruse;
2. 提供对列的单独访问
　　SQL Server 2008提供了授予或拒绝访问单独列的权限，这个特性提供了灵活的拒绝访问机制，例如，保护某些列上的机密数据。如表5-5所示的是可以管理的列权限。
　　表5-5  列权限
　　权    限
　　描    述
　　SELECT
　　可以选择列
　　UPDATE
　　可以更新列
　　REFERENCE
　　可以通过外键引用列
　　授权对列的访问，也使用GRANT语句。以下示例为Peteruse用户授予了在表Adminschema.Student的StudentDate和ClientID列上SELECT和UPDATE的权限。
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Grant SELECT and UPDATE permissions to Peteruse
　　-- on some specific columns of the Adminschema.Student table
　　GRANT SELECT,UPDATE (
　　StudentDate,
　　ClientID)
　　ON Adminschema.Student
　　TO Peteruse;
　　取消对列的访问授权，与取消对表的访问授权类似，可以使用REVOKE语句来实现，但如果要阻止一个用户获得某种权限，则需要使用DENY语句。
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Revoke previosly granted or denied permissions
　　-- from Peteruse on the StudentDate column.
　　REVOKE UPDATE (StudentDate)
　　ON Adminschema.Student
　　TO Peteruse;
5.5.3  管理对可编程对象的访问
　　可编程对象，如存储过程及用户定义的函数，具有自己的安全上下文。数据库用户需要获得授权才能执行存储过程、函数和程序集。一旦数据库引擎检查了执行可编程对象的权限，就会在可编程对象内部对其所执行的操作进行权限检查。当数据库对象按顺序相互访问时，该访问顺序将形成一个所有权链。
1. 管理存储过程的安全性
　　在各种数据库对象中，存储过程是数据库开发人员最常使用的数据库对象。与其他数据库对象一样，存储过程也是需要保护的对象。用户需要具备执行操作的权限，就像创建一个存储过程一样，用户需要具备相应的权限来执行一个存储过程。如表5-6所示是可以为存储过程授予的权限。
　　表5-6  存储过程权限
　　权    限
　　描    述
　　ALTER
　　可以更改存储过程属性
　　CONTROL
　　可以提供所有权之类的权限
　　EXECUTE
　　可以执行存储过程
　　TAKE OWNERSHIP
　　可以取得存储过程的所有权
　　VIEW DEFINITION
　　可以查看存储过程的元数据
　　在执行一个存储过程时，SQL Server会检查当前数据库用户是否具有该存储过程的EXECUTE权限。下面的语句将为数据库用户Peteruse 授予存储过程dbo.uspGetBillOfMaterials的EXECUTE权限：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Grant EXECUTE permission to Peteruse on a stored procedure.
　　GRANT EXECUTE On dbo.uspGetBillOfMaterials
　　TO Peteruse;
　　同样地，如果要阻止一个用户执行某个存储过程，可以取消或者拒绝该用户的EXECUTE权限。
2. 管理用户定义函数的安全性
　　用户定义函数和存储过程一样，也是可编程对象。主要存在两种类型的用户定义函数：只返回单一值的标量函数和返回一个表数据类型值的表值函数。根据用户定义函数类型的不同，可以对函数授予EXECUTE或SELECT权限，如表5-7所示。
　　表5-7  用户定义函数权限
　　权    限
　　描    述
　　ALTER
　　可以更改函数属性
　　CONTROL
　　可以提供所有权之类的权限
　　TAKE OWNERSHIP
　　可以取得函数的所有权
　　VIEW DEFINITION
　　可以查看函数的元数据
　　SELECT
　　可以选择表值函数所返回的数据(只对表值函数有效)
　　EXECUTE
　　可以执行用户定义函数(只对标量函数有效)
　　对于执行表值函数，SQL Server将检查用户是否拥有此函数所返回的表的SELECT权限。可以采用与为表授予SELECT权限相同的方式来为表值函数授予SELECT权限。如下语句将授予数据库用户Peteruse对用户定义函数dbo.ufnGetContactInformation的SELECT权限：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Grant permission to Peteruse to execute a user defined function.
　　GRANT SELECT ON dbo.ufnGetContactInformation
　　TO Peteruse;
　　注意：
　　表值函数还有另外一种类型，叫做内联函数。内联函数在功能上等同于视图，但是它支持参数。从安全角度来讲，这种类型的函数等同于视图。
　　对于执行标量函数，数据库用户需要在函数上具备EXECUTE权限。可以采用与为存储过程授予EXECUTE权限相同的方式来为标量函数授予EXECUTE权限。如下语句将授予数据库用户Peteruse对用户定义函数dbo.ufnGetContactInformation的EXECUTE权限：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Grant Peteruse permission to execute a user defined function.
　　GRANT EXECUTE ON dbo.ufnGetStock
　　TO Peteruse;
3. 管理程序集的安全性
　　SQL Server 2008提供了在数据库引擎内部包含.NET程序集(引用.dll文件的对象)，并在存储过程及函数中调用这些程序集的能力。可以为程序集分配与存储过程一样的权限，这些权限如表5-6所示。
　　(1) 权限集
　　创建一个程序集时，需要指定一个权限集。权限集指定了程序集在SQL Server中所授予的一个代码访问权限的集合。权限集具有如下3种不同的类型。
　　●       SAFE类型：程序集执行的代码不能访问外部系统资源。SAFE类型是最受限制的权限集合，并且是默认的类型。
　　●       EXTERNAL_ACCESS类型：程序集可以访问外部系统资源。
　　●       UNSAFE类型：程序集可以执行非托管代码。
　　对于不需要访问外部资源的程序集，推荐使用SAFE类型的权限集。
　　(2) 执行一个程序集
　　当一个应用程序尝试访问程序集中的对象时，数据库引擎会检查当前用户是否具有该程序集的EXECUTE权限。如下语句将授予数据库用户Peteruse对程序集的EXECUTE权限：
　　-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Grant Peteruse permission to execute an assembly.
　　GRANT EXECUTE ON
　　TO Peteruse;
　　通过为一个程序集授予EXECUTE权限，可以为数据库用户授予对程序集中所有对象的EXECUTE权限。
4. 管理所有权链
　　所有权链是数据对象互相访问的顺序。例如，在一个存储过程中，向一个表中插入一行数据，存储过程称为调用对象，表称为被调用对象。SQL Server遍历这个链中的链接时，与单独访问数据库对象时的方式不同，数据库引擎会以另一种方式评估对对象的访问权限。
　　在一个链中访问对象时，SQL Server首先会比较对象的所有者与调用对象的所有者。如果两个对象的所有者相同，则不评估被引用对象的权限。这个特性对管理对象权限非常有用。例如，假设数据库用户Peteruse创建了一个名称为Person.SupplierContacts的表，并在一个名称为Person.InsertSupplierContacts的存储过程中向PersonSupplierContacts表中插入了行。由于这两个数据对象具有同样的所有者Peteruse，因此，只需授予其他用户对存储过程Person.InsertSupplier- Contacts的EXECUTE权限，以允许其他用户在访问表PersonSupplierContacts时，依然具有EXECUTE权限。
　　注意：
　　所有权链提供了一种强大的封装算法。一个数据可以被设计成只通过充分文档化的公共接口(例如存储过程和用户定义函数)来对外提供数据访问，这些存储过程和用户定义函数隐藏了数据设计实现的复杂性。数据库开发人员可以充分利用所有权链，在拒绝所有用户对数据库中所有表的访问的同时，仍然可以允许其访问数据。
5. 管理执行上下文
　　执行上下文由连接到相应会话的用户、登录名或者由执行(调用)相应模块的用户或登录名确定。在SQL Server 2008进行对象权限检查时，登录名和用户令牌为其提供了所需的信息。在SQL Server 2008中，可以使用EXECUTE AS语句来更改执行上下文。这一操作称为切换执行上下文。
　　(1) 运行EXECUTE AS
　　EXECUTE AS语句允许显式地定义当前连接的执行上下文。可以用EXECUTE AS更改当前连接的登录名或者数据库用户。上下文的变化在另一个上下文变更发生前、连接关闭前或者一个REVERT语句执行前始终是有效的。如下语句使用了EXECUTE AS语句为数据库用户Peteruse更改执行上下文：
　　z-- Change the connection context to the database 实例数据库.
　　USE 实例数据库;
　　GO
　　-- Change the execution context to the user Peteruse.
　　EXECUTE AS USER=' Peteruse ';
　　-- The following statement will be executed under Peteruse 's credentials.
　　TRUNCATE TABLE dbo.ErrorLog;
　　由于用户Peteruse没有truncate表的权限，因此，上述代码将会产生一个错误。而去下truncate表的语句则能成功执行：
　　-- Change the execution context back to the original state
　　REVERT;
　　-- Now the following statement will be executed under
　　-- the original execution context.
　　TRUNCATE TABLE dbo.ErrorLog;
　　(2) 管理上下文切换
　　除了控制批处理(批处理是包含一个或多个Transact-SQL语句的组，这个组从应用程序一次性地发送到SQL Server执行，就像前面的TRUNCATE TABLE示例一样)的执行上下文，还可以控制存储过程和用户定义函数的执行上下文。在这些模块中切换上下文时，可以控制在这些存储过程或者函数中使用哪个用户帐户来访问它所引用的数据库对象。为此，只需要对EXECUTE AS语句进行如下改动即可。
　　●       CALLER：存储过程或者用户定义函数内的语句都在模块调用者的上下文中执行。
　　●       SELF：所有语句在创建或者更改存储过程或者用户定义函数的用户的上下文中执行。
　　●       OWNER：所有语句在存储过程或者用户定义函数的当前所有者的上下文中执行。
　　●       ：所有语句在指定数据库用户或者登录名的上下文中执行。
　　以下示例将切换上下文到数据库用户dbo的上下文中以创建一个存储过程。然后，为数据库用户Peteruse授予这个新建存储过程的EXECUTE权限，并更改上下文以测试存储过程的执行：
　　-- Create a stored procedure to execute statements
　　-- as dbo.
　　CREATE PROCEDURE dbo.usp_TruncateErrorLog
　　WITH EXECUTE AS 'dbo'
　　AS
　　TRUNCATE TABLE dbo.ErrorLog;
　　GO
　　-- Grant permissions to execute this procedure to Peteruse.
　　GRANT EXECUTE ON dbo.usp_TruncateErrorLog TO Peteruse
　　-- Change the execution context of this batch to Peteruse.
　　EXECUTE AS [USER=]'Peteruse'
　　-- Execute the stored procedure.
　　EXECUTE dbo.usp_TruncateErrorLog
　　由此可见，在不能通过授权来使用户执行某些操作时，尤其适合使用上下文切换。