Home arrow MySQL arrow Page 7 - Access Granted

The Mechanics - MySQL

mySQL comes with a pretty powerful security system, the grant tables, which allows database administrators to precisely control access to databases, tables and even specific rows and columns. In this article, find out how the five grant tables combine to offer power users a tremedous amounts of flexibility and control over database access and operations.

  1. Access Granted
  2. Meet Joe User
  3. Beeping Turkeys
  4. Born Privileged
  5. The Perfect Host
  6. Cream Of The Crop
  7. The Mechanics
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 9
April 24, 2001

print this article


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


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



And that's about it. I hope you find this information useful, and that you can use it when maintaining your own databases. Ciao!

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: