MSSQL服务器掌控用户角色权限的快速指南-猿码集

1. 概述

每个MSSQL数据库都由许多对象组成，包括表、视图、函数、存储过程和用户，每一个对象都可以被赋予角色并在数据库当中执行。角色可以被视为权限集合，决定了用户可以在数据库中执行的操作。MSSQL服务器提供了许多不同的角色，可以在不同的安全需求和访问级别下使用。本文将介绍如何掌握MSSQL服务器中用户角色权限的使用方法。

2. 默认角色

2.1 public角色

public是MSSQL服务器中的一个默认角色，所有用户都是自动成为该角色的成员。其目的是为了让所有用户都能够访问数据库中的共享对象。因此，public角色不能用于控制访问权限。

2.2 guest角色

guest是MSSQL服务器中的另一个默认角色，当一个数据库用户没有被分配到任何其他角色时，该用户就成为了guest角色的成员。guest角色具有最小的权限集合，只能查询系统表。这样做是为了防止未经授权的访问。因此，打算给用户提供访问权限的时候，就不应该使用guest角色，因为这个角色不能执行任何操作。

3. 建立自定义角色

在大多数情况下，我们需要建立自定义角色，以满足特定的安全需求。可以通过CREATE ROLE语句来创建角色：

CREATE ROLE role_name AUTHORIZATION owner_name;

其中，role_name是角色名称，owner_name是角色的所有者。要将权限赋给该角色，请使用GRANT语句：

GRANT permission ON object_name TO role_name;

例如，要将SELECT权限授予一个名为test_role的新角色：

CREATE ROLE test_role AUTHORIZATION dbo;
GRANT SELECT ON some_table TO test_role;

或者将该角色授予其他用户或角色：

EXEC sp_addrolemember 'test_role', 'user_name'; EXEC sp_addrolemember 'test_role', 'role_name';

4. 预定义角色

除了建立自定义角色之外，MS SQL SERVER还预定义了一些角色，这些角色可以将某些操作权限集中起来，在授权时更加方便。这些角色包括：

db_owner：拥有该数据库的所有权限

db_datawriter：具有在数据库中写入数据的权限

db_datareader：具有在数据库中读取数据的权限

db_executor：允许用户执行程序、函数和存储过程

5. 授权

要向用户授权，请使用GRANT语句。例如，要授予一个名为user_name的用户查询表some_table的权限：

GRANT SELECT ON some_table TO user_name;

注意，如果将权限直接授予给用户，即便该用户作为其他角色的成员，也不会有任何影响。同样，一个用户可以成为多个角色的成员，这样他就具有了多个角色的所有权限。

6. 撤回

要撤回用户的权限，请使用REVOKE语句。例如，要撤回一个名为user_name的用户对表some_table的查询权限：

REVOKE SELECT ON some_table TO user_name;

还可以使用DENY语句拒绝用户对某个对象或操作的访问权限。

7. 检查权限

要检查用户是否具有特定的权限，请使用IS_MEMBER和HAS_PERMS_BY_NAME函数。例如，要检查一个名为user_name的用户是否是某个角色的成员：

IF IS_MEMBER('role_name') = 1
    PRINT 'user_name is a member of role_name';
ELSE
    PRINT 'user_name is not a member of role_name';

要检查用户是否具有特定的操作权限：

IF HAS_PERMS_BY_NAME('some_table', 'object', 'SELECT') = 1
    PRINT 'user_name has SELECT permission on some_table';
ELSE
    PRINT 'user_name does not have SELECT permission on some_table';

8. 总结

在MSSQL服务器中，通过使用角色可以轻松地控制用户对数据库的访问权限。可以创建自定义角色，并向这些角色授权，或者使用预定义角色，将权限集中在一起。也可以使用IS_MEMBER和HAS_PERMS_BY_NAME函数检查用户是否具有某种权限，使用户的访问更加安全和可控。

MSSQL服务器掌控用户角色权限的快速指南