6

I've searched for an answer to this and all I can seem to find are questions asking whether it is better to use multiple databases or multiple tables in a single database. This isn't my question though.

QUESTION 1.
I want to set up a new database alongside my current DB and don't know how. I want to give a user full admin access to DB2 without seeing DB1. This way I can host a friend's site and they can create and delete as many tables as they want without disturbing my own DB. I could also use it for demo sites that aren't secure and shouldn't exist inside my primary DB.

I figure I could do this pretty easily with a virtual machine and run a separate instance of MySQL but my resources are limited so that isn't really an option.

I'd like to set this up in one of 2 ways. I would prefer to have "server.example.com" host both DBs and open the proper DB based on user login. If not I could do it routing to server1.example.com and server2.example.com.

QUESTION 2.
If this isn't possible I'd like to know how to properly set up restricted access to a single DB in sequel pro. I have been messing around with it and so far prefer it to PHPMyAdmin. For some reason if I set up a new user with no permissions they have full access to my 'information_schema' and 'test' tables but can't create new tables. I don't want other users to access these tables though and I want them to be able to set up their own tables. I'd like to set it up so a new user can create a limited number of tables and only see and edit those tables. I can't seem to find information on this either.

Even if my first question is possible I'd like to know the answer to question 2. I've been searching for a long time and can't find reliable information anywhere. Maybe my brain is just tired...

Improve this question

4 Answers 4

Reset to default
2

You can set up multiple instances of mysql but for your situation you are better off creating different databases within the same instance.

You can create databases and then add users that only have access to manipulate the database they are given and nothing else.

Essentially the heirarchy is as follows:

Mysql (root or any other super user can see everything)
- Your DB
  - Your Users
    - Your tables/functions/Procedures/etc
- Their DB
  - Their Users
    - Their tables/functions/procedures/etc.

You basically separate the access for each, and in PHPMyAdmin it is very easy. The steps are:

  1. Add Database enter image description here)
  2. Add User, restricting them to that database allowing only priveleges you want to give to that user and only to that database. (Guide here)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Cool. This looks really easy. I'll try it out after lunch.
Yeah even in console it isn't hard. You essentially follow the same procedure.
2

You can grant access to different database to different user using GRANT in MySQL.

https://dev.mysql.com/doc/refman/5.1/en/grant.html has the information you need.

The most simple you can do is

CREATE DATABASE db_for_user_a
CREATE DATABASE db_for_user_b
GRANT ALL PRIVILEGES ON db_for_user_a.* TO user_a IDENTIFIED BY 'user_a_s_password'
GRANT ALL PRIVILEGES ON db_for_user_b.* TO user_a IDENTIFIED BY 'user_b_s_password'
Improve this answer

Comments

0

You are going to need to provide more information about your set up to answer this question of setting up multiple databases specifically.

Servers typically have methods to create multiple databases with software that is designed specifically to run on those platforms (Apache, and Windows server are a couple servers that can run software like WAMP or phpMyAdmin to manage these databases).

And in answer to the permissions: Yes, you can designate users that can have specific privileges on one, both, or neither of the databases. But, you can also set up table-specific roles and actions as well. This is more obvious with Microsoft's management studio though, where Mysql you may want to use something like Mysql Workbench initially.

On cPanel, for example, you can add a new database if your host allows it. On windows, you'll have to use other tools to set up a new database.

In answer to your first inquiry, each database requires its own connection, and there are database-wide operations that you can do such as migration and backups. A rule of thumb is to only keep entirely separate data in different databases, unless there is absolutely a reason to separate types of information into a different kind of database for efficiency. Typically, you do not relate data between different databases except for much more complex situations.

You can create separate databases and use them separately in sequel pro, I believe. Most platforms have an option to create a new db in the databases list.

Improve this answer

Comments

0

Well I think I was confusing some stuff here. I apologize for that. I was calling databases 'tables'.

I was wanting to allow users to create new databases but not see the ones that others create. I think I can make this work by just limiting permissions and allowing users to access one or two databases.

It seems like PHPMyAdmin has some easier to use options than Sequel Pro. I've only briefly used it in the past but I'll give it another shot.

As for command line stuff, I love being able to work in command line but I don't know all the commands so it makes things generally difficult to figure out and the man pages weren't all that helpful.

Thank you for your answers and I'm sorry for my newbie questions.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.