Simple and easy logon, but secure? | Tech Off

ZippyV
Soapbox = Fail

I'm building an application that will be used in a warehouse. The pc's will only have a touchscreen and a tag reader (workers carry a unique tag bracelet). Because my application connects to a database and updates certain information (like who is working on an order and when it's completed) I need to identify who is logged in on that moment.
I was thinking about this solution: every person in the warehouse has a unique useraccount in the SQL Server database with an extended property that contains the tag code. A special user account is made which can only lookup which useraccount has a certain tag code and retrieve that username. All further actions in the database are executed under the looked up useraccount until he logs off. Of course, everything is locked down so that these users can only use some stored procedures.

Do you guys think this is the best solution? I prefer to use the builtin security system for creating/managing useraccounts but maybe a simple users table in my database is a better idea?

figuerres
???

well if it were me && if this is a windows app && if you can use IIS then....

use the asp.net membership system.

with WCF and asp.net there is a builtin ready to use WCF services that you can setup to access the asp.net roles and membership from a windows forms app.

first setup normal username and password login as that will work out of the box and get you rolling.

then get the sameple membership provider code and see if you want to make a custom provider or just hack the sql membership procs to use your id data.

then you have the users in a database and do not have add / remove sql users all the time.

also if you add sql users you have security issues to manage and the number of connectioons you open impact system performance.

also if you do this you can use wcf to serv up your data and use the IIS server and wcf services to help it scale for more users, not expose sql connections onnthe network and allow the sql server and the web server to sit behind a firewall.

safer, scalable, minimal work to hook it up. and it could grow to work with more than one wharehouse.

TommyCarlier
Trust me, I'm from the Internets

For most applications that access SQL Server, I try to avoid direct access from client to SQL Server. Client <-> (Web)Server <-> Database. If the client cannot access the database directly, the attack surface is reduced considerably. SQL Server security configuration also becomes easier (only 1 user-account). And like figuerres mentioned, WCF and ASP.NET have an excellent Roles and Membership system.

ZippyV
Soapbox = Fail

In reply to TommyCarlier

#Jun 8th @ 10:57 PM

Why would I make it more complicated by using a webserver for an internal business application?

TommyCarlier
Trust me, I'm from the Internets

In reply to ZippyV

#Jun 10th @ 11:38 AM

To make it more enterprise-y? If there aren't a lot of users (and you don't expect the number of users to increase a lot), you can get away with this. At our company, we have some internal tools I developed a while ago that also connect directly to the database. I do plan to rewrite them to Client <-> Server <-> Database when they need to be rewritten. One of the disadvantages of direct connections (that I have experienced) is that you have to update all the clients when you move the database to a different machine (with a different name). Other than that, you should be fine.

figuerres
???

In reply to ZippyV

#Jun 10th @ 11:38 AM

just little things like security....

management of the app / system.

blowdart
Peek-a-boo

By having separate SQL logins per person you lose connection pooling; which has an adverse affect on scalability. You could, instead, just accept the tag ID as a parameter for the stored procs.

figuerres
???

In reply to TommyCarlier

#Jun 10th @ 10:36 PM

also if you find that you need to make a change to the tables or procs that add a new requiremet for a call, column value etc...

if you are direct connected then any chnage has to be working in all the clients, a Web Service middle layer gives you a place to make chnages w/o having to update the clients all the time. it also allows you to have a better seperation between back and front so that your client app does not have table names, sql strings and other back end stuff embeded in it.

it also means that you can some times get better performance by running the data logic on a server.

also it uses more of a "Best practice" approach so that you have a habit of keeping them seperate and more "modular"

and the connection pooling issue....

and how many cals is the company going to buy to connect all the client pc's ??
and do you have enough ram on the sql server to handle the connections ?? each one has a memory overhead.

lot's of small things that add up to a big set of benefits.

ZippyV
Soapbox = Fail

In reply to figuerres

#Jun 11th @ 6:09 AM

I tried to make my application a 3 tier app that is the application + a datalayer (a dll). Communication between those 2 layers happens using business objects. The datalayer then calls stored procedures on the database server and I lock the permissions to execute those sp based on the role they have in the database.

I estimate the amount of users around 30. The people in the warehouse are not going to be logged on all the time, only when they need to start and finish a task.