MySQL user question

[mcdougrs]

Hey guys sorry for posting another MySQL user question but I never really got the first one answered.

Ok if I issue this command:

mysql> grant select,insert,update,delete,create,drop 
-> on *.* to someusr@"%" identified by 'passwrd';

I already know that this statement creates the user someusr w/ password=passwrd and it can connect from anywhere. What I don't know: Can this user now mess up the privilage tables? Can this user mess with other users DBs and tables?

I want to create a normal user for me that can create my own DBs and delete them if I want (it my stuff anyway ) but I don't want me to be able to see or do anything to anybody elses stuff. Would I then have to run this command???

mysql> grant select,insert,update,delete,create,drop 
-> on somedb.* to someusr@"%" identified by 'passwrd';

And create this test DB so that this user can have initial privilages on some DB then that user will be ale to create their own DBs at will?? I just don't get the whole user aspect of MySQL... I want it to be like a normal *nix user create their own crap and delete it if they want... but don't touch whats not theirs! Is this accomplished with either of those commands, if so which one, if not how do I do that?

PLEASE I'M GOING CRAZY trying to figure this out... my book doesn't say anything about what EXACTLY a user can/can't touch with which privilages :-\ ??? :-\

[mcdougrs]

Why can't MySQL just rely on the OS for the user privilages? I guess it gets more difiicult when you have several people on one DB all with different purposes... but then make that a group and then give thier group ownership of the DB... I don't know just WAY to confusing for me right now good night y'all

[Stix]

I feel your pain man,
I too would like to have this question answered. I mean what, do I have to take a class on SQL users ?

Far too complicated if you ask me.

[mcdougrs]

Well some good news and some bad news... I'll give you the bad news first.

Bad News:
That's not how it works. You can grant privileges for "any database", but you can't grant privileges for "any database except certain databases".

Good News:
One thing that may help is that you can use a pattern to specify the database name in the GRANT statement. Then the user can create any database that has a name matching the pattern. For example, GRANT ALL ON "xyz%".* ... will allow the user to create and mess with any database having a name that begins with "xyz".

This is according to Paul DuBois... he has a book out for MySQL I think his knowledge is credtible