Solved

What is the best approach for 'indirect' access to secure data.

Posted on 2010-08-24
14
308 Views
Last Modified: 2012-05-10
I have a secure SQL DB that contains user data that is needed for login authentication by a web app.  The web app is being developed by a third party, and we do not want to allow their app to authenticate login data with our secure DB directly.

What is the best approach to provide the authentication (verify the user is in our DB) without allowing a direct connection to our server and DB?  The only thing I can think of is to develop a Windows 'service' that would run all the time and look for inbound login request packets.  Then the 'service' would query the DB and return a 'pass/fail' packet for subsequent pickup by the web app.

I am sure there are a number of approaches, but I can think of only the one.
0
Comment
Question by:mdreed
  • 7
  • 5
  • 2
14 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33513563
I would provide a LoginValidation stored procedure that they have only execute priveleges on.  That SP can handle all of the validation exercises, touching whatever it needs to, and then, basically, return a True/False result.  (Although, I have been known to return a "application-level permissions code" that uses 0 for only having permission to try to log in or to exit and other, in effect binary coded values, to control which aspects of the application/web app the user can access once they are signed in.
0
 

Author Comment

by:mdreed
ID: 33513647
A SP seems like a reasonable approach, but wouldn't the third-party app have to log in to the server containing the SP?  If so, that might be a problem since we do not want them to be able to do that.  Do you see this as a problem, and if so, any way around it?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33513826
What I do is have a special log in (e.g. User Name: GateKeeper, Password: Dragon ;-) that can only execute that one procedure.  Once the validation is accomplished, the connection string for the user (and it can literally be user specific) is returned.  The User connection strings provide access to additional SP's . . . but not the data in the tables or even the views.
If you have concerns about the security and don't want your 3rd party web app "vendor" to have access to things, then SP's are the way to go.  They can obfuscate the source and destination of data.  However, at some point and in some way, the web app has to get connected to the database.  If you want it to work without a connection being established, than it isn't going to be able to do much. ;-)
A SP seems like a reasonable approach, but wouldn't the third-party app have to log in to the server containing the SP?  If so, that might be a problem since we do not want them to be able to do that.
Well, how do you expect the 3rd party app to be able to do anything if it can't get to the database in any way?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33513849
If you have such high concerns over the access to the database, set up a develoment database that the vendor can log into during the testing of the app and then have different passwords and connection strings that get returned (except for the GateKeeper UN/PWD) in the Production world.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33513992
I think you may be underestimating the complexity of what you would need to develop with the web service approach.  Typical authentication process are way more complex than just 'pass/fail'.  I mean wouldn't you need to accomodate  other processes like create login, confirm email, forgotten password, etc.  You might be taking on more than it seems at first glance.

A simpler approach is to use another database for app authentication. Or at least to "front" the connection form the web app and use remote procedure calls or service broker to exchange information with the secure database.    

Having said that, I'm still not sure I quite grasp your comfort with the windows service solution.  Assume you get the app authentication piece worked out, then won't the app subsequently need access to the database?  So, the web app will neccessarily be configured with a connection to the secure database and your protection is based on VOLUNTARY compliance by the web app to stay away from the secure database until after authentication.  Wouldn't it be just as safe to permit the web app to connect to the secure database for authentication and VOLUNTARILY not do anything else until after authentication?

When all's said and done, isn't your security scheme ultimately based on cooperation from the application layer?





 
0
 

Author Comment

by:mdreed
ID: 33514039
So, if I understand you correctly, your user is logging in to an application that you wrote and you are providing the results via a SP?  Or is your user logging in to the server and then executing the SP?  Since you are building the connection string, it sounds like the former as opposed to the latter.  

If the third-party app can be limited to executing only the one SP, that might be sufficiently secure.  How is that limited access (to only the one SP) accomplished - I assume in SQL Server.  And, I am embarrassed to admit that I have not used SPs before . . .
0
 

Author Comment

by:mdreed
ID: 33514118
dqmq:
What I was suggesting - and admitting that it was not desireable - was that the TP app would send a request 'transaction' or message record.  My app (service) would take the request and see if it is a valid user in our DB.  Then, I would send back a separate message indicating that the userid passed or failed.  That is the only requirement - no other authentication issues.  We simply do not want the TP app to connect directly to our server or DB, and I am looking for a little more efficient alternative to a 'listener' or 'service'.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 33514334
So, if I understand you correctly, your user is logging in to an application that you wrote and you are providing the results via a SP?  Or is your user logging in to the server and then executing the SP?  Since you are building the connection string, it sounds like the former as opposed to the latter.  
The user is accessing the Web App (or my app on a desktop) and the only thing they can do at that point is try to log in or to exit the app.  In attempting to log in, they supply a UserName and Password to the WEb/Desktop app's "Login Process" and that is passed to the database using teh LoginValidation connection that can only access the validation SP.  If the SP gets a match, it will return (in my implementation) a value that then is used to enable other functionalities in the app.  It also gets a connectionstring that is very probably restricted to having access to only certain SP's but, in any case, can only gain access to SP's.  
Limiting the 3rd party app to only having access to SPs means that you do not expose anything other than the parameters of the SPs to the 3rd party app and you can completely control/change how the SP obtains or works with the data.
As for "Then, I would send back a separate message indicating that the userid passed or failed. "
Okay, let's say the 3rd party app sends a valid username and password . . . then what.  Is that all that the 3rdparty app is ever expected to do?  Just validate the user?  Why not write that part yourself and save teh concerns over security.  Or, is it that the 3rd party app is also going to do other things?  In which case, are you planning on creating, in addition to the piece that validates the user against the databse, pieces to handle everything else?
@dqmq,
Or at least to "front" the connection form the web app and use remote procedure calls or service broker to exchange information with the secure database.    
Essentially, that is what my approach does . . . I just do it in the one database.  The initial connection only has access to execute the validation SP.  Subsequent connections only have access to execute SP's.  In fact, I frequently provide different connection strings based upon the level of access I wish to give the user and limit a given connection string to subseets of the SP's.
0
 

Author Comment

by:mdreed
ID: 33514612
I think that SPs appear to have the most promise, but I would like to avoid the TP app from accessing our server directly if possible.
Let's say I am a Government entity, say the Navy.  I have a vast DB of Navy personnel data who have privileges to shop at the Navy Exchanges.  Let's also say that I have hired WebDev Inc to develop a shopping portal to buy our merchandise via the web.  The web app must first determine at user login that the user has exchange shopping privileges, but I don't want the web app (that we didn't write) to have direct access to our server or DB.  How would I make that happen?  I believe that, based on your experience, that a SP could be the answer, but the web app would likely have to connect to the server containing the secure DB - which is not desirable.
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 33514912
mdreed,
Alternatively, you could house the SP's on a separate server that has a connection to the target DB and the SP's would, in effect, be using a separate connection string to get to the actual data.  However, at some point, the target database's data has to be access to some extent, otherwise, what's the point of the exercise.
If you take the Dev/Prod approach, the 3rd party developer doesn't know anything about the connections to the Prod database, even though they might know something about the Dev database connections.
For the specific example you cited, you can either house the basic merchandise information or the SP's to access that data (from the secured database) on a less secure database and still do the authentication (using the SP trick) from the main, secured database.
0
 

Author Comment

by:mdreed
ID: 33514971
Thanks for all you input.  I think connecting and executing a SP that resides on a 'public' server that in turn retrieves the verification information from the secure server might just do the trick.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33515176
Sounds like a plan!
I am designing a database and web/desktop app situation that is also using Schemas within the database to further segregate the data so that groups of users may have access to one schema while others have access to another schema.  That way, I can, in effect, maintain different  companies/organizations with identical table structures in one datbase using the same SP's.
If you're working with something like a Navy PX situation, that would also let you segregate the Marine products from the Navy products. ;-)
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33515275
If you don't want the TP app to access your secure server, then have it access another server for the authentication info.   The other server can either host the authentication data or can in turn connect to the secure database to get it.  By putting the logic in an SP on the "authentication" server, you can avoid a direct connection between the TP app and the secure server for authentication purposes.

But that's not the end of the story.  After authentication what happens.  The tp app then connects to the secure database to do real work, right?  To accomplish that, the tp app must be configured with a connection to the secure database.  When all's said and done, the tp app can access the secure database any time developers choose.  So, I really don't see the point of keeping them off it for the purpose of authentication.

You can accomplish the same thing much easier, by providing the web app two logins: one with only the minimal permissions to do authentication and another with the other permissions necessary for the app.  You may even extend the concept a step further and have multiple levels of authority within the app, each tied to a different login that is established during authentication.

Using SP's to strengthen security is a very good technique, but it addresses a different problem:  rogue access by authenticated users rather direct access by unauthenticated users.  SP's are simply a means to reduce the means for accessing tables and permitting you to manage access at functional level.  By that I mean, you might have two processes that update the same table in different ways, each controlled by a different SP.  By denying direct table access, only the SP's can do their respective updates and only for the users that have execute authority for the respective SP.

 

 

 
 


 

 




0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33515396
dqmq,
I agree with your overall assessment.  In fact, that is the basis for the approach I have used.  There is a fairly public connection that only allows authentication of the user and then, once authenticated, a connectionstring that is associated with (and, thus provides control of privileges and schema access) is returned for any further access.  Between the SP's, the scheam shifts, and the ease of changing the connection strings (not only Dev and Prod but also at will within either ;-), I ham quite satisfied with my solution. ;-)
However, if the OP wishes to put one extra layer inbetween, who am I to argue?  ;-)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now