19

I have successfully created multiple databases in phpMyAdmin. I would now like to assign an existing user + respective password to all of these new databases by running a script on the SQL tab.

Please can you confirm what script I need to run, or if there is an easier script which can be run via root access, please then confirm the root-command for this?

Thorin Schiffer
  • 2,818
  • 4
  • 25
  • 34
user3383762
  • 191
  • 1
  • 1
  • 3

2 Answers2

27

Use the grant permissions command.

If your database was call "newDatabase" and your user was name "newUser" the command to grant all privileges on all the tables contained within would be:

GRANT ALL PRIVILEGES ON `newDatabase`.* TO 'newUser'@'localhost';

This would restrict the user from access the database only from the localhost, to enable access from all host change localhost to '%'

You then need to refresh the privileges with the following command:

FLUSH PRIVILEGES;

EDIT:

To grant privileges to every database on the mysql server use the following command (notice the *.*): 

GRANT ALL PRIVILEGES ON *.* TO 'newUser'@'localhost';
PiTheNumber
  • 22,828
  • 17
  • 107
  • 180
Digital Fu
  • 2,877
  • 1
  • 14
  • 20
  • Hi there, thank you very much for the quick response! Is this the root-command? I am trying to execute it using Putty and which does not recognize the command. Also, is there a global single command to change all the databases at once? (I need to add the user to 256 databases) – user3383762 Mar 05 '14 at 14:11
  • How do I open the mysqld program? – user3383762 Mar 05 '14 at 14:26
  • I deleted that comment because it was completely wrong, you want the mysql admin console, this question should help. (http://stackoverflow.com/questions/6200215/how-to-log-in-to-mysql-and-query-the-database-from-linux-terminal) – Digital Fu Mar 05 '14 at 14:46
  • Thank you very much for the update! I managed to access mysql admin using Putty and which returned a line starting with: mysql> However when I ran the command script the following message popped up: -> ERROR 1045 (28000): Access denied for user 'dboco_user1'@'localhost' (using password: YES) – user3383762 Mar 05 '14 at 16:32
  • `GRANT ALL PRIVILEGES ON *.* TO 'newUser';` worked for me, only without the 'localhost' part though – Adam Jagosz Nov 11 '22 at 16:48
5
GRANT SELECT, INSERT, INDEX ON `db_name`.* TO 'user'@'%';

change SELECT, INSERT, INDEX in what you need

Jerko W. Tisler
  • 996
  • 9
  • 29