What is the best approach for 'indirect' access to secure data.
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.
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.
8080_Diver
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.
ASKER
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?
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?
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?
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.
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?
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?
ASKER
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 . . .
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 . . .
ASKER
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'.
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'.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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. ;-)
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. ;-)
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.
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.
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? ;-)
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? ;-)