Securing the Database - Database Design Manual: Using MySQL for Windows - page 142

Table 12.2 Some privileges to use with GRANT.

Privilege Allows the user to:

ALL PRIVILEGES Use all privileges available on the given object

ALTER Change table data structures with ALTER TABLE

CREATE Create tables

CREATE TEMPORARY TABLES Create temporary tables

DELETE Remove rows from tables with DELETE

DROP Remove tables with DROP

EXECUTE Run a stored procedure, however stored procedures are not implemented in the

current version of MySQL

FILE Read and write files

INDEX Create and remove indexes

INSERT Insert data into tables

LOCK TABLES LOCK tables that they have the SELECT privilege on

RELOAD Use the FLUSH command

SELECT Retrieve data from tables using SELECT

SHUTDOWN Shut down the MySQL server

UPDATE Change the contents of tables with UPDATE

In the code:

•

privilege is the action that you are revoking from the user and uses Table 12.2 in the
same way as the GRANT command.

•

systemobject is what the user is being denied access to.

If you revoke a privilege successfully you get no feedback from the MySQL server.
However, if you revoke a privilege that a user does not have, for example:

REVOKE SELECT ON mysql.tables_priv

FROM Mary@localhost

you will get the error:

There is no such grant defined for user 'Mary' on host 'localhost' on
table 'user'

If you REVOKE all privileges that have been previously given to a user, this does not
delete that user from the user table, but they will have no further access to the system until
GRANTed more privileges. The user will stay valid until they are explicitly deleted from the
user table.

Restricting Users

Bear in mind that users of your MySQL database will very probably be different from any
users that authenticate to a web system that you are building. Your MySQL table will prob-