CODEWITHSUNDEEP
mysql
Avinash
Avinash

Reputation: 2005

Create new user in mysql with access to only certain databases

Dont know if this is possible.

I need to create a new mysql user with access to only databases with database name with prefix say user_db_.

I have a php application in which when a new user is added it creates a database user_db_'userid'. Suppose three users are created such that their databases are user_db_1, user_db_2, user_db_3.

I need to create a new user say user_accounts which has access to only user_db_1, user_db_2, user_db_3,..........,user_db_n.

Upvotes: 4

Views: 7052

Answers (3)

JegsVala
JegsVala

Reputation: 1867

There are the three steps

  1. Create Database
  2. Create new User
  3. Give the rights with grant all command

Like this 

CREATE DATABASE DBTEST

If this is run successfully without any error, then create new user

CREATE USER 'NEWUSER'@'LOCALHOST' IDENTIFIED BY 'NEWUSERPASSWORD'

After user creation give the right of create table drop table

GRANT ALL PRIVILEGES ON *.* TO 'NEWUSER'@'LOCALHOST'

And after all give one command 

FLUSH PRIVILEGES

Update 31/03/2015 (as it stated in comments)

You can give the rights on particular database like this 

GRANT ALL PRIVILEGES ON DBTEST.* TO 'NEWUSER'@'LOCALHOST

Upvotes: 2

K S Nidhin
K S Nidhin

Reputation: 2650

Use the following code for granting privileges on a particular DB.

grant all privileges on DB_NAME.* to 'USER'@'REMOTE_IP' identified by 'PASSWORD';

Upvotes: 5

peterm
peterm

Reputation: 92845

IMHO you can use a combination of

SHOW DATABASES LIKE 'user_db_%'

to get a current list of user databases, and 

CREATE USER 'username'@'hostname' ...

to create a user, and

GRANT ALL ON user_db_N.* TO 'username'@'hostname'

to set database privileges to the user for each of databases you get from SHOW DATABASES.

Upvotes: -1

Related Questions