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

mhusbyn 21 Reputation points
2021-09-03T13:57:10.603+00:00

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.

Azure Database for PostgreSQL
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
20,087 questions
0 comments No comments
{count} votes

Accepted answer
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-09-06T05:53:03.997+00:00

    @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.

    0 comments No comments

0 additional answers

Sort by: Most helpful