Azure SQL database - application user setup

Question

I am using Azure SQL database with sharding (single tenant approach). So, when the application connects to DB it goes into Shard and creates a proper connection to the tenant's DB (tenant is identified by login name). However, we've been using a server admin credentials for that on a development stage. Now, I'd like to create a separate application user with much more limited permissions compared to server admin. In a very general case, what I want is to have a user that can connect to the Shard Map and figure out a connection string to any of the Shards, but have different permissions for each of the shards. For example, some application user may need to be able to connect to DB_1 with full read-write permissions, to DB_2 with read-only permissions and no permissions to connect to DB_3.

In a simpler case I just need a user that doesn't have any update permissions to ShardMap and other internal databases, but has a normal read/write/execute access to all tenant databases(shards).

I was googling around and din't find any good recipe how to do that, what are the best practices, etc. I'd appreciate if someone could answer me or point to a docs. Thank yuo!

Answer 1

In each database create a role for the Application Users, and grant the minimal permissions needed for the application to run. Granting permissions on the Schema level is a good choice here, as you don't have to manage object-level permissions.

create role ApplicationUsers;
grant select, insert, update, delete, execute on schema::dbo to ApplicationUsers;

Then if you want a single identity to access all the databases, create a login with a password. Then in each Tenant database create a user mapped to that login.

--create a server-level Login
create login AppUser with Password ='asdfAds01980(*)(*)(#&$)#@';
--add a user mapped to that login in each database 
create user AppUser for login AppUser;
alter role ApplicationUsres add member AppUser;

Or create a user in each database with a different password or a database user mapped to an Azure Active Directory identity.

create user AppUser with Password ='asdfAds01980(*)(*)(#&$)#@';
alter role ApplicationUsers add member AppUser;

or

create user [tenant123user@mydomain.onmicrosoft.com] from external provider;
alter role ApplicationUsers add member [tenant123user@mydomain.onmicrosoft.com];