MySQL User Account Management
=============================
`GRANT' and `REVOKE' Syntax
---------------------------
BY [PASSWORD] '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 #]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
`GRANT' is implemented in MySQL Version 3.22.11 or later. For earlier
MySQL versions, the `GRANT' statement does nothing.
The `GRANT' and `REVOKE' commands allow system administrators to create
users and grant and revoke rights to MySQL users at four privilege
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 *.*' will grant and revoke only global
privileges.
*Database level*
Database privileges apply to all tables in a given database. These
privileges are stored in the `mysql.db' and `mysql.host' tables.
`GRANT ALL ON db.*' and `REVOKE ALL ON db.*' will 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
ALL ON db.table' and `REVOKE ALL ON db.table' will grant and
revoke only table privileges.
columns that were granted.
For the `GRANT' and `REVOKE' statements, `priv_type' may be specified
as any of the following:
TABLE'
`CREATE TEMPORARY Allows usage of `CREATE TEMPORARY TABLE'
TABLES'
`DELETE' Allows usage of `DELETE'
`DROP' Allows usage of `DROP TABLE'.
`EXECUTE' Allows the user to run stored procedures (MySQL
5.0)
`FILE' Allows usage of `SELECT ... INTO OUTFILE' and
`LOAD DATA INFILE'.
`INDEX' Allows usage of `CREATE INDEX' and `DROP INDEX'
`INSERT' Allows usage of `INSERT'
`LOCK TABLES' Allows usage of `LOCK TABLES' on tables for which
one has the `SELECT' privilege.
`PROCESS' Allows usage of `SHOW FULL PROCESSLIST'
`REFERENCES' For the future
`RELOAD' Allows usage of `FLUSH'
`REPLICATION CLIENT' Gives the right to the user to ask where the
slaves/masters are.
`REPLICATION SLAVE' Needed for the replication slaves (to read
binlogs from master).
`SELECT' Allows usage of `SELECT'
`SHOW DATABASES' `SHOW DATABASES' shows all databases.
`SHUTDOWN' Allows usage of `mysqladmin shutdown'
`SUPER' Allows one connect (once) even if max_connections
is reached and execute commands `CHANGE MASTER',
`KILL thread', `mysqladmin debug', `PURGE MASTER
LOGS' and `SET GLOBAL'
`UPDATE' Allows usage of `UPDATE'
`USAGE' Synonym for "no privileges."
`GRANT OPTION' Synonym for `WITH GRANT OPTION'
`USAGE' can be used when you want to create a user that has no
privileges.
The privileges `CREATE TEMPORARY TABLES', `EXECUTE', `LOCK TABLES',
`REPLICATION ...', `SHOW DATABASES' and `SUPER' are new for in version
4.0.2. To use these new privileges after upgrading to 4.0.2, you have
to run the `mysql_fix_privilege_tables' script. *Note
Upgrading-grant-tables::.
In older MySQL versions, the `PROCESS' privilege gives the same rights
as the new `SUPER' privilege.
To revoke the `GRANT' privilege from a user, use a `priv_type' value of
`GRANT OPTION':
mysql> REVOKE GRANT OPTION ON ... FROM ...;
The only `priv_type' values you can specify for a table are `SELECT',
`INSERT', `UPDATE', `DELETE', `CREATE', `DROP', `GRANT OPTION',
`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'.
MySQL allows you to create database level privileges even if the
database doesn't exist, to make it easy to prepare for database usage.
Currently MySQL does however not allow one to create table level grants
if the table doesn't exist. MySQL will not automatically revoke any
privileges even if you drop a table or drop a database.
for
that database. (*Warning*: if you specify `ON *' and you *don't* have
a current database, you will affect the global privileges!)
*Please note*: the `_' and `%' wildcards are allowed when specifying
database names in `GRANT' commands. This means that if you wish to use
for instance a `_' character as part of a database name, you should
specify it as `\_' in the `GRANT' command, 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_name' value in the form
`user@host'. If you want to specify a `user' string containing special
characters (such as `-'), or a `host' string containing special
characters or wildcard characters (such as `%'), you can quote the user
or host name (for example, `'test-user'@'test-hostname'').
You can specify wildcards in the hostname. For example,
`user@'%.loc.gov'' applies to `user' for any host in the `loc.gov'
domain, and `user@'144.155.166.%'' applies to `user' for any host in
the `144.155.166' class C subnet.
The simple form `user' is a synonym for `user@"%"'.
MySQL doesn't support wildcards in user names. Anonymous users are
defined by inserting entries with `User=''' into the `mysql.user' table
or creating an user with an empty name with the `GRANT' command.
*Note*: if you allow anonymous users to connect to the MySQL server,
you should also grant privileges to all local users as `user@localhost'
because otherwise the anonymous user entry for the local host in the
`mysql.user' table will be used when the user tries to log into the
MySQL server from the local machine!
You can verify if this applies to you by executing this query:
mysql> SELECT Host,User FROM mysql.user WHERE User='';
For the moment, `GRANT' only supports host, table, database, and column
names up to 60 characters long. A user name can be up to 16 characters.
`SELECT'
privilege, this can't 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
system::.
If you grant privileges for a user/hostname combination that does not
exist in the `mysql.user' table, an entry is added and remains there
until deleted with a `DELETE' command. In other words, `GRANT' may
create `user' table entries, but `REVOKE' will not remove them; you
must do that explicitly using `DELETE'.
In MySQL Version 3.22.12 or later, if a new user is created or if you
have global grant privileges, the user's password will be set to the
password specified by the `IDENTIFIED BY' clause, if one is given. If
the user already had a password, it is replaced by the new one.
If you don't want to send the password in clear text you can use the
`PASSWORD' option followed by a scrambled password from SQL function
`PASSWORD()' or the C API function `make_scrambled_password(char *to,
const char *password)'.
*Warning*: if you create a new user but do not specify an `IDENTIFIED
BY' clause, the user has no password. This is insecure.
Passwords can also be set with the `SET PASSWORD' command. *Note
`SET': SET OPTION.
If you grant privileges for a database, an entry in the `mysql.db'
table is created if needed. When all privileges for the database have
been removed with `REVOKE', this entry is deleted.
If a user doesn't have any privileges on a table, the table is not
displayed when the user requests a list of tables (for example, with a
`SHOW TABLES' statement). The same is true for `SHOW DATABASES'.
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' privilege,
as two users with different privileges may be able to join privileges!
one hour. If `#' is 0 (default), then this means that there are
no limitations for that user. *Note User resources::. Note: to specify
any of these options for an existing user without adding other
additional privileges, use `GRANT USAGE ON *.* ... WITH MAX_...'.
You cannot grant another user a privilege you don't have yourself; the
`GRANT' privilege allows you to give away only those privileges you
possess.
Be aware that when you grant a user the `GRANT' privilege at a
particular privilege level, any privileges the user already possesses
(or is given in the future!) at that level are also grantable by that
user. Suppose 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 the `INSERT',
`SELECT' and `UPDATE'.
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
tables!
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 will slow down MySQL a bit.
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', etc.), you should execute a `FLUSH
PRIVILEGES' statement or run `mysqladmin flush-privileges' to tell the
server to reload the grant tables. *Note Privilege changes::.
The biggest differences between the SQL standard and MySQL versions of
`GRANT' are:
* In MySQL privileges are given for an username + hostname
combination and not only for an username.
* SQL-99 doesn't have global or database-level privileges, nor does
it support all the privilege types that MySQL supports. MySQL
doesn't support the SQL-99 `TRIGGER' or `UNDER' privileges.
* SQL-99 privileges are structured in a hierarchal manner. If you
remove an user, all privileges the user has granted are revoked. In
MySQL the granted privileges are not automatically revoked, but
you have to revoke these yourself if needed.
* 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. SQL-99 requires you to have
the `INSERT' privilege on all columns.
* With SQL99, when you drop a table, all privileges for the table
are revoked. With SQL-99, 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' commands or by manipulating the MySQL grant tables.
For a description of using `REQUIRE', see *Note Secure connections::.
MySQL User Names and Passwords
------------------------------
There are several distinctions between the way user names and passwords
are used by MySQL and the way they are used by Unix or Windows:
* User names, as used by MySQL for authentication purposes, have
nothing to do with Unix user names (login names) or Windows user
names. Most MySQL clients by default try to log in using the
current Unix user name as the MySQL user name, but that is for
convenience only. Client programs allow a different name to be
specified with the `-u' or `--user' options. This means that you
can't make a database secure in any way unless all MySQL user
names have passwords. Anyone may attempt to connect to the server
using any name, and they will succeed if they specify any name
that doesn't have a password.
* MySQL user names can be up to 16 characters long; Unix user names
typically are limited to 8 characters.
* MySQL passwords have nothing to do with Unix passwords. There is
no necessary connection between the password you use to log in to
a Unix machine and the password you use to access a database on
that machine.
* MySQL encrypts passwords using a different algorithm than the one
used during the Unix login process. See the descriptions of the
`PASSWORD()' and `ENCRYPT()' functions in *Note Miscellaneous
functions::. Note that even if the password is stored
'scrambled', and knowing your 'scrambled' password is enough to be
able to connect to the MySQL server! From version 4.1, MySQL
employs a different password and login mechanism that is secure
even if TCP/IP packets are sniffed and/or the mysql database is
captured.
MySQL users and their privileges are normally created with the `GRANT'
command. *Note GRANT::.
When you login to a MySQL server with a command-line client you should
specify the password with `--password=your-password'. *Note
Connecting::.
mysql --user=monty --password=guess database_name
If you want the client to prompt for a password, you should use
`--password' without any argument
mysql --user=monty --password database_name
or the short form:
mysql -u monty -p database_name
Note that in the last example the password is *not* 'database_name'.
If you want to use the `-p' option to supply a password you should do so
like this:
mysql -u monty -pguess database_name
On some systems, the library call that MySQL uses to prompt for a
password will automatically cut the password to 8 characters. Internally
MySQL doesn't have any limit for the length of the password.
When Privilege Changes Take Effect
----------------------------------
When `mysqld' starts, all grant table contents are read into memory and
become effective at that point.
Modifications to the grant tables that you perform using `GRANT',
`REVOKE', or `SET PASSWORD' are noticed by the server immediately.
to reload the grant tables. Otherwise, your changes will have _no
effect_ until you restart the server. If you change the grant tables
manually but forget to reload the privileges, you will be wondering why
your changes don't seem to make any difference!
When the server notices that the grant tables have been changed,
existing client connections are affected as follows:
* Table and column privilege changes take effect with the client's
next request.
* Database privilege changes take effect at the next `USE db_name'
command.
* Global privilege changes and password changes take effect the next
time the client connects.
Setting Up the Initial MySQL Privileges
---------------------------------------
the grant tables to contain the following set of privileges:
* The MySQL `root' user is created as a superuser who can do
anything. Connections must be made from the local host.
*Note*: The initial `root' password is empty, so anyone can
connect as `root' _without a password_ and be granted all
privileges.
local user can connect without a password and be treated as the
anonymous user.
* Other privileges are denied. For example, normal users can't use
`mysqladmin shutdown' or `mysqladmin processlist'.
*Note*: the default privileges are different for Windows. *Note
Windows running::.
using
the `PASSWORD()' function):
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
Replace `'new_password'' with the password that you want to use.
If you know what you are doing, you can also directly manipulate the
privilege tables:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;
Another way to set the password is by using the `mysqladmin' command:
shell> mysqladmin -u root password new_password
commands or
with `SET PASSWORD=PASSWORD('new_password')'.
otherwise.
Once the `root' password has been set, thereafter you must supply that
password when you connect to the server as `root'.
You may wish to leave the `root' password blank so that you don't need
to specify it while you perform additional setup or testing. However,
be sure to set it before using your installation for any real
production work.
See the `scripts/mysql_install_db' script to see how it sets up the
default privileges. You can use this as a basis to see how to add
other users.
If you want the initial privileges to be different from those just
described above, you can modify `mysql_install_db' before you run it.
To re-create the grant tables completely, remove all the `.frm',
`.MYI', and `.MYD' files in the directory containing the `mysql'
database. (This is the directory named `mysql' under the database
directory, which is listed when you run `mysqld --help'.) Then run the
`mysql_install_db' script, possibly after editing it first to have the
privileges you want.
*Note*: for MySQL versions older than Version 3.22.10, you should not
delete the `.frm' files. If you accidentally do this, you should copy
them back from your MySQL distribution before running
`mysql_install_db'.
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 are also several contributed programs (such as `phpMyAdmin') that
can be used to create and administrate users.
The following examples 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 here.
First, use the `mysql' program to connect to the server as the MySQL
`root' user:
shell> mysql --user=root mysql
Then you can add new users by issuing `GRANT' statements:
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:
and
`monty@"%"'. If we don't add the entry with `localhost', the
anonymous user entry for `localhost' that is created by
`mysql_install_db' takes 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-level 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. No privileges are granted--the `USAGE' privilege type
allows you to create a user with no 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.
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;
For the
`admin' user, the more readable extended `INSERT' syntax using `SET'
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.
In the last `INSERT' statement (for the `dummy' user), 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 the same thing
that `GRANT USAGE' does.
The following example adds a user `custom' who can access the
`bankaccount' database only from the local host, the `expenses'
database only from the host `whitehouse.gov', and the `customer'
database only from the host `server.domain'. He wants to use the
password `obscure' 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 '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';
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('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;
As in the preceding example that used `INSERT' statements, you may need
to use a different number of `'Y'' values, depending on your version of
MySQL.
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, after you modify the grant tables directly ,
you must 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 any machine in a given
domain (for example, `mydomain.com'), you can issue a `GRANT' statement
like the following:
mysql> GRANT ...
-> ON *.*
-> TO myusername@'%.mydomain.com'
-> IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomain.com', 'myusername',
-> PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
Deleting Users from MySQL
-------------------------
DROP USER user_name
This command was added to MySQL 4.1.1.
It deletes a user that doesn't have any privileges.
To delete a user from MySQL you should do things in the following order:
1. Check which privileges the user has with `SHOW PRIVILEGES'. *Note
SHOW PRIVILEGES::.
2. Delete all privileges from the user with `REVOKE'. *Note GRANT::.
3. Delete the user with `DROP USER'.
If you are using and older MySQL version you should first revoke the
privileges and then delete the user with:
DELETE FROM mysql.user WHERE user='username' and host='hostname';
FLUSH PRIVILEGES;
Limiting user resources
-----------------------
Starting from MySQL 4.0.2 one can limit certain resources per user.
does not
allow for management of individual users, which could be of particular
interest to Internet Service Providers.
Therefore, management of three resources is introduced on the
individual user level:
* Number of all queries per hour: All commands that could be run by
a user.
* Number of all updates per hour: Any command that changes any table
or database.
* Number of connections made per hour: New connections opened per
hour.
A user in the aforementioned context is a single entry in the `user'
table, which is uniquely identified by its `user' and `host' columns.
GRANT ... WITH MAX_QUERIES_PER_HOUR N1
MAX_UPDATES_PER_HOUR N2
MAX_CONNECTIONS_PER_HOUR N3;
One can specify any combination of the above resources. `N1', `N2',
and `N3' are integers and represent counts per hour.
If a user reaches the limit on number of connections within one hour, no
further connections will be accepted until that hour is up. Similarly,
if the user reaches the limit on number of queries or updates, further
queries or updates will be rejected until the hour is up. In all cases,
an appropriate error message shall be issued.
Current usage values for a particular user can be flushed (set to zero)
by issuing a `GRANT' statement with any of the above clauses, including
a `GRANT' statement with the current values.
Also, current values for all users will be flushed if privileges are
reloaded (in the server or using `mysqladmin reload') or if the `FLUSH
USER_RESOURCES' command is issued.
The feature is enabled as soon as a single user is granted with any of
the limiting `GRANT' clauses.
As a prerequisite for enabling this feature, the `user' table in the
`mysql' database must contain the additional columns, as defined in the
table creation scripts `mysql_install_db' and `mysql_install_db.sh' in
`scripts' subdirectory.
Setting Up Passwords
--------------------
In most cases you should use `GRANT' to set up your users/passwords, so
the following only applies for advanced users. *Note `GRANT': GRANT.
The examples in the preceding sections illustrate an important
principle: when you store a non-empty password using `INSERT' or
`UPDATE' statements, you must use the `PASSWORD()' function to encrypt
it. This is because the `user' table stores passwords in encrypted
form, not as plaintext. If you forget that fact, you are likely to
attempt to set passwords like this:
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
The result is that the plaintext value `'biscuit'' is stored as the
password in the `user' table. When the user `jeffrey' attempts to
connect to the server using this password, the `mysql' client encrypts
it with `PASSWORD()', generates an authentification vector based on
*encrypted* password and a random number, obtained from server, and
sends the result to the server. The server uses the `password' value
in the `user' table (that is *not encrypted* value `'biscuit'') to
perform the same calculations, and compares results. The comparison
fails and the server rejects the connection:
shell> mysql -u jeffrey -pbiscuit test
Access denied
Passwords must be encrypted when they are inserted in the `user' table,
so the `INSERT' statement should have been specified like this instead:
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey',PASSWORD('biscuit'));
You must also use the `PASSWORD()' function when you use `SET PASSWORD'
statements:
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');
you,
so you would specify a password of `'biscuit'' like this:
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';
or
shell> mysqladmin -u jeffrey password biscuit
*Note*: `PASSWORD()' is different from Unix password encryption. *Note
User names::.
Keeping Your Password Secure
----------------------------
with an
assessment of the risks of each method:
shouldn't
be able to see the real password you used (if you happen to use a
similar password with your other applications).
* Use a `-pyour_pass' or `--password=your_pass' option on the command
line. This is convenient but insecure, because your password
becomes visible to system status programs (such as `ps') that may
be invoked by other users to display command-lines. (MySQL
clients typically overwrite the command-line argument with zeroes
during their initialisation sequence, but there is still a brief
interval during which the value is visible.)
* Use a `-p' or `--password' option (with no `your_pass' value
specified). In this case, the client program solicits the
password from the terminal:
shell> mysql -u user_name -p
Enter password: ********
The `*' characters represent your password.
for
programs that you run interactively. If you want to invoke a
client from a script that runs non-interactively, there is no
opportunity to enter the password from the terminal. On some
systems, you may even find that the first line of your script is
read and interpreted (incorrectly) as your password!
* Store your password in a configuration file. For example, you can
list your password in the `[client]' section of the `.my.cnf' file
in your home directory:
[client]
password=your_pass
If you store your password in `.my.cnf', the file should not be
group or world readable or writable. Make sure the file's access
mode is `400' or `600'.
*Note Option files::.
to display the environment of running processes; your password
will be in plain sight for all to see if you set `MYSQL_PWD'.
Even on systems without such a version of `ps', it is unwise to
assume there is no other method to observe process environments.
*Note Environment variables::.
All in all, the safest methods are to have the client program prompt
for the password or to specify the password in a properly protected
`.my.cnf' file.
Using Secure Connections
------------------------
Basics
......
with them can skip this part.
By default, MySQL uses unencrypted connections between the client and
the server. This means that someone could watch all your traffic and
look at the data being sent or received. They could even change the
data while it is in transit between client and server. Sometimes you
need to move information over public networks in a secure fashion; in
such cases, using an unencrypted connection is unacceptable.
SSL is a protocol that uses different encryption algorithms to ensure
that data received over a public network can be trusted. It has
mechanisms to detect any change, loss or replay of data. SSL also
incorporates algorithms to recognise and provide identity verification
using the X509 standard.
Encryption is the way to make any kind of data unreadable. In fact,
today's practice requires many additional security elements from
encryption algorithms. They should resist many kind of known attacks
like just messing with the order of encrypted messages or replaying data
twice.
X509 is a standard that makes it possible to identify someone on the
Internet. It is most commonly used in e-commerce applications. In basic
terms, there should be some company (called a "Certificate Authority")
that assigns electronic certificates to anyone who needs them.
Certificates rely on asymmetric encryption algorithms that have two
encryption keys (a public key and a secret key). A certificate owner
can prove his identity by showing his certificate to other party. A
certificate consists of its owner's public key. Any data encrypted with
this public key can be decrypted only using the corresponding secret
key, which is held by the owner of the certificate.
MySQL doesn't use encrypted connections by default, because doing so
would make the client/server protocol much slower. Any kind of
additional functionality requires the computer to do additional work and
encrypting data is a CPU-intensive operation that requires time and can
delay MySQL main tasks. By default MySQL is tuned to be fast as
possible.
If you need more information about SSL, X509, or encryption, you should
use your favourite Internet search engine and search for keywords in
which you are interested.
Requirements
............
To get secure connections to work with MySQL you must do the following:
1. Install the OpenSSL library. We have tested MySQL with OpenSSL
0.9.6. `http://www.openssl.org/'.
2. Configure MySQL with `--with-vio --with-openssl'.
to MySQL
4.0.0. The procedure is described in *Note
Upgrading-grant-tables::.
4. You can check if a running `mysqld' server supports OpenSSL by
examining if `SHOW VARIABLES LIKE 'have_openssl'' returns `YES'.
Setting Up SSL Certificates for MySQL
.....................................
Here is an example for setting up SSL certificates for MySQL:
DIR=`pwd`/openssl
PRIV=$DIR/private
mkdir $DIR $PRIV $DIR/newcerts
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf
# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)
touch $DIR/index.txt
echo "01" > $DIR/serial
#
# Generation of Certificate Authority(CA)
#
openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
-config $DIR/openssl.cnf
private key to '/home/monty/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:
#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
$DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/monty/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove the passphrase from the key (optional)
#
openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
#
# Sign server cert
#
openssl ca -policy policy_anything -out $DIR/server-cert.pem \
-config $DIR/openssl.cnf -infiles $DIR/server-req.pem
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL admin'
# Certificate is to be certified until Sep 13 14:22:46 2003 GMT (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
$DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/monty/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be incorporated
# into your certificate request.
# What you are about to enter is what is called a Distinguished Name or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove a passphrase from the key (optional)
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
#
# Sign client cert
#
openssl ca -policy policy_anything -out $DIR/client-cert.pem \
-config $DIR/openssl.cnf -infiles $DIR/client-req.pem
# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create a my.cnf file that you can use to test the certificates
#
cnf=""
cnf="$cnf [client]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/client-cert.pem"
cnf="$cnf ssl-key=$DIR/client-key.pem"
cnf="$cnf [mysqld]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf
#
# To test MySQL
mysqld --defaults-file=$DIR/my.cnf &
mysql --defaults-file=$DIR/my.cnf
You can also test your setup by modifying the above `my.cnf' file to
refer to the demo certificates in the mysql-source-dist/SSL direcory.
SSL `GRANT' Options
...................
MySQL can check X509 certificate attributes in addition to the normal
username/password scheme. All the usual options are still required
(username, password, IP address mask, database/table name).
There are different possibilities to limit connections:
* Without any SSL or X509 options, all kind of encrypted/unencrypted
connections are allowed if the username and password are valid.
mysql> GRANT ALL PRIVILEGES ON test.* TO root@localhost
-> IDENTIFIED BY "goodsecret" REQUIRE SSL;
* `REQUIRE X509' means that the client should have a valid
certificate but we do not care about the exact certificate, issuer
or subject. The only restriction 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 a restriction on connection
attempts: The client must present a valid X509 certificate issued
by CA `"issuer"'. Using X509 certificates always implies
encryption, so the `SSL' option is unneccessary.
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/Email=tonu@mysql.com";
* `REQUIRE SUBJECT "subject"' requires clients to have valid X509
certificate with subject `"subject"' on it. If the client presents
a certificate that is valid but has a different `"subject"', the
connection is disallowed.
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/Email=tonu@mysql.com";
* `REQUIRE CIPHER "cipher"' is needed to assure enough strong ciphers
and keylengths will be used. SSL itself can be weak if old
algorithms with short encryption keys are used. Using this option,
we can ask for some exact cipher method to allow a connection.
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/Email=tonu@mysql.com"
-> AND ISSUER "C=FI, ST=Some-State, L=Helsinki,
"> O=MySQL Finland AB, CN=Tonu Samuel/Email=tonu@mysql.com"
-> AND CIPHER "EDH-RSA-DES-CBC3-SHA";
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 twice.
SSL Command-line Options
........................
line or in
option files.
`--ssl'
For the server, specifies that the server allows SSL connections.
For a client program, allows the client to connect to the server
using SSL. This option itself is not sufficient to cause an SSL
connection to be used. You must also specify the `--ssl-ca',
`--ssl-cert', and `--ssl-key' options.
Note that this option doesn't *require* an SSL connection. For
example, if the server or client are compiled without SSL support,
a normal unencrypted connection will be used.
to the server, with both a server and client that have SSL support
enabled.
You can use this option to indicate that the connection should not
use SSL. Do this by specifying the option as `--skip-ssl' or
`--ssl=0'.
`--ssl-ca=file_name'
The path to a file with a list of trusted SSL CAs.
`--ssl-capath=directory_name'
The path to a directory which contains trusted SSL CA certificates
in pem format.
`--ssl-cert=file_name'
The name of the SSL certificate file to use used for establishing
a secure connection.
`--ssl-cipher=cipher_list'
A list of allowable ciphers to use for SSL encryption.
`cipher_list' has the same format as the `openssl ciphers' command.
Example: `--ssl-cipher=ALL:-AES:-EXP'
`--ssl-key=file_name'
The name of the SSL key file to use used for establishing a secure
connection.
[Назад] [Содержание] [Вперед]
| Главная |