PHP —

MySQL Permissions

Users can be added to the system with an INSERT INTO the user table in the mysql database or, as previously illustrated, you can use the GRANT statement. Moreover, privileges can be adjusted with an UPDATE, added with GRANT, or removed with REVOKE.

GRANT

The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

Consider the following example:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  ON winestore.*
  TO dave@localhost
  IDENTIFIED BY 'password';

This adds a new user dave and allows him to use only the SQL statements listed in the winestore database. The parameter winestore.* means all tables within the winestore database.

REVOKE

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts. Privileges can be removed with the REVOKE statement. For example:

REVOKE DROP,CREATE ON winestore.* FROM dave@localhost;

If the privilege or privileges are to be revoked for all databases in the DBMS, not just a single database, winestore.* can be replaced with *.*.

The following privileges can be used in GRANT and REVOKE statements:

ALL PRIVILEGES, FILE, RELOAD, ALTER, INDEX, SELECT,
CREATE, INSERT, SHUTDOWN, DELETE, PROCESS, UPDATE,
DROP, REFERENCES, USAGE