Databases
StackOverflow2013
Postsdbo
POMVC3 Multiple Users/Connection Strings
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMVC3 Multiple Users/Connection Strings
    primarykey
    data
    text
    <p>I want to make sure that I’m on the right track with this one.</p> <p>I have a site which has two backend areas, one for school and one for admin. I would like to authenticate the areas so that the school area has less DB privileges than admin. To do this my idea is to have two logins in Sql Server Management Studio School and Admin, which I can map certain roles to. Maybe the school section will have only read access and the admin read and write etc..</p> <p>Please can somebody advise me as to the best way to go about implementing this. Do I need multiple connection strings (one for admin and one for school)? Can this be done using Form Authentication?</p> <p>I am currently connecting to an existing database that I have created using Sql Server Management Studio and already have a means of logging in which will set the FormsAuthentication and this works well in the sense that I can add the authorize attribute to my school backend controller which stops the pages that require a logged in school from being displayed unless a school is logged in. The question is really how would I make this more specific so that only a school login is allowed to see this area and not an admin member who has logged in as they would also have set the FormsAuthentication. </p> <p>I have done a lot of googling in but have not found anything specific to my problem, hence this post. </p> <p>I can produce code if needed and am not asking for somebody to write it for me but a theoretical explanation of how to solve this kind of security model.</p> <p>Thanks in advance for your help.</p> <p><strong>Working Solution using a Custom Role Provider</strong></p> <p>Account Controller code (Now one for both Admin and School)</p> <pre><code>[HttpPost] public ActionResult LogOn(LogOn model, string returnUrl) { if (ModelState.IsValid) { if (Membership.ValidateUser(model.UserName, model.Password)) { FormsAuthentication.SetAuthCookie(model.UserName, model.RememberMe); if (Url.IsLocalUrl(returnUrl) &amp;&amp; returnUrl.Length &gt; 1 &amp;&amp; returnUrl.StartsWith("/") &amp;&amp; !returnUrl.StartsWith("//") &amp;&amp; !returnUrl.StartsWith("/\\")) { return Redirect(returnUrl); } else { // Now it's our job to route the user to the correct place. Ask our DB Helper to tell // us what kind of user they are and route accordingly. string sPage = "Index"; string sController = "Home"; var Role = DBHelperFunctions.Instance().GetRoleForUser(model.UserName); switch (Role.role_name) { case LanguageSchoolsConstants.m_RoleAdministrator: { sController = "AuthorisedAdmin"; } break; case LanguageSchoolsConstants.m_RoleSchool: { sController = "AuthorisedSchool"; } break; } return RedirectToAction(sPage, sController); } } else { ModelState.AddModelError("", "The user name or password provided is incorrect."); } } </code></pre> <p>DB Helper Function used in above method:</p> <pre><code>public role GetRoleForUser(string sUserName) { // Should only ever have one role... role Role = (from roles in DBModel.roles join userrole in DBModel.user_role on roles.role_id equals userrole.role_id join users in DBModel.users on userrole.user_id equals users.user_id where users.username == sUserName select roles).FirstOrDefault(); return Role; } </code></pre> <p>Web.config changes to allow role providor to be invoked:</p> <pre><code>&lt;roleManager defaultProvider="RoleProvider" enabled="true" cacheRolesInCookie="true"&gt; &lt;providers&gt; &lt;clear /&gt; &lt;add name="RoleProvider" type="namespace.Models.Security.CustomRoleProvider" /&gt; &lt;add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="Entities" applicationName="/" /&gt; &lt;add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" /&gt; &lt;/providers&gt; &lt;/roleManager&gt; </code></pre> <p>My Role Provider</p> <pre><code> using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Security; namespace neamsepace.Models.Security { public class CustomRoleProvider : RoleProvider { public override void AddUsersToRoles(string[] usernames, string[] roleNames) { throw new NotImplementedException(); } public override string ApplicationName { get { throw new NotImplementedException(); } set { throw new NotImplementedException(); } } public override void CreateRole(string roleName) { throw new NotImplementedException(); } public override bool DeleteRole(string roleName, bool throwOnPopulatedRole) { throw new NotImplementedException(); } public override string[] FindUsersInRole(string roleName, string usernameToMatch) { throw new NotImplementedException(); } public override string[] GetAllRoles() { throw new NotImplementedException(); } public override string[] GetRolesForUser(string username) { using (DB db = new DB()) { string[] RolesForUser = null; user User = db.users.FirstOrDefault(u =&gt; u.username.Equals(username, StringComparison.CurrentCultureIgnoreCase) || u.email.Equals(username, StringComparison.CurrentCultureIgnoreCase)); var roles = from dbroles in db.roles join userroles in db.user_role on dbroles.role_id equals userroles.role_id join users in db.users on userroles.user_id equals users.user_id where users.user_id == User.user_id select dbroles.role_name; if (roles != null) { RolesForUser = roles.ToArray(); } else { RolesForUser = new string[] { }; } return RolesForUser; } } public override string[] GetUsersInRole(string roleName) { throw new NotImplementedException(); } public override bool IsUserInRole(string username, string roleName) { using (DB db = new DB()) { bool bUserInRole = false; user User = db.users.FirstOrDefault(u =&gt; u.username.Equals(username, StringComparison.CurrentCultureIgnoreCase) || u.email.Equals(username, StringComparison.CurrentCultureIgnoreCase)); var roles = from dbroles in db.roles join userroles in db.user_role on dbroles.role_id equals userroles.role_id join users in db.users on userroles.user_id equals users.user_id where users.user_id == User.user_id select dbroles.role_name; if (User != null) { bUserInRole = roles.Any(r =&gt; r.Equals(roleName, StringComparison.CurrentCultureIgnoreCase)); } return bUserInRole; } } public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames) { throw new NotImplementedException(); } public override bool RoleExists(string roleName) { throw new NotImplementedException(); } } } </code></pre> <p>Controller that uses Authorize.</p> <pre><code>[Authorize(Roles = LanguageSchoolsConstants.m_RoleAdministrator)] public class AuthorisedAdminController : Controller { // // GET: /AuthorisedAdmin/ public ActionResult Index() { return View(); } } </code></pre> <p>I really hope that this helps anybody please feel free to comment!</p> <p>Thanks for all your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USYos
    1. USYos
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI see `Membership.ValidateUser` which means you're using a membership provider. What provider are you using? And more importantly, how do you currently manage your users (i.e. students, professors)?
      singulars
      1. POMVC3 Multiple Users/Connection Strings
      1. USDaniel Liuzzi
    2. COI have created my own which inherits from MembershipProvidor. I have called it SchoolMembershipProvider and I will post the code above that it contains. I also have a SchoolMembershipUser which inherits from MembershipUser. I will also post the code for that above. I hope that it helps illustrate what I am doing at the moment. Thanks.
      singulars
      1. POMVC3 Multiple Users/Connection Strings
      1. USYos
    3. COIf you feel comfortable coding a custom membership provider, then I would advice you to implement a custom role provider as well. The idea is basically the same; you code it, hook it up in your `web.config`, and that's it. All role checks will be performed against the roles your custom provider returns.
      singulars
      1. POMVC3 Multiple Users/Connection Strings
      1. USDaniel Liuzzi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload