Public Role Permissions – Back to Basics

Permissions in the database environment is such an important task. Setting permissions correctly is paramount to a successful audit and one of your best defenses against improper/unwanted access. Yet, in spite of the importance, security is often overlooked, neglected, improperly configured or just flat out ignored. Let’s not forget the times that security is intentionally misconfigured so certain individuals are permitted a backdoor access.

Security, just like performance tuning, is a perpetual (and sometimes iterative) task. There is no excuse for setting your security and forgetting it. It must be routinely reviewed.

While performing a script review for a client, I was reminded of the need to also review their security setup. The reminder was more of a slap in the face as I noticed that the developer had built in some permissions assignments for some upgrade scripts. Unfortunately, we were not permitted to alter any of the scripts due to them being from a third party vendor (and that vendor refused as well to fix the problems with the scripts but I digress).

What could be wrong with this?

GRANT REFERENCES,
      SELECT,
      INSERT,
      DELETE,
      UPDATE,
      ALTER,
      VIEW CHANGE TRACKING,
      VIEW DEFINITION,
      CONTROL
ON [dbo].[tblRptWeeklyCorporateBookingListing]
TO  PUBLIC;
GO

I want you to digest that for just a moment. This is an example of the permissions this particular vendor insists on setting for the public role. What could possibly be wrong with that? Let’s examine a couple of the permissions like “Control” and “View Change Tracking”.

View Change Tracking

This permission is an elevated permission that is required in order to use the change tracking functions. This permission is necessary for the following reasons:

1. Change tracking records contain the PK value for rows that have been deleted. If sensitive information was deleted that a user should not be able to access, the user would be able to reverse engineer the data from the change tracking data.
2. A user may be denied access to a column that contains sensitive data. If data in the column is changed, the data would be stored in the change tracking and a user can determine the values that were updated for the sensitive data.

Control

I am going to take this one direct from the Microsoft documentation.

Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable. A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.

Now digest that a bit. Once digested, consider what the public role does to user access in a database. The public role permissions are inherited by all users of the database whether the users have been granted the permission or not. You should only grant permissions to the public role that you really honestly believe that ALL users should have. If you are being serious in your role, then the amount of times you grant permissions to the public role should either be a) never, b) when you want to have a data breach, or c) you are testing in a sandbox to improve your skills.

Check for Perms

When you are uncertain of which permissions have been assigned to the public role, or you just haven’t reviewed your permissions real-estate in some time, it is best to pull out a script and start the process. As luck would have it, I have a few scripts that can help with that (here or here) and I have a new one that I am sharing now.

Let’s start with a basic query that will display all of the permissions assigned to the public role in a specific database.

SELECT
sp.state_desc
  , sp.permission_name
  , s.name AS SchemaName
  , o.name
FROM sys.database_permissions sp
LEFT JOIN sys.all_objects o
ON sp.major_id = o.object_id
LEFT OUTER JOIN sys.schemas s
ON s.schema_id = o.schema_id
JOIN sys.database_principals u
ON sp.grantee_principal_id = u.principal_id
WHERE u.name = 'public'
  AND o.name IS NOT NULL
  AND o.is_ms_shipped = 0
ORDER BY o.name;

There is nothing super special about this query. Looking at it, it is querying the permissions for the public role specifically. I display where the permission is a “Deny” or “Grant”. Then we list the permission name and then the schema and the object.

Let’s take that script and evolve it now. I am going to plan for the worst and expect that some permissions have been applied that shouldn’t have by some vendor upgrade script (because – well, history). Since I am expecting the worst, I am going to add some script generating code that will revoke the unwanted permissions. And still expecting the worst would be that revoking the permissions will break something, I will also add some code that can generate the appropriate “Grant” statements.

SELECT
sp.state_desc
  , sp.permission_name
  , s.name   AS SchemaName
  , o.name
  , 'REVOKE ' + sp.permission_name COLLATE Latin1_General_CI_AS_KS_WS + ' ON [' + s.name + '].[' + o.name
+ '] FROM PUBLIC;' AS RevokeStmnt
  , 'GRANT ' + sp.permission_name COLLATE Latin1_General_CI_AS_KS_WS + ' ON [' + s.name + '].[' + o.name
+ '] TO PUBLIC;'   AS GrantStmnt
FROM sys.database_permissionssp
LEFT OUTER JOIN sys.all_objects o
ON sp.major_id = o.object_id
LEFT OUTER JOIN sys.schemass
ON s.schema_id = o.schema_id
JOIN sys.database_principalsu
ON sp.grantee_principal_id = u.principal_id
WHERE u.name = 'public'
  AND o.name IS NOT NULL
  AND o.is_ms_shipped = 0
ORDER BY o.name;

That looks better. I have a way of identifying the unwanted permissions as well as an easy script I can execute to remove the unwanted permissions. Note the use of the collate in the final two columns. As it turns out, permission_name from sys.database_permissions has a column collation of Latin1_General_CI_AS_KS_WS. Since I ran into some errors (shown below), it is easier to direct the DB engine to use the collation that matches the permission_name column.

Msg 451, Level 16, State 1, Line 11
Cannot resolve collation conflict between “SQL_Latin1_General_CP850_CS_AS” and “Latin1_General_CI_AS_KS_WS” in add operator occurring in SELECT statement column 5.
Msg 451, Level 16, State 1, Line 11
Cannot resolve collation conflict between “SQL_Latin1_General_CP850_CS_AS” and “Latin1_General_CI_AS_KS_WS” in add operator occurring in SELECT statement column 6.

Alas, this is still not quite as efficient of a script as I would like. I may have hundreds of databases on the instance and need to evaluate all of them. Time for the bigger guns.

/* now do it for all databases */DECLARE
     @DBName  SYSNAME
 ,@SQL VARCHAR(2048);
DECLARE @PermsTab AS TABLE (DBName VARCHAR(128),PermissionState VARCHAR(20), PermissionName VARCHAR(128), SchemaName VARCHAR(128), ObjName VARCHAR(128), RevokeStmnt VARCHAR(256), GrantStmnt VARCHAR(256))
 
DECLARE pubperms CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR SELECT d.name
FROM sys.databases d
WHERE d.state_desc  NOT IN ('SUSPECT','OFFLINE')
OPEN pubperms;
 
FETCH NEXT FROM pubperms INTO @DBName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'SELECT
''[' + @DBName + ']''
,sp.state_desc
  , sp.permission_name
  , s.name   AS SchemaName
  , o.name
  , ''REVOKE '' + sp.permission_name COLLATE Latin1_General_CI_AS_KS_WS + '' ON ['' + s.name + ''].['' + o.name
+ ''] FROM PUBLIC;'' AS RevokeStmnt
  , ''GRANT '' + sp.permission_name COLLATE Latin1_General_CI_AS_KS_WS + '' ON ['' + s.name + ''].['' + o.name
+ ''] TO PUBLIC;''   AS GrantStmnt
FROM [' + @DBName + '].sys.database_permissionssp
LEFT OUTER JOIN [' + @DBName + '].sys.all_objects o
ON sp.major_id = o.object_id
LEFT OUTER JOIN [' + @DBName + '].sys.schemass
ON s.schema_id = o.schema_id
JOIN [' + @DBName + '].sys.database_principalsu
ON sp.grantee_principal_id = u.principal_id
WHERE u.name = ''public''
  AND o.name IS NOT NULL
  AND o.is_ms_shipped = 0
ORDER BY o.name;'
--PRINT @SQL;
INSERT INTO @PermsTab
(
DBName
  , PermissionState
  , PermissionName
  , SchemaName
  , ObjName
  , RevokeStmnt
  , GrantStmnt
)
EXECUTE (@SQL)
FETCH NEXT FROM pubperms INTO @DBName;
END
 
CLOSE pubperms;
DEALLOCATE pubperms;
SELECT DBName
 , PermissionState
 , PermissionName
 , SchemaName
 , ObjName
 , RevokeStmnt
 , GrantStmnt
FROM @PermsTab;

That will take care of all of the permissions for the public role in all of the databases, with a slight caveat. I am only checking against that objects that are not flagged as is_ms_shipped. Now, isn’t there also a public role at the server scope? Indeed there is! Let’s also capture those permissions.

SELECT sp.state_desc, sp.permission_name, sp.class_desc, sp.major_id, sp.minor_id, e.name
FROM sys.server_permissions AS sp
  INNER JOIN sys.server_principals AS l
    ON sp.grantee_principal_id = l.principal_id
  LEFT OUTER JOIN sys.endpoints AS e
    ON sp.major_id = e.endpoint_id
WHERE l.name = 'public';

Now, I feel many times better about what could possibly be going wrong with the public role.

If you are in a tightly controlled environment or you are just sick of people doing this sort of thing to your servers, there are more extreme measures that can be taken. You can read about it here or here.

The Wrap

It is amazing what some people will do that just doesn’t make sense. Granting permissions to the public role is one of these cases. That behavior also explains why there are documents and procedures for hardening the public role (here and here).

If necessary, I recommend locking down your public role. It will make your job a little easier and give you better rest at night.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.