[Opensim-users] Uniqueness in MySQL 'useraccounts'

Wed Mar 7 02:18:24 UTC 2012

The problem here is the ScopeID, which I believe allows a useraccounts table to host users on different grids.  Two 
users on different grids could have the same name.

I think this question is much better asked on the opensim-dev mailing list since not all developers subscribe to this list.

On 06/03/12 13:54, Kevin Buckley wrote:
> The original question was whether there was an issue with giving the
> composite key 'Name' (in useraccounts) the UNIQUE attribute as that is
> useful when implementing a concurrent sign-up system.
>
> And I guess the next question would be: is it possible to make 'Name' UNIQUE
> by default in the Opensim release?
>
>
> -----Original Message-----
> From: opensim-users-bounces at lists.berlios.de
> [mailto:opensim-users-bounces at lists.berlios.de] On Behalf Of Diva Canto
> Sent: 06 March 2012 03:31
> To: opensim-users at lists.berlios.de
> Subject: Re: [Opensim-users] Uniqueness in MySQL 'useraccounts'
>
> 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
>>
>>
>
> _______________________________________________
> 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
>

-- 
Justin Clark-Casey (justincc)
http://justincc.org/blog
http://twitter.com/justincc