Saturday, August 27, 2016

The "Public" Database Role in SQL Server

In SQL Server, the Public database role in SQL server is a special database role that is given by default to all logins. You cannot remove a login's membership to this role because this behavior is built into SQL Server.

Public database role membership in SQL Server









































But what this role does? Should we make use of it or at the end of the day just ignore it?

When you create a new login (and consequently a new database user) in SQL Server and do not give it any permissions anywhere (that is securable objects), then this database user inherits the permissions the public database role has.

So, let's see an example. On a SQL Server 2014 instance I have just created a login named 'testLogin' (not quite  unique, isn't it? :) and just granted access to the 'Adventureworks2014' database without doing anything else. As you can see from the below screenshot, my login was just granted the Public database role on the database.


Public database role membership in SQL Server










































The question now is what permissions the 'testLogin' inherited on AdventureWorks2014? I have not provided any permission so it's just the permissions provided by the Public database role.

If we check the securableS for the Pulbic database role in AdventureWorks2014 security, we can see that the Public database role is granted with SELECT access to certain system catalog views such as:


  • sys.all_columns
  • sys.all_objects
  • sys.all_parameters
  • sys.all_sql_modules
  • sys.all_views
  • sys.allocation_units
  • sys.assemblies
  • etc.



Public database role membership in SQL Server
















































Now here comes the security recommendation:



Even though the Public database role is granted by default SELECT permissions to certain system catalog views, you should never, ever add more permissions to this role. Just leave it as it is. If you make the mistake and add more permissions to the public database role then this will mean that any login that will be granted access to the database it will inherit all these permissions. So please, don't do that!

Let's try again to rephrase this with even more plain words: any permissions the Public database role has, are automatically granted to all database users. That's why you should never add permissions to this role.

By the way, if you want to check if you have accidentally added more permissions to this role, you can try the below query:

USE [Database Name];
GO

SELECT 
DB_NAME() as DBName,
pm.[permission_name] as PermissionName,
ob.[name] as ObjectName,
pm.class_desc as ObjectType,
ob.type_desc as TypeDescription
FROM sys.database_permissions pm
INNER JOIN sys.database_principals pr ON pm.grantee_principal_id= pr.principal_id
LEFT JOIN sys.objects ob ON pm.[major_id] = ob.[object_id]
WHERE [state]='G' and major_id>=0 and pr.[name]='public' and ob.[name] IS NOT NULL;
GO

If the above query return any results, you should revise the permissions granted to the Public database role for the specific database.

Useful Information
I have recently released a brand new software tool called "DBA Security Advisor" which checks SQL Server instances for vulnerabilities using a large number of security checks. Among other, it checks if the Public database role has been granted additional permissions besides the defaults. Check it out here!



The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)


Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: