Database access control: Application or Database level control?

Question

I have been developing an application in Access 2003 that uses SQL Server as the back end data store. Access is used only as a GUI and does not store any data. All the code in the application is written in VBA using ADO for data access.

In recent meetings the DBA that works in my organization has become increasingly concerned over the fact that the application logic controls what data is available for viewing and for update. The way I have been developing the application up until this point is to use a single database login for all access to the database. This database login is the only user allowed access to the database and all other databases users (other than DBA types) are restricted.

The DBA for this project is insisting that each user of the application have their account mapped to only those objects in the database to which they should have access. I can certainly see his concern and that is why I was hoping to ask two questions ...

1. Is having a single application level login to the database a bad practice? I had planned to implement a role based security model where the "access" users were given was dependent upon their application role. However, the application logic determined whether certain queries/updates were allowed to proceed.

2. Does anyone know of some resources (articles/books) that go over how to design an application where database access is controlled from within SQL Server and not through the application?

Answer 1

In my opinion

1. Yes it is bad practice, as a user could use the credentials to access the database in another way, circumventing your applications access control and perform actions that they shouldn't be able to. If you are on a windows domain you can create a group in AD for each of the roles and assign users to the group, then apply permissions based on that group so you don't have to add new users to SQL.

If you go down the active directory route you can use an LDAP query to see what groups the user belongs to, and you can decide whether they should have access.

It'll be interesting to read the other responses on this.

Answer 2

You don't say what the size of your database is or the business environment, so the answer is - it depends, but the presumption would be that your DBA is correct.

In a corporate environment the primary concern is usually the data, not the application used to access it. Indeed the data will often have a longer life than the application and changing business considerations may dictate that the data is used, and potentially modified by, different sources and not just 'your' application. In this situation it makes sense to build in security at the database level because you are ensuring the integrity of the database no matter how it is accessed, now or in the future, legitimately or illegitimately.

For 'departmental' level applications, that is where access is limited to half a dozen or so users, the data is not business-critical, and there will never be a need to use the data outside the original application then application-level security tends to be more convenient and the risks are often acceptable. I have clients who sell bespoke vertical application software to small businesses using this approach and as there's no internal IT it's difficult to imagine how else one could conveniently do it without incurring high support overheads.

However one of the defining traits of a corporate as opposed to a departmental level situation is that in the former there will be a dedicated DBA and in the latter there probably won't even be dedicated IT support, so you almost certainly must view the database as a corporate asset, and hence you should follow your DBA's advice. It's more work defining the database objects and security, but the final result is you can be confident about the integrity of your database and you'll safe yourself work when the inevitable upgrade/extension comes around.

Answer 3

1. It is bad practice but as you have seen - that is how most applications "evolve" starting out as wide open to a few users and getting tightened down as more people (IT/DBAs) get involved.

2. Your DBA should be able to help out - almost all general SQL Server books have a chapter or two on users and roles and security. They will also be able to explain the nuances of the different security options and what works best in your environment.

Alot of the setup will depend on the environment and the application. For example - if all your users are on Windows (based) connections you will want to use Windows Authentication instead of SQL Authentication. If you have many various roles you will want to use SQL Server Roles. You may want to incorporate AD groups as well as roles (or instead of). Your DBA can help you make those decisions, or even make them for you, as you explain more about your application to them.

The people on SO can certainly provide our opinions as well if you post more information about the environment and application and usage.