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
Sujith Kumar has over 25 years of professional experience in the IT industry. Sujith has been extensively involved in designing and delivering innovative solutions for the Fortune 500 companies in the United States and across the globe for disaster recovery and high availability preparedness initiatives. Recently after leaving Quest Software/Dell after 19 years of service he was working at Cirro, Inc. focusing on database management and security. His main focus and area of interest is anything data related.
Sujith has a Master of Science in engineering degree from Texas A&M University and a Bachelor of Science in engineering degree from Bangalore University and has published several articles in referred journals and delivered presentations at several events.