Are There That Many GUIDs?

Question

Are There That Many GUIDs?

Viewing 15 posts - 121 through 135 (of 169 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Jeff Moden (10/26/2010)
As a side bar, the article also states...
Computers with ethernet/token ring addresses generate UUIDs that are guaranteed to be globally unique.

Yup.

So, does anyone know of an article that guarantees that ethernet addresses a globally unique because that's the only way such a guarantee could be made.

Aside from the article you just read you mean? 😛

There's plenty out there - use your favourite search engine 🙂

I also don't like the idea of the MAC address being involved and need to find out if the MAC address could be reverse engineering on sequential GUIDS. I know they could be reverse engineered on true type 1 GUIDs, but I don't know if these are true type 1 GUIDS.

Yes there are, and yes it is. There is some byte re-ordering performed in SQL Server to fool other parts of the engine into believing they are sequential, but it's not at all hard to undo that, and obtain the MAC address. The following code snippet demonstrates the byte reordering performed:

WITH FiveGUIDs (the_guid)

AS (

SELECT NEWID() UNION ALL

SELECT NEWID()

)

SELECT -- The GUID in string form

G.the_guid,

-- Converted diectly to BINARY(16)

CA.as_binary16,

-- The 'correct' decoding

decoded =

SUBSTRING(CA.as_binary16, 11, 6) +

SUBSTRING(CA.as_binary16, 09, 2) +

SUBSTRING(CA.as_binary16, 07, 2) +

SUBSTRING(CA.as_binary16, 05, 2) +

SUBSTRING(CA.as_binary16, 01, 4)

FROM FiveGUIDs G

CROSS

APPLY (

SELECT CONVERT(BINARY(16), G.the_guid)

) CA (as_binary16)

ORDER BY

G.the_guid;

Personally, I don't care that someone could decode my MAC address and work out at what time I generated the GUID, but your paranoia level may vary.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

The whole reason that MS changed the NEWID() from Type 1 to Type 4 was because of the security risks of exposing the MAC address. If they thought it was that much of a security risk as to make such a change as the GUID no longer being globablly unique, I'll probably go along with the notion that it actually was a security risk and not use Type 1's.

Heh... listen to me ... what am I saying???? Despite living through several company migrations, I haven't even found the need for Type 4's, yet, never mind Type 1's. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

Jeff Moden (10/26/2010)
]If they thought it was that much of a security risk as to make such a change as the GUID no longer being globablly unique, I'll probably go along with the notion that it actually was a security risk and not use Type 1's.

What do you mean by "GUID no longer being globally unique"? :unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Paul White NZ (10/26/2010)
So, does anyone know of an article that guarantees that ethernet addresses a globally unique because that's the only way such a guarantee could be made.
Aside from the article you just read you mean? 😛

BWAA-HAAA!!! Oh no... Not quite what I meant. I know that 48 bit MAC addresses are larger than they'll ever use and when they run out of those in about a billion years, they can switch over the the 64 bit version. 😉 What I meant was the safe-guards a manufacturer uses to not accidentally repeat numbers. Some of the less popular brands may not be so careful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 5

Jeff Moden (10/27/2010)
BWAA-HAAA!!! Oh no... Not quite what I meant. I know that 48 bit MAC addresses are larger than they'll ever use and when they run out of those in about a billion years, they can switch over the the 64 bit version. 😉 What I meant was the safe-guards a manufacturer uses to not accidentally repeat numbers. Some of the less popular brands may not be so careful.

There are some details on how ranges are assigned here: http://en.wikipedia.org/wiki/MAC_address

As far as the finest level of detail is concerned - how the individual manufacturers ensure that burned-in addresses are never assigned more than once - I have no idea.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

Some of the smaller manufacturers aren't careful. In the 90s we had two separate NICs have duplicate MACs when we bought an off brand.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Steve Jones - SSC Editor (10/27/2010)
Some of the smaller manufacturers aren't careful. In the 90s we had two separate NICs have duplicate MACs when we bought an off brand.

I know I'm simple to please but you have no idea how happy that paragraph just made me. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 8

Jeff Moden (10/27/2010)
Steve Jones - SSC Editor (10/27/2010)
Some of the smaller manufacturers aren't careful. In the 90s we had two separate NICs have duplicate MACs when we bought an off brand.
I know I'm simple to please but you have no idea how happy that paragraph just made me. 🙂

Why? The network card address is just one component of a version 1 GUID - what conclusions are you drawing here?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Eric M Russell SSC Guru Points: 125560 More actions · Answer 9

I still think that combining a logical identifier like client_id or store_id with another column containing either a date/time stamp or sequential integer makes more sense than stamping the network card's MAC address and random data on every transaction record. If someone on the implementation team screws up and assigns the wrong client or store id, then the installation and data needs to be straightened out before it's consolidated into another database. If you're merging data from multiple independent sources, then the ETL process can insert an additional column identifying the data source. Using GUIDs as identifiers makes sense in non-relational scenarios, like identifying your Windows installation or as an object class id.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eric M Russell SSC Guru Points: 125560 More actions · Answer 10

Steve Jones - SSC Editor (10/27/2010)
Some of the smaller manufacturers aren't careful. In the 90s we had two separate NICs have duplicate MACs when we bought an off brand.

Of course, the MAC address can also be spoofed at the operting system level and stubbed with some number like "000000000000". I don't know why, but where there's a way, there's a will.

http://www.klcconsulting.net/Change_MAC_w2k.htm

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Paul White SSC Guru Points: 150467 More actions · Answer 11

Eric Russell 13013 (10/28/2010)
I still think that combining a logical identifier like client_id or store_id with another column containing either a date/time stamp or sequential integer makes more sense than stamping the network card's MAC address and random data on every transaction record.

Indeed. I don't think anyone is suggesting replacing perfectly good keys with GUIDs just for the sake of it. That's not to say that GUID keys haven't ever been used inappropriately - they sure have.

Nevertheless, there are some systems that are much better suited to GUID keys than any alternative scheme. Until you have worked on such a system, I accept that it may be hard to visualise quite why it should be so.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Thomas-282729 Right there with Babe Points: 715 More actions · Answer 12

Eric Russell 13013 (10/28/2010)
I still think that combining a logical identifier like client_id or store_id with another column containing either a date/time stamp or sequential integer makes more sense than stamping the network card's MAC address and random data on every transaction record. If someone on the implementation team screws up and assigns the wrong client or store id, then the installation and data needs to be straightened out before it's consolidated into another database. If you're merging data from multiple independent sources, then the ETL process can insert an additional column identifying the data source. Using GUIDs as identifiers makes sense in non-relational scenarios, like identifying your Windows installation or as an object class id.

My experience is that this does not work in a multi-tenant scenario where the tenants are separate corporations. If you have multiple stores in the same organization, you can ensure that each store have a unique identifier. If you have multiple customers from desperate corporations using your database product solution, ensuring the mutually exclusive tenant identifiers is not realistic.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

Paul White NZ (10/27/2010)
Jeff Moden (10/27/2010)
Steve Jones - SSC Editor (10/27/2010)
Some of the smaller manufacturers aren't careful. In the 90s we had two separate NICs have duplicate MACs when we bought an off brand.
I know I'm simple to please but you have no idea how happy that paragraph just made me. 🙂
Why? The network card address is just one component of a version 1 GUID - what conclusions are you drawing here?

I won a $100 bet on it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 14

Jeff Moden (10/29/2010)
Paul White NZ (10/27/2010)
Jeff Moden (10/27/2010)
Steve Jones - SSC Editor (10/27/2010)
Some of the smaller manufacturers aren't careful. In the 90s we had two separate NICs have duplicate MACs when we bought an off brand.
I know I'm simple to please but you have no idea how happy that paragraph just made me. 🙂
Why? The network card address is just one component of a version 1 GUID - what conclusions are you drawing here?
I won a $100 bet on it. 😉

Let's see if I can get this right:

BWWAAAAAAAAHAAAAAAAAA!!!!!

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 15

Jeff Moden (10/29/2010)
I won a $100 bet on it. 😉

Wow!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi