September 2, 2009 at 11:47 am
ddl information for problem
create table goods (
goodsplaced id primary key int identity (1,1),
name varchar(50)
)
Hey guys, I have a design that increments an identity column with a seed and increment value i.e. identity (1,1) e.t.c. For security purposes however, what is the best way to implement this design in such a way that everytime a good is placed, it gives random numbers (descending or ascending) as opposed to the regular fixed numbers that the identity provides.
Any other way besides changing the seeds and incremental value of the identity? what about the newid()? any suggestions would be appreciated. Thanks
September 2, 2009 at 11:49 am
I can think of a couple of ways to achieve that. Easiest would be checksum and newID. But I have to ask what that could possibly have to do with security? I don't see any possible connection between randomizing your ID numbers, and security.
- 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
September 2, 2009 at 12:16 pm
well, maybe not exactly security, but more like preventing some people from guestimating how many goods placed by looking at the id and doing some calculations. How can I implement that with the checksum and newid () please?
September 2, 2009 at 12:24 pm
why not just seed your identity at 10,000, so they could only infer from 10,000 to 99,999;
an identity serves a much better purpose for indexing than a randome number ever would.
anyway,
here's an example of creating a random number, just by using a default value:
create table #test( testtext varchar(30), z varchar(5) DEFAULT(REPLACE( STR( ABS( CHECKSUM( NEWID() ) ) % 10000, 5 ), ' ', '1' ) ) )
insert into #test(testtext)
select 'apples' union all
select 'grapes' union all
select 'bananas'
select * from #test
Lowell
September 2, 2009 at 12:32 pm
also, you don't need to expose your Id to end users.
another post a while back asked how to generate a combo varchar/number field, like AAA001 thru ZZZ999;
would something like that help, so you can hide your PK?
--three char table: bigger range
CREATE TABLE X(XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CHECK ((XID> 0) AND (XID < 196040000)),
XCALCULATED AS
CHAR((XID/260000)%26+65) --1st Letter
+ CHAR((XID/26000)%26+65) --2nd Letter
+ CHAR((XID/1000)%26+65) --3rd Letter
+ REPLACE(STR(XID%10000,4),' ','0'), --The 4 digit numeric part
SOMEOTHERCOL VARCHAR(30)
)
INSERT INTO X(SOMEOTHERCOL) VALUES('WHATEVER')
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(675999,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SET IDENTITY_INSERT X ON
INSERT INTO X(XID,SOMEOTHERCOL) VALUES(676000,'MORESTUFF')
SET IDENTITY_INSERT X OFF
SELECT * FROM X
--Results
XID XCALCULATED SOMEOTHERCOL
1 AAA0001 WHATEVER
675999 CZZ5999 MORESTUFF
676000 CAA6000 MORESTUFF
Lowell
September 2, 2009 at 12:37 pm
iruagawal (9/2/2009)
well, maybe not exactly security, but more like preventing some people from guestimating how many goods placed by looking at the id and doing some calculations. How can I implement that with the checksum and newid () please?
As mentioned, set the seed to either a higher or lower value when you start.
If, for example, you set the starting seed at negative 2-billion (-2000000000), with an increment of 1, you certainly won't get anyone infering any useful data from it. Plus you just about double the number of valid identies for the table. (Assuming Int data type.)
- 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
September 2, 2009 at 12:41 pm
Hey Lowell and Gsquared, Thanks for taking time out to help. Quick questions, would that default random number generator code be good for performance (indexing e.t.c)? How would seeding it from 10,000 help? They can guestimate that too if they place back to back to back goods can't they? Thanks for the code
September 2, 2009 at 12:48 pm
The random number piece should perform well, but it's no good as a primary key or as the leading edge of a clustered index, which kind of defeats the purpose of having it in the first place.
I'm still not clear on why anyone would ever need to see the ID value in the first place, or, if they do, how that would give them any data that actually matters.
- 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
September 3, 2009 at 12:00 am
I agree it seems like an odd requirement, but that said:
One way to do this would be to dump the IDENTITY column and use a Sequence Table instead. This is essentially a secondary, internal-only table that holds the next value to allocate to a new row. You could start off around -9223372036854775808 (for a BIGINT) and add small random values each time the Sequence Table is used.
Here's a link to an example I posted some while ago: Link
The requirement was very different, but hopefully you will see the idea. The core technique is in the Demo.usp_Allocate procedure and the dbo.AllocationMaster table.
Anyone wanting to take me up on the relative merits of sequence tables had better be in determined mood :laugh:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 6, 2009 at 7:22 am
Paul, your idea is quite interesting but when you are using NewId() - there are "only" standard problems with indexes (you can use fillfactor and proper index defargmantation strategy to avoid it).
your idea require some additional afort during running apps (generating new values, optimizing access to this table during inserts) - of course like always it depends but for small/medium apps i prefer to use newid when i need to avoid "id guessing"
September 6, 2009 at 4:21 pm
Marcin Gol [SQL Server MVP] (9/6/2009)
Paul, your idea is quite interesting but when you are using NewId() - there are "only" standard problems with indexes (you can use fillfactor and proper index defargmantation strategy to avoid it).your idea require some additional afort during running apps (generating new values, optimizing access to this table during inserts) - of course like always it depends but for small/medium apps i prefer to use newid when i need to avoid "id guessing"
Thanks. Yes both approaches have merit, and both have advantages and disadvantages.
As far as issues with page splits, fill factors, and defragmentation are concerned - I would make this a non-clustered primary key in all probability.
A pseudo-random value is a poor choice for a clustered index (however implemented), and it is likely only going to be used for singleton lookups - for which fragmentation is not a concern. Page splitting on an NC index using a 4-byte INT column would probably be tolerable anyway (maybe slightly less so for a 16-byte UNIQUEIDENTIFIER - who knows?) but the index will likely be small anyway...
If large range/full index scans were to be common, an appropriate fill factor and index reorganization/rebuilding strategy would work well.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply