你的位置:首页 > 数据库

[数据库]12. 查询数据库账号的所有权限


在SQL Server数据库中,登录账号分类如下:

(1) SQL账号,需要单独设置密码,比如:sa;

(2) Windows账号,登录windows的账号,比如: administrator,不需要另设密码;

(3) Windows Group 账号, 为本地用户组或域用户组,将组添加到登录,组成员不需要单独创建登录;

查看Windows账号,是否属于某一个/多个用户组:

exec xp_logininfo 'windows_acount','ALL' --域用户格式为:domain_name\account_name

以下脚本,均假设最终登录账号为:test_login,所有数据库对应的user为test_user

 

. 有没有权限

1. 检查有没有登录权限

--是否存在有效的登录账号:是否被禁用,sql login还有:密码是否过期,是否被锁定select is_disabled, loginproperty(name,'Isexpired') is_expired, loginproperty(name,'Islocked') is_locked, * from sys.server_principalswhere name = 'test_login'

  

2. 检查有没有访问某数据库的权限

USE DBAGO--检查是否有数据库的CONNECT权限即可select b.* from sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idwhere SUSER_SNAME(a.sid) = 'test_login'and b.permission_name = 'CONNECT'--老的系统表sysusers也可以检查SELECT name, hasdbaccess,* FROM sysusers aWHERE SUSER_SNAME(a.sid) = 'test_login'

如果有很多个数据库,写个游标1个个去检查即可。

 

3. 检查有没有某个对象的权限

检查有没有某个对象的权限,一般是去尝试运行下脚本比较直观,如果去查各种权限表,角色错综复杂时,很难分辨;

SQL Server 2008之后引入了HAS_PERMS_BY_NAME这个函数,它可以检查当前账号的各种权限,检查其他用户需要用EXECUTE AS来切换:

USE DBAGOEXECUTE AS user = 'test_user'GO--对象权限SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT');SELECT HAS_PERMS_BY_NAME('sp_send_dbmail', 'OBJECT', 'EXEC');--架构权限SELECT HAS_PERMS_BY_NAME('test_schema', 'SCHEMA', 'SELECT');REVERT;GO

对于是否有登录、访问数据库的权限,用这个函数也可以判断:

USE masterGOEXECUTE AS login = 'test_login'GO--登录权限,本机前2个参数为空即可SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'CONNECT SQL');REVERT;GOUSE DBAGOEXECUTE AS user = 'test_user'GO--数据库权限SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CONNECT');REVERT;

 

. 有哪些权限

权限可以直接分配给账号,也可以分配给账号所属的role,所以要把账号自身权限、所属role权限合并才是最终的账号权限。

Windows账号权限还可以通过用户组分配,所以还要检查这个Windows账号有没有属于某个用户组,如果有还需要加上这个用户组的权限;

下面的脚本,仅检查单个用户/用户组权限。

1. 实例级的权限

use masterGOdeclare @svr_principal_name varchar(1024)set @svr_principal_name = 'test_login'declare @svr_principal_id intselect @svr_principal_id = principal_id from sys.server_principals pwhere p.name = @svr_principal_nameif OBJECT_ID('tempdb..#tmp_svr_role','U') is not null  drop table #tmp_svr_role;create table #tmp_svr_role(member_principal_id   int,member_principal_name  varchar(512),role_principal_id    int, role_principal_name   varchar(512))--获取登录账号的所有server role, 从sql server 2012开始,server role可以自定义,成员仅可为fixed server role;with tmpas(select * from sys.server_role_members where member_principal_id = @svr_principal_idunion allselect rm.* from sys.server_role_members rminner join tmp on rm.member_principal_id = tmp.role_principal_id)insert into #tmp_svr_role select a.member_principal_id, b.name,    a.role_principal_id, c.name from tmp ainner join sys.server_principals bon a.member_principal_id = b.principal_idinner join sys.server_principals con a.role_principal_id = c.principal_id--登录账号自身权限, sys.server_permissions不包含fixed server role权限,同时手动排除掉public权限select a.principal_id as member_principal_id, a.name as member_principal_name,    null as role_principal_id, null as role_principal_name,    b.permission_name, b.state_descfrom sys.server_principals ainner join sys.server_permissions bon a.principal_id = b.grantee_principal_idwhere a.principal_id = @svr_principal_idand b.permission_name <> 'CONNECT SQL'union all--server role权限,包含fixed server role和自定义的server roleselect a.member_principal_id, a.member_principal_name,    a.role_principal_id, a.role_principal_name,    isnull(b.permission_name,'Fixed Server-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_descfrom #tmp_svr_role aleft join sys.database_permissions bon a.role_principal_id = b.grantee_principal_idunion all--public server role权限,不可以取消public权限,它是每个登录账号的最小权限,仅可连接数据库实例select @svr_principal_id as member_principal_id,@svr_principal_name as member_principal_name,    principal_id as role_principal_id, name as role_principal_name,    'CONNECT SQL' as permission_name, 'GRANT' as state_descfrom sys.server_principalswhere name = 'public'

Instance-Level Permissions

注意:服务器角色的权限可以做什么具体的事情,exec sp_srvrolepermission 有大致的介绍,但是也并没有全部列出每一种数据库操作,因为有些操作是被更高级的操作包含的。

 

2. 数据库级的权限

仅列出数据库级别的权限,具体的对象名称并没有列出。

use DBAGOdeclare @svr_principal_name varchar(1024)set @svr_principal_name = 'test_login'declare @db_principal_id  int,    @db_principal_name varchar(512)select @db_principal_id = principal_id,    @db_principal_name = namefrom sys.database_principals pwhere SUSER_SNAME(sid) = @svr_principal_nameif OBJECT_ID('tempdb..#tmp_db_role','U') is not null  drop table #tmp_db_role;create table #tmp_db_role(member_principal_id   int,member_principal_name  varchar(512),role_principal_id    int, role_principal_name   varchar(512))--获取登录账号在当前数据库的所有database role;with tmpas(select * from sys.database_role_members where member_principal_id = @db_principal_idunion allselect rm.* from sys.database_role_members rminner join tmp on rm.member_principal_id = tmp.role_principal_id)insert into #tmp_db_role select a.member_principal_id, b.name,    a.role_principal_id, c.name from tmp ainner join sys.database_principals bon a.member_principal_id = b.principal_idinner join sys.database_principals con a.role_principal_id = c.principal_id--登录账号在当前数据库的自身权限, sys.database_permissions不包含fixed database role权限,同时手动排除掉public权限select a.principal_id as member_principal_id, a.name as member_principal_name,    null as role_principal_id, null as role_principal_name,    b.permission_name, b.state_descfrom sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idwhere a.principal_id = @db_principal_idand b.permission_name <> 'CONNECT'union all--database role权限,包含fixed database role和自定义的database roleselect a.member_principal_id, a.member_principal_name,    a.role_principal_id, a.role_principal_name,    isnull(b.permission_name,'Fixed Database-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_descfrom #tmp_db_role aleft join sys.database_permissions bon a.role_principal_id = b.grantee_principal_idunion all--public database role权限,不可以取消public权限,它是每个登录账号映射到当前数据库的最小权限,仅可连接当前数据库select @db_principal_id as member_principal_id, @db_principal_name as member_principal_name,    principal_id as role_principal_id, name as role_principal_name,    'CONNECT' as permission_name, 'GRANT' as state_descfrom sys.database_principalswhere name = 'public'

Database-Level Permissions

注意:sysadmin的账号在数据库里可能并没有做映射,但权限是有的,隐式映射的用户是dbo

 

3. 对象级的权限

sys.database_permissions有很多对象类型,major_id, minor_id取决于class_desc,不同的对象关联不同的系统表/视图,脚本里仅列出了最常见的OBJECT_OR_COLUMN, SCHEMA对象权限。

--建立测试用的架构,对象,列use DBAGOif object_id('test_grant','U') is not null  drop table test_grantGOcreate table test_grant(c1 int, c2 int, c3 int)grant select (c1, c2) on test_grant to test_user;if object_id('test_schema.test_t1','U') is not null  drop table test_schema.test_t1GOif exists(select 1 from sys.schemas where name = 'test_schema')  drop schema test_schemaGOcreate schema test_schemacreate table test_schema.test_t1(c1 int, c2 int)grant select on schema::test_schema to test_user;GO--开始获取对象权限use DBAGOdeclare @svr_principal_name varchar(1024)set @svr_principal_name = 'test_login'declare @db_principal_id  int,    @db_principal_name varchar(512)select @db_principal_id = principal_id,    @db_principal_name = namefrom sys.database_principals pwhere SUSER_SNAME(sid) = @svr_principal_nameif OBJECT_ID('tempdb..#tmp_db_role','U') is not null  drop table #tmp_db_role;create table #tmp_db_role(member_principal_id   int,member_principal_name  varchar(512),role_principal_id    int, role_principal_name   varchar(512))--获取登录账号在当前数据库的所有database role;with tmpas(select * from sys.database_role_members where member_principal_id = @db_principal_idunion allselect rm.* from sys.database_role_members rminner join tmp on rm.member_principal_id = tmp.role_principal_id)insert into #tmp_db_role select a.member_principal_id, b.name,    a.role_principal_id, c.name from tmp ainner join sys.database_principals bon a.member_principal_id = b.principal_idinner join sys.database_principals con a.role_principal_id = c.principal_id--登录账号在当前数据库的自身对象权限(OBJECT_OR_COLUMN)select a.principal_id as member_principal_id, a.name as member_principal_name,    null as role_principal_id, null as role_principal_name,    o.name as major_name, c.name as minor_name,    b.permission_name, b.state_descfrom sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idleft join sys.objects oon b.major_id = o.object_idleft join sys.columns c on (b.major_id = c.object_id and b.minor_id = c.column_id)where a.principal_id = @db_principal_idand b.class_desc = 'OBJECT_OR_COLUMN'union all--登录账号在当前数据库的自身对象权限(SCHEMA)select a.principal_id as member_principal_id, a.name as member_principal_name,    null as role_principal_id, null as role_principal_name,    s.name as major_name, null as minor_name,    b.permission_name, b.state_descfrom sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idleft join sys.schemas son b.major_id = s.schema_idwhere a.principal_id = @db_principal_idand b.class_desc = 'SCHEMA'union all--database role的对象权限(OBJECT_OR_COLUMN)select a.member_principal_id, a.member_principal_name,    a.role_principal_id, a.role_principal_name,    o.name as major_name, c.name as minor_name,    b.permission_name, b.state_descfrom #tmp_db_role ainner join sys.database_permissions b --inner join, 仅自定义的database roleon a.role_principal_id = b.grantee_principal_idleft join sys.objects oon b.major_id = o.object_idleft join sys.columns c on (b.major_id = c.object_id and b.minor_id = c.column_id)where b.class_desc = 'OBJECT_OR_COLUMN'union all--database role的对象权限(SCHEMA)select a.member_principal_id, a.member_principal_name,    a.role_principal_id, a.role_principal_name,    s.name as major_name, null as minor_name,    b.permission_name, b.state_descfrom #tmp_db_role ainner join sys.database_permissions b --inner join, 仅自定义的database roleon a.role_principal_id = b.grantee_principal_idleft join sys.schemas son b.major_id = s.schema_idwhere b.class_desc = 'SCHEMA'/*union all--public role有一些系统视图的select权限,可以忽略select a.principal_id as member_principal_id, a.name as member_principal_name,    null as role_principal_id, null as role_principal_name,    o.name as major_name, c.name as minor_name,    b.permission_name, b.state_descfrom sys.database_principals ainner join sys.database_permissions bon a.principal_id = b.grantee_principal_idleft join sys.all_objects oon b.major_id = o.object_idleft join sys.all_columns c on (b.major_id = c.object_id and b.minor_id = c.column_id)where a.name = 'public'*/

Object-Level Permissions

注意:如果对象的权限是通过role衍生的,而不是直接分配给user或者role,那么并不会被列出来。试想sysadmin 的角色,难道要列出所有数据库的所有对象吗?

 

. 查看自己的权限

1. 有没有登录权限

登录失败并不一定是没权限,还是找别人来检查自己账号的登录权限吧;

 

2. 有没有数据库访问权限

--列出所有可访问的数据库SELECT *FROM sys.databasesWHERE HAS_DBACCESS(name) = 1

  

3. 有没有对象访问权限

用上面提到HAS_PERMS_BY_NAME函数,它可以检查当前账号的各种权限;

SELECT HAS_PERMS_BY_NAME('test_sp', 'Object' , 'Execute')SELECT HAS_PERMS_BY_NAME('test', 'Database' , 'Execute')

  

4. 有哪些权限

--实例级权限SELECT * FROM fn_my_permissions(NULL, 'SERVER'); --数据库级权限SELECT * FROM fn_my_permissions ('DBA', 'DATABASE'); --对象权限,只能一个个对象检查,不能一次返回所有对象权限,和HAS_PERMS_BY_NAME类似SELECT * FROM fn_my_permissions ('test_grant', 'OBJECT'); 

 用于检查自己权限的方法,同样也可以检查其他账号,用EXECUTE AS切换账号即可。