Should my users connect directly to the database? How to connect users to database;

Question

First i have to mention that I am fairly new to databases;

Currently i set up a MySQL server and i am unsure how to connect my users to the database; I want them to be able to use my software application to send certain predefined queries ( SELECT / ALTER ).

Currently how i'm doing this is : I ask for an username/password in my first form.I then connect to the database using a default username : "javaApp" / password :"javaApp" and then i check if the data given in the login form corresponds to an user in the UserTable;

Can someone offer an insight about how to do this more efficently / more professional / more safe?

Maybe some web-services or something? I really don't have any ideea.

How is this solved by professional software developers?

Thanks in advance!

Answer 1

That depends a lot on what you want to achieve in terms of security, ease of use etc., I think. Each have their advantages and disadvantages. Often it's a trade off.

Connect with one database account for all users

Advantages:

• Flexibility: You are not bound to the methods of access restriction the DBMS provides.

  E.g. you can include business rules in the rights management of your application that might not be applicable to the rights management of the DBMS.

  It's possibly easier to change the DBMS too, if you didn't use any specialties of its rights management. If the application stays the same, the access controls stay the same.

  You can design the user administration in your application as you like, making it as easy and convenient as possible for you. If you use the DBMS to administer the users, you are restricted to the tools it provides for that job.

  If the DBMS has a bug regarding the rights management and the respective company or community isn't providing a patch, you cannot do much. If such a case occurs in your program you can fix it yourself.

• Use the tools you like an have mastered: It might be easier for you to do it properly in your application, simply because you know it and the respective programming language well. Maybe you don't know the DBMS that well (yet).

  That might have an impact on the quality of your solutions.

Disadvantages:

• Single point of failure: From the point of view of the DBMS all your users are the same user.

  That is, if this account gets into the wrong hands, for whatever reason, then all is lost. Of course you can (and should!) restrict such a single DBMS account as much as possible on the DBMS' side. But anyone who has access to this account can do whatever the most highest privileges of your application allow.

  Also logging who did what can be circumvented in such a case. As the intruder has all the rights your application has, and the DBMS has no way of distinguishing who that might be, they can mimic all the users they like. Maybe even manipulate the (in database) logs. Finding the traces of an attack might be harder.

  The password (or whatever credential) for the single database account must be known by your application in order to supply it to the DBMS upon logon. Obviously and especially you cannot "outsource" this knowledge to the users' brains. If they knew it, they could easily bypass your application by simply using a generic client (e.g. MySQL Workbench) doing things your application had never allowed. And attacks from inside are not that uncommon. E.g. some user who manipulates the work of others, to look better before management regarding promotions. Or somebody who feels betrayed by the company and wants to take revenge. You name it. (Or replace company by community if it's not a commercial project.)

  It must be physically stored somewhere, where your application has access to it. Though there might be measures that allow handling that well and secure, it's still more exposed than being just in a user's head.

  And if you have to change the password for that single account, you might have to deploy it somehow to all installations of your application. This might not be a (big) issue, depending on how your application is deployed though.

• Lesser well tested: If you take one of the well known DBMS out there, you can be sure a lot of other people use it. A lot of people have tested it. Some may even have explicitly audited it.

  The probability that one of these DBMS has a bug, that allows an account to break free from its restrictions, yet not 0, might be far lower than some hidden flaw in your maybe lesser well spread and supported application.

  (Please don't take that as an insult. I don't doubt your skills or anything. But I simply assume you are no heavyweight cooperation as e.g. Oracle with some billion dollars of resources you could throw in, or (yet) have a large open source community behind your back with a lot of willing volunteers.)

For each user, a different database account

Advantages:

• Reducing the impact: You don't have all the problems the single point of failure aka single DBMS account for all users comes with.

  Of course, if an attacker gets the superuser account, OK, then you're done, either way. But even if someone can steal the account data of one of your regular users, they cannot do more as this user could have done. Of course you'd have to keep the rights here as low as possible as well to reduce the possible impact of such an incident.

  As the DBMS distinctively knows who it's dealing with, user action can be logged safe from possible manipulation. You can reconstruct what was done and who did it. And be sure the logs don't lie to you.

  You can "outsource" the logon credentials to the users brains. There's no need, that it has to be stored physically somewhere accessible for your application. It's less exposed. (Provided that the users don't write it on some postit next to their keyboards or such funny things. But, that's another story...) And if a password needs a change for one user it doesn't affect other users or the installations of the program at all.

• Well tested, well supported: As I stated above, if you take one of the well known DBMS out there, you can be sure a lot of other people use it. It's been tested well may have even been explicitly audited.

  The probability of a vulnerability in the DBMS can therefore be a lot lower than regarding your application.. And a fix for such a bug might be readily available by the supporting community or company.

  It might not be an important thing for your project. I cannot judge the scale. But I don't want it go unmentioned, that certain DBMS provide provide means to integrate in complex IT landscapes of an organization. That is also regarding the user administration. For example in SQL Server Active Directory users can be used. That might help to overall simplify, centralize and/or standardize the user administration within an organization and therefore make it less prone to errors. It might also be convenient for the users -- a single sign on can be provided.

Disadvantages:

• Less freedom: Using the means of the DBMS for user and rights management, you have to adhere to its rules.

  The DBMS might not provide a certain means or a certain level of access restriction you need. You may find it impossible to make the DBMS' rights management behave upon some of your business rules. In such a case, you cannot do much about it.

  If you want to change the DBMS you may find, you have used a lot of the specialties in user management of the old one, that cannot be implemented in the new one. You may have to start from scratch.

  You're bound to the support of the company or community, that produces the DBMS. If there is a bug in the rights management, that poses a danger to you and the company or community is not providing a patch, there's not much you can do about it.

• Might require learning: Especially when you're new to the DBMS world you'd have some learning to do to understand the means of access control a DBMS can provide in general and how a certain DBMS provides them or what it has to offer additionally.

  Of course this doesn't come effortlessly and in the beginning you might make some mistakes.

Conclusion

Each of the methods have their own advantages and disadvantages.

Taking the "single DBMS user for all" route might be the right choice if the security requirements are not high. The greater flexibility might outweigh it.

On the other hand, the security level multiple DBMS accounts provides is far higher. If this is a concern, especially, when we're talking about sensitive data, this might be the better choice, or even the only one reasonable.

Maybe even a hybrid solution might be the right choice. Always have a fall back in the DBMS' access restrictions and on top of that, place your own in the application.

To decide which approach is best for a project, the needs of it have to be taken into account and the advantages and disadvantages have to be weighted against each other. There is no single best way I guess.

Answer 2

I would not expose the database directly. I would use a layer between the user and the database so that you can control how they can query the database and what they can retrieve.

Do not define the database connection in code. Rather, use a JNDI resource (especially if you are running on top of an app server e.g. Tomcat which manages JNDI resources for you). This will decouple your code from the connection information. Also, you can use things like connection pooling with JNDI which will enhance the performance of your app (and will also handle things like connection closed). Concurrency will be handled by the connection pool. See this Oracle article on JNDI resources and this one specific to Tomcat.

Use a service account i.e. an account that will be used to authenticate the app to the database. That account has nothing to do with the user accounts of the individuals accessing your app. Limit what the service account can do. If all you want to do is read data via the app, then limit the service account to just that (at least in the beginning).

You can use an ORM e.g. Hibernate in Java that will give you an abstracted view of the database. That could be too much for you though if all you have is a single table. In that case, use something called a PreparedStatement.

You want to be weary of letting users define their own SQL statements. That opens you up to a lot of SQL attacks (look for SQL injections).

Never ever store user credentials in the clear in a database. Hash the values.

Lastly, have a look at these awesome best practices.