Connect Digest : 2009-07-18

AaronBertrand — Sat, 18 Jul 2009 14:54:00 GMT

I missed last week because I was having fun up in Canada... mostly without any kind of computer access at all. It was a nice break, but now I'm back in the thick of things again. So this week, I am going to try to beef it up a bit to compensate for last week's missing entry.

===================================

DELETE vs. DROP

I have always found the mix of these two terms a little perplexing, but over time I see more and more people asking questions like, "can I issue DELETE against a stored procedure?" and, "I ran DELETE dbo.table, but dbo.table still exists, why?" Part of the confusion comes from the fact that if you right-click a table in Object Explorer, there is a context menu item that says "Delete" but this is actually a DROP which drops the table, not a DELETE that deletes the data in the table. I find this confusing, but Microsoft's response is that changing this to DROP would make this more confusing. I disagree with this and I hope you do as well. I would hope that they could add a DROP menu option, and if they want to support a DELETE also then they could add an option like "Edit Top N rows" - which should of course give you the opportunity to edit the WHERE and ORDER BY clauses before running. And of course there should never be a "Delete" menu item for a stored procedure; DROP is more appropriate. The tool shouldn't be used as encouragement for people to not bother learning the difference between DROP and DELETE.

#473286 : SSMS : change "Delete" to "DROP" on context menu for objects

===================================

YYYY-DD-MM is for the birds

Fellow MVP Steve Kass has been a huge proponent of deprecating the YYYY-DD-MM format for DATETIME values. He didn't get his wish fulfilled for SQL Server 2008, so now there are all kinds of confusing behaviors with the new DATE/DATETIME2 types (which behave correctly) and the legacy DATETIME/SMALLDATETIME types (which do not). So at the very least, the behavior should be better documented.

#290971 : Deprecate the date literal interpretation 'YYYY-DD-MM'

#363229 : Confusing and undocumented behavior changes in some expressions involving literal dates

===================================

Seeing too many databases

I have been complaining for a long time that when you connect to a shared SQL Server with hundreds of databases, and you only have access to a few ( or in a lot of cases one), you still see all of the databases you don't have access to, and have to wait for Object Explorer to enumerate them all. They fixed a few bugs early on which actually caused errors when certain properties were checked against the databases you didn't have access to, which created havoc for at least one ISP I know of when their users started upgrading to the 2008 version of SSMS. And I searched around for quite a while, certain that this was a duplicate, but I couldn't find an existing Connect item that treated the true core of the problem. Thankfully one was created the other day:

#474490 : Limiting/restricting view of SQL Server databases in Object Explorer

===================================

Easily determining path information

Fellow MVP Louis Davidson ("Dr. SQL") makes a good point, that we have very ugly hacks in place for programmatically determining the configuration for the locations of key file types such as data, log, error, etc. He is asking for something relatively simple: a new DMV (or additional columns in an existing DMV) that exposes this information. This is one of the few pieces of data that is easier to get through the SSMS UI than through code.

#474826 : Access to path information about SQL Server

===================================

Statistics enhancements

"Mpls Mike" filed two items that I can agree with. They are quite similar in that they are both asking for more information in the DMV sys.stats and the STATS_DATE() function. One is that there should be columns indicating the date/time when the stats were created and when they were last updated; the other asks for columns that indicate when the stats have become stale or otherwise invalid.

#475268 : Add create_date and modify_date to sys.stats catalog view

#475270 : Add column to sys.stats to indicate if the optimizer considers statistics out of date/invalid

===================================

Can't perform index maintenance on CDC-enabled tables

This is a big one that was unfortunately missed by a lot of us throughout the SQL Server 2008 beta. If you have Change Data Capture enabled on a table, any ALTER statement fails, including simple index maintenance scripts. You can use DBCC DBREINDEX instead, but this is not a perfect answer either, since this command has been deprecated in favor of ALTER INDEX ... REBUILD.

#474589 : Alter index on CDC enabled tables

===================================

SQL Server Agent and job history purging

Ranga Narasimhan pointed out that, depending on your retention settings and number of jobs, you can easily lose all of the history for a job. I agree that this can be troublesome on busy servers where you often find out about job failures long after they've failed. The item is marked as Closed (Won't Fix) but if you read the comments you'll see that this is a mistake that they haven't yet corrected.

#368649 : Leave at least one Job history entry for a job

===================================

Limited typing capabilities in "Edit Top N Rows"

Last week when I was trying to reproduce an end user issue, I came across an annoying behavior in the Open Table Edit Top N Rows dialog, where if you type SHIFT+SPACE (e.g. type "FOO BAR" while holding the Shift key) while editing the cell data, you get dumped out of the cell (however you can keep typing) and only "FOO" appears. This can be pretty annoying and counter-intuitive; while my "workaround" is to revert to what I always do (write DML statements like a normal person), this is still something that should be fixed if they want people to continue trusting the UIs within SSMS.

#473303 : SSMS : "Edit Top n Rows" mishandles a space character

===================================

So, I hope those are enough items to keep you busy for a while. :-)

Hacking Social Security Numbers

Mike C — Mon, 13 Jul 2009 00:09:00 GMT

According to this paper from the Proceedings of the National Academy of the Sciences (PNAS), social security numbers (SSNs) are pretty easy for hackers, identity thieves, and other miscreants to predict based on publicly available data. I found this interesting partly because I just recently (a few months ago) wrote a chapter for a book discussing security for SSNs.

Here's the deal - all SSNs have a very regular structure that looks like this: xxx-yy-zzzz. With 9 numeric digits there are 1 billion possible combinations that can be assigned. And of course we have the same information that identity thieves have - the rules for SSN assignment are posted for the public at the Social Security Administration website.

Here are some of the key rules that determine how SSNs are assigned, summarized from the SSA website:

xxx is a 3-digit Area Number, and is assigned based on the ZIP Code from which the request to assign the SSN originates.
yy is a 2-digit Group Number, which is assigned in a predictable (nonconsecutive) order. The order of assignment of Group Numbers is also documented on the SSA website as well. It's always a number between "01" and "99".
zzzz is a 4-digit Serial Number, which is a number between "0001" and "9999".
There are a few stray SSNs that have been taken out of circulation for various reasons (used in marketing campaigns, etc.)
And of course no SSN is ever reassigned.

According to the rules a bad guy can narrow down the scope of his search substantially just by eliminating all SSNs that begin with 8xx, 9xx, 666, and 000. That eliminates a couple 100 million+. No SSNs have been assigned with a Group Number above 772, eliminating tens of millions in the 773 - 799 range. No SSNs have, or will be, assigned with Group Numbers of 00 or Serial Numbers of 0000, eliminating millions more. In addition the Group Numbers that have been assigned are available from the SSA website high group list, knocking hundreds of millions more possible SSNs off the list.

This is just the beginning -- it gets better:

If you know where a person applied for their SSN (in many cases this will be where they were born, or close to it) you can use the SSN Allocations list to narrow down the search substantially. In some cases this won't work though, since some parents don't apply for an SSN for their child immediately at birth.

All this is to show how an identity thief can use the location and approximate date of birth to accurately guess the first 5 digits of the SSN. The PNAS authors were able to correctly guess the first 5 digits of SSNs with a single try for 44% of their test records.

At the other end of the spectrum, identity thieves can use the SSA's Death Master File (DMF) to narrow down the last 4 digits (the Serial Number). The PNAS authors used the DMF to figure out statistical distributions of SSN Serial Numbers to dramatically narrow down the last 4 digits. They correctly guessed the complete SSNs for 8.5% of the test records with less than 1,000 attempts each; making the SSN for 8.5% of those tested less secure than a 4-digit ATM card PIN (in fact the authors compared it to an insecure 3-digit financial PIN).

The authors' testing showed that overall full SSNs can be guessed with an accuracy of between 0.08% to 10% with less than 1,000 attempts each. In rural areas they guessed complete SSNs at the rate of >60% for rural areas on the very first attempt.

To put some hard numbers to it, the authors estimated (based on various fairly reasonable assumptions), that an identity thief targeting a specific location (like a given state) could guess SSNs and obtain credit card accounts at the rate of about 47 per minute.

Makes you wonder how secure your SSN is, really.

Search results matching tags 'Statistics' and 'security'

Connect Digest : 2009-07-18

Hacking Social Security Numbers