I’d like to wrap up this week editorials with some observations on database security. We’ve been talking about application role level security, and application to REST services security. Including database security covers the three basic layers of an Internal WAN based application. Opening your application to the world wide web is another matter altogether.
In the old days of two tiered client/server applications, we used the database engine for our authentication and for authorization. We created user accounts in SQL Server or Active Directory, and granted those accounts access to individual databases. Then, after granting them access to the database, we granted them access to schemas or objects, with the appropriate capabilities…Execute, Select, Insert, Update, Delete.
Even though most of that kind of granularity has been abstracted from the data engine, and relegated to an authorization tier, using interfaces such as OAuth, we still have to connect to our SQL data engine using some real credentials. I still see a lot of applications using the SA account and a SQL password to connect to the database, because it is easy to implement, and there are NO restrictions.
Using a web service application to connect to the database, a mature application will have the credentials for the App Domain executing as a Active Directory account. That account can then be granted permissions to any SQL database, while not having to expose the whole engine for attack. It may be assigned to a schema, having the correct permissions, or individual granted access to different database objects.
Implementing this final leg of security provides a great deal of protection in a WAN environment. It doesn’t mean you don’t have any exposure. However, since the account used for the app domain hosting your web service has limited permissions on your SQL Server, you have drastically limited the scope of hacks a user may execute through your application. Limiting this user account to specific database(s), objects, and capabilities on those objects, disallows them from nearly every SQL Injection technique that can be executed outside of your database.
Note that I said you have excluded SQL Injection outside of your database. However, if they have adequate rights within your database, it is possible to use SQL Injection to corrupt or hack your database.
Hope you have a great weekend.
Cheers,
Ben