Page 27 - Open Soource Technologies 304.indd

Open Source Technologies

Notes FILE User can read and write files; this is used to import or dump data.
INDEX User can add or delete indexes.

INSERT User can add records to tables.
PROCESS User can view and stop system processes; only trusted users should be able to do this.

REFERENCES Not currently used by My SQL, but a column for REFERENCES privileges exists
in the user table.
RELOAD User can issue FLUSH statements; only trusted users should be able to do this.
SELECT User can select records from tables.

SHUTDOWN User can shut down the My SQL server; only trusted users should be able to do this.

UPDATE User can update (modify) records in tables.
USAGE User can connect to My SQL but has no privileges.
If, for instance, you want to create a user called john with a password of 99hjc!5, with SELECT
and INSERT privileges on all tables in the database called myDB, and you want this user to be
able to connect from any host, use
GRANT SELECT, INSERT

ON myDB.*
TO john@”%”

IDENTIFIED BY “99hjc!5”;
Note the use of two wildcards: * and %. These wildcards are used to replace values. In this
example, * replaces the entire list of tables, and % replaces a list of all hosts in the known World
a very long list indeed.
Here’s another example of adding a user using the GRANT command, this time to add a user
called jane with a password of 45sdg11, with ALL privileges on a table called employees in the
database called myCompany. This new user can connect only from a specific host:

GRANT ALL
ON myCompany.employees

TO jane@janescomputer.company.com
IDENTIFIED BY “45sdg11”;

If you know that janescomputer.company.com has an IP address of 63.124.45.2, you can substitute
that address in the hostname portion of the command, as follows:

GRANT ALL
ON myCompany.employees

TO jane@’63.124.45.2’
IDENTIFIED BY “45sdg11”;

22 LOVELY PROFESSIONAL UNIVERSITY