Stealthbits

Public Role in SQL Server

Blog >Public Role in SQL Server
| Sujith Kumar | | Leave a Comment

Roles in relational databases make it easier to grant and revoke privileges from multiple users at once.  Multiple users can be grouped into one or more roles in a database. Rather than revoking or granting a privilege to a user or a group of users, the privilege can be granted or revoked from the ROLE.  In my previous blog, I talked about the PUBLIC role in Oracle.  In this blog, I will try to explain what the public role means in Microsoft SQL Server and some of the best practices related to the public role. I am deliberately using lower case letters when defining public roles, since that is how it is referred to in the SQL Server world, unlike in the Oracle world.

All versions of SQL Server come with two distinct categories of pre-built roles – fixed server roles and fixed database roles.  Unlike other database platforms, SQL Server provides another role called the application role.  The application role is a database principal that can be used by an application to run with its own set of permissions and is disabled by default. Microsoft continues to implement fixed server roles in recent versions of SQL Server for convenience and backward compatibility. All database platforms come with a pre-defined role called public. The implementation of the PUBLIC role varies from one database platform to another.  In SQL Server, the public role is part of the fixed server role and is implemented differently compared to other roles.  The reason they are called fixed roles is that, except for the public role, they cannot be modified or dropped.  Microsoft started supporting user-defined roles in addition to fixed server roles starting with SQL Server 2012.  However, please be aware that only server-level permissions can be added to user-defined roles. 

In SQL Server, permissions can be granted, denied or revoked, from the public role, unlike other fixed roles. When a SQL Server login is created, the public role is assigned to the login and cannot be revoked. After creating the login, if that server principal is not granted or denied specific permissions on any securable object, the login will automatically inherit the permissions granted to the public role. 

Given the fact that there are server-level roles and database-level roles, let us explore the server-level permissions assigned to the public role.

SELECT sp.state_desc as "Permission State", sp.permission_name as "Permission", 
sl.name "Principal Name",sp.class_desc AS "Object Class", ep.name "End Point"
FROM sys.server_permissions AS sp
JOIN sys.server_principals AS sl
ON sp.grantee_principal_id = sl.principal_id
LEFT JOIN sys.endpoints AS ep
ON sp.major_id = ep.endpoint_id WHERE sl.name = 'public';

The result is pretty underwhelming; there are only five server-level permissions assigned to the public role.  Note that the VIEW ANY DATABASE permission does not give the user with only public role access to any database objects.  It only allows the user to list all the databases in a SQL Server instance. Therefore, if you create a new login and assign no other roles or permissions, the user can only log in to the instance and is unable to do anything else.

Next, let us create SQL Server login that uses SQL Authentication by assigning a default database to that user.

USE [master]
GO
CREATE LOGIN [Stealth] WITH PASSWORD=N'nhggLboBn6SHolSWfipjzO/7GYw8M2RMbCt1LsCTK5M=', DEFAULT_DATABASE=[SBITS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

If you log in as that user and list all the database level permissions for the user’s default database SBITS, we can see what permissions they have. As seen in the screenshot below, the user Stealth does not have any permissions on the SBITS database even though that is the user’s default database.  The point of this is that just because a default user database is assigned to a user login, it does not mean the user will be able to see the database objects or data.

EXECUTE AS LOGIN='Stealth';
GO
USE SBITS
GO
SELECT dp.state_desc AS "Class Description", dp.permission_name AS "Permission Name",
SCHEMA_NAME(ao.schema_id) AS "Schema Name", ao.name AS "Object Name"
FROM sys.database_permissions dp
  LEFT JOIN sys.all_objects ao
    ON dp.major_id = ao.object_id
  JOIN sys.database_principals du
    ON dp.grantee_principal_id = du.principal_id
WHERE du.name = 'TestLoginPerms'
  AND ao.name IS NOT NULL
ORDER BY ao.name;
REVERT

Since the user login Stealth is part of the public role by default, let us see the permissions inherited by the public role on the master database.

USE master;
GO 

 SELECT sp.state_desc AS "Permission State", sp.permission_name AS "Permission", 
 SCHEMA_NAME(ao.schema_id) AS 'Schema', ao.name AS "Object Name"
 FROM sys.database_permissions sp
 LEFT JOIN sys.all_objects ao
 ON sp.major_id = ao.object_id
 JOIN sys.database_principals dp
 ON sp.grantee_principal_id = dp.principal_id
 WHERE dp.name = 'public'
  AND ao.name IS NOT NULL
 ORDER BY ao.name

As you can from the screenshot above, in SQL Server 2016 there are 2,089 permissions in the master database granted to public role.  While it might seem daunting, they are all SELECT permissions and do not allow the user Stealth to make any changes in the master database. However, it is good practice to revoke some of the grants based on the security policies in your organization. Exercise caution while revoking the grants as some of those grants are required by users for normal operations in certain circumstances.

I recommend the following best practices when it comes to public role in SQL Server:

  • Do not grant any additional privileges to the public role outside of the default privileges, under any circumstance.  If necessary, make use of a user-defined role.
  • Do not modify the server-level permissions to the public role as it may prevent users from connecting to the database.
  • Review the public role privileges every time you upgrade your SQL Server to a higher version as Microsoft often makes changes to the public role.

StealthAUDIT is a Data Access Governance that can enumerate all the SQL Server roles and privileges including the public role and produce detailed entitlement reports out-of-the-box. Rather than resorting to custom scripts to figure out the issues related to public role in one instance at a time, consider using StealthAUDIT for SQL. It can offer a single-pane-of-glass view into the public role across all the SQL Servers in your enterprise and assist you in remediating any issues you might encounter with a click of a button.  Below is a screenshot from StealthAUDIT that shows all the permissions allocated to the public role broken down by server and instance with support for all versions of SQL Server.

To learn more about how Stealthbits can help with auditing  your SQL Server instances, visit our website: https://www.stealthbits.com/stealthaudit-for-sql-product

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe

DON’T MISS A POST. SUBSCRIBE TO THE BLOG!

 

Loading

© 2020 Stealthbits Technologies, Inc.

Start a Free Stealthbits Trial!

No risk. No obligation.

FREE TRIAL