IDENTIFIED BY "password"[WITH GRANT OPTIONS]
This may seem a little intimidating at first, but it is quite easy. After the keyword GRANT, you would list all
the privileges you want to grant the new user. The following are the privileges you can grant:
ALL Gives the user all the available privileges.
ALTER Allows the user to alter tables, columns, and indexes.
CREATE Allows the user to create tables or databases.
DELETE Allows the user to delete records from tables.
DROP Allows the user to delete tables or databases.
FILE Allows the user to read and write files on the server.
INDEX Allows the user to add or delete indexes.
INSERT Allows the user to add records to the database.
PROCESS Allows the user to view and kill MySQL system processes.
REFERENCES Not currently used in MySQL.
RELOAD Allows the user to use the FLUSH statement.
SELECT Allows the user to perform SELECT queries.
SHUTDOWN Allows the user to shut down the MySQL server.
UPDATE Allows the user to edit existing records in the database.
USAGE Allows the user to connect to the server. This type of user has no privileges at
all.
The next part of the command is optional. It allows you to specify what columns of a table to which to
apply the privileges. The column names are case sensitive, so be careful when entering them.
After the keyword ON, you list the database and table or tables to which to apply these privileges. To
specify all the tables in the database, you would use an asterisk:
Meet_A_Geek.*
If you wanted to apply these permissions globally, you would use two asterisks:
.
The next part of the GRANT command is the TO clause. This is where you state the user and his or her
hostname. Again, wildcard characters can be used here. If you want to give privileges to Scott, you can
use any of the following:
"Scott@localhost"
"Scott@%jsmc.org"
"Scott@%"
The next part of the statement is the password. You do not need to use the password function in the
GRANT statement. MySQL does it for you automatically.
The final part of the command is entirely optional. It gives the specified user the ability to issue the
GRANT statement as well.
Now look at some examples. You’ll use your friend Scott again. To begin, give him the ability to connect
to the MySQL server with absolutely no permission at all. To do this, use the following command:
GRANT USAGE ON . TO Scott@% IDENTIFIED BY "dinosaur";
This statement allows Scott to connect from anywhere using the password "dinosaur." If you look at the
grant tables, you’ll see that Scott only has an entry in the user table. You’ll also see that the
password is encrypted. Pretty easy.
Now give Scott SELECT privileges on all tables in the Meet_A_Geek database. To do this, issue the
following statement:
GRANT SELECT ON Meet_A_Geek.* TO Scott@% IDENTIFIED BY "dinosaur";
This gives Scott the ability to connect from any computer and issue SELECT statements to any of the
tables in the Meet_A_Geek database. Next, you'll get a little more strict with Scott. Give him SELECT
privileges only to the Customers table in the Meet_A_Geek database. To do this, use the following
command:
GRANT SELECT ON Meet_A_Geek.Customers
TO Scott@% IDENTIFIED BY "dinosaur";
Notice how the dot (.) notation is used. The first part is the database name, and the second part is the
table name. This is pretty much standard for all database systems.