GNU Info

(mysql.info)Adding users

---

Next: Passwords Prev: Default privileges Up: User Account Management

---

Enter node , (file) or (file)node

Adding New Users to MySQL
-------------------------

You can add users two different ways: by using `GRANT' statements or by
manipulating the MySQL grant tables directly.  The preferred method is
to use `GRANT' statements, because they are more concise and less
error-prone. Note: GRANT.

There is also a lot of contributed programs like `phpmyadmin' that can
be used to create and administrate users. Note: Contrib.

The examples below show how to use the `mysql' client to set up new
users.  These examples assume that privileges are set up according to
the defaults described in the previous section.  This means that to
make changes, you must be on the same machine where `mysqld' is
running, you must connect as the MySQL `root' user, and the `root' user
must have the *insert* privilege for the `mysql' database and the
*reload* administrative privilege.  Also, if you have changed the
`root' user password, you must specify it for the `mysql' commands
below.

You can add new users by issuing `GRANT' statements:

     shell> mysql --user=root mysql
     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;

These `GRANT' statements set up three new users:

`monty'
     A full superuser who can connect to the server from anywhere, but
     who must use a password `'some_pass'' to do so.  Note that we must
     issue `GRANT' statements for both `monty@localhost' and
     `monty@"%"'.  If we don't add the entry with `localhost', the
     anonymous user entry for `localhost' that is created by
     `mysql_install_db' will take precedence when we connect from the
     local host, because it has a more specific `Host' field value and
     thus comes earlier in the `user' table sort order.

`admin'
     A user who can connect from `localhost' without a password and who
     is granted the *reload* and *process* administrative privileges.
     This allows the user to execute the `mysqladmin reload',
     `mysqladmin refresh', and `mysqladmin flush-*' commands, as well as
     `mysqladmin processlist' .  No database-related privileges are
     granted.  (They can be granted later by issuing additional `GRANT'
     statements.)

`dummy'
     A user who can connect without a password, but only from the local
     host.  The global privileges are all set to `'N'' -- the `USAGE'
     privilege type allows you to create a user with no privileges.  It
     is assumed that you will grant database-specific privileges later.

You can also add the same user access information 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;

Depending on your MySQL version, you may have to use a different number
of `'Y'' values above (versions prior to Version 3.22.11 had fewer
privilege columns).  For the `admin' user, the more readable extended
`INSERT' syntax that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a `user' table
entry with the privilege fields set to `'Y''.  No `db' or `host' table
entries are necessary.

The privilege columns in the `user' table were not set explicitly in the
last `INSERT' statement (for the `dummy' user), so those columns are
assigned the default value of `'N''.  This is the same thing that
`GRANT USAGE' does.

The following example adds a user `custom' who can connect from hosts
`localhost', `server.domain', and `whitehouse.gov'.  He wants to access
the `bankaccount' database only from `localhost', the `expenses'
database only from `whitehouse.gov', and the `customer' database from
all three hosts.  He wants to use the password `stupid' from all three
hosts.

To set up this user's privileges using `GRANT' statements, run these
commands:

     shell> mysql --user=root mysql
     mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
                ON bankaccount.*
                TO custom@localhost
                IDENTIFIED BY 'stupid';
     mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
                ON expenses.*
                TO custom@whitehouse.gov
                IDENTIFIED BY 'stupid';
     mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
                ON customer.*
                TO custom@'%'
                IDENTIFIED BY 'stupid';

The reason that we do to grant statements for the user 'custom' is that
we want the give the user access to MySQL both from the local machine
with Unix sockets and from the remote machine 'whitehouse.gov' over
TCP/IP.

To set up the user's privileges by modifying the grant tables directly,
run these commands (note the `FLUSH PRIVILEGES' at the end):

     shell> mysql --user=root mysql
     mysql> INSERT INTO user (Host,User,Password)
            VALUES('localhost','custom',PASSWORD('stupid'));
     mysql> INSERT INTO user (Host,User,Password)
            VALUES('server.domain','custom',PASSWORD('stupid'));
     mysql> INSERT INTO user (Host,User,Password)
            VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
     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('%','customer','custom','Y','Y','Y','Y','Y','Y');
     mysql> FLUSH PRIVILEGES;

The first three `INSERT' statements add `user' table entries that allow
user `custom' to connect from the various hosts with the given
password, but grant no permissions to him (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 the grant tables are modified directly,
the server must be told to reload them (with `FLUSH PRIVILEGES') so
that the privilege changes take effect.

If you want to give a specific user access from any machine in a given
domain, you can issue a `GRANT' statement like the following:

     mysql> GRANT ...
                ON *.*
                TO myusername@"%.mydomainname.com"
                IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

     mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
                PASSWORD('mypassword'),...);
     mysql> FLUSH PRIVILEGES;

You can also use `xmysqladmin', `mysql_webadmin', and even `xmysql' to
insert, change, and update values in the grant tables.  You can find
these utilities in the Contrib directory of the MySQL Website
(http://www.mysql.com/Downloads/Contrib/).