Database Reference
In-Depth Information
FLUSH PRIVILEGES
There is one point that you need to bear in mind though. When you use the GRANT and
REVOKE queries, the system realizes what you are doing and so makes any access changes
immediately. Some changes, such as global privileges, will take effect the next time the user
connects to the server. However, if you use normal SQL queries to change a user's privi-
leges, the system just treats these as normal database queries. The user's access rights how-
ever
do not
change immediately. To make the server realize that you have just committed
some privilege changes, you have to type the following:
FLUSH PRIVILEGES
This alerts the MySQL server to a privilege change and makes it reload the grant tables.
GRANT
GRANT is used to give a privilege to a user. If details of that user do not exist in any of the
grant tables then a new user is created. GRANT can therefore be used to create users.
GRANT takes the following format:
GRANT
privilege ON systemobject
TO
username
IDENTIFIED BY 'password'
In the above:
•
username
and
password
are the credentials that the user needs to connect to the data-
base. Note that you need the quote marks around the password string. The IDENTIFIED
BY clause is optional, but can be used to change the password of a user that already
exists. If this is omitted when GRANTing a privilege to a user that doesn't exist, the user
is created without a password.
•
privilege
is the action that you are going to allow the user to perform. Table 12.2 shows
some of the privileges that are available.
•
systemobject
is what the user will be performing that access on. It is usually used with
the following format:
database.table
so *.* refers to all tables in all databases on this server.
REVOKE
REVOKE is used to remove a given a privilege from a user. REVOKE will fail if the user has
not been previously granted the privilege. REVOKE takes the following format:
REVOKE privilege ON systemobject
FROM
username
Search WWH ::
Custom Search