Securing Azure SQL Database Access with Microsoft Entra Groups: A Guide to Granting Read-Only Permissions

 Introduction:

In this blog, we will explore how to manage access control to an Azure SQL Database using a Microsoft Entra (Azure AD) group while ensuring that the principle of least privilege is followed. Specifically, we’ll discuss how to provide the members of a Microsoft Entra group with read-only access to a SQL database, ensuring that they can only read data without performing any other database operations. This approach is crucial for securing your database, limiting access to the minimum required, and adhering to security best practices.

---

Table of Contents:

1. What is the Principle of Least Privilege?
2. Understanding Azure SQL Database Access Control
3. Step-by-Step Solution: Granting Read-Only Access
   • Step 1: Map Microsoft Entra Group to the Database
   • Step 2: Assign db_datareader Role to the Group
4. Memory Techniques and Mnemonics
5. Story-Based Memory Technique
6. Conclusion

---

1. What is the Principle of Least Privilege?

The principle of least privilege (PoLP) is a security concept that dictates that users should only have the minimum access necessary to complete their job functions. In the context of Azure SQL Database, this means granting the least amount of permission, so users can only read the data without the ability to modify it.

---

2. Understanding Azure SQL Database Access Control

Azure SQL Database uses a combination of SQL authentication and Azure Active Directory (Microsoft Entra) authentication. In this scenario, we use an Entra group (db_readers) to grant read-only access to the database. This ensures better control over permissions, especially when the group membership dynamically updates based on organizational roles.

---

3. Step-by-Step Solution: Granting Read-Only Access

In order to allow the members of db_readers to read data from db1 while following the principle of least privilege, you need to perform the following two actions:

Step 1: Map Microsoft Entra Group to the Database

The first step is to map the db_readers group to the Azure SQL Database (db1). This allows the group to authenticate against the database using Azure Active Directory.

SQL Command:

sql

CREATE USER [db_readers] FROM EXTERNAL PROVIDER;

This command creates a user in the database that corresponds to the Entra group db_readers.

Step 2: Assign db_datareader Role to the Group

Next, you assign the db_datareader role to the newly created database user. This role allows the group members to read data from all tables and views in the database without modifying the data.

SQL Command:

sql

ALTER ROLE db_datareader ADD MEMBER [db_readers];

By assigning this role, you ensure that the members of db_readers only have read permissions and no access to write, delete, or alter data. This perfectly aligns with the principle of least privilege.

---

4. Memory Techniques and Mnemonics

Mnemonic: "MAP-READ"

• M: Map the Entra group to the database.
• A: Assign the db_datareader role to the group.
• P: Permission granted only for read operations.

This mnemonic can help you quickly remember the steps required to grant read-only access to a Microsoft Entra group.

---

5. Story-Based Memory Technique

Imagine you're running a secure library where visitors (the db_readers group) are allowed to read books (the database). However, you want to make sure they can't take books off the shelves, write in the books, or make changes to the content. To ensure this:

1. First, you issue a library card (mapping the group to the database).
2. Then, you restrict them to a "read-only" room (assigning the db_datareader role).
3. They can read all they want, but they can’t alter anything!

This simple analogy helps you remember that you map the Entra group to the database and restrict access to "read-only" operations.

---

6. Conclusion

In this blog, we've demonstrated how to follow the principle of least privilege by granting members of the db_readers group read-only access to the Azure SQL Database (db1). By mapping the Microsoft Entra group to the database and assigning the db_datareader role, you ensure that the group members have the minimum necessary permissions to read data while preventing them from making any unwanted modifications. With security best practices in mind, this method is highly recommended for organizations looking to manage access control efficiently while maintaining a secure database environment.