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

Question

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

Answer 1

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

Answer 2

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