I was hoping to get some advice on a .NET application I am designing and developing for my company. It's your run-of-the-mill data-driven application (a PO system), with a SQL Server 2000 back-end and IIS 6.0 running ASP.NET for the front-end. I'm developing in Visual Studio.
My original plan was to implement this as a 4-tier solution with a:
1. Database layer
2. Data access layer (using typed datasets and table adapters)
3. Business object/logic layer (classes that use the data access layer's typed datasets and implement business logic), and
4. Presentation layer
To simplify security, I would assign users usernames & DB roles on the SQL Server, and send those as part of the connection string. This is opposed to having a dedicated login for the application, in which case I would have to create my own method of authenticating users (verifying password, managing roles, etc). Since we have a mixed environment, Windows Authentication is not an option.
The conflict I'm having with this architecture is with the business logic being implemented on its own layer rather than in the DB. If the application connects with the users' credentials, which flow from the SQL Server, then a user could also connect to the server with ODBC, or using Query Analyzer. They could then get around the business logic, which would not be enforced at the DB level.
Do you folks have a recommendation? My hunch is I should either not use individual SQL Server logins, or I should implement business logic in the database if I do. Not sure which of these options is a smaller pain in the butt. Appreciate any help.
My original plan was to implement this as a 4-tier solution with a:
1. Database layer
2. Data access layer (using typed datasets and table adapters)
3. Business object/logic layer (classes that use the data access layer's typed datasets and implement business logic), and
4. Presentation layer
To simplify security, I would assign users usernames & DB roles on the SQL Server, and send those as part of the connection string. This is opposed to having a dedicated login for the application, in which case I would have to create my own method of authenticating users (verifying password, managing roles, etc). Since we have a mixed environment, Windows Authentication is not an option.
The conflict I'm having with this architecture is with the business logic being implemented on its own layer rather than in the DB. If the application connects with the users' credentials, which flow from the SQL Server, then a user could also connect to the server with ODBC, or using Query Analyzer. They could then get around the business logic, which would not be enforced at the DB level.
Do you folks have a recommendation? My hunch is I should either not use individual SQL Server logins, or I should implement business logic in the database if I do. Not sure which of these options is a smaller pain in the butt. Appreciate any help.
