SQL⽤户权限（表、函数、存储过程等）
第5章 安全与权限
数 据 库 管 理 权 限
对数据的访问是通过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 ALTER TRACE TO Marylogin;
⽤户可以通过使⽤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
用switch case语句解决税率问题>快速排序方法在什么情况下最不利于发挥其长处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 实例数据库;
GOcstring是什么数据类型
-- Grant EXECUTE permission to Peteruse on a stored procedure.
GRANT EXECUTE On dbo.uspGetBillOfMaterials
TO Peteruse;
同样地，如果要阻⽌⼀个⽤户执⾏某个存储过程，可以取消或者拒绝该⽤户的EXECUTE权限。
同样地，如果要阻⽌⼀个⽤户执⾏某个存储过程，可以取消或者拒绝该⽤户的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权限：
mysql语句的执行顺序-- 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. 管理程序集的安全性
html编辑器apk
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 <AssemblyName>
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：所有语句在存储过程或者⽤户定义函数的当前所有者的上下⽂中执⾏。
●      <User>：所有语句在指定数据库⽤户或者登录名的上下⽂中执⾏。
以下⽰例将切换上下⽂到数据库⽤户dbo的上下⽂中以创建⼀个存储过程。然后，为数据库⽤户Peteruse授予这个新建存储过程的EXECUTE权限，并更改上下⽂以测试存储过程的执⾏：
-- Create a stored procedure to execute statements
-- as dbo.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。