I have seen an interesting issue surface recently, one that many other corporations are probably facing: the dividing forces of security and auditing. It could be argued that auditing practices should strengthen security, however this may depend on the situation. Let's take database access control as an example.
In a typical two-tier application, connections established to a database server are performed using a shared account. Usually, shared accounts are considered less secured than network accounts due to the lack of strong authentication offered by network services like Kerberos. Also, database accounts are considered an issue with database auditing since these are shared accounts: who really accessed my data?
So at first sight it might appear that database accounts are neither good for auditing nor for security.
However, let's consider these additional factors:
- shared accounts used for application connections are usually kept secret; users do not know the user id/password of the database connection, and as a result can only see or act against the data through the application layer
- when using network accounts (SSPI) to establish a database connection, a password is usually not required at the time of the database connection; the connection is granted automatically if the user is granted access; that's a benefit of Single Sign-On
- database servers do not differentiate connections coming from users or from applications; the connection is granted if it is authorized
- in many configuration settings, the accounts used through an application need to have complete access to the database so the application can perform its normal CRUD operations, while the application controls what users can do depending on application-layer access control rules
With those additional factors, we can see that configuring two-tier applications with SSPI can open significant security challenges. While corporations gain in auditing by knowing who is actually accessing which data, users can now technically connect directly to the database server without using the application, bypassing application layer security. This can create a significant security challenge and may leave databases open for attacks or unintentional data loss.
Can anything be done? With SQL Server 2005 SP2 and higher, administrators can start enforcing connection rules through the use of Logon Triggers. These triggers allow more control over the connection when it is taking place, such as verifying that the connection is indeed established from the application, instead of Excel for example. Also, database firewalls can be useful in limiting the use of database or network accounts, such as the one that my company creates.
This is an interesting topic and one that can be a real challenge for certain organizations. It appears finding the right balance between security and auditing may not be as straight forward as it seems. At least not in this case.