User priviledges question

[coza73]

Is it possible to setup a mysql user to be able to create new databases but only be able to access/edit/see their own databases, and none of the existing databases or ones created by other users?

i.e multible uses on one server creating, editing and removing their own databases with no access to any other databases on the server. And root to have global access to all databases created.

Thanks.

[shoz]

The following GRANT should allow the users to create databases starting with "username_" and unless there's an error already in the permissions, users shouldn't be able to see databases they haven't been given explicit access to.

[code]
GRANT ALL ON `username\_%`.* TO username@localhost;
GRANT ALL ON `username\_%`.* TO username@localhost.localdomain;
[/code]

Users by default have access to Db 'test' btw.

[coza73]

Worked great, thank you.

;D

[shoz]

I should also mention that if you're adding new users you'll want to also add
[code]
IDENTIFIED BY 'password here'
[/code]
to the end of the GRANT statement or you'll create users with blank passwords.

[code]
GRANT ALL ON ... IDENTIFIED BY 'password'
[/code]