Designing a secure database application...well trying too :)
Well I thought i would be blogging on the wonderful new world of learning OLAP, but that is not the case. Right now I am trying to design a secure n-tier application with a asp.net pages as as its front end, and a data access layer thar runs as a either a windows service or web service that connects to a layer of stored procedures on SQL Server 2000.
The question I am having trouble answering is what sort of security model to use on the database itself. My preference is database roles with registered user groups from the windows network. This ties the user to the role and the role's permissions in the database. If the user changes role, and they change groups, the group registered on the database will not need to be changed.
The suggestion from a dba colleage was application roles. The problem I have with app roles is the maintenance module now required to be built into the application. The application will now have to manage role changes, additions and deletions. Seems like a lot of overhead that could be managed on the network. The dba basically does not want any access to the database except via the application. My argument is that with the services layer, and role-based security, they will never really go 'outside' the application and they will not see anything they don't already have rights to see to begin with. This is all 'read' situations mind you, not update situations. I can almost buy the argument of locking the update routines to the application role.
So so far I have a planned mish-mash of network security, database roles, and application roles. Haven't even given thought to what I can lock down on the services layer ;).
I would appreciate any thoughts you might have for this situation.
--dao