Chapter 2 User Authentication, Authorization, and Security（8）：创建映射到登录名的数据库用户

原文出处：http://blog.csdn.net/dba_huangzj/article/details/38944121，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

未经作者同意，任何人不得以“原创”形式发布，也不得已用于商业用途，本人不负责任何法律责任。

        前一篇：http://blog.csdn.net/dba_huangzj/article/details/38895357

 

前言：

 

登录名用于授权并访问服务器资源，如果需要访问数据库，需要对数据库内部的用户进行映射。用户是数据库级别的安全主体，访问数据库资源是授予给用户，而不是登录名。

 

实现：

 

有两种方法可以实现创建数据库用户，第一种是在服务器级别的登录名的属性页，另外一种是在数据库的【安全性】→【用户】节点中实现，这里使用第二种方式演示：

1.在SSMS中，选择【数据库】节点，并展开对应的数据库，选择【安全性】，右键【用户】选择【新建用户】

2. 在【常规】页，可以选择下面的用户类型：

 

类型简要说明：

用户类型 描述 SQL user with login（带登录名的SQL用户） 映射到一个SQL登录的用户。 SQL user without login（不带登录名的SQL用户） 没有映射到任何服务器级别的登录的用户，可以使用EXECUTE AS USER命令模拟执行。

User mapped to a  certificate/User mapped  to an asymmetric key （映射到证书/非对称密钥的用户）

通过一个签名密钥创建的用户，允许映射自一个远程实体。 Windows user （Windows用户） 映射到Windows身份验证的登录名的用户。

 

3. 输入用户名，可以和登录名一样，或者使用其他允许的名字，但是不能使用一些关键字、保留字等。选择一个登录名（或者输入映射的证书、密钥）。然后选择默认架构，如果没有选择架构，SQL Server会使用dbo作为默认架构。除了图形化操作，还可以用下面的T-SQL实现：

USE marketing; CREATE USER [Fred] FOR LOGIN [Fred];

 

4. 如果想查询已创建的用户，可以用sys.database_pr\incipals目录视图实现：

SELECT dp.name as UserName, sp.name as LoginName, dp.default_language_name, dp.default_schema_name, dp.type_desc, dp.create_date FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE dp.type IN ('S', 'U') AND dp.principal_id > 4;

 

其中1~4的principal_id为系统预设的用户。

 

原理：

 

你可以创建不带有登录名的用户，这种情况多数用在测试过程，或者仅用于安全上下文模拟（在第三章介绍）。要创建一个用户，需要在数据库层面上有ALTER ANY USER权限，等于固定数据库角色db_accessadmin，可以用GRANT ALTER ON USER::[用户名] to [特定需要用于管理的登录名]来实现。比如：

GRANT ALTER ON USER::[Fred] TO [Mary];

代表Mary这个登录名有权管理Fred这个用户。但是不给Mary过多的权限。

 

禁用用户：

 

和登录名的禁用不同，对于用户，没有ALTER USER XXXX DISABLE命令，在SSMS中也能看到没有这类勾选项。另外，一些系统用户，如guest、INFORMATION_SCHEMA，默认是禁用的。

如果要禁用用户，必须回收其CONNECT权限：

USE marketing; REVOKE CONNECT TO [Fred];

 

更多：

 

如果一个Windows 帐号属于某个Windows组，并且这个组作为一个登录被添加到SQL Server中，不仅可以创建一个数据库用户映射到整个组，也可以创建用户映射到单独的Windows帐号。比如，如果DOMAIN\Fred是DOMAIN\Developers组的成员，并且这个组已经定义为SQL Server登录名，但是DOMAIN\Fred登录未被添加，可以单独创建用户并授予权限：

CREATE USER [DOMAIN\Fred] FROM LOGIN [DOMAIN\Fred];

 

谁是dbo？

 

dbo代表Database Owner，是一个映射数据库拥有者的特殊数据库用户，当创建数据库时，所使用的登录名会被授予成owner，可以用下面语句查询：

SELECT SUSER_SNAME(owner_sid), name FROM sys.databases; -- or : SELECT SUSER_SNAME(sid) FROM sys.database_principals WHERE principal_id = USER_ID('dbo');

 

 

此时，登录名会自动映射到dbo中，并且授予整个库的所有权限，可以用下面语句修改：

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;

 

把数据库marketing的owner更改给sa。这种做法在把数据库移动到别的服务器时，而拥有者的SID不存在，就很有用。

 

dbo用户不能被重命名或者删除，也不能从db_owner角色中移除，数据库拥有者和sysadmin服务器角色成员会被视为数据库的dbo用户，逻辑上，作为数据库拥有者会映射到dbo中，而sysadmin角色成员不需要映射，实际上，即使当sysadmin成员映射到一个数据库的用户是，它会被视为dbo。

 

什么是guest用户？

 

在每一个数据库中，你会看到一个用户名叫guest的用户，它是一个不能移除的数据库固定系统用户，目的是允许没有映射到一个用户的登录名能匿名访问数据库。但是默认是禁用的，以防不必要访问。从安全性角度来说，保持它的禁用是比较好的方法，如果想启用，可以授予CONNECT权限：

USE marketing; GRANT CONNECT TO guest;

 

可以使用下面代码检查guest用户是否启用：

SELECT CAST(IIF(dp.state IN ('G', 'W'), 1, 0) AS bit) AS [HasDBAccess] FROM sys.database_principals u LEFT JOIN sys.database_permissions dp     ON dp.grantee_principal_id = u.principal_id and dp.type = 'CO' WHERE u.name = 'GUEST';

返回1即启用，-返回0为禁用。还可以用下面语句查看guest的权限：

SELECT permission_name, state_desc, OBJECT_NAME(major_id) as securable FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('guest');

 

使用系统函数标识用户和登录

可以使用很多系统函数获取用户和登录的信息。

SYSTEM_USER和SUSER_SNAME()，返回当前登录信息，并包含SID。其中SUSER_SNAME()返回一个来自登录名或当前登录的SID，如果没有带有参数，在安全上下文更改时（EXECUTE AS ），会返回模拟登录。

CURRENT_USER和SESSION_USER返回当前数据库的当前上下文的用户名，USER_ID()或DATABASE_PRINCIPAL_ID()返回数据库的principal_id，而SUSER_SID()返回SID。

可以使用下面语句列出所有可以访问的数据库名：

SELECT [Name] FROM sys.databases WHERE HAS_DBACCESS ([Name]) = 1   AND database_id > 4 ORDER BY [Name];

 

如果需要查询某个用户是否属于Windows的组成员，可以使用IS_MEMBER()函数：

SELECT IS_MEMBER('DOMAIN\developers');

下面是可以使用的函数既描述：

函数名 描述 SYSTEM_USER 返回当前服务器登录名 SUSER_SNAME() 返回当前服务器登录名 SUSER_ID() 返回当前服务器SID CURRENT_USER 返回当前数据库用户 SESSION_USER 返回当前数据库用户 USER_ID() 返回当前数据库principal_id DATABASE_PRINCIPAL_ID() 返回当前数据库principal_id ORIGINAL_LOGIN() 返回在上下文切换钱的原始登录名 HAS_DBACCESS('database') 1代表当前登录可以访问数据库 IS_MEMBER('group') 1代表当前登录是Windows组成员

下一篇：http://blog.csdn.net/dba_huangzj/article/details/39003679