GRANT (privileges)

• see SHOW

• http://dev.mysql.com/doc/refman/4.1/en/grant.html

• Global Privileges (all databases and tables):

  GRANT ALL PRIVILEGES ON *.* TO username@localhost
  IDENTIFIED BY 'password';

• Global Privileges for “acme” database (and all acme tables):

  GRANT ALL PRIVILEGES ON acme.* TO username@localhost
  IDENTIFIED BY 'password';

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

The accounts created by these GRANT statements have the following properties:

• Two of the accounts have a username of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 5.7.5, “Access Control, Stage 1: Connection Verification”.)
• One account has a username of admin and no password. This account can be used only by connecting from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges allow the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges later by issuing additional GRANT statements.
• One account has a username of dummy and no password. This account can be used only by connecting from the local host. No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.
• The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a non-empty password.

• SEE ALSO: authentication_problems

• http://dev.mysql.com/doc/refman/4.1/en/revoke.html
• remove a user from all global, database, table, and column privileges

  REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

• revoke removes a user from all privilege tables, but not the user table; you must use one of the following:
  • DROP USER

    DROP USER user [, user] ...

  • DELETE

    DELETE FROM mysql.user WHERE User='user_name' and Host='host_name';
    FLUSH PRIVILEGES;

You need to flush privileges after manually making changes to user tables.

FLUSH PRIVILEGES;