SQL Authentication
Application Authentication to SQL Server frequently seems to be one of the last things we think about when writing web applications. I make that assertion because I see so many companies where I consult having a connection string embedded in their application code, or config file in plain text.
When I go look at SQL Server, the credentials for that login generally have System Administration Role privileges, and/or Database Owner rights on accessed databases.
If you restrict the capabilities of the SQL Server Service by creating and using a service account with limited access rights in the operating system, your risk is more likely confined to that of your database(s). However, isn’t that more often the asset you have a high interest in protecting?
What I am really getting at is that security, even for your database, should be one of the first things you create for your application. If you don’t do that first, you will find it next to impossible to go back and add it in at a later time.
Let’s say you want to really lock things down, allowing access to your tables only through stored procedures. Typically, one or more schemas are created having permission to execute the stored procedures. Application logins are assigned to these schemas as needed. As a result, the application login has no direct table access. If you create all the tables and stored procedures under one schema, it becomes a lot of work to separate the stored procedures out from the same schema used for table definitions.
The point is, when defining SQL Authentication, it is a good practice to begin with an understanding of the roles your application users may perform, and have appropriate schema design allowing that separation of access rights to only those necessary for a role.
What do you think? Is this still important in our applications today? Can this kind of separation work well with ORM generation tools? Do we still need the separation of schema from command? Share your thoughts by writing btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Total Database Information At Your Fingertips (Part – VII)
This article will help to get some basic information from your databases that may help you in different situations.
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)
Featured Script
Create_Update_Proc
This procedure will look to the system tables and create a default Update proc…. (read more)