Using Azure AD acess token to acess a *specific* database server

Question

Hi,

In my company we're currently using the method described here to sign into our various database servers, by getting an access token from Azure AD and using that as the password.

However we have multiple database servers (e.g. production database, staging database, etc). Currently by running az account get-access-token --resource-type oss-rdbms --query accessToken that token will work as a password for all our database servers. This can make it very easy to accidentally connect to the wrong database and run a query meant for our staging environment accidentally in our production database.

I'm wondering, is there any way of retrieving that access token so it will work only for a specific database server? Essentially, it'd be beneficial for us if one has to do something 'special' to access the production environment compared to the others.

Answer 1

@mhusbyn Welcome to Microsoft Q&A forums.

The access token grants access to a particular resource, Azure PostgreSQL in this case.
Authorization of the access token is done by the database.

In other words, if you are able to access development, production & staging database with the same access token, it means that the user fetching (running the cli commands) the access token, has access to all the databases.

You should have separate roles and Azure AD groups for different environments and you should add users to these groups accordingly.

Production environment

Create Azure AD group ProdDBReadUser from Azure Portal/CLI
Add users who need production DB access to the group
Provision access for the group on staging database

CREATE ROLE "Prod DB Readonly" WITH LOGIN IN ROLE azure_ad_user;  
GRANT azure_ad_user TO "ProdDBReadUser";  

Staging environment

Create Azure AD group StagingDBReadUser from Azure Portal/CLI
Add users who need staging DB access to the group
Provision access for the group on production database

CREATE ROLE "Stagin DB Readonly" WITH LOGIN IN ROLE azure_ad_user;  
GRANT azure_ad_user TO "StagingDBReadUser";  

Now, even though the user acquires a token for the resource, they will not be authorized by the database to access the data.

Please let us know if you have any further questions.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.