question

SiegfriedHeintze-9929 avatar image
SiegfriedHeintze-9929 asked ·

How to Generate Primary Key and store in AAD Token for Future Sessions?

Let's assume I have a MSSQL Table called projects and there is a one-to-one relationship between users and projects and I'm using authenticated calls to CRUD functions implement as Azure Functions to manipulate this table. (That is to say, the Azure functions are protected by AAD).

Let's further assume that I am using the IDENTITY feature of MSSQL to generate primary keys (sequential integers) so each authenticated user has a unique integer.

(1) How do I associate a newly authenticated user with a newly created primary key so that AAD will provide that primary key for me in the token the next time he/she creates a new session?

(2) Do I need to be concerned about malicious users guessing at future and past primary keys and accessing the projects (rows) of other users via my AAD authenticated Azure functions?

Thank you

Siegfried

azure-active-directoryazure-sql-database
1 comment
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for asking such a detailed question. I am inquiring to see if this functionality can be implemented. If you have any additional questions, please detail those while I engage the product group and present this use case.

0 Votes 0 · ·
Mike-Ubezzi-MSFT avatar image
Mike-Ubezzi-MSFT answered ·

HI Siegfried,

Here is the approach that was offered:

The path I’d explore is rather, for (1), insert a new row in the projects table grabbing AAD identity and storing it in a column (with a UNIQUE constraint if they want to enforce the one-to-one relationship). They can then have either an IDENTITY column or a SEQUENCE to generate the sequential integers during the insert.
For (2), they can use Row Level Security to create a policy where an AAD authenticated user can only access the rows he owns.

If there is a specific piece of functionality that you are seeking that is currently not available, or you feel would be a great feature to have please post this to the UserVoice forum so others can up vote and comment on what is being suggested to the product group.

Regards,
Mike

Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SiegfriedHeintze-9929 avatar image
SiegfriedHeintze-9929 answered ·


grabbing AAD identity

(1) Can you elaborate on this please? I assume you mean a field in the token that I can see with http://jwt.ms? I'm looking at a token now and feeling a bit overwhelmed as there are so many fields. Perhaps you mean the OID?

(2) Hmm... "Row Level Security" is a new one for me. Is this a feature exclusive to Azure SQL Svr or can it be used with other databases like Cosmos and MySQL?

Thank you
Siegfried

2 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zagato36:
I'm looking forward to your response on #1.

Regarding #2: I've been reading about Row Level Security (RLS). Consider RLS for a B2C site where Facebook, Google and Github users can authenticate using AAD B2C.
Would that require that I execute a SQL "Create User" command for every new user?

Is this advisable? Is MS SQL Server designed to accommodate thousands of users? I'm thinking RLS might be only for internal web sites...

Thanks


Siegfried

0 Votes 0 · ·
Mike-Ubezzi-MSFT avatar image Mike-Ubezzi-MSFT SiegfriedHeintze-9929 ·

Hi Siegfried,

Yes, the id_token value that can be decoded and stored as an OID. With Azure AD B2C, which supports OpenID Connect, you would be able to extract unique user information (See example: AAD B2C Web Applications) and store this in the database with a UNIQUE constraint on email address, as an example, to mitigate duplicating users.

I do not think you would need to execute CREATE USER in the database, as you would leverage Azure AD Auth. Users would be added to a security group at the directory level where there is a one-time set-up to allow access to the database for the security group. (See: Azure AD features and limitations). The OpenID Connect information still needs to be passed for the UNIQUE constraint to be enforced.


0 Votes 0 · ·