The correct MySQL GRANT syntax
It is surprising how many people run into problems trying to use the GRANT command: everything from not being able to access a database table to losing admin access to MySQL altogether. There’s a bunch of Web sites giving incorrect examples of using this command. Among these sites is, surprisingly, mysql.org. So, to save humanity, here’s my take on the GRANT issue and pay attention to the use of single and double quotes.
Example 1
Let’s say you want to create a new database called “my_drug_deals”, so you do this:
mysql -uroot -ppassword mysql> CREATE DATABASE my_drug_deals ; mysql> quit
Now, you want to connect to this DB as root from another machine and have full access. Here’s what you do:
mysql -uroot -ppassword mysql> GRANT ALL PRIVILEGES ON my_drug_deals.* to root@'%' IDENTIFIED BY 'password' WITH GRANT OPTION ; mysql> quit
This will make this database fully accessible to a “root” user connecting from ANY system, as long as the correct password is specified.
Popularity: 6% [?]
Related posts:
[...] MySQL to grant your database user account necessary permissions to access the new [...]