MySQL Tutorial

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.