4

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.

Avinash
  • 1,935
  • 7
  • 29
  • 55
  • possible duplicate of [MySQL: Grant \*\*all\*\* privileges on database](http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database) – akostadinov Aug 04 '14 at 14:15

3 Answers3

6

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

grant all privileges on DB_NAME.* to 'USER'@'REMOTE_IP' identified by 'PASSWORD';
K S Nidhin
  • 2,622
  • 2
  • 22
  • 44
3

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
Athafoud
  • 2,898
  • 3
  • 40
  • 58
JegsVala
  • 1,789
  • 1
  • 19
  • 26
0

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.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • actually its not like that the databases can be created after i set this privailage also. So its only possible to create privilage with prefix. – Avinash Jan 08 '13 at 05:37
  • What do you mean by "...its not like that the databases can be created after i set this privilege..."? What you certainly will need to do is to reassign privileges for your user_accounts user for newly created databases each time you create them. – peterm Jan 08 '13 at 05:48
  • Hey Peter. I understand what you're saying. Does this mean there's no way to easily give a user permission to multiple but not all databases at the same time? – Govind Rai Sep 24 '16 at 08:41