I am working on a Windows application that utilizes a MySQL database for its user authentication and data storage.

Within the application, the highest level of user (Administrator) can create and delete other users. Because of this, the application itself connects to the database with a MySQL user which has SELECT, INSERT, DELETE, and UPDATE permissions for the users table.

It would be trivial for a savvy "standard" user to simply connect to the database using a client, and modify the users table; changing themselves to an Administrator, for example. (Because they have to know the MySQL user and password to configure the application. The application stores its MySQL credentials in a serialized XML file, the password is plain text because the user already has to know it to connect.)

I'd like to improve the security of this software, but I'm not sure how to proceed. If I restrict the permissions of the database user, the application won't give administrator users the ability to modify users. If I don't, standard users could take advantage of this obvious loophole.

The MySQL users and application users (users within a database table) are totally distinct at present. Should this be reconsidered?

How should I restructure my application so that it offers high level privileges to "administrator" users, but prevents standard users from taking advantage of the application's authentication to circumvent security?

  • 2
    There should be a server-side application that interacts with the database and offers an API to the client-side application. All database credentials should be stored with the server-side application. When using the client, users (application users) get authenticated and according to their authorization level they're allowed to perform certain actions.
    – Adi
    Commented Jun 26, 2013 at 17:37
  • This is one good solution and a general best practice, but it can be hard to implement in a mature application, especially when using an ORM or other "rich query" framework. There are other options as I cover in my answer.
    – KeithS
    Commented Jun 26, 2013 at 17:57

1 Answer 1


This is a common problem in database application development, with a few valid solutions.

First, just to say it, a single SQL account for the application, with permissions to do anything the program is capable of, is bad. Doubly bad when this account's credentials are hard-coded into your app to allow the connection. The reason why is exactly the reason you posit; someone could feed the credentials into a SQL console and run delete * from User to blow away your security system.

The first possible solution is to not grant any non-admin account CRUD rights to tables, and of course never hard-code or give an admin account to client software. Instead, Read rights can be granted with SELECT permissions on views, and C_UD rights through EXECUTE permissions on stored procedures. This is an old and widely-accepted solution, and is still used even though best practices for most languages and environments now encourage other patterns.

The second solution is similar in theory but different in implementation; implement a service layer endpoint that defines the possible data operations. This is the new best practice, especially for situations involving "anonymous" usage over public networks (web services). Instead of the client connecting to the DB, the client establishes a service channel to the endpoint and from that point can only call methods the service layer allows, and may also be required to pass in authentication data such as a token in order to make any data call other than the initial login.

The downsides to both of the above approaches are that you will likely have to make deep sweeping changes to your client-side data access to call stored procedures or service methods instead of direct table-based operations. You now also have business logic on the server side and even in the data layer, relating to ensuring users can only do what they're supposed to and are not trying to do what they shouldn't. Lastly, by necessity, you narrow the scope of valid data retrieval operations by exposing specific access methods, which is a step backward in DAL design for most modern languages like .NET and Java (which have ORMs like Hibernate and Entity Framework to provide rich querying ability without "magic strings").

The last solution requires the least implementation but can be more vulnerable; the user account hard-coded into the client has the minimum possible permissions needed to verify the credentials of a user (ideally by being given EXECUTE rights to a single stored procedure "PerformLogin"). As part of user authentication, after verifying credentials, the routine will give back the SQL credentials of a DB user that has the data-related permissions correlating to what the authenticated user account is allowed to do. The client software then re-connects to the database after authentication using the provided credentials. The advantage over other solutions is that only minor changes to your persistence layer (and a reconnect after authentication is successful) are required to implement it in your current system of a direct DB connection and table operations, instead of deeper changes to how you get data by invoking stored procs or a service.

The downsides are that the SQL users available through authentication have to represent useful cross sections of functionality, typically lending the overall structure more toward "tiers" of user permissions instead of a more modular "a la carte" scheme. Means to mitigate this (such as dynamically creating and setting permissions on a user after authentication) are typically their own can o' worms from a security standpoint. You'll will however need many "built-in" users, one for each tier, for this two-step connection process to have much real value (if Johnny User has the lowest user level, but just by authenticating he gets a near-administrator SQL user account for the rest of his session, your client is now the only layer of security, and as we have discussed it can be easily bypassed). Lastly, even with well-tailored SQL user accounts, the permissions needed can still be rather broad; UPDATE rights on a table are needed for the client to update one record, but can be used maliciously by the attacker from a console to blank out all of them. The other solutions very specifically limit the allowable data operations to both approaches.

  • +1 Very thorough and helpful. I think I prefer the idea of implementing a service layer which the client app connects to instead of the database itself. One of the other major problems with the existing application is that datetime values are derived from the client clock, which can be spoofed easily by users to falsify data. A server-side service would not only improve security but also provide a means to fix the datetime hole. Thank you for your insight.
    – JYelton
    Commented Jun 26, 2013 at 18:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .