Providing a user access to Azure SQL databases using RBAC.

Question

Pulumi · Accepted Answer

To provide a user access to Azure SQL databases using Role-Based Access Control (RBAC), you'll define a role assignment for the user with the appropriate role definition that grants the desired level of access. Azure SQL supports creating users associated with Azure Active Directory identities, and using RBAC, you can control what those users can do at both the server and database scope.

We'll accomplish our goal with Pulumi by doing the following:

1. Create an Azure SQL Server and Database.
2. Assign a role to an Azure AD User for the Azure SQL Database using the `AzureADUser` resource from the Pulumi MSSQL package.

The following TypeScript program defines an Azure SQL Server and Database and then grants a user the `db_datareader` role, which allows read access to all database data. Replace the `userObjectId`, `resourceGroupName`, `sqlServerName`, and `databaseName` with the actual values you intend to use.

```typescript
import * as pulumi from "@pulumi/pulumi";
import * as sql from "@pulumi/azure-native/sql";
import * as resources from "@pulumi/azure-native/resources";
import { AzureadUser } from "@pulumi/mssql";

// Replace these variables with actual values
const userObjectId = "00000000-0000-0000-0000-000000000000"; // Azure AD User Object ID
const resourceGroupName = "my-resource-group";
const sqlServerName = "my-sqlserver";
const databaseName = "my-database";

// Create a resource group, if you don't have one already
const resourceGroup = new resources.ResourceGroup(resourceGroupName);

// Create an Azure SQL Server
const mySqlServer = new sql.Server(`${sqlServerName}`, {
    resourceGroupName: resourceGroup.name,
    location: resourceGroup.location,
    serverName: sqlServerName,
    administratorLogin: "sqladmin",
    administratorLoginPassword: "strongpassword" // Replace with a more secure password
});

// Create a SQL Database in the SQL Server
const mySqlDatabase = new sql.Database(`${databaseName}`, {
    resourceGroupName: resourceGroup.name,
    serverName: mySqlServer.name,
    databaseName: databaseName,
    location: mySqlServer.location
});

// Grant an Azure AD User 'db_datareader' access to the SQL Database
const azureADUser = new AzureadUser("myAzureADUser", {
    userObjectId: userObjectId,
    databaseId: mySqlDatabase.id,
    // 'db_datareader' is a built-in role that grants read access to all data in the database
    roles: ["db_datareader"]
});

// Export outputs that you might want to access
export const sqlServerFqdn = mySqlServer.fullyQualifiedDomainName;
export const sqlDatabaseId = mySqlDatabase.id;

```

### Explanation

- **Resources and Resource Group**: We first create a Resource Group to manage the lifecycle of all of our Azure resources together. Then, we create an SQL Server and Database within this resource group.

- **Azure SQL Server**: We deploy an instance of an Azure SQL Server specifying the `serverName`, `location`, `administratorLogin`, and `administratorLoginPassword`.

- **Azure SQL Database**: Inside the SQL Server, we deploy a SQL Database where `databaseName` will be the name of the database that you want.

- **`AzureadUser` Resource**: Using the `AzureADUser` Pulumi component resource from the MSSQL package, we assign the specified Azure AD user account with read privileges on the database created earlier (represented as `db_datareader` role). `userObjectId` is the unique identifier for the AAD user you wish to grant access.

Note: The username and password are hardcoded for demonstration purposes, but for a real-world scenario, you should securely manage these credentials using a service like Azure Key Vault. Be sure to replace placeholders with appropriate values before running the program.

Remember that the `AzureADUser` assignment will not succeed unless the specified user is a member of the Azure Active Directory tenant associated with the subscription in use. Also, actual role assignments in Azure can take some time to propagate.

To run this Pulumi program, you must have the Pulumi CLI installed, have an Azure account, and have the Azure CLI installed and logged in. Make sure to set up the required configuration settings for the Azure provider using Pulumi before executing the code.