Database Access Control: Application or Database Level Control?

Question

I am developing an application in Access 2003 that uses SQL Server as the backend data warehouse. Access is used only as a graphical interface and does not store any data. All the code in the application is written in VBA, using ADO to access the data.

In recent meetings, the database administrator who works for my organization is increasingly concerned about the fact that the application logic controls what data is available for viewing and updating. The way I develop the application up to this point is to use single sign-on to the database to access the database. This database entry is the only user access to the database, and all other database users (except DBA types) are restricted.

The DBA for this project insists that each user of the application has their own account only for those objects in the database to which they must have access. Of course, I can see his concern, and that is why I was hoping to ask two questions ...

• Does one level of user login to the database have bad practice? I planned to implement a role-based security model where "access" users were granted, depending on their application role. However, the application logic determined whether certain requests / updates are allowed.

• Does anyone know of some resources (articles / books) that talk about how to develop an application in which access to the database is controlled from SQL Server, and not through the application?

Answer 1

• This is bad practice, but as you have seen, that’s how most applications “evolve”, starting with the power of multi-user capabilities and taking longer when more people are involved (IT / database administrators).

• Your database administrator should be able to help - almost all SQL Server general books have a chapter or two about users and roles and security. They can also explain the nuances of the various security options and what works best in your environment.

The installation attribute is environment and application dependent. For example, if all your users are connected to Windows (based), you will want to use Windows authentication instead of SQL authentication. If you have many different roles, you will want to use SQL Server roles. You might want to include AD groups as well as roles (or instead). Your database administrator can help you make these decisions or even make them for you as you explain more about your application to them.

People on SO can of course also give their opinion if you post additional information about the environment, application and use.

Answer 2

To my mind

• Yes, this is bad practice, as the user can use the credentials to access the database in a different way, bypassing access control to applications and perform actions that they cannot perform. If you are in a Windows domain, you can create a group in AD for each of the roles and assign users to the group, and then apply permissions based on this group so that you do not have to add new users to SQL.

If you go through the directory of the active directory, you can use the LDAP query to find out which groups the user belongs to, and you can decide if they should have access.

It will be interesting to read other answers to this.

Answer 3

You do not say what size your database or business environment is, so the answer is up to you, but the presumption is that your database administrator is right.

In a corporate environment, the main problem is usually data, not the application used to access it. Indeed, data will often have a longer lifespan than an application, and changing business considerations may dictate that the data is used and can be changed by different sources, not just your application. In this situation, it makes sense to build security at the database level, because you ensure the integrity of the database, regardless of how it will be available, now or in the future, legally or illegally.

For departmental applications, where access is limited to half or so users, data is not critical for business, and there will never be a need to use data outside the original application, the security level is usually more convenient, and the risks are often acceptable. I have clients who sell custom vertical application software for small businesses using this approach, and since there is no internal IT, it’s hard to imagine how else it would be convenient to do this without bringing a lot of overhead for maintenance.

However, one of the defining features of corporations, unlike the situation at the departmental level, is that the former will have a dedicated DBA, and the latter will probably not even focus on IT support, so you should almost certainly view the database as a corporate asset. and therefore, you should follow the recommendations of the database administrator. This works more by defining database objects and security, but the end result is that you can be sure of the integrity of your database and you will be safe to work when an inevitable update / extension occurs.