Thursday, September 2, 2021

Server roles for Azure SQL Database Server: Database Management without admin-access

Server roles for Azure SQL Database Server: Database Management without admin-access

 

Until now, in Azure SQL Database, to gain access to server-wide information like system-wide wait-stats, resource stats etc., the Server Admin or AAD Admin was the only account with sufficient permissions since server-level permissions are not grantable in SQL Database.

Today I am happy to announce that we are bringing built-in roles that in effect hold permissions applicable to all databases including databases residing in elastic pools a on a logical server for Azure SQL Database. This extends to the logical master-database which contains certain system-wide information. We are starting with 3 new roles:

  1. ##MS_DefinitionReader##
  2. ##MS_ServerStateReader##
  3. ##MS_ServerStateManager##

Benefit

The new server-roles that can be assigned to server enable customers to assign and delegate job functions for server-wide metadata access and access to certain management commands without requiring Server Admin or AAD Admin privileges. This helps comply with the Principle of Least Privilege and implement role separation.

 

Technical background

Server-roles are not a new concept in the SQL Server universe, but for Azure SQL Database, our PaaS service offering, the server as such is just a logical concept. More on this here: What is a server in Azure SQL Database and Azure Synapse Analytics? | Microsoft Docs and Azure SQL databases in logical servers, elastic pools, and managed instances - Microsoft Tech Community.

Because of this architectural difference, the same mechanics as for Managed Instance or SQL Server in a VM do not apply: Databases residing on the same logical server may not reside on the same physical server. This poses some challenges, but I am happy that we can bring an almost identical experience that works for the other Service offerings as well: Server level roles with permissions that have effects on any database on the logical server.

Each of these new server-roles includes a specific set of permissions. In SQL Database those permissions cannot be assigned individually on the server scope but using those roles one can grant them to a SQL Login. This is a new method to grant server permissions across a logical server and all databases hosted on that logical server in Azure SQL Database, aside from assigning Server/AAD Admin.

In the following let’s have a look at each role individually:

 

##MS_DefinitionReader##

The ##MS_DefinitionReader##-role predominantly will be useful for read-only server-auditing where an auditor will need to see the contents of static system metadata such as Login/principal names, Firewall settings, permissions of principals and of course object definition (code). It will often be combined with membership in the ##MS_ServerStateReader##-role.

It has VIEW ANY DEFINITION permission on the server, which covers and hence includes the VIEW DEFINITION permission on any database that a principal in that role has access to.

Here the results of sys.fn_my_permissions(NULL, 'SERVER') – called from the master-database and sys.fn_my_permissions(NULL, 'DATABASE') – called from a user-database:

AndreasWolter_6-1630624973250.jpeg

 

Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION, respectively VIEW DEFINITION on any database on which the member of this role has a user account.

 

##MS_ServerStateReader##

The ##MS_ServerStateReader##-role is ideal for read-only performance Monitoring and troubleshooting access. It will often be combined with membership in the ##MS_DefinitionReader##-role to see the actual names and definition (code) of objects involved.

It has VIEW SERVER STATE-permission on the Server which covers and hence includes the VIEW DATABASE STATE on any database that a principal in that role has access to.

Here the results of sys.fn_my_permissions(NULL, 'SERVER') – called from the master-database and sys.fn_my_permissions(NULL, 'DATABASE') – called from a user-database:

AndreasWolter_7-1630624973253.jpeg

 

Members of the ##MS_ServerStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER STATE, respectively VIEW DATABASE STATE on any database on which the member of this role has a user account.

This role allows querying several DMVs that exist solely in the master database such as sys.resource_stats that specifically contain information concerning the whole server, or sys.elastic_pool_resource_stats for all elastic pools on a logical server.

To make this role more useful and generally help customers comply with the Principle of Least Privilege and implement proper role-separation, we changed the permission requirements for several DBCC commands. Those formerly requiring elevated privileges are now covered by VIEW SERVER STATE – the permission which this role holds:

  • DBCC INPUTBUFFER()
  • DBCC SQLPERF (LOGSPACE)
  • DBCC SQLPERF (NETSTATS)

 

##MS_ServerStateManager##

The ##MS_ServerStateManager##-role is useful for delegating management tasks without requiring to assign full admin permissions.

It has ALTER SERVER STATE on the server. ALTER SERVER STATE also covers the VIEW SERVER STATE permission on the server and by that the VIEW DATABASE STATE on any database that a principal in that role has access to.

Here the results of sys.fn_my_permissions(NULL, 'SERVER') – called from the master-database and sys.fn_my_permissions(NULL, 'DATABASE') – called from a user-database:

AndreasWolter_8-1630624973257.jpeg

 

Members of the ##MS_ServerStateManager## fixed server role have the same permissions as the ##MS_ServerStateReader## role. In addition, it holds the ALTER SERVER STATE permission, which allows access to several management operations, such as: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF():

  • DBCC FREEPROCCACHE;
  • DBCC FREESYSTEMCACHE ('ALL');
  • DBCC SQLPERF(); with the option to clear the wait- and latch-stats (this is only available on the master-database and has no effect on the wait-stats of the user databases which reside on different physical instances.)
    • DBCC SQLPERF("sys.dm_os_wait_stats", CLEAR);
    • DBCC SQLPERF("sys.dm_os_latch_stats", CLEAR);

 

Limitations

  • Role assignments may take up to 5 minutes to become effective. In addition, for existing sessions, changes to server role assignments do not take effect until the connection is closed and re-opened. This is due to distributed architecture between the master database and other databases on the same logical server.
    Partial workaround: to reduce the up to 5-minute waiting period and ensure that server role assignments are current in a database, a Server Admin/AAD Admin can run DBCC FLUSHAUTHCACHE in the user database(s) on which the login has access. Currently logged on users still have to reconnect after this for the membership-changes to take effect on them.
  • These roles can be assigned to SQL Logins only – AAD Logins are not yet supported.

 

Further references:

 

Acknowledgement

Big kudos to all the team-members involved in this seemingly small addition that is much more complex than apparent: Maciej Szeszko, Steven Gott, Nikolas Ogg from engineering and Dimitri Furman for being a great helper and advisor

 

Posted at https://sl.advdat.com/3yGFn0e
Labels: ,