[Opensim-users] Uniqueness in MySQL 'useraccounts'

Tue Mar 6 03:31:29 UTC 2012

Users are uniquely identified with UUIDs. If you search out there, 
you'll find the probability of UUID collisions, which is very small. But 
that mathematical probability doesn't really matter compared to the high 
probability that *people* will do all sorts of weird things regarding 
UUID reuse, some by accident, some maliciously.

The Hypergrid has security in place for dealing with collisions of user 
UUIDs. Basically if an HG visitor with a certain UUID comes into a grid 
where there is a local user with the same UUID, that visitor is denied 
entry.

On 3/5/2012 12:26 PM, Karen Palen wrote:
> As was pointed out, hypergrid avatar names are not stored in the
> database, however this is only one possible source of confusion or
> duplication if only FirstName + LastName are used.
>
> As far as I know, the only reliable identifier of an avatar is the UUID
> in the database, but that is entirely local! The UUID for example is
> used to identify the Creator and Owner of objects for permissions.
>
> My understanding is that you are correct in identifying unique hypergrid
> IDs, but uniquely identifying a hypergrid visitor is an area where much
> work is being done. This is important when assigning permissions to
> objects created/owned by a visiting Avatar for example.
>
> I have not looked at this for nearly a year now (i.e. several OpenSim
> revisions!) so there are almost certainly things that are different now.
>
> I have always found that a few test cases combined with inspection of
> associated the database entries via an SQL manager  package is the
> easiest and fastest way to nail down exactly what is happening (as a
> Linux hacker I like MYSQL Admin, but it is only one of many). This
> process is aided greatly by using a Diva distro to create a "one of"
> grid with only the database entries of interest.
>
> Karen
>
> On 03/05/2012 09:54 AM, Kevin Buckley wrote:
>> Hi Karen
>>
>> As far as I understand (or can guess!), the concept is that the AV UID
>> (PrincipalID) and asset UID's are created by using a timestamp variant of
>> UUID (so I'm using the one provided conveniently by MySQL which provides
>> temporal separation plus 48 bit random, spatial separation).  So that takes
>> care of the nuts and bolts.
>>
>> And yes I can see that, in the hypergrid case, there is the issue of names
>> which are unique within each OS implementation (which I'm trying to deal
>> with now) but not able to be guaranteed unique everywhere as they are chosen
>> by people who are not known for their randomness.  I am assuming that the
>> combination of locally enforced, unique FirstName+LastName plus host ID,
>> provides a globally unique name scheme even in the hypergrid scenario.
>>
>> Is that correct?
>>
>> Is the host ID you referred to, the ServiceURL field in the same table
>> (useraccounts)?
>>
>> Kevin
>>
>> -----Original Message-----
>> From: opensim-users-bounces at lists.berlios.de
>> [mailto:opensim-users-bounces at lists.berlios.de] On Behalf Of Karen Palen
>> Sent: 05 March 2012 16:10
>> To: opensim-users at lists.berlios.de
>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>
>> I suspect the answer is that you need more information than just those
>> two fields.
>>
>> I am thinking of the fairly common case in Hypergrid usage where two
>> avatars have the same name, distinguished only by the field that tells
>> their host system.
>>
>> I have used this for testing on many occasions.
>>
>> Karen
>>
>> On 03/05/2012 04:45 AM, Kevin Buckley wrote:
>>> Hi - Thanks for the quick response.
>>>
>>> I understand the reasons why PrincipalID is the Primary Key and hence
>> should
>>> be unique (and I now realise my original question was malformed).
>>>
>>> The actual question is: Why isn't 'Name' (the composite key including
>>> FirstName and LastName) defined as Unique?
>>>
>>> Defining it as Unique certainly works and causes MySQL to throw Error 1062
>>> in response to an attempt to create  a duplicate FirstName/LastName entry
>>> which is the (my) required behaviour.
>>>
>>> So I am wondering why 'Name' isn't defined as Unique when the database
>>> tables are created (or upgraded) by Opensim, given that it goes to the
>>> trouble of defining it in the first place!
>>>
>>> Thanks.
>>>
>>> -----Original Message-----
>>> From: opensim-users-bounces at lists.berlios.de
>>> [mailto:opensim-users-bounces at lists.berlios.de] On Behalf Of M.E. Verhagen
>>> Sent: 04 March 2012 23:42
>>> To: opensim-users at lists.berlios.de
>>> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>>>
>>> the PrincipalID field is already the primary key.
>>>
>>> There can be only one primary key.
>>>
>>> This uuid key is the unique identifier on wich an avatar is idenified.
>>>
>>> When you write you own member system with direct database manipulation
>>> than you should check if the avatar is unique or else you will end up
>>> with more than one avatar name attached to an uuid.
>>>
>>>
>>>
>>> 2012/3/4, Kevin Buckley<kevin at buckley70.freeserve.co.uk>:
>>>> I hope this is not covering old ground.  I'm playing with a member system
>>>> for standalone OpenSim and I'm trying to decide the best way to deal with
>>>> the FirstName/LastName uniqueness issue (as my sign-up process allows
>>>> multiple, concurrent users).
>>>>
>>>>
>>>>
>>>> I notice that the default useraccounts table has a composite key called
>>> Name
>>>> (which consists of FirstName and LastName).  But it isn't defined as a
>>>> primary key.  So duplicate name inserts are not trapped as a result of
>>> that
>>>> (I believe that is the reason, anyway).
>>>>
>>>>
>>>>
>>>> Does anyone know if there is a reason 'Name' is not defined as a primary
>>>> key?  Is there a problem with making it so?
>>>>
>>>>
>>>>
>>>> I have other ways I can deal with this but trapping it at the
>> useraccounts
>>>> table insert would be the best way.
>>>>
>>>>
>>>>
>>>> Thanks.
>>>>
>>>>
>> _______________________________________________
>> Opensim-users mailing list
>> Opensim-users at lists.berlios.de
>> https://lists.berlios.de/mailman/listinfo/opensim-users
>>
>> _______________________________________________
>> Opensim-users mailing list
>> Opensim-users at lists.berlios.de
>> https://lists.berlios.de/mailman/listinfo/opensim-users
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>
>