locked
How to Select a database connection string after login? RRS feed

  • Question

  • User1700504264 posted

    Hello

    I am initiating a new web application, it will be a cloud service.

    Every registered user will use his dedicated database. and he can create logins and accounts for other users (in his company) in the same database.

    The logins and accounts of super users (database owners) are stored in one shared database.

    If a <g class="gr_ gr_420 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="420" data-gr-id="420">super user</g> signed in, then the application reads his dedicated database name from the shared database, then generates a connection string for him.

    All his next operations should be performed on his dedicated database.

    Is there a best practice or approach for doing this?

    I am planning to store that database name for each user in the AspNetUsers table, read it after login, then make it the default connection string for the user. is this a good approach?

    How could I make it a default connection string for a logged in user? should I use cookies? session variable?

    Any Ideas, please?

    thank you

    Monday, October 1, 2018 6:23 PM

All replies

  • User475983607 posted

    I don't understand the approach.  Usually the application limits access to secured resources.  Why change the connection string?  Is to make absolutely sure non admins cannot execute DDL?

    Are you trying to build a Database control panel?  Why centralize only the admin logins?

    Monday, October 1, 2018 6:38 PM
  • User1700504264 posted

    I don't understand the approach.  <g class="gr_ gr_46 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="46" data-gr-id="46">Usually</g> the application limits access to secured resources.  Why change the connection string?  Is to make absolutely sure <g class="gr_ gr_47 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="47" data-gr-id="47"><g class="gr_ gr_44 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="44" data-gr-id="44">non</g> admins</g> cannot execute DDL?

    Are you trying to build a Database control panel?  Why centralize only the admin logins?

    Hello

    I am trying to implement (Single Application / Multiple databases)

    The application should switch between databases according to the login account

    Monday, October 1, 2018 7:11 PM
  • User475983607 posted

    I am trying to implement (Single Application / Multiple databases)

    The application should switch between databases according to the login account

    I still do not understand the design of the shared super user database.  I would create a central login for all users not just the super admin.   

    Add a application setting (or configuration) that has a dictionary of all the database connections keyed by the company name.  Each user is assigned a claim/role that has the company name.   That will allow you to get the connection string by company name.  The key can be company + role if you need elevated user rights.

    Monday, October 1, 2018 8:18 PM
  • User61956409 posted

    Hi 0591666959,

    I am trying to implement (Single Application / Multiple databases)

    The application should switch between databases according to the login account

    It seems that you map each user to a dedicated database and persist that mapping in your shared database, which could be approach to achieve your requirement. Currently you store dedicated database name for each user, if you create a Database user for each application user with same Login name and password, you can also directly store connection string for each user in your shared database table. 

     Besides, if possible, you can use Multi-Tenants database architecture, your users belong to same tenant will share same connection string. 

    With Regards,

    Fei Han

    Tuesday, October 2, 2018 3:04 AM
  • User1700504264 posted

     Besides, if possible, you can use Multi-Tenants database architecture, your users belong to <g class="gr_ gr_6 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="6" data-gr-id="6">same</g> tenant will share same connection string. 

    is there a step by step tutorial on this?

    Thank you

    Tuesday, October 2, 2018 9:51 AM
  • User61956409 posted

    Hi 0591666959,

    You can refer to this article to know multi tenants Database architecture:

    https://www.codeproject.com/Articles/51334/Multi-Tenants-Database-Architecture 

    With Regards,

    Fei Han

    Wednesday, October 3, 2018 6:07 AM
  • User1700504264 posted

    <g class="gr_ gr_9 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="9" data-gr-id="9">Hi</g> 0591666959,

    You can refer to this article to know multi tenants Database architecture:

    https://www.codeproject.com/Articles/51334/Multi-Tenants-Database-Architecture 

    With Regards,

    Fei Han

    Hi Fei Han

    Thank you, Yes that link is very useful, <g class="gr_ gr_121 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="121" data-gr-id="121">actually</g> I read it

    I aim to implement the Multi-Tenant structure (Dedicated database/per tenant)

    in addition to a master database, which will map each tenant to a specific database

    I just need an example or a <g class="gr_ gr_479 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="479" data-gr-id="479">tutorial</g> how to implement that by code!

    I understand the concept, but I am looking how could I implement it in <g class="gr_ gr_540 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="540" data-gr-id="540">a asp</g>.net web application

    Thank you

    Wednesday, October 3, 2018 10:51 AM
  • User475983607 posted

    I understand the concept, but I am looking how could I implement it in a asp.net web application

    We have no idea what data access you are using or the type of application; Web Forms, MVC, Razor pages.

    I would centralize the login, as I suggested in the first post.  Use Identity to handle user login and claims/roles and add a company table that joins the user account to a company.  Simply assign the company to the user claim on a successful login.  Storing the connections strings can be as simple as adding the connections string to the web.config or appsetting.json (depending on the app) keyed by company name (or ID).  Again, this was suggested above.

    From here we need to know the type of application you are building.  For example MVC you can use routes to skin the URL and filters to get the connection string in the controller. If you are building ASP.NET Core, you'll build middleware.

    Anyway, I recommend that you at least try to create design.

    Wednesday, October 3, 2018 11:32 AM
  • User1700504264 posted

    Simply assign the company to the user claim on a successful login

    Hi

    This is exactly what I am looking for (How could I assign the database to the user after login?

    I use <g class="gr_ gr_176 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="176" data-gr-id="176">configure</g> the connection string in the web.config in previous applications, I need an example on how could I change the connection string after login?

    It is a web forms application 

    Thank you

    Wednesday, October 3, 2018 3:14 PM
  • User475983607 posted

    This is exactly what I am looking for (How could I assign the database to the user after login?

    I use configure the connection string in the web.config in previous applications, I need an example on how could I change the connection string after login?

    Use an API like ASP Identity to assign a claim to the user.  The claim value can be the user's company or any unique identifier.  I suggest a claim because claims are cached in the the user's auth cookie and easily retrieved. 

    var userClaims = await UserManager.GetClaimsAsync(user.Id);
    Claim companyClaim  = userClaims.Claims.Where(c => c.Type = "CompanyClaim").FirstOrDefault();

    Then just add Connections string nodes to the web.config using the key.  the value of the "companyClaim" Claim can be WidgetCompany, FooBarCompany, or AdventureWorksCompany.

    <connectionStrings>
    	<add name="WidgetCompany" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=WidgetCompany;Integrated Security=True" />
    	<add name="FooBarCompany" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=FooBarCompany;Integrated Security=True" />
    	<add name="AdventureWorksCompany" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=AdventureWorksCompany;Integrated Security=True" />
    </connectionStrings>

    Then simply get the claim value and use it to get the connection string when you are about to make a DB connection to execute SQL.

    string connectionString = ConfigurationManager.ConnectionStrings[companyClaim.Value].ConnectionString;

    Of course when the user account is created is when you'll assign the correct company.  I assume the super user will do this bit of work.

    I recommend clicking in the "Learn" link above and going through a few tutorials if you are new to Identity.

    https://www.asp.net/identity

    Wednesday, October 3, 2018 5:01 PM