Database Authentication Reader Comments
Eric:
I know this all too well. I have inherited a database that was setup before moving into the DBA role. First I found ‘Domain Users’ were sysadmins, which was corrected. Then I recently found that the DSN’s the clients use to connect are connecting with ‘sa’!!
I believe the ‘sa’ was setup by the system vendor and it will take quite some time to coordinate with the vendor the necessary rights and migrate all the DSN’s to use a more appropriate login.
I always say “I love to hate security”, since I both love to have security in place to make sure the data is save, but at the same time hate it because the layers of security often times can become quite confusing.
Jim:
Forgive the clichés, but “failing to plan is planning to fail” and “there’s always time to do it over, but never time to do it right”.
… and what the heck mean: “premature optimization” and “overthinking”?
Okay, I expand the subject matter, but my point is in the parallel.
A system that will ultimately have to meet an SLA has to have a performance plan right from the beginning, and that means thinking the matter through progressively.
The same applies to security. If your first thought is just after the first breach, your last thought may be just before you replace your system.
I like, and have used, your suggestions – and they work.
The process is simple. It is also iterative and progressive:
- What are your mandated security requirements (e.g., HIPPA, SOX)?
- What information assets are confidential or sensitive and to what degree?
- Who are your need-to-know actors? … use cases? … exceptions?
- Who provides/oversees data governance/stewardship?
- Who/what are your threats – both internal and external?
- What are the consequences of a breach or unintentional/unauthorized disclosure?
- Can I determine if/when/how/with-whom/of-what a breach occurred?
With the answers to questions like these in hand (and yes, there are more), you can tag your data, process and presentation models with security and access profiles.
In the SQL context, these access profiles distill into authentication plans (roles, schemata (pretentious?), access group (much better than individual user) authorization, SQL vs. Windows login, etc.). Further, “SQL” is not limited to SSDS; it must include SSIS, SSAS and SSRS as part of a comprehensive plan.
As you noted, stored procedures in their own schemas (there, I’ve said it), by indirection, provide access control. I’ve worked for clients who disallow all direct table access (except for production DBAs whose responsibilities required it) and require that views be created to expose the data in a controlled fashion. Here again, (schema based if needed/appropriate) access control can be provided.
As is evident, authentication and access control is only a slice of the pie, but again, I expand the subject matter.
It is, however, a slice that can be addressed early, well and with tools ready to hand.
Mark:
The last shop I was in started with open, free form security generated and maintained in source by developers. This worked when there were three developers, but as the shop grew standardization of the security was not a primary concern and led to a mess. The largest problem was an inconsistency from environment to environment, making following a build process from dev to test to stage to prod nearly impossible.
Setting up a security model (using database schemas), separating code and table in different schemas, and automating the deploy of security principals stabilized the environments which paved the way for a build process to be put in place.
The seperation also led to a seocnday benefit in that developers had to involve dbas when they were going directly from managed code to the data. This allowed evealuation of the efficiency and effectiveness of data access we would have not known about until it failed in prod.
Setting up this security model improved every aspect of software development at the shop by forcing consistency as well as making it necessary for devs and sql devs and dbas to work together to accomplish the goal, getting positive contributions from all.
Chris:
Ben, I have dealt with this issue first hand and the reality is, that is very common in today’s market place. The other frightening reality of it all is that Dev/App teams using SQL Authentication store the web config login and password in plain text. What does that mean? That means if your SQL app login has dbo rights, then so does the developer that has access to that web config and they can do whatever they want on the database.
All your points are valid and we could go on forever discussing the pitfalls of SQL Authentication and permissions. Instead, why don’t we address the real root issue here which is why aren’t more companies using Integrated security for their web configs. Using a domain login for the application to authenticate to the database is much more secure and it not visible to App developers to use or a hacker that might have hacked into SQL box or your network. I could go on and on, but reality is that, yes, SQL Authentication is used for applications to authenticate to the database, but is it best practice? In my opinion, the answer is no.
Useful Cartesian Products
I thought today would be a good time to talk about the benefits of a little used JOIN capability when writing your TSQL queries. In SQL Server you use the CROSS JOIN syntax to enable a Cartesian product. Consider the following SQL
SELECT Quaters.QuarterLable,
Regions.RegionLabel,
ISNULL(QuarterlySales.TotalSales, 0) AS Sales
FROM Quarter
CROSS JOIN Region
LEFT OUTER JOIN QuarterlySales ON Quarter.QuarterID = QuarterlySales.QuarterID
AND Region.RegionID = QuarterlySales.RegionID
This query joins every quarter in the Quarter table with every Region in the Region table. There is no criteria for the join of these two tables resulting in a row for every possible combination.
The results of the CROSS JOIN are then outer joined to the QuarterlySales datamart table. This query results in a list of every quarter for every region, regardless of activity that may have occurred. Say the company only expanded into a region for the last quarter.
Because of the CROSS JOIN, that region will be represented for every region/quarter. Then by performing an outer join to the QuarterlySales table, any region with no activity in a particular quarter returns null. The ISNULL function turns missing data into the value 0.
This is one way a Cartesian product may be used to your advantage. There are many others. This is one of my favorite interview questions for Application developers or DBA candidates. Believe it or not, this is something you may use more often than you would guess.
If you drop an Email to btaylor@sswug.org with ways you have used CROSS JOIN I would gladly include it in future newsletters.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with SQL Server 2008 R2 Business Intelligence Development Studio
In this article, Alexander Chigrik explains some problems that you can have with SQL Server 2008 R2 Business Intelligence Development Studio.
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)