VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > sql数据库 >

  • sql语句大全之企业信息平台数据库权限管理

企业信息平台数据库权限管理

SQL Server包含大量的安全特性，可以帮助管理员实现更高程度的安全防御。本节主要内容如下：
l   权限管理基础
l   创建登录帐号
l   角色管理
l   权限管理

3.1  权限管理基础

每个SQL Server的安全都同授予宿主的权限相关联。表3显示了权限应用于特定的安全对象。
表 3  权限应用于的安全对象
权限 应用于
SELECT 同义词
表 +列
表-值函数+列
视图 + 列
UPDATE 同义词
表+ 列
视图+ 列
REFERENCES 标量或者聚集函数
服务代理队列
表+ 列
表-值函数 + 列
视图+ 列
INSERT 同义词
表+ 列
视图+ 列
DELETE 同义词
表+ 列
视图+ 列
EXECUTE 过程
标量或者聚集函数
同义词
RECEIVE 服务代理队列
VIEW DEFINITION 过程
服务代理队列
标量或者聚集函数
同义词
表
表-值函数
视图
ALTER 过程
标量或者聚集函数
服务代理队列
表
表-值函数
视图
TAKE OWNERSHIP 过程
标量或者聚集函数
同义词
表
表-值函数
视图
CONTROL 过程
标量或者聚集函数
服务代理队列
同义词
表
表-值函数
视图
 
SQL Server提供了权限信息视图，如下所示，用户可以查询这些视图，获取权限信息。
l  sys.database_principals，显示数据库中每个安全宿主对象（用户、组、角色、应用角色等等）的信息。
l  sys.server_role_members，显示每个服务器角色的每个角色。
l  sys.certificates，包含数据库中创建的每个证书。
l  sys.asymmetric_keys ，包含非对称公钥的信息。
l  sys.crypt_properties ，包含同安全对象相关的密码信息。
l  sys.credentials，包含使用CREATE CREDENTIAL创建的每个证书。
l  sys.symmetric_keys ，包含对称公钥的信息。
l  sys.database_role_members，包含每个数据库角色的成员信息。
l  sys.sql_logins ，包含SQL登录的每个安全宿主信息。
l  sys.key_encryptions，包含使用CREATE SYMMETRIC KEY语句的ENCRYPTION BY子句定义的每个对称公钥的信息。
l  sys.database_permissions ，包含每个数据库级权限或者列异常权限的信息。
l  sys.server_principals 包含服务器级的每个安全宿主的信息。
l  sys.server_permissions，包含每个服务器级权限的信息。
同时，SQL Server提供了固定数据库角色和服务器角色，从而引入更多的权限控制策略。固定服务器角色如表4所示。
表 4  固定服务器角色
固定数据库角色 数据库级权限
db_accessadmin 授予的权限：ALTER ANY USER
db_accessadmin 使用CRANT选项授予的权限：CONNECT
db_accessadmin 授予的权限：CREATE SCHEMA
db_backupoperator 授予的权限：BACKUP DATABASE
db_backupoperator 授予的权限：BACKUP LOG
db_backupoperator 授予的权限：CHECKPOINT
db_datareader 授予的权限：SELECT
db_datawriter 授予的权限：DELETE
db_datawriter 授予的权限：INSERT
db_datawriter 授予的权限：UPDATE
db_ddladmin 授予的权限：ALTER ANY ASSEMBLY
db_ddladmin 授予的权限：ALTER ANY CERTIFICATE
db_ddladmin 授予的权限：ALTER ANY CONTRACT
db_ddladmin 授予的权限：ALTER ANY EVENT NOTIFICATION
db_ddladmin 授予的权限：ALTER ANY DATASPACE
db_ddladmin 授予的权限：ALTER ANY FULLTEXT CATALOG
db_ddladmin 授予的权限：ALTER ANY MESSAGE TYPE
db_ddladmin 授予的权限：ALTER ANY REMOTE SERVICE BINDING
db_ddladmin 授予的权限：ALTER ANY ROUTE
db_ddladmin 授予的权限：ALTER ANY SCHEMA
db_ddladmin 授予的权限：ALTER ANY SERVICE
db_ddladmin 授予的权限：ALTER ANY SYMMETRIC KEY
db_ddladmin 授予的权限：ALTER ANY TRIGGER
db_ddladmin 授予的权限：ALTER ANY XML SCHEMA COLLECTION
db_ddladmin 授予的权限：CHECKPOINT
db_ddladmin 授予的权限：CREATE AGGREGATE
db_ddladmin 授予的权限：CREATE ASSEMBLY
db_ddladmin 授予的权限：CREATE CONTRACT
db_ddladmin 授予的权限：CREATE DEFAULT
db_ddladmin 授予的权限：CREATE FUNCTION
db_ddladmin 授予的权限：CREATE MESSAGE TYPE
db_ddladmin 授予的权限：CREATE PROCEDURE
db_ddladmin 授予的权限：CREATE QUEUE
db_ddladmin 授予的权限：CREATE REMOTE SERVICE BINDING
db_ddladmin 授予的权限：CREATE ROUTE
db_ddladmin 授予的权限：CREATE RULE
db_ddladmin 授予的权限：CREATE SCHEMA
db_ddladmin 授予的权限：CREATE SERVICE
db_ddladmin 授予的权限：CREATE SYMMETRIC KEY
db_ddladmin 授予的权限：CREATE SYNONYM
db_ddladmin 授予的权限：CREATE TABLE
db_ddladmin 授予的权限：CREATE TYPE
db_ddladmin 授予的权限：CREATE VIEW
db_ddladmin 授予的权限：CREATE XML SCHEMA COLLECTION
db_ddladmin 授予的权限：REFERENCES
db_denydatareader 禁用的权限: SELECT
db_denydatawriter 禁用的权限: DELETE
db_denydatawriter 禁用的权限: INSERT
db_denydatawriter 禁用的权限: UPDATE
db_owner 使用CRANT选项授予的权限：CONTROL
db_securityadmin 授予的权限：ALTER ANY APPLICATION ROLE
db_securityadmin 授予的权限：ALTER ANY ROLE
db_securityadmin 授予的权限：CREATE SCHEMA
db_securityadmin 授予的权限：VIEW DEFINITION
 
固定服务器角色如表5所示：
表 5  固定数据库角色
固定服务器角色 对应的服务器级权限
bulkadmin 授予的权限： ADMINISTER BULK OPERATIONS
dbcreator 授予的权限： CREATE DATABASE
diskadmin 授予的权限： ALTER RESOURCES
processadmin 授予的权限： ALTER SERVER STATE
processadmin 授予的权限： ALTER ANY CONNECTION
securityadmin 授予的权限： ALTER ANY LOGIN
serveradmin 授予的权限： ALTER SETTINGS
serveradmin 授予的权限： SHUTDOWN
serveradmin 授予的权限： CREATE ENDPOINT
serveradmin 授予的权限： ALTER SERVER STATE
serveradmin 授予的权限： ALTER ANY ENDPOINT
serveradmin 授予的权限： ALTER RESOURCES
setupadmin 授予的权限： ALTER ANY LINKED SERVER
sysadmin 带GRANT 选项授予的权限：CONTROL SERVER
 
在SQL Server权限体系结构中，授予特定的权限可能会隐式转换其他权限。高级权限将覆盖底层权限。

3.2  创建登录帐号

管理SQL Server登录主要包括以下内容：
l  创建登录
l  修改登录
l  删除登录
（1）创建登录
SQL Server提供的用于创建登录的Transact-SQL语句如下：
CREATE LOGIN login_name { WITH < option_list1 > | FROM < sources > }
 
< sources >::=
    WINDOWS [ WITH windows_options [,...] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name
 
< option_list1 >::=
    PASSWORD = ' password ' [ HASHED ] [ MUST_CHANGE ]
    [ , option_list2 [ ,... ] ]
 
< option_list2 >::= 
    SID = sid
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    [ CREDENTIAL = credential_name ]
 
< windows_options >::=     
    DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
其中：
l  login_names，定义登录名；
l  WINDOWS，定义映射为Windows的登录名；
l  CERTIFICATE certname，定义同登录相关的证书名；
l  ASYMMETRIC KEY asym_key_name，定义同登录相关的非对称金钥的名称；
l  PASSWORD = 'password'，定义登录的口令；
l  HASHED，只用于SQL Server登录；当PASSWORD参数被哈西处理之后，定义输入的口令。如果没有设置该选项，采用字符串格式存储口令。
l  MUST_CHANGE，只用于SQL Server登录；如果定义了该选项，SQL Server将提示用户在第一次登录时修改口令；
l  CREDENTIAL = credential_name，定义映射新的SQL Server登录的身份；
l  SID = sid，只用于SQL Server登录。定义新SQL登录的GUID；
l  DEFAULT_DATABASE = database，定义该登录支持的默认数据库；
l  DEFAULT_LANGUAGE = language，定义赋予登录的默认语言；
l  CHECK_EXPIRATION = { ON | OFF }，只用于SQL Server登录；定义使用对口令采用过期策略。默认为ON；
l  CHECK_POLICY = { ON | OFF }，只用于SQL Server登录；定义SQL Server所运行的Windows口令策略是否用于该登录。
（2）修改登录
SQL Server提供的用于修改登录的Transact-SQL语句如下：
ALTER LOGIN login_name
    {
    < status_option >
    | WITH set_option [ ,... ]
    }
 
< status_option >::=
    ENABLE | DISABLE
 
< set_option >::=           
    PASSWORD = ' password '
    [
      OLD_PASSWORD = ' oldpassword '
      | secadmin_pwd_option [ secadmin_pwd_option ]
    ]
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | NAME = login_name
    | CHECK_POLICY = { ON | OFF }
    | CHECK_EXPIRATION = { ON | OFF }
    | CREDENTIAL = credential_name
    | NO_CREDENTIAL
 
< secadmin_pwd_opt >::=
    MUST_CHANGE | UNLOCK
其中：
l  login_name，需要修改的SQL Server登录名；
l  ENABLE | DISABLE，启用或者禁用该登录；
l  PASSWORD = ' password '，定义新口令；
l  OLD_PASSWORD = ' old password '，定义登录的当前口令；
l  MUST_CHANGE，，只用于SQL Server登录；如果定义了该选项，SQL Server将提示用户在第一次登录时修改口令；
l  DEFAULT_DATABASE = database，定义该登录支持的默认数据库；
l  DEFAULT_LANGUAGE = language，定义赋予登录的默认语言；
l  NAME = login_name，新的登录名；
l  CHECK_EXPIRATION = { ON | OFF }，只用于SQL Server登录；定义使用对口令采用过期策略。默认为ON；
l  CHECK_POLICY = { ON | OFF }，只用于SQL Server登录；定义SQL Server所运行的Windows口令策略是否用于该登录。
l  CREDENTIAL = credential_name，映射到该SQL Server登录的身份证名，它必须在服务器中存在；
l  NO_CREDENTIAL，删除登录到服务器身份证的映射；
l  UNLOCK，定义加锁登录应当被解锁。
（3）删除登录
删除登录的Transact-SQL语句较为简单，具体如下：
DROP LOGIN login_name
其中：
l  login_name，需要删除的登录名。
下面的代码显示如何创建一个服务器登录“yjs”，并创建对应的数据库用户“yjs”。
CREATE LOGIN yjs WITH PASSWORD = '1111112221';
USE EAMS;
CREATE USER yjs;

3.3  角色管理

角色是一个强大的工具，得以将用户集中到一个单元中，然后对该单元应用权限。对一个角色授予、拒绝或废除的权限也适用于该角色的任何成员。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限。当工作人员开始工作时，只须将他们添加为该角色成员，当他们离开工作时，将他们从该角色中删除。而不必在每个人接受或离开工作时，反复授予、拒绝和废除其权限。权限在用户成为角色成员时自动生效。
前面我们介绍了SQL Server提供的固定服务器角色和固定数据库角色，用户也可以使用Create ROLE Transact-SQL语句创建角色，其语法如下：
CREATE ROLE role_name [ AUTHORIZATION owner_name ]
其中：
l  role_name，需要创建的角色名；
l  AUTHORIZATION owner_name，拥有新角色的数据库用户名或者角色名。
当创建了角色之后，可以使用存储过程sp_addrolemember将安全帐户作为当前数据库中现有 Microsoft SQL Server数据库角色的成员进行添加，具体语法如下：
sp_addrolemember [ @rolename = ] ' role ' ,
    [ @membername = ] ' security_account '
其中：
l  [ @rolename = ] ' role '，当前数据库中 SQL Server 角色的名称。role 的数据类型为 sysname，没有默认值。
l  [@membername =] 'security_account'，添加到角色的安全帐户。security_account 的数据类型为 sysname，没有默认值。security_account 可以是所有有效的 SQL Server 用户、SQL Server 角色或是所有已授权访问当前数据库的 Microsoft Windows 用户或组。当添加 Windows用户或组时，请指定在数据库中用来识别该 Windows用户或组的名称。
当需要从角色中删除成员时，可以使用sp_droprolemember过程，其语法如下：
sp_droprolemember [ @rolename = ] 'role' ,
    [ @membername = ] 'security_account'
其中：
l  'role'，某个角色的名称，将要从该角色删除成员。role 的数据类型为 sysname，没有默认值。role 必须已经存在于当前的数据库中。
l  'security_account'，正在从角色中删除的安全帐户的名称。security_account 的数据类型为 sysname，没有默认值。
当需要修改角色名时，可以执行ALTER ROLE语句，其语法如下：
ALTER ROLE role_name WITH NAME = new_name
其中：
l  role_name，需要修改的角色名；
l  WITH NAME = new_name，定义角色新的名称。
当需要删除某个角色时，可以执行DROP ROLE语句，其语法如下：
DROP ROLE role_name
其中：
l  role_name，需要删除的角色名。
下面的代码说明如何创建一个数据库角色：
USE EAMS;
CREATE ROLE buyers AUTHORIZATION BenMiller;

3.4  权限管理

管理SQL Server权限主要包括：
l  授予权限
l  拒绝权限
l  废除权限
（1）授予权限
授予权限主要是授予允许用户帐户执行下列活动的语句权限和对象权限：
l  在当前数据库中执行活动或处理数据。
l  限制它们执行某些活动或获取某些信息，这些活动或信息都不是其预期功能的一部分。
SQL Server提供的授予权限的Transact-SQL语句是GRANT，其语法如下：
GRANT < permission > [ ,...n ] [ ON < scope > ]
    TO < principal > [ ,...n ] [ WITH GRANT OPTION ]
    [ AS
       {
       Windows_group | SQL_Server_login | database_user
       | database_role | application_role
       }
    ]
 
< permission >::=  ALL [ PRIVILEGES ] | permission_name
    [ ( column [ ,...n ] ) ]
 
< scope >::= [ securable_class :: ] securable_name
 
< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
    | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG
    | LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE
    | ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE
    | USER | XML SCHEMA COLLECTION
 
< principal >::= Windows_login | SQL_Server_login
    | SQL_Server_login_mapped_to_certificate
    | SQL_Server_login_mapped_to_asymmetric_key
    | Database_user | Database_role | Application_role
    | Database_user_mapped_to_certificate
    | Database_user_mapped_to_asymmetric_key
其中：
l  GRANT OPTION，表示给予了 security_account 将指定的对象权限授予其它安全帐户的能力。WITH GRANT OPTION 子句仅对对象权限有效。
l  TO Windows_login，定义将权限授予的Windows登录。
l  TO SQL_Server_login，定义将权限授予的SQL Server登录。
l  TO SQL_Server_login_mapped_to_certificate，定义将权限授予的证书映射SQL Server登录。
l  TO SQL_Server_login_mapped_to_asymmetric_key，定义将权限授予的非对称金钥映射的SQL Server登录。
l  TO Database_user，定义将权限授予的数据库用户。
l  TO Database_role，定义将权限授予的数据库角色。
l  TO Database_user_mapped_to_certificate，定义将权限授予的证书映射数据库用户。
l  TO Database_user_mapped_to_asymmetric_key，定义将权限授予的非对称金钥映射数据库用户。
l  TO Application_role，定义将权限授予的应用角色。
l  AS Windows_group，定义执行查询的安全对象驱动权限授予的Windows组。
l  AS SQL_Server_login，定义执行查询的安全对象驱动权限授予的SQL Server登录。
l  AS Database_role，定义使用固定数据库角色授予权限。
l  AS Application_role，定义使用应用角色授予权限。
l  TO Windows_group，定义将权限授予Windows组。
l  TO SQL_Server_login，定义将权限授予SQL Server登录。
l  TO database_user，定义将权限授予数据库用户。
l  TO database_role，定义将权限授予数据库角色。
l  TO Application_role，定义将权限授予应用角色。
l  AS Windows_group，定义作为Windows组授予权限。
l  AS SQL_Server_login，定义作为SQL Server登录授予权限。
l  AS Database_role，定义采用数据库角色授权权限。
l  AS Application_role，定义作为应用角色授予权限。
l  ALL，表示授予所有可用的权限。
l  PRIVILEGES，是可以包含在符合 SQL-92 标准的语句中的可选关键字。
l  Securable_name，表示安全对象的名称。
l  Permission_name，是当前授予的权限。
l  Column，是当前数据库中授予权限的列名。
l  securable_class，定义授予权限的安全对象类。
l  APPLICATION ROLE，表示安全对象是一个应用角色。
l  ASSEMBLY，assembly表示安全对象是一个集合。
l  ASYMMETRIC KEY，表示安全对象是一个非对称金钥。
l  CERTIFICATE，表示安全对象是一个证书。
l  CONTRACT，表示安全对象是一个服务代理合同。
l  DATABASE，表示安全对象是数据库。
l  ENDPOINT，表示安全对象是一个断点。
l  FULLTEXT CATALOG，表示安全对象是全文目录。
l  LOGIN，表示安全对象是SQL Server登录。
l  MESSAGE TYPE，表示安全对象是服务代理消息类型。
l  OBJECT，表示安全对象是模式层对象。
l  REMOTE BINDING，表示安全对象是服务代理远程绑定。
l  ROLE，表示安全对象是服务代理角色。
l  ROUTE，表示安全对象是服务代理路由。
l  SCHEMA，表示安全对象是模式。
l  SERVER，表示安全对象是服务器。
l  SERVICE，表示安全对象是服务代理服务。
l  SYMMETRIC KEY，表示安全对象是对称金钥。
l  TYPE，表示安全对象是类型。
l  USER，表示安全对象是用户。
l  XML SCHEMA COLLECTION，表示安全对象是XML模式集合。
（2）拒绝权限
拒绝用户帐户上的权限，主要用于：
l  删除以前授予用户、组或角色的权限。
l  停用从其它角色继承的权限。
l  确保用户、组或角色将来不继承更高级别的组或角色的权限。
SQL Server提供的拒绝权限的Transact-SQL语句语法如下：
DENY < permission > [ ,...n ] [ ON < scope > ]
    TO < principal > [ ,...n ] [ CASCADE ]
    [ AS
       {
       Windows_group | SQL_Server_login
       | database_role | application_role
       }
    ]
 
< permission >::=  ALL [ PRIVILEGES ] | permission_name
    [ ( column [ ,...n ] ) ]
 
< scope >::= [ securable_class :: ] securable_name
 
< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
    | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG
    | LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE
    | ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE
    | USER | XML SCHEMA COLLECTION
 
< principal >::= Windows_login | SQL_Server_login
    | SQL_Server_login_mapped_to_certificate
    | SQL_Server_login_mapped_to_asymmetric_key
    | Database_user | Database_role | Application_role
    | Database_user_mapped_to_certificate
    | Database_user_mapped_to_asymmetric_key
具体参数说明参考GRANT语法的对应参数。
（3）废除权限
废除权限用于废除以前授予或拒绝的权限。废除类似于拒绝，因为二者都是在同一级别上删除已授予的权限。但是，废除权限是删除已授予的权限，并不妨碍用户、组或角色从更高级别继承已授予的权限。因此，如果废除用户查看表的权限，不一定能防止用户查看该表，因为已将查看该表的权限授予了用户所属的角色。
SQL Server提供用于废除权限的Transact-SQL语句REVOKE，其语法如下：
REVOKE [ GRANT OPTION FOR ] < permission > [ ,...n ] [ ON < scope > ]
    { TO | FROM } < principal > [ ,...n ] [ CASCADE ]
    [ AS
       {
       Windows_group | SQL_Server_login
       | database_role | application_role
       }
    ]
 
< permission >::=  ALL [ PRIVILEGES ] | permission_name
    [ ( column [ ,...n ] ) ]
 
< scope >::= [ < securable_class > :: ] securable_name
 
< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
    | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG
    | LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE
    | ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE
    | USER | XML SCHEMA COLLECTION
 
< principal >::= Windows_login | SQL_Server_login
    | SQL_Server_login_mapped_to_certificate
    | SQL_Server_login_mapped_to_asymmetric_key
    | Database_user | Database_role | Application_role
    | Database_user_mapped_to_certificate
    | Database_user_mapped_to_asymmetric_key
具体参数说明请参考GRANT语法的对应参数。
下面的示例给用户 yjs授予多个语句权限。
GRANT CREATE DATABASE, CREATE TABLE
TO yjs


相关教程