每一个SQL Server安全对象(Securable)都有相应的权限(Permission)被授予到安全主体(Principal)上,在服务器级别上,使用Login和Server Role来管理权限;在数据库级别上,使用User和Database Role来管理权限。
一,权限命名约定
一下列表列出权限的一般约定:
- CONTROL:是最高的权限,在被授予者身上赋予近似所有者的权限,被授予者实际上拥有对安全对象(Securable)的所有权限。已被授予CONTROL权限的主体(Principal)也可以授予对安全对象的权限。 由于SQL Server安全模型是分层的,因此特定范围内的CONTROL隐式包括该范围下所有安全对象的CONTROL。 例如,对数据库的CONTROL意味着对数据库的所有权限,对数据库中所有程序集的所有权限,对数据库中所有Schema的所有权限以及对数据库内所有Schema中的对象的所有权限。
- IMPERSONATE:模拟指定的Login或User的权限
- EXECUTE:调用模块(函数,存储过程等)的权限
- DELETE、INSERT、SELECT和 UPDATE:对数据库对象执行修改和查询的权限
- VIEW DEFINITION:查看对象元数据的权限
- ALTER:对安全对象执行alter、create或drop命令的权限,
- CREATE :创建对象的权限
- REFERENCES:引用数据库对象的权限,包括外键、密钥、架构绑定的函数等
在服务器级别,CONTROL SERVER 和 sysadmin的权限有什么区别?概况来说,sysadmin的权限比CONTROL SERVER更高,CONTROL SERVER几乎具有sysadmin的全部权限,但是被授予CONTROL SERVER的principal的权限可以根据需要被限制。
总结这两个权限之间的主要区别是:
- sysadmin是一个角色,具有服务器的所有权限,而CONTROL SERVER只是一个权限。
- SQL Server不会对sysadmin角色成员做任何安全检查,其他任何权限(包括CONTROL SERVER)都需要做安全检查。
- 大多数的DBCC命令和系统存储过程都需要principal是sysadmin角色的成员。
- sysadmin角色的成员不能被deny限制权限,但是被授予CONTROL SERVER的principal的权限可以被deny限制。
数据级别的CONTROL DATABASE 和 db_owner的区别:
- db_owner是数据库级别的角色,具有数据的所有权限;CONTROL DATABASE 是数据库级别的权限
- db_owner角色的成员不能被deny限制权限,但是被授予CONTROL DATABASE的principal的权限可以被deny限制。
二,权限的层次结构和可传递性
权限具有父子层次结构,并且可以沿着层次结构由上向下传递,这意味着,如果被授予顶层的权限,那么同样被授予对顶层之下所有子对象的权限。
举个例子,如果User被授予数据库的select权限,那么User可以select数据库中的所有schema;如果User被授予schema的select权限,那么实际上,User能够对该schema下的所有子对象,包括table和view,执行select命令。由于权限是可以沿着层次结构传递,这就意味着,如果User被授予数据库的select权限,他可以查看该数据库的任意table和view。
三,权限列表
返回服务器的内置权限层次结构的描述,sys.fn_builtin_permissions只能在SQL Server和Azure SQL数据库上调用,并且它将返回所有权限,无论当前平台是否支持它们。 大多数权限适用于所有平台,但有些不适用于所有平台。
sys.fn_builtin_permissions ( [ DEFAULT | NULL ]
| empty_string | '<securable_class>' } )
返回特定安全对象(Securable)上实际被授予权限的安全主体(Principal):
fn_my_permissions ( securable , 'securable_class' )
四,服务器级别和数据库级别的权限
系统视图:sys.server_permissions,用于查看服务器级别的权限
- class:权限存在的分类,常见的分类是:SERVER、SERVER_PRINCIPAL、ENDPOINT
- grantee_principal_id:指定被授予权限的主体ID,
- grantor_principal_id 指定:授予者的主体ID。
- type:服务器级别的权限类型(server permission type);
- permission_name:服务器级别的权限的名称;
- state和state_desc:权限的状态,分别是DENY、REVOKE、GRANT、GRANT_WITH_GRANT_OPTION;
服务器基本的权限,通常用于访问服务器级别的对象,比如 Connect Database, Database等
系统视图:sys.database_permissions 用于查看数据库级别的权限:
- class:权限存在的分类,常见的分类是:DATABASE、OBJECT_OR_COLUMN、SCHEMA、DATABASE_PRINCIPAL
-
grantee_principal_id指定:被授予权限的主体ID,grantor_principal_id 指定:授予者的主体ID。
- type:数据库级别的权限类型(server permission type);
- permission_name:数据库级别的权限的名称;
- state:权限的状态,分别是DENY、REVOKE、GRANT、GRANT_WITH_GRANT_OPTION;
- major_id和minor_id:
安全对象(Securable):通过major_id 和 minor_id 指定安全对象
major_id:该字段共有3种类型的数值:
- 正整数,标识数据库对象,是object_id;
- 0,标识数据库级别的授权,class是DATABASE;
- 负整数,标识系统对象;
minor_id:该字段共有2种类型的数值:
- 正整数,标识的是数据库对象的column_id,该字段连接到sys.columns中的column_id;
- 0,标识的是整个数据库对象object;
五,CONNECT 权限
对于User来说,如果回收了User对数据库的CONNECT权限,那么也就意味着把User禁用了,user无法连接到数据库,也就无法获得任何权限。
REVOKE CONNECT FROM "domain\user"
要启用User,首先需要对User授予连接数据库的权限:
GRANT CONNECT TO "domain\user"
而对于Login来说,需要通过ALTER LOGIN命令来禁用和启用:
ALTER LOGIN loginname DISABLE;
ALTER LOGIN loginname ENABLE;
参考文档: