Checking user roles yields exception RSS

• • Reply
  

  Aquiace

  Member

  4 Points

  5 Posts

  Checking user roles yields exception

  Jul 13, 2010 06:48 PM|LINK

  This appears to be a SQL exception.  (I'm using MySQL 5.01)

  It comes up when trying to determine the user roles.

  Line 2638:        subscription_dateIsr.Value = GetSubscriptionDate(sUser, "isr");
  Line 2639:
  Line 2640:        string[] userRoles = Roles.GetRolesForUser(sUser);
  Line 2641:        // just in case current logged in user/client doesn't have a role
  Line 2642:        if(userRoles.Length < 0)

  It then throws the following exception:

  User does not have permission to perform this action.

  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
  
  Exception Details: System.Data.SqlClient.SqlException: User does not have permission to perform this action.

  [SqlException (0x80131904): User does not have permission to perform this action.]
     System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6244425
     System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
     System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
     System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
     System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +248
     System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +6260362
     System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6260328
     System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +354
     System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +300
     System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +54
     System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6261592
     System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +81
     System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1657
     System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +88
     System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6265031
     System.Data.SqlClient.SqlConnection.Open() +258
     System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +82
     System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +3986458
     System.Web.Security.SqlRoleProvider.GetRolesForUser(String username) +1223
     System.Web.Security.RolePrincipal.GetRoles() +226
     System.Web.Security.Roles.GetRolesForUser(String username) +549
     sub_Main._processClientMainPage() in f:\keystock-local\sub\Main.aspx.cs:2640
     sub_Main.Page_Load(Object sender, EventArgs e) in f:\keystock-local\sub\Main.aspx.cs:98
     System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +25
     System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +42
     System.Web.UI.Control.OnLoad(EventArgs e) +132
     System.Web.UI.Control.LoadRecursive() +66
     System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2428

  
  The thing that makes me wonder (I'm fairly new to ASP, but not .NET in general), it's using Forms authentication... sort of.  It never actually calls the FormsAuthentication.Authenticate function.  It just calls the RedirectFromLoginPage and compares the user name and password to an internal list of logins inside the database.  The connection string continues to use the Root login and password for all reading and writing.  So wouldn't that mean it HAS permission (being the Root login)?

  This is what makes it more baffling to me:  It's using .NET roles to load different elements of a page:

  if (!IsPostBack)
          {
              if (User.IsInRole("Administrator"))
              {
                  nav.Visible = false;
  
  ..etc..
  
  else if (User.IsInRole("Analyst"))
              ... change visuals...
  
  and so forth

  
  So if it's using the .NET user roles, but not using the authentication, how the hell does it determine whether you have that role?  Is that why it's throwing the exception?  When I view the user name, it is the login name from the MySQL login table.  But as far as I can see, there is no .NET user with that name.

  I'm fairly new to ASP and not worked with IIS that much before.  I've used google for the most part to get myself up to speed, but I simply can't resolve this.  It works fine on the live site, but not on a local test server.  I'm running out of ideas, so if anyone has any insight to resolve this exception, I would be deeply indebted.

  Thanks

  user roles mysql exception ASP .net forms authentication asp.net 2.0 authentication

  • Moved by mbanavige on Jul 13, 2010 07:35 PM
• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: Checking user roles yields exception

  Jul 13, 2010 08:07 PM|LINK

  It looks like the user context under whom you are trying to execute the stored procedure to get the user roles does not have rights to execute the sql command

  I think you may have to give db_executor role or grant access on that procedure.

  correct me if I am wrong

  Santhosh
  • Marked as answer by Mikesdotnetting on Oct 30, 2011 09:44 AM
• • Reply
  

  Aquiace

  Member

  4 Points

  5 Posts

  Re: Checking user roles yields exception

  Jul 14, 2010 01:01 AM|LINK

  Ok, this is confusing.  I checked the live MySQL database and the only user is Root which has all privileges.

  When it calls User.IsInRole, it's checking the SQL database role?  That can't be right.

  The User object is a type Page.User which derives System.Security.Principal.IPrincipal.  It seems to be talking about the Server Role and gives no mention of SQL databases.  It's in the namespace System.Web.UI.

  So when it says IsInRole(), does that mean MySQL database role or the windows user role?