“孤立”用户

在数据库安全体系中,Login和User是两个最基本的安全主体(Principal),Login用于登陆到SQL Server实例,而User用于访问数据库。Login和User之间有一个映射关系,通过SID(安全标识,Security ID)连接到一起。在一个数据库中,如果一个User没有相应的Login,称作孤立用户(Orphaned User),也就是说,该User的SID存在于sys.database_principals 中, 而不存在于 sys.server_principals 中。一般情况下,把备份的数据库还原到在其它SQL Server实例之后,会产生孤儿用户。还有一种情况,孤立用户是DBA故意创建的,通过权限模拟来实现特定的系统维护任务。

一,检查和修复孤立用户

Login 和 User的映射关系是通过SID来关联的,如果一个SID 存在于sys.database_principals,而不存在于  sys.server_principals,那么这个User 除非是system user,否则就是孤立用户。出现孤立用户的根本原因是:数据库User没有对应的Login,解决孤立用户问题的方法是:创建Login,建立Login和孤立用户之间的映射。

1,使用以下脚本查看孤立用户

在查看孤立用户时,应该过滤掉系统预先创建的用户,例如,dbo、sys和guest(来宾用户),一般情况下,只查看SQL User,Windows Users和Windows Group这三个安全主体类型,通过以下脚本查看孤立用户,并修复孤立用户。大多数情况下,在把数据库备份还原到不同的SQL Server实例时,会出现孤立用户。

select dp.name as UserName
    ,dp.type
    ,dp.type_desc
    ,dp.default_schema_name
    ,dp.is_fixed_role
    ,dp.authentication_type
    ,dp.authentication_type_desc
    ,dp.sid,dp.principal_id
from sys.database_principals dp
left join sys.server_principals sp 
    on dp.sid=sp.sid
where sp.sid is null
    and dp.[type] IN (N'U', N'S',N'G')
    and dp.is_fixed_role = 0
    and dp.[Name] NOT IN (N'dbo', N'guest', N'sys', N'INFORMATION_SCHEMA')

在视图 sys.database_principals中,Principal的类型注释如下:

  • S = SQL user
  • U = Windows user
  • G = Windows group

2,创建Windows Login

创建Windows Login,Logon Name的格式是:[<domainName>\<login_name>]

CREATE LOGIN [DomainName\WindowsLoginName] -- or [DomainName\WindowsGroupName] 
FROM WINDOWS WITH DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english]

3,重新创建Login 和 name之间的映射关系

通过为User指定新的Login,重新把User映射到Login。Login 和User的映射关系是通过SID(security Identifier)来关联的,在重新映射时,数据库引擎会把User的SID修改为Login的SID,以建立映射关系。

ALTER USER userName  
WITH LOGIN = loginName

注:The WITH LOGIN clause enables the remapping of a user to a different login. 

二,自动修复孤立用户问题

对于孤立用户,由于User  Name和 Login Name之间没有直接的关系,因此,完全修复孤立用户的可能性几乎是没有的。在特定的情况下,当使用Windows验证创建User和Login时,把User Name 和 Login Name设置成相同的,这样,可以检测数据库的User是否有对应的Login;如果没有对应的Login,管理员可以新建相应的Windows Login,重建映射关系,进而修复孤立用户。

以下是自动修复孤立用户的脚本,仅供参考:

declare @username sysname
declare @sqlcmd nvarchar(max)

declare cur_orphaned cursor 
    local
    forward_only
    fast_forward
    read_only
for 
select dp.name as UserName
from sys.database_principals dp
left join sys.server_principals sp 
    on dp.sid=sp.sid
where sp.sid is null
    and dp.[type] IN (N'U',N'G')
    and dp.is_fixed_role = 0
    and dp.[Name] NOT IN (N'dbo', N'guest', N'sys', N'INFORMATION_SCHEMA');

open cur_orphaned
fetch next from cur_orphaned into @username

while @@fetch_status=0
begin
    --create login
    set @sqlcmd = N'create login [' + @username + N'] from windows'
    exec(@sqlcmd)

    --remap user and login
    set @sqlcmd = N'alter user [' + @username + N'] with login = [' + @username + N']'
    exec(@sqlcmd)

    fetch next from cur_orphaned into @username
end

close cur_orphaned
deallocate cur_orphaned
View Code

三,来宾用户(guest)

登录(Login)用于用户身份验证,而数据库用户(User)用于数据库的访问和权限验证。登录(Login)通过安全识别符 (SID) 与用户(User)关联。如果数据库中不存在针对特定登录(Login)的用户(User),使用该登录(Login)的用户即使能够连接到 SQL Server 服务器,也无法访问数据库。但是,该情形的唯一例外是当数据库包含“guest”用户(User)时,与用户(User)不关联的登录(Login)将被映射到 guest 用户。如果存在数据库用户(User),但没有与其关联的登录(Login),则该用户将无法登录到 SQL Server 服务器中。

四,创建孤立用户

通过Create User 命令创建User和Login之间的映射关系,上文提到,这种映射关系是通过SID来关联的,即Login访问数据库使用的User的SID和Login相同。

在创建新的User时,指定关联的Login,就可以创建User和Login的映射关系,脚本如下:

CREATE USER user_name 
--FOR LOGIN login_name 
[ WITH DEFAULT_SCHEMA = schema_name ] 

在创建User时,如果没有指定for login子句,那么新建的User是孤立用户,一般用作系统维护等特殊用途。

 

参考文档:

Fixing orphaned database users in 2005 to 2012 – T-SQL Tuesday #025

Do you still use sp_change_users_login instead of ALTER USER UserName WITH LOGIN = UserName

 

posted @ 2017-07-26 13:00  悦光阴  阅读(1213)  评论(2编辑  收藏  举报