Manage Azure SQL database authorization Security

Manage Azure SQL database authorization Security

When planning the deployment of Microsoft Azure SQL Database, you must consider all the security measures that need to be implemented in the deep defense policy. In the previous article, we have introduced in detail one aspect of implementing firewall-based protection measures. In addition, we also outline the built-in verification, authorization, and encryption features of this platform as a service PaaS. Now it is time to learn more about some authorization methods that support fine-grained data access control. They can control at most various database objects and statement types.

To a large extent, the authentication and authorization mechanisms integrated with Azure SQLDatabase are based on the same principles used by mature SQL Server instances. They can be deployed on internal networks, it can also be deployed on the Azure IaaS virtual machine. However, there are some important differences worth noting between them. The most important aspect of authentication is the lack of support for Windows integrated authentication. In fact, every time you establish an SQL Database connection, users must specify their logon identities. This method is the same as the traditional SQL Server authentication method. Even so, there is an important small problem. Specifically, the password is reset, which will trigger automatic re-verification in local use scenarios, resulting in session interruption, that is, the current session must be interrupted, in addition, you can continue to use Azure SQL Database After reconnecting. This method is selected to eliminate the negative impact of automatic reconnection on performance, which is especially important for cloud-based services .)

From the perspective of authorization, Azure SQLDatabase implements a method that is more suitable for use at the server level. The most obvious reason is that you cannot use the sa Logon account with the highest permissions, the corresponding sysadmin SQL Server role, and all other fixed server roles ). On the contrary, when you manage a logical SQL Server hosting each SQL Database, including the Azure platform structure that represents a Database management unit, you must use two pre-defined roles in the primary Database, including:

• Loginmanager-assigned sufficient permissions to create and manage logon accounts, rather than the fixed server role of securityadmin)
• Dbmanager-assigned permissions to create and manage databases, rather than the fixed service role of dbcreator)

This difference is reflected in the Management of service-level Security. It can prevent administrators from relying too much on the graphical interface of the Security folder in Object Explorer of the SQL Server Management Studio, forces them to execute corresponding T-SQL statements when they connect to the primary database. Although SQL ServerManagement Studio automatically redirects the connection to the primary database and generates an SQL logon template in the query window to achieve transparent logon ).

User Database roles correspond to existing roles implemented by traditional SQL Server and include the following roles:

• Db_accessadmin is assigned the permission to create and manage database users.
• Db_backupoperator is assigned the permission to back up the database.
• Db_datareader is assigned the permission to read data from all database tables and views.
• Db_datawriter is assigned the permission to write data to all database tables and views.
• Db_ddladmin is assigned the permission to create and manage objects in the database.
• Db_denydatareader rejects the permission to read data from any table or view in the database.
• Db_denydatawriter rejects the permission to write data to any table or view in the database.
• Db_owner is assigned the permission to perform all database configuration and management.
• Db_securityadmin is assigned permissions to manage database role members and permissions.

If you want to further segment access permissions, you can choose to use mode-level and Object-level security, just like the database role, which is identical to the local database management familiar to the Administrator. Specifically, you can use GRANT, REVOKE, and DENY T-SQL statements to control permissions at each schema, object instance, or statement level. Remember that DENY has a higher priority than explicitly assigned or role-based permissions.

To create a Logon account, you need to create a master database connection session. To create a user account, you need to directly connect to the target database. This is important because the same session cannot be switched to the database context it would have been normally possible by executing the USEdatabase T-SQL statement)-instead, you have to create a separate session for each database that needs to be managed. The initial login account is the Server-level primary login account.) users with the same name in the primary database are automatically created when the SQL Server instance is initialized, this applies to both AzureManagement Portal, Azure PowerShell module, and corresponding rest api initialization. From the authorization perspective, this Logon account is unique because it implicitly assigns the permissions associated with the loginmanager and dbmanager roles, even if it is not actually a member of these roles ). In addition, you can use it to connect to any database on the same logic server.

Therefore, you can use the create login T-SQL statement to CREATE more LOGIN accounts to query the sys. SQL _logins view of the primary database to list all LOGIN accounts ). To use the account information to connect to any database, including the primary database, you must first create a corresponding user account on the database. In fact, if you want to specify a new member of the loginmanager or dbmanager role, you must first use a server-level primary Logon account to log on to the primary database and create a new Logon account, then create a user associated with this login account to use CREATEUSER (...) from login T-SQL statement), and then execute the sp_addrolemember stored procedure. Similarly, if you want these login accounts to be used to connect to or manage any user database, you must first connect to the database, creating an associated user account on the database also uses T-SQL statements), and then execute the sp_addrolemember stored procedure to assign the newly created user to one or more database-level roles. However, if dbmanager members need to connect to the data they have created, they are not restricted by this requirement, because they are implicitly associated with the dbo user account, which means it is already a member of the db_owner role ).

This is the summary of the Azure SQLDatabase data protection management method, which focuses on using permissions as an additional measure to defend against unauthorized access. In subsequent articles, we will continue to introduce SQL Server running in cloud and hybrid environments.