Assigning MySQL privileges via the command line

Categories:
The final task once you have created your MySQL database and created your MySQL user account is to assign privileges. With MySQL 5 this can be done on a global, database, table, column and routine level. Typically though once you have your root account created then other accounts are assigned privileges at the database level, and that is what will be covered in this guide.
  • Start the MySQL command line client by selecting the MySQL Command Line Client item from your MySQL program group in your start menu.
  • The MySQL command line client will prompt you for the root password. Enter it and push enter.
  • To assign privileges to a user simply type in;
GRANT ALL PRIVILEGES ON `testdb\_%` . * TO 'test'@'localhost';
  • This will grant all privileges a user called test on localhost for all databases beginning with testdb_. So if you created a database called testdb_mydb this user will have full privileges to it even if created with the root account, but other standard user accounts with no global permissions will be able to access this database. This is very handy for multi user environments as you can set each user to have their own prefix so that they can create and administer their own databases without them being accessible by other users.
  • Finally, lets verify that the privileges have been assigned properly. Type in;
SHOW GRANTS FOR 'test'@'localhost';
  • This will list all the privileges that the test account has on the server, and you should see something similar to what is displayed below in figure 1.
Figure 1
That's it. You should be able to now create a database, user and assign permissions from the command line interface for MySQL.
Average rating
(3 votes)

Comments

Anonymous's picture

Not Helpful

This doesn't help if you don't want to "GRANT ALL". In fact, this is a security hazard.

Brashquido's picture

Comment not helpful

This articles purpose is to show basic command line syntax for granting privileges in MySQL via the command line. Nowhere does it ever claim to cover every single iteration of MySQL privilege possible, so I don't see where the problem is.

Also, your comment about security is useless without giving some reference as to what exactly you are talking about. Yes, you can tighten security a lot more than is shown in this guide, however to do this you'd need to look at the privilege requirements of every single application you are running. This is impossible for a single guide.

This guide uses the GRANT ALL syntax at the database level without the GRANT option, meaning no GLOBAL privileges are assigned and the user is unable to set privileges of their own. Granting privileges at this level allows for maximum flexibility, with a basic security implementation (i.e, users can't see other users databases or grant other users privileges).

With a proper firewall setup so that only your application servers are able to connect to your MySQL server, along with the use of strong passwords means real world risk of using the GRANT ALL privilege at the database level (without the GRANT privilege) is very very low. Even if you did manage to hack the username and password of a user with the GRANT ALL privilege set at the database level, you'd only have access to the databases that user has permissions to. The integrity of your MySQL server would not be compromised.

----------------
Dominic Ryan
5 x Microsoft IIS MVP, MCSE, MCSA
IIS Aid owner/webmaster

Anonymous's picture

You can't please everyone


But then again, you are an MSCE

Anonymous's picture

MCSE or not, he has a point.

MCSE or not, he has a point. The first poster's request would have made the situation even more insecure.

Disclaimer: I in no way condone any actions taken by Microsoft that earned the bad PR.

Anonymous's picture

Better way is using any gui

Better way is using any gui tool like dbforge studio to manage users rather than using mysql console.

Brashquido's picture

Wouldn't say better, just different

There are a dozen different ways to do pretty much any technological task. Gui interfaces are more intuative to use, however that alone does not make them better as by learning command line you are better positioned for developing automation via scripting.

----------------
Dominic Ryan
6 x Microsoft IIS MVP, MCSE, MCSA
IIS Aid owner/webmaster