Summary

GRANT and REVOKE

• The GRANT statement is used to grant privileges to a user or to create a user account. It has the following format:

   GRANT  priv_type  [(  column_list  )] [,  priv_type  [(  column_list  )] ...] ON {  tbl_name  *  *.*  db_name  .*} TO  user_name  [IDENTIFIED BY [PASSWORD] '  password  ']     [,  user_name  [IDENTIFIED BY '  password  '] ...] [REQUIRE     NONE      [{SSL X509}]     [CIPHER  cipher  [AND]]     [ISSUER  issuer  [AND]]     [SUBJECT  subject  ]] [WITH [GRANT OPTION  MAX_QUERIES_PER_HOUR  #  MAX_UPDATES_PER_HOUR  #  MAX_CONNECTIONS_PER_HOUR  #  ]] 

• The REVOKE statement is used to take privileges away from a user. It has the following format:

   REVOKE  priv_type  [(  column_list  )] [,  priv_type  [(  column_list  )] ...] ON {  tbl_name  *  *.*  db_name  .*} FROM  user_name  [,  user_name  ...] 

Privileges

• Individual privileges can be granted to users.

• These are the user privileges:

  CREATE

  CREATE TEMPORARY TABLES

  DELETE

  EXECUTE

  INDEX

  INSERT

  LOCK TABLES

  SELECT

  SHOW DATABASES

  UPDATE

  USAGE

• These are the administrator privileges:

  ALL

  ALTER

  DROP

  FILE

  PROCESS

  RELOAD

  REPLICATION CLIENT

  REPLICATION SLAVE

  SHUTDOWN

  SUPER

  WITH GRANT OPTION

Privilege Tables

• MySQL's account and privilege information is stored in the database called mysql .

• There are five privilege tables.

• The user table stores usernames, passwords, and global privilege information.

• The db table stores information about privileges for specific databases.

• The host table stores information about which databases can be accessed from which hosts .

• The tables_priv table stores information about table-level privileges.

• The columns_priv table stores information about column-level privileges.