(mysql.info.gz) Adding users

(mysql.info.gz) User Account Management
 
 5.6.2 Adding New User Accounts to MySQL
 ---------------------------------------
 
 You can create MySQL accounts in two ways:
 
    * By using `GRANT' statements
 
    * By manipulating the MySQL grant tables directly
 
 The preferred method is to use `GRANT' statements, because they are
 more concise and less error-prone. `GRANT' is available as of MySQL
 3.22.11; its syntax is described in  `GRANT' GRANT.
 
 Another option for creating accounts is to use one of several available
 third-party programs that offer capabilities for MySQL account
 administration.  `phpMyAdmin' is one such program.
 
 The following examples show how to use the `mysql' client program to
 set up new users.  These examples assume that privileges are set up
 according to the defaults described in  Default privileges.
 This means that to make changes, you must connect to the MySQL server
 as the MySQL `root' user, and the `root' account must have the `INSERT'
 privilege for the `mysql' database and the `RELOAD' administrative
 privilege.
 
 First, use the `mysql' program to connect to the server as the MySQL
 `root' user:
 
      shell> mysql --user=root mysql
 
 If you have assigned a password to the `root' account, you'll also need
 to supply a `--password' or `-p' option for this `mysql' command and
 also for those later in this section.
 
 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
       Connection access.)
 
    * 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 allows 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.
 
 
 As an alternative to `GRANT', you can create the same accounts directly
 by issuing `INSERT' statements and then telling the server to reload
 the grant tables:
 
      shell> mysql --user=root mysql
      mysql> INSERT INTO user
          ->     VALUES('localhost','monty',PASSWORD('some_pass'),
          ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
      mysql> INSERT INTO user
          ->     VALUES('%','monty',PASSWORD('some_pass'),
          ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
      mysql> INSERT INTO user SET Host='localhost',User='admin',
          ->     Reload_priv='Y', Process_priv='Y';
      mysql> INSERT INTO user (Host,User,Password)
          ->     VALUES('localhost','dummy','');
      mysql> FLUSH PRIVILEGES;
 
 The reason for using `FLUSH PRIVILEGES' when you create accounts with
 `INSERT' is to tell the server to re-read the grant tables.  Otherwise,
 the changes will go unnoticed until you restart the server. With
 `GRANT', `FLUSH PRIVILEGES' is unnecessary.
 
 The reason for using the `PASSWORD()' function with `INSERT' is to
 encrypt the password. The `GRANT' statement encrypts the password for
 you, so `PASSWORD()' is unnecessary.
 
 The `'Y'' values enable privileges for the accounts.  Depending on your
 MySQL version, you may have to use a different number of `'Y'' values
 in the first two `INSERT' statements. (Versions prior to 3.22.11 have
 fewer privilege columns, and versions from 4.0.2 on have more.)  For the
 `admin' account, the more readable extended `INSERT' syntax using `SET'
 that is available starting with MySQL 3.22.11 is used.
 
 In the `INSERT' statement for the `dummy' account, only the `Host',
 `User', and `Password' columns in the `user' table record are assigned
 values. None of the privilege columns are set explicitly, so MySQL
 assigns them all the default value of `'N''.  This is equivalent to
 what `GRANT USAGE' does.
 
 Note that to set up a superuser account, it is necessary only to create
 a `user' table entry with the privilege columns set to `'Y''.  `user'
 table privileges are global, so no entries in any of the other grant
 tables are needed.
 
 The next examples create three accounts and give them access to specific
 databases. Each of them has a username of `custom' and password of
 `obscure'.
 
 To create the accounts with `GRANT', use the following statements:
 
      shell> mysql --user=root mysql
      mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
          ->     ON bankaccount.*
          ->     TO 'custom'@'localhost'
          ->     IDENTIFIED BY 'obscure';
      mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
          ->     ON expenses.*
          ->     TO 'custom'@'whitehouse.gov'
          ->     IDENTIFIED BY 'obscure';
      mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
          ->     ON customer.*
          ->     TO 'custom'@'server.domain'
          ->     IDENTIFIED BY 'obscure';
 
 The three accounts can be used as follows:
 
    * The first account can access the `bankaccount' database, but only
      from the local host.
 
    * The second account can access the `expenses' database, but only
      from the host `whitehouse.gov'.
 
    * The third account can access the `customer' database, but only
      from the host `server.domain'.
 
 To set up the `custom' accounts without `GRANT', use `INSERT'
 statements as follows to modify the grant tables directly:
 
      shell> mysql --user=root mysql
      mysql> INSERT INTO user (Host,User,Password)
          ->     VALUES('localhost','custom',PASSWORD('obscure'));
      mysql> INSERT INTO user (Host,User,Password)
          ->     VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
      mysql> INSERT INTO user (Host,User,Password)
          ->     VALUES('server.domain','custom',PASSWORD('obscure'));
      mysql> INSERT INTO db
          ->     (Host,Db,User,Select_priv,Insert_priv,
          ->     Update_priv,Delete_priv,Create_priv,Drop_priv)
          ->     VALUES('localhost','bankaccount','custom',
          ->     'Y','Y','Y','Y','Y','Y');
      mysql> INSERT INTO db
          ->     (Host,Db,User,Select_priv,Insert_priv,
          ->     Update_priv,Delete_priv,Create_priv,Drop_priv)
          ->     VALUES('whitehouse.gov','expenses','custom',
          ->     'Y','Y','Y','Y','Y','Y');
      mysql> INSERT INTO db
          ->     (Host,Db,User,Select_priv,Insert_priv,
          ->     Update_priv,Delete_priv,Create_priv,Drop_priv)
          ->     VALUES('server.domain','customer','custom',
          ->     'Y','Y','Y','Y','Y','Y');
      mysql> FLUSH PRIVILEGES;
 
 The first three `INSERT' statements add `user' table entries that allow
 the user `custom' to connect from the various hosts with the given
 password, but grant no global privileges (all privileges are set to the
 default value of `'N'').  The next three `INSERT' statements add `db'
 table entries that grant privileges to `custom' for the `bankaccount',
 `expenses', and `customer' databases, but only when accessed from the
 proper hosts.  As usual when you modify the grant tables directly, you
 tell the server to reload them with `FLUSH PRIVILEGES' so that the
 privilege changes take effect.
 
 If you want to give a specific user access from all machines in a given
 domain (for example, `mydomain.com'), you can issue a `GRANT' statement
 that uses the `%' wildcard character in the host part of the account
 name:
 
      mysql> GRANT ...
          ->     ON *.*
          ->     TO 'myname'@'%.mydomain.com'
          ->     IDENTIFIED BY 'mypass';
 
 To do the same thing by modifying the grant tables directly, do this:
 
      mysql> INSERT INTO user (Host,User,Password,...)
          ->     VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
      mysql> FLUSH PRIVILEGES;
Info Catalog
(mysql.info.gz) User names
(mysql.info.gz) User Account Management
(mysql.info.gz) Removing users
automatically generated byinfo2html