14

For about 10 years I've worked on various in-house desktop client applications with SQL Server data stores. Rarely did I start these projects - most are takeover work.

One thing that seemed constant everywhere was that there was a single global SQL Server user account that this application used that granted it permission to the common database, and yes in some naive situations it used the sa user account, which I generally tried to fix when possible.

You can't really effectively hide this username and password that the application uses to access the database. They're usually stored in an ini or config file, or possibly baked into the executable itself. In all cases, they're visible to the user if they do a little digging. In one case we actually used a config file but encrypted it, but of course the encryption key had to be stored in the executable (we weren't naive to the limitations of this, but it did effectively stop people from poking around who were savvy enough to look in config files).

All of these systems had a user-authentication system built into the application, but of course they were all managed through the application itself, meaning the user information was stored in the database. The application restricted what things you could do based on your access level, but it's all kind of moot if you can just connect to the database and run ad-hoc queries.

I'm interested to know what other systems do to get around this problem. Here are the options I know of:

  1. Use SQL Server's security mechanism to maintain a user and roles list, and make the desktop application add and remove users through T-SQL queries.
  2. Instead of connecting directly to the database, create some kind of web service that runs on the server and put the authentication logic in there. Make every request do security validation.

The first options is a bit ugly because you're separating users from the database so users are no longer first class entities and you can't reference them with foreign key relationships, etc.

The second just seems like a major performance problem, and a lot of extra work, plus you can't as easily use ORM mappers like NHibernate (I think).

Does anyone have experience with this? Best practices?

Edit

Thinking a bit more, can SQL Server Authentication actually solve this problem? For instance, if your user must be able to insert and update timesheet records so you can edit your timesheet, there's no way SQL server can disallow access to other rows in the timesheet details table, meaning you can read and write other people's timesheets too.

6
  • On the topic of bindings; not using ORM like NHibernate is (I think) a non issue. If you use web services as an example, you will find many ways to efficiently bind your data to XML.
    – jasonk
    May 28, 2012 at 22:06
  • You shouldn't be using your ORM as a direct mapping between business objects and DB entities anyway, its a poor approach that makes for fragile interfaces. Make requests to a business layer that gets raw DB entities and returns only the required data to the client.
    – gbjbaanb
    Feb 20, 2015 at 14:20
  • @gbjbaanb - sure, I'll change the whole architecture this afternoon. :) Feb 20, 2015 at 14:58
  • I suppose you could wait until someone hacks you before changing it, but on the bright side, at least then you'll have no problems getting your boss to fund the re-architecting :-)
    – gbjbaanb
    Feb 20, 2015 at 15:06
  • You can prevent a user from updating someone else's records - by using a stored procedure as the only way to update the records and using the user who is running the proc as part of the query. See CURRENT_USER
    – gbjbaanb
    Feb 20, 2015 at 15:09

7 Answers 7

11

I'm afraid adding a Web Service layer is probably the correct solution to your problem.

Separating the client from the underlying database implementation will probably help you in the long run too.

Adding a web service layer doesn't necessarily have to hurt performance...

Indeed, with an appropriate API, a web service can actually improve performance, by batching together multiple database queries within the data center LAN, rather than requiring multiple round trips over the WAN.

And of course a web service layer can often be scaled horizontally, and add appropriate caching to your database queries, perhaps even a change notification mechanism.

A server layer adds security that you cannot possibly ensure with apps running on a remote client. Anything that runs on a client can be "hacked" and should not really be considered in any way trusted. You should only really put presentation logic in the client, and host anything important on hardware you have complete control of.

I don't know about your apps, but my web apps are naturally split into several layers, with the presentation code separated from the persistence layer by at least one level of business logic that keeps the two apart. I find this makes it much easier to reason about my app, and so much faster to add or modify functionality. If the layers are separated anyway, it is relatively easy to keep the presentation layer in the client, and the rest on a server under my control.

So while you can solve your problems without introducing a "web service" layer, by the time you have written all the stored procedures (or equivalent) necessary to fill in the holes in the standard database security implementation, you would probably be better off writing a server-side application that you can write proper unit tests for.

2
  • I concede that it need not be a performance bottleneck, but it certainly adds an extra layer to the architecture, which means a lot more maintenance. May 28, 2012 at 18:14
  • 3
    It adds a layer, but not necessarily maintenance. Consider that with all the logic placed in the service, not the client, changes can be 'rolled out' without requiring users to update their client applications. May 28, 2012 at 20:30
5

Similar to the answer by jmoreno, you can deny a user access to everything aside from EXECUTE permissions on stored procedures, then take advantage of ownership chaining to have the stored procedure perform the required operations on the tables.

See here for details https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx

When the user enters their username/password client side, I store them and send as parameters to every stored procedure call. You can then verify them against values stored in a table before performing the desired operation.

Definitely not the last word in security, but might be necessary if your PCs have generic logins, limiting your ability to use AD groups for permissions, or you have limited access to AD itself.

3

What you hint at as 'web service' is called n-tier architecture. Its generally the way to go in cases where security or configuration issues are likely (for ex, distribution of an application across many offices). It does not have to be 'web based', though. Many work with other protocols.

You create an application server to act as an intermediary between the client and the database (and other resources). The application server handles your application based authentication and performs actions on behalf of the client. In fact, ideally you wouldnt be doing any SQL in your client - rather you call methods on the app server. The application server would handle all data manipulation.

There's a number of benefits to the approach. You dont need to configure database connections and drivers on clients. You dont store database users, passwords, & servers. Configuration of the clients isnt even necessary - just point them in code to the right url or address. Also, with the 'logic' in the application server, you dont have to repeat yourself when developing other applications - the same app server can be reused by different types of clients.

1
  • Even better, if (or when) someone hacks your desktops (or webserver in a web-based equivalent) the attacker may have full access to the OS, but they still do not have any access to the DB. And so they cannot then run "select * from users" piped to a file that they take away, crack at their leisure and let your CEO explain to the media why your secure system was compromised. If you also use sprocs on the DB that only allows execute access, then the attacker can hack your app sever too and they still can't get your entire user database.
    – gbjbaanb
    Feb 20, 2015 at 14:12
2

One approach is to use AD groups and stored procedures to limit what the user can do -- for instance your time sheet DB, could allow inset, update and deletes of the users hours, but not allow updating of anyone else's hours. The user's ID would be provided by the DB engine, user would have no direct access to the DB tables, just to sp's that ran queries based upon their login id.

Of course this isn't always feasible, but it can be. The best approach will depend upon your requirements and resources.

1
  • This is something I hadn't considered. I'm not sure it's a great fit, but it would work. May 29, 2012 at 15:48
1

Technology has changed a bit. If you authenticate each user to the database itself, and use database roles, you can now use what's called an Updatable View to solve this problem, at least in SQL Server.

Here's what an updatable view might look like for a table called SomeTable where each row in that table is linked to an employee. The employee should be able to see the rows linked to them, and members of the HR role should be able to see all rows, for instance:

CREATE VIEW [dbo].[vwSomeTable]
AS
    SELECT SomeTable.*
    FROM SomeTable
        INNER JOIN Employee ON SomeTable.Employee_ID = Employee.Employee_ID
    WHERE Employee.Username = USER_NAME() OR IS_MEMBER('HR_Role')=1

GO

Then what you do is give read (and possibly write) permissions on the view (vwSomeTable) to all users, and give no permissions on the table (SomeTable).

You can test this like this:

EXECUTE AS USER = 'Some_Regular_Username'
SELECT * FROM vwSomeTable

...which should only return their row(s). Or:

EXECUTE AS USER = 'Some_HR_Username'
SELECT * FROM vwSomeTable

...which will return all rows. Note that you'll need the execute as (impersonation) permissions to do this test.

The views are updatable, so even the regular user can do this, as long as the row is linked to their Employee row:

UPDATE vwSomeTable
SET SomeColumn = 5
WHERE SomeTable_ID = 'TheID'
0

Using certificate based authentication is the "correct" way of implementing a shared sql account. The goal is it eliminate the password use for this kind of thing.

Update:

I suppose misunderstood the question. I thought it had to do with trying to find an alternative to putting a db username and password either in an application config, or backed into the application itself.

You can eliminate the problem of managing passwords in applications by using client side certificates instead. The cert itself is not enough, you have to have a distribution and management system capable of operations like cert revocation.

Reference: http://en.wikipedia.org/wiki/Public-key_infrastructure

1
  • Can you provide some more info on this? Sounds like it might be orthogonal to the intent of my original question, but it's interesting. May 29, 2012 at 15:49
0

Building a new desktop security solution, we opted for the web service solution I´ll try to describe bellow.

We compile the desktop application executables in a separate environment from the developers. And calculate a HASH from that executable that is recorded to the database.

One web service that provides all the needed information for the application to run, the DB password, connection string information, user permissions, etc...

we use single DB log in per application and record the user details in the database in session variables to be able to audit records.

A DLL handles all the communication from the desktop application to the web-service, that is only accessible with a token build into the DLL.

To be able to get the application DB password from the web service the DLL calculates the DLL callers HASH in runtime and passes as a parameter to the web service that validates the DLL token and the executable runtime calculated HASH to the one recorded when it was deployed (the application is only available in a single network shared installation).

That way we fell it is a good solution to the security problem we where most concerned with and are well aware of a few designs flaws. Are are almost finishing this implementation and so far we are happy with the results.

Edit: You could replace the hash idea by using digital signatures and X.509 certificates.

3
  • 2
    It seems pretty obvious where the glaring security hole is. The DLL you speak of is on the client's system and there's no way for your server code to verify that it's talking to a legitimate copy of the DLL or a hacked/malicious/fake one. You just created a lot of work for yourself without adding much, if any, extra security. All a malicious person needs is the token and algorithm, both of which are in the DLL for anyone who wants to look. Feb 17, 2014 at 15:20
  • @ScottWhitlock, Yes, I agree. we are looking into obfuscating the DLL, and the traffic going over HTTPS. We are trying to improve on that, I´d really like any input in how to improve it. But that solution already solves a lot of issues the current system has, including plain text passwords stored in network files. Also the webservice allows reuse of a lot of code being acessible by any of the client languages we use here, Including Delphi and Clipper (Harbor) clients! Feb 17, 2014 at 16:23
  • In your system, the user logs in and is presumably authenticated by the web service. Assuming use of HTTPS, isn't that good enough? You don't have to trust the client software, since you know that the user is who they say they are, and you control the web service, so make sure that web service only hands out information that the given user is authorized to see. Even if they reverse-engineered the client and wrote their own, what damage could they do? Only your web service knows the DB password, and that should be safe. Feb 17, 2014 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.