Access Granted - The Mechanics
(Page 7 of 7 )
Now that you know how the grant tables work, the final item on the agenda is the mechanics of implementing changes to the tables. MySQL offers two methods of altering access rights in the grant tables - you can either use INSERT, UPDATE and DELETE queries to alter the information in the tables, or use the GRANT and REVOKE commands.
Personally, I prefer the former, since it's much easier to understand and remember - although typing in long-winded SQL queries is sometimes a little tedious. Power users would do well to learn GRANT and REVOKE command syntax - details are available in the mySQL manual. For the moment, I'll simply take you through a couple of examples, using both methods, so that you have some insight into the differences between the two methods.
The first example sets up a user "tom", password "tommygun", who has permission to access the "recipes" database only from "localhost"
mysql> INSERT INTO user (Host, User, Password)
VALUES('localhost','tom',PASSWORD('tommygun'));
mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv) VALUES
('localhost','recipes','tom','Y','Y','Y','Y','N','N');
The equivalent GRANT command is:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, ON recipes.* TO tom@localhost
IDENTIFIED BY 'tommygun';
You could set up an equivalent of the "root" user
with
mysql> GRANT ALL PRIVILEGES ON *.* TO god@localhost IDENTIFIED BY 'master';
or
mysql> INSERT INTO user (Host, User, Password, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
Index_priv, Alter_priv) VALUES ('localhost', 'god', PASSWORD('master'),
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')
It should be noted that privileges set using GRANT and
REVOKE are immediately activated; however, privileges set via regular SQL queries require a server reload to come into effect. A server reload can be accomplished via the "mysqladmin" command
$ mysqladmin reload
or with the
mysql> FLUSH PRIVILEGES;
command.
And that's about it. I hope you find this
information useful, and that you can use it when maintaining your own databases. Ciao!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
