SQL Server public 角色权限管理

服务器角色public

所有登陆名都属于public服务器角色，登陆名是不能移除public角色的，且public角色是不能删除的。这对于我们来说是相当恐怖的事情，因为权限分配的第一原则即最小权限原则，对于一个不可掌控的权限，我们的心里是非常忐忑的。那么public角色具有哪些权限？属于public角色的成员能做些什么操作？public权限是否可以收回？本文将带你解决这些问题。

public 服务器角色有两个权限：

• VIEW ANY DATABASE

• CONNECT ON ENDPOINT

可以通过sys.server_permissions 查看public服务器角色所具有的权限，脚本如下：

SELECT * FROM sys.server_permissions
WHERE grantee_principal_id=2;

了解到public服务器角色具有哪些 权限，我就能很好的管理它了。

而其中重要的权限是查看任何数据库（VIEW ANY DATABASE），这是很危险的。数据库架构暴露出去，是存极大安全隐患的，因为一旦暴露数据库架构，黑客们就有了明确的攻击目标。所以对于大部分登陆名，都要收回其查看数据库的权限。我们可以通过如下语句收回登陆名查看数据库的权限：

REVOKE VIEW ANY DATABASE FROM PUBLIC;

收回服务器角色public查看任意数据库权限，不包括master和tempdb数据库，即此时所有登陆名仍具有查看master和tempdb的权限。尤其master数据库，存储有账户等重要信息，这个库也是不能对外开放的，后面将介绍更为严苛的管理方式。

数据库角色public

每个数据库的所有用户都是public角色，用户同样不能退出public角色成员。我们也需要了解public数据库角色具有哪些权限。通过如下脚本，可以查看public具有的权限：

SELECT DB_NAME(grantee_principal_id ) database_name
       ,prin.name database_role_name
       ,class_desc permission_class
       ,state_desc permision_state
       ,permission_name
       ,v.type_desc AS object_type
       ,SCHEMA_NAME(SCHEMA_ID) AS object_schema_name
       ,v.name AS OBJECT_NAME
FROM sys.all_objects AS v
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=v.OBJECT_ID
INNER JOIN sys.database_principals AS prin ON prmssn.minor_id=prin.principal_id
WHERE prin.name='public';

结果如下图：

上面通过收回服务器角色public的VIEW ANY DATABASE 权限，用户不能在左侧列表查看除master和tempdb以外所有数据库，但如果我们整个左侧列表都不想给所有普通用户看到的话，可以通过如下脚本收回或赋予左侧列表的权限：

--收回左侧列表查看权限
DENY SELECT  TO PUBLIC;
--赋予左侧列表查看权限
GRANT SELECT TO PUBLIC;

收回public的SELECT权限后，我们发现，点对象资源管理器下任何对象，都会报错，如上图。

注意：这个对于sysadmin系统角色的成员无效。

看到这里，对于没有开发环境，或者有开发环境，管理不规范，开发环境和生产环境数据库结构不一致的，或者习惯了使用左侧列表查看数据库结构的开发人员就要跳脚了。根据最小权限原则，开发人员只需要查看其工作相关的内容即可，而这些内容通常开发人员都比较熟悉，如其创建的表、编写的存储过程等，我们给相应的账户开启查看定义权限VIEW DEFINITION即可，脚本如下：

use [DB1]
GO
GRANT VIEW DEFINITION TO [loginName2]
GO

这样我们可以使用sp_help tableName 或者sp_helptext procedureName查看表结构和存储过程的内容了：

use DB1
exec sp_help [test]​

use DB1
exec sp_helptext [test1]

这是不是比你使用鼠标点击查看表结构和存储过程更cool呢，希望你能喜欢这种操作。更多精彩，可以搜索关注公众号MSSQLSERVER哦！！！