Info Catalog ( DROP USER ( Account management SQL ( RENAME USER `GRANT' and `REVOKE' Syntax
          ON {TBL_NAME | * | *.* | DB_NAME.*}
              [, USER [IDENTIFIED BY [PASSWORD] 'PASSWORD']] ...
              NONE |
              [{SSL| X509}]
              [CIPHER 'CIPHER' [AND]]
              [ISSUER 'ISSUER' [AND]]
              [SUBJECT 'SUBJECT']]
                                MAX_UPDATES_PER_HOUR COUNT |
                                MAX_CONNECTIONS_PER_HOUR COUNT |
                                MAX_USER_CONNECTIONS COUNT]]
          ON {TBL_NAME | * | *.* | DB_NAME.*}
          FROM USER [, USER] ...
 The `GRANT' and `REVOKE' statements allow system administrators to
 create MySQL user accounts and to grant rights to and revoke them from
 accounts.  `GRANT' and `REVOKE' are implemented in MySQL 3.22.11 or
 later. For earlier MySQL versions, these statements do nothing.
 MySQL account information is stored in the tables of the `mysql'
 database. This database and the access control system are discussed
 extensively in  MySQL Database Administration, which you should
 consult for additional details.
 Privileges can be granted at several levels:
 *Global level*
      Global privileges apply to all databases on a given server. These
      privileges are stored in the `mysql.user' table.  `GRANT ALL ON
      *.*' and `REVOKE ALL ON *.*' grant and revoke only global
 *Database level*
      Database privileges apply to all objects in a given database.
      These privileges are stored in the `mysql.db' and `'
      tables.  `GRANT ALL ON DB_NAME.*' and `REVOKE ALL ON DB_NAME.*'
      grant and revoke only database privileges.
 *Table level*
      Table privileges apply to all columns in a given table. These
      privileges are stored in the `mysql.tables_priv' table.  `GRANT
      grant and revoke only table privileges.
 *Column level*
      Column privileges apply to single columns in a given table. These
      privileges are stored in the `mysql.columns_priv' table.  When
      using `REVOKE', you must specify the same columns that were
 *Routine level*
      privileges apply to stored routines. They can be granted at the
      global and database levels. Also, except for `CREATE ROUTINE',
      these privileges can be granted at the routine level for
      individual routines and are stored in the `mysql.procs_priv' table.
 To make it easy to revoke all privileges, MySQL 4.1.2 has added the
 following syntax, which drops all global, database-, table-, and
 column-level privileges for the named users:
 Before MySQL 4.1.2, all privileges cannot be dropped at once.  Two
 statements are necessary:
 For the `GRANT' and `REVOKE' statements, `priv_type' can be specified
 as any of the following:
 *Privilege*            *Meaning*
 `ALL [PRIVILEGES]'     Sets all simple privileges except `GRANT OPTION'
 `ALTER'                Allows use of `ALTER TABLE'
 `ALTER ROUTINE'        Alter or drop stored routines
 `CREATE'               Allows use of `CREATE TABLE'
 `CREATE ROUTINE'       Create stored routines
 `CREATE VIEW'          Allows use of `CREATE VIEW'
 `DELETE'               Allows use of `DELETE'
 `DROP'                 Allows use of `DROP TABLE'
 `EXECUTE'              Allows the user to run stored routines
 `FILE'                 Allows use of `SELECT ... INTO OUTFILE' and `LOAD
                        DATA INFILE'
 `INDEX'                Allows use of `CREATE INDEX' and `DROP INDEX'
 `INSERT'               Allows use of `INSERT'
 `LOCK TABLES'          Allows use of `LOCK TABLES' on tables for which
                        you have the `SELECT' privilege
 `PROCESS'              Allows use of `SHOW FULL PROCESSLIST'
 `REFERENCES'           Not implemented
 `RELOAD'               Allows use of `FLUSH'
 `REPLICATION CLIENT'   Allows the user to ask where slave or master
                        servers are
 `REPLICATION SLAVE'    Needed for replication slaves (to read binary log
                        events from the master)
 `SELECT'               Allows use of `SELECT'
 `SHOW DATABASES'       `SHOW DATABASES' shows all databases
 `SHOW VIEW'            Allows use of `SHOW CREATE VIEW'
 `SHUTDOWN'             Allows use of `mysqladmin shutdown'
 `SUPER'                Allows use of `CHANGE MASTER', `KILL', `PURGE
                        MASTER LOGS', and `SET GLOBAL' statements, the
                        `mysqladmin debug' command; allows you to connect
                        (once) even if `max_connections' is reached
 `UPDATE'               Allows use of `UPDATE'
 `USAGE'                Synonym for "no privileges"
 `GRANT OPTION'         Allows privileges to be granted
 were added in MySQL 4.0.2.  (`EXECUTE' is not operational until MySQL
 5.0.3.)  `CREATE VIEW' and `SHOW VIEW' were added in MySQL 5.0.1.
 `CREATE ROUTINE' and `ALTER ROUTINE' were added in MySQL 5.0.3.  To use
 these privileges when upgrading from an earlier version of MySQL that
 does not have them, , you must upgrade your grant tables.  
 The `REFERENCES' privilege currently is unused.
 In older MySQL versions that do not have the `SUPER' privilege, specify
 the `PROCESS' privilege instead.
 `USAGE' can be specified when you want to create a user that has no
 Use `SHOW GRANTS' to determine what privileges the account has.  
 You can assign global privileges by using `ON *.*' syntax or database
 privileges by using `ON DB_NAME.*' syntax. If you specify `ON *' and
 you have selected a default database, the privileges are granted in
 that database.  (*Warning:* If you specify `ON *' and you have _not_
 selected a default database, the privileges granted are global!)
 privileges are administrative privileges that can only be granted
 globally (using `ON *.*' syntax).
 Other privileges can be granted globally or at more specific levels.
 The only `priv_type' values you can specify for a table are `SELECT',
 `INDEX', and `ALTER'.
 The only `priv_type' values you can specify for a column (that is, when
 you use a `column_list' clause) are `SELECT', `INSERT', and `UPDATE'.
 The only `priv_type' values you can specify at the routine level are
 routine-level privilege because you must have this privilege to be able
 to create a routine in the first place.
 For the global, database, table, and routine levels, `GRANT ALL'
 assigns only the privileges that exist at the level you are granting.
 For example, if you use `GRANT ALL ON DB_NAME.*', that is a
 database-level statement, so none of the global-only privileges such as
 `FILE' will be granted.
 For column-level privileges (that is, when you specify COLUMN_LIST),
 you must explicitly name the privileges to be granted. You cannot use
 `ALL' as a privilege specifier.
 MySQL allows you to grant database-level privileges even if the database
 doesn't exist, to make it easy to prepare for database use.  However,
 MySQL currently does not allow you to grant table-level or column-level
 privileges if the table doesn't exist. Similarly, you cannot grant
 routine-level privileges to a non-existent routine.
 MySQL does not automatically revoke any privileges even if you drop a
 table or drop a database. If you drop a routine, and routine-level
 privileges granted for the routine are revoked.
 names in `GRANT' statements that grant privileges at the global or
 database levels. This means, for example, that if you want to use a `_'
 character as part of a database name, you should specify it as `\_' in
 the `GRANT' statement, to prevent the user from being able to access
 additional databases matching the wildcard pattern; for example, `GRANT
 ... ON `foo\_bar`.* TO ...'.
 In order to accommodate granting rights to users from arbitrary hosts,
 MySQL supports specifying the USER value in the form
 `USER_NAME@HOST_NAME'.  If a USER_NAME or HOST_NAME value is legal as
 an unquoted identifier, you need not quote it. However, quotes are
 necessary to specify a USER_NAME string containing special characters
 (such as `-'), or a HOST_NAME string containing special characters or
 wildcard characters (such as `%'); for example,
 `'test-user'@'test-hostname''. Quote the username and hostname
 You can specify wildcards in the hostname.  For example,
 `USER_NAME@''' applies to USER_NAME for any host in the
 `' domain, and `USER_NAME@'144.155.166.%'' applies to USER_NAME
 for any host in the `144.155.166' class C subnet.
 The simple form USER_NAME is a synonym for `USER_NAME@'%''.
 MySQL doesn't support wildcards in usernames.  Anonymous users are
 defined by inserting entries with `User=''' into the `mysql.user' table
 or creating a user with an empty name with the `GRANT' statement:
      mysql> GRANT ALL ON test.* TO ''@'localhost' ...
 When specifying quoted values, quote database, table, column, and
 routine names as identifiers, using backticks (``').  Quote hostnames,
 usernames, and passwords as strings, using apostrophes (`'').
 *Warning:* If you allow anonymous users to connect to the MySQL server,
 you should also grant privileges to all local users as
 `USER_NAME@localhost'. Otherwise, the anonymous-user account for the
 local host in the `mysql.user' table will be used when named users try
 to log in to the MySQL server from the local machine!  (This
 anonymous-user account is created during MySQL installation.)
 You can determine whether this applies to you by executing the
 following query:
      mysql> SELECT Host, User FROM mysql.user WHERE User='';
 If you want to delete the local anonymous-user account to avoid the
 problem just described, use these statements:
      mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='';
      mysql> FLUSH PRIVILEGES;
 `GRANT' supports hostnames up to 60 characters long. Database, table,
 column, and routine names can be up to 64 characters.  Usernames can be
 up to 16 characters.
 The privileges for a table or column are formed additively from the
 logical OR of the privileges at each of the four privilege levels.  For
 example, if the `mysql.user' table specifies that a user has a global
 `SELECT' privilege, the privilege cannot be denied by an entry at the
 database, table, or column level.
 The privileges for a column can be calculated as follows:
      global privileges
      OR (database privileges AND host privileges)
      OR table privileges
      OR column privileges
 In most cases, you grant rights to a user at only one of the privilege
 levels, so life isn't normally this complicated. The details of the
 privilege-checking procedure are presented in  Privilege system.
 If you grant privileges for a username/hostname combination that does
 not exist in the `mysql.user' table, an entry is added and remains
 there until deleted with a `DELETE' statement.  In other words, `GRANT'
 may create `user' table entries, but `REVOKE' will not remove them; you
 must do that explicitly using `DROP USER' or `DELETE'.
 In MySQL 3.22.12 or later, if a new user is created or if you have
 global grant privileges, the user's password is set to the password
 specified by the `IDENTIFIED BY' clause, if one is given.  If the user
 had a password, it is replaced by the new one.
 *Warning:* If you create a new user but do not specify an `IDENTIFIED
 BY' clause, the user has no password.  This is insecure.  As of MySQL
 5.0.2, you can enable the `NO_AUTO_CREATE_USER' SQL mode to prevent
 `GRANT' from creating new users if it would otherwise do so, unless
 `IDENTIFIED BY' is given to provide a password.
 Passwords can also be set with the `SET PASSWORD' statement.  
 In the `IDENTIFIED BY' clause, the password should be given as the
 literal password value.  It is unnecessary to use the `PASSWORD()'
 function as it is for the `SET PASSWORD' statement. For example:
      GRANT ... IDENTIFIED BY 'mypass';
 If you don't want to send the password in clear text and you know the
 hashed value that `PASSWORD()' would return for the password, you can
 specify the hashed value preceded by the keyword `PASSWORD':
 In a C program, you can get the hashed value by using the
 `make_scrambled_password()' C API function.
 If you grant privileges for a database, an entry in the `mysql.db'
 table is created if needed. If all privileges for the database are
 removed with `REVOKE', this entry is deleted.
 If a user has no privileges for a table, the table name is not displayed
 when the user requests a list of tables (for example, with a `SHOW
 TABLES' statement).
 The `SHOW DATABASES' privilege allows the account to see database names
 by issuing the `SHOW DATABASE' statement. Accounts that do not have this
 privilege see only databases for which they have some privileges, and
 cannot use the statement at all if the server was started with the
 `--skip-show-database' option.
 The `WITH GRANT OPTION' clause gives the user the ability to give to
 other users any privileges the user has at the specified privilege
 level.  You should be careful to whom you give the `GRANT OPTION'
 privilege, because two users with different privileges may be able to
 join privileges!
 You cannot grant another user a privilege you don't have yourself; the
 `GRANT OPTION' privilege allows you to give away only those privileges
 you possess.
 Be aware that when you grant a user the `GRANT OPTION' privilege at a
 particular privilege level, any privileges the user possesses (or is
 given in the future!) at that level are also grantable by that user.
 Suppose that you grant a user the `INSERT' privilege on a database.  If
 you then grant the `SELECT' privilege on the database and specify `WITH
 GRANT OPTION', the user can give away not only the `SELECT' privilege,
 but also `INSERT'.  If you then grant the `UPDATE' privilege to the
 user on the database, the user can give away `INSERT', `SELECT', and
 You should not grant `ALTER' privileges to a normal user.  If you do
 that, the user can try to subvert the privilege system by renaming
 `MAX_CONNECTIONS_PER_HOUR COUNT' options are new in MySQL 4.0.2.  They
 limit the number of queries, updates, and logins a user can perform
 during one hour. If COUNT is 0 (the default), this means there is no
 limitation for that user.
 The `MAX_USER_CONNECTIONS COUNT' option is new in MySQL 5.0.3.  It
 limits the maximum number of simultaneous connections that the account
 can make.  If COUNT is 0 (the default), the `max_user_connections'
 system variable determines the number of simultaneous connections for
 the account.
 Note: To specify any of these resource-limit options for an existing
 user without affecting existing privileges, use `GRANT USAGE ON *.* ...
 WITH MAX_...'.
  User resources.
 MySQL can check X509 certificate attributes in addition to the usual
 authentication that is based on the username and password.  To specify
 SSL-related options for a MySQL account, use the `REQUIRE' clause of
 the `GRANT' statement.  (For background on the use of SSL with MySQL,
 see  Secure connections.)
 There are different possibilities for limiting connection types for an
    * If an account has no SSL or X509 requirements, unencrypted
      connections are allowed if the username and password are valid.
      However, encrypted connections also can be used at the client's
      option, if the client has the proper certificate and key files.
    * The `REQUIRE SSL' option tells the server to allow only
      SSL-encrypted connections for the account. Note that this option
      can be omitted if there are any access-control records that allow
      non-SSL connections.
           mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
               -> IDENTIFIED BY 'goodsecret' REQUIRE SSL;
    * `REQUIRE X509' means that the client must have a valid certificate
      but that the exact certificate, issuer, and subject do not matter.
      The only requirement is that it should be possible to verify its
      signature with one of the CA certificates.
           mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
               -> IDENTIFIED BY 'goodsecret' REQUIRE X509;
    * `REQUIRE ISSUER 'issuer'' places the restriction on connection
      attempts that the client must present a valid X509 certificate
      issued by CA `'issuer''.  If the client presents a certificate
      that is valid but has a different issuer, the server rejects the
      connection.  Use of X509 certificates always implies encryption,
      so the `SSL' option is unnecessary.
           mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
               -> IDENTIFIED BY 'goodsecret'
               -> REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/
                  O=MySQL Finland AB/CN=Tonu Samuel/';
      Note that the `ISSUER' value should be entered as a single string.
    * `REQUIRE SUBJECT 'subject'' places the restriction on connection
      attempts that the client must present a valid X509 certificate
      with subject `'subject'' in it.  If the client presents a
      certificate that is valid but has a different subject, the server
      rejects the connection.
           mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
               -> IDENTIFIED BY 'goodsecret'
               -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
                  O=MySQL demo client certificate/
                  CN=Tonu Samuel/';
      Note that the `SUBJECT' value should be entered as a single string.
    * `REQUIRE CIPHER 'cipher'' is needed to ensure that strong enough
      ciphers and key lengths will be used. SSL itself can be weak if
      old algorithms with short encryption keys are used. Using this
      option, you can ask for some exact cipher method to allow a
           mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
               -> IDENTIFIED BY 'goodsecret'
               -> REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
 The `SUBJECT', `ISSUER', and `CIPHER' options can be combined in the
 `REQUIRE' clause like this:
      mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
          -> IDENTIFIED BY 'goodsecret'
          -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
             O=MySQL demo client certificate/
             CN=Tonu Samuel/'
          -> AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
             O=MySQL Finland AB/CN=Tonu Samuel/'
 Note that the `SUBJECT' and `ISSUER' values each should be entered as a
 single string.
 Starting from MySQL 4.0.4, the `AND' keyword is optional between
 `REQUIRE' options.
 The order of the options does not matter, but no option can be specified
 When `mysqld' starts, all privileges are read into memory.  Database,
 table, and column privileges take effect at once, and user-level
 privileges take effect the next time the user connects.  Modifications
 to the grant tables that you perform using `GRANT' or `REVOKE' are
 noticed by the server immediately.  If you modify the grant tables
 manually (using `INSERT', `UPDATE', and so on), you should execute a
 `FLUSH PRIVILEGES' statement or run `mysqladmin flush-privileges' to
 tell the server to reload the grant tables.   Privilege changes.
 Note that if you are using table or column privileges for even one
 user, the server examines table and column privileges for all users and
 this slows down MySQL a bit.  Similarly, if you limit the number of
 queries, updates, or connections for any users, the server must monitor
 these values.
 The biggest differences between the standard SQL and MySQL versions of
 `GRANT' are:
    * In MySQL, privileges are associated with a username/hostname
      combination and not with only a username.
    * Standard SQL doesn't have global or database-level privileges, nor
      does it support all the privilege types that MySQL supports.
    * MySQL doesn't support the standard SQL `TRIGGER' or `UNDER'
    * Standard SQL privileges are structured in a hierarchical manner.
      If you remove a user, all privileges the user has been granted are
      revoked. This is also true in MySQL 5.0.2 and up if you use `DROP
      USER'. Before 5.0.2, the granted privileges are not automatically
      revoked; you must revoke them yourself.   `DROP USER' DROP
    * With standard SQL, when you drop a table, all privileges for the
      table are revoked.  With standard SQL, when you revoke a
      privilege, all privileges that were granted based on the privilege
      are also revoked. In MySQL, privileges can be dropped only with
      explicit `REVOKE' statements or by manipulating the MySQL grant
    * In MySQL, if you have the `INSERT' privilege on only some of the
      columns in a table, you can execute `INSERT' statements on the
      table; the columns for which you don't have the `INSERT' privilege
      will be set to their default values. Standard SQL requires you to
      have the `INSERT' privilege on all columns.
Info Catalog ( DROP USER ( Account management SQL ( RENAME USER
automatically generated byinfo2html