How to Create Databases with Same Name for Different MySQL Users

mysql-5.5

I am using MySQL 5.5 running on Ubuntu 12.04.

I have created two different users as 'root' and 'abc' and I want to create 'Items' database in this both user accounts but I want this two databases separated.

Now my problem is that I have created 'Items' database in root user with 10 tables and when I am trying to create a new user 'abc' then 'Items' database comes automatically in that new user with 10 tables and that is what I don't want to do.

How I can create 2 same name databases in different user accounts with different tables in both DBs?

Best Answer

Users on MySQL are sharing the same context in terms of databases. So if you are creating a database for one user, it will be also visible to user2.

You could solve this issue by prefixing your tables for each user, e.g. abc_item1 and setting correct privileges via grant for it -- so only one user is allowed to use this table.

Another way would be to just create different databases for each user -- which is I think the MySQL way of doing such things.

Related Question