CODEWITHSUNDEEP
mysql
user3383762
user3383762

Reputation: 211

Assign an existing user to mysql databases

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?

Upvotes: 21

Views: 29495

Answers (2)

Digital Fu
Digital Fu

Reputation: 2917

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';

Upvotes: 29

Jerko W. Tisler
Jerko W. Tisler

Reputation: 989

GRANT SELECT, INSERT, INDEX ON `db_name`.* TO 'user'@'%';

change SELECT, INSERT, INDEX in what you need

Upvotes: 4

Related Questions