You can add users two di erent ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, becausethey are more concise and less error-prone.
There are also a lot of contributed programs like phpmyadmin that can be used to create and administrate users.
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.
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:
MySQL Technical Reference for Version 4.0.1-alpha 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;
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 gr ant 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 e ect.
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 web site (http://www.mysql.com/Downloads/Contrib/).
Setting Up Passwords
In most cases you should use GRANT to set up your users/passwords, so the following only
applies for advanced users.
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’); If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD() function is unnecessary. They both take care of encrypting the password for 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() does not perform password encryption in the same way that Unix passwords are encrypted. You should not assume that if your Unix password and your MySQL password are the same, that PASSWORD() will result in the same encrypted value as is stored in the Unix password file.or
shell> mysqladmin -u jeffrey password biscuit
It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed below, along with an assessment of the risks of each method:
• Never give a normal user access to the mysql.user table. Knowing the encrypted password for a user makes it possible to login as this user. The passwords are only scrambled so that one 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.
It is more secure to enter your password this way than to specify it on the command line
because it is not visible to other users. However, this method of entering a password is suitable only 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.
• You can store your password in the MYSQL_PWD environment variable, but this method
must be considered extremely insecure and should not be used. Some versions of ps include an option 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.
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
MySQL has support for SSL encrypted connections. To understand how MySQL uses SSL,
we need to explain some basics about SSL and X509. People who are already aware of it
can skip this part.
By default, MySQL uses unencrypted connections between client and server. This means
that someone could watch all your tra c and look at the data being sent/received. Actually,
they could even change the data while it is in transit between client and server. Sometimes
you need to move really secret data over public networks and in such a case using an
unencrypted connection is unacceptable.
SSL is a protocol which uses di erent encryption algorithms to ensure that data which
comes from public network can be trusted. It has mechanisms to detect any change, loss or
replay of data. SSL also incorpores 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 order of encrypted messages or replaying
data twice.
X509 is a standard that makes it possible to identify someone in the Internet. It is most
commonly used in e-commerce applications. In basic terms, there should be some company
called "Certificate Authority" which assigns electronic certificates to anyone who needs
them. Certificates rely on asymmetric encryption algorithms which have two encryption
keys - public and secret. A certificate owner can prove his identity by showing his certificate
to other party. A certificate consists of his owner’s public key. Any data encrypted with
this public key can only be decrypted using the corresponding secret key, which is held by
the owner of the certificate.
MySQL doesn’t use encrypted on connections by default, because this would make the
client/server protocol much slower. Any kind of additional functionality requires computer
to do additional work and encrypting data is CPU-intensive operation require 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/encryption, you should use your favourite
internet search engine and search for keywords you are interested in.
MySQL Database AdministrationRequirements
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.
3. If you are using an old MySQL installation, you have to update your mysql.user
table with some new columns. You can do this by running the mysql_fix_privilege_
tables.sh script.
4. You can check if a running mysqld server supports openssl by examining if SHOW
VARIABLES LIKE ’have_openssl’ returns YES.
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 di erent possibilities to limit connections:
• Without any SSL/X509 options, all kind of encrypted/unencrypted connections are
allowed if username and password are valid.
• REQUIRE SSL option limits the server to allow only SSL encrypted connections. Note
that this option can be omitted if there are any ACL records which allow non-SSL
connections.
GRANT ALL PRIVILEGES ON test.* TO root@localhost
IDENTIFIED BY "goodsecret" REQUIRE SSL
• REQUIRE X509 means that client should have 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.
GRANT ALL PRIVILEGES ON test.* TO root@localhost
IDENTIFIED BY "goodsecret" REQUIRE X509
• REQUIRE ISSUER issuer makes connection more restrictive: now client must present
a valid X509 certificate issued by CA "issuer". Using X509 certificates always implies
encryption, so the option "SSL" is not neccessary anymore.
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 client have valid certificate but having di erent "subject" then the
connection is still not allowed.
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.
GRANT ALL PRIVILEGES ON test.* TO root@localhost
IDENTIFIED BY "goodsecret"
REQUIRE CIPHER "EDH-RSA-DES-CBC3-SHA"
Also it is allowed to combine these options with each other like this:
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"
But it is not allowed to use any of options twice. Only di erent options can be mixed.
Disaster Prevention and Recovery
Database Backups
Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables followed by FLUSH TABLES for the tables.
You only need a read lock; this allows other threads to continue to query the tables while you are
making a copy of the files in the database directory. The FLUSH TABLE is needed to ensure
that the all active index pages is written to disk before you start the backup.
If you want to make a SQL level backup of a table, you can use SELECT INTO OUTFILE
or BACKUP TABLE.
Another way to back up a database is to use the mysqldump program or the mysqlhotcopy
script.
1. Do a full backup of your databases:
shell> mysqldump --tab=/path/to/some/dir --opt --full
or
shell> mysqlhotcopy database /path/to/some/dir
You can also simply copy all table files (‘*.frm’, ‘*.MYD’, and ‘*.MYI’ files) as long as the server isn’t updating anything. The script mysqlhotcopy does use this method.
2. Stop mysqld if it’s running, then start it with the --log-update[=file_name] option.
Information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.
If you have to restore something, try to recover your tables using REPAIR TABLE or yisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure (this will only work if you have started MySQL with --log-update,
1. Restore the original mysqldump backup.
2. Execute the following command to re-run the updates in the binary log:
shell> mysqlbinlog hostname-bin.[0-9]* | mysql
If you are using the update log you can use:
shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
ls is used to get all the update log files in the right order.
You can also do selective backups with SELECT * INTO OUTFILE ’file_name’ FROM tbl_
name and restore with LOAD DATA INFILE ’file_name’ REPLACE ... To avoid duplicate
records, you need a PRIMARY KEY or a UNIQUE key in the table. The REPLACE keyword
causes old records to be replaced with new ones when a new record duplicates an old record
on a unique key value.
If you are using a Veritas file system, you can do:
1. Execute in a client (perl ?) FLUSH TABLES WITH READ LOCK
2. Fork a shell or execute in another client mount vxfs snapshot.
3. Execute in the first client UNLOCK TABLES
4. Copy files from snapshot
5. Unmount snapshot
4.4.2 BACKUP TABLE Syntax
BACKUP TABLE tbl_name[,tbl_name...] TO ’/path/to/backup/directory’
Make a copy of all the table files to the backup directory that are the minimum needed to restore it. Currenlty only works for MyISAM tables. For MyISAM table, copies .frm (definition) and .MYD (data) files. The index file can be rebuilt from those two.
During the backup, read lock will be held for each table, one at time, as they are being backed up. If you want to backup several tables as a snapshot, you must first issue LOCK TABLES obtaining a read lock for each table in the group.
The command returns a table with the following columns:
Column
Value
Table
Table name
Op
Always “backup”
MySQL Technical Reference for Version 4.0.1-alpha
Msg type
One of status, error, info or warning.
Msg text
The message.
Note that BACKUP TABLE is only available in MySQL version 3.23.25 and later.
RESTORE TABLE Syntax
RESTORE TABLE tbl_name[,tbl_name...] FROM ’/path/to/backup/directory’
Restores the table(s) from the backup that was made with BACKUP TABLE. Existing tables will not be overwritten - if you try to restore over an existing table, you will get an error.
Restore will take longer than BACKUP due to the need to rebuilt the index. The more keys you have, the longer it is going to take. Just as BACKUP TABLE, currently only works of MyISAM tables.
The command returns a table with the following columns:
Column
Value
Table
Table name
Op
Always “restore”
Msg type
One of status, error, info or warning.
Msg text
The message.
CHECK TABLE Syntax
CHECK TABLE tbl_name[,tbl_name...] [option [option...]]
option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED
CHECK TABLE only works on MyISAM tables. On MyISAM tables it’s the same thing as running myisamchk -m table_name on the table.
If you don’t specify any option MEDIUM is used.
Checks the table(s) for errors. For MyISAM tables the key statistics is updated. The com-
mand returns a table with the following columns:
Column
Value
Table
Table name.
Op
Always “check”.
Msg type
One of status, error, info, or warning.
Msg text
The message.
Note that you can get many rows of information for each checked table. The last row will be of Msg_type status and should normally be OK. If you don’t get OK, or Not checked you should normally run a repair of the table.
Not checked means that the table the given TYPE told MySQL that there wasn’t any need
to check the table.
The di erent check types stand for the following:
Type Meaning
QUICK Don’t scan the rows to check for wrong links.
FAST Only check tables which haven’t been closed properly.
CHANGED Only check tables which have been changed since last check or haven’t
been closed properly.
MEDIUM Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calcualted checksum for the keys.
EXTENDED Do a full key lookup for all keys for each row. This ensures that the table is 100 % consistent, but will take a long time!
For dynamic sized MyISAM tables a started check will always do a MEDIUM check. For static
size rows we skip the row scan for QUICK and FAST as the rows are very seldom corrupted.
You can combine check options as in:
CHECK TABLE test_table FAST QUICK;
Which only would do a quick check on the table if it wasn’t closed properly.
Note: that in some case CHECK TABLE will change the table! This happens if the table is marked as ’corrupted’ or ’not closed properly’ but CHECK TABLE didn’t find any problems in the table. In this case CHECK TABLE will mark the table as okay.
If a table is corrupted, then it’s most likely that the problem is in the indexes and not in the data part. All of the above check types checks the indexes throughly and should thus find most errors.
If you just want to check a table that you assume is okay, you should use no check options or the QUICK option. The latter should be used when you are in a hurry and can take the very small risk that QUICK didn’t find an error in the data file. (In most cases MySQL should find, under normal usage, any error in the data file. If this happens then the table will be marked as ’corrupted’, in which case the table can’t be used until it’s repaired.)
FAST and CHANGED are mostly intended to be used from a script (for example to be executed
from cron) if you want to check your table from time to time. In most cases you FAST is to
be prefered over CHANGED. (The only case when it isn’t is when you suspect a bug you have
found a bug in the MyISAM code.)
EXTENDED is only to be used after you have run a normal check but still get strange errors
from a table when MySQL tries to update a row or find a row by key (this is very unlikely
if a normal check has succeeded!).
• Found row where the auto_increment column has the value 0.
This means that you have in the table a row where the auto_increment index column contains the value 0. (It’s possible to create a row where the auto increment column is 0 by explicitely setting the column to 0 with an UPDATE statement).
This isn’t an error in itself, but could cause trouble if you decide to dump the table and restore it or do an ALTER TABLE on the table. In this case the auto increment column will change value, according to the rules of auto increment columns, which could cause problems like a duplicate key error.
To get rid of the warning, just execute an UPDATE statement to set the column to some
other value than 0.
REPAIR TABLE Syntax
REPAIR TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED]
REPAIR TABLE only works on MyISAM tables and is the same as running myisamchk -r
table_name on the table.
Normally you should never have to run this command, but if disaster strikes you are very likely to get back all your data from a MyISAM table with REPAIR TABLE. If your tables get corrupted a lot you should try to find the reason for this!
REPAIR TABLE repairs a possible corrupted table. The command returns a table with the
following columns:
Column
Value
Table
Table name
Op
Always “repair”
Msg type
One of status, error, info or warning.
Msg text
The message.
Note that you can get many rows of information for each repaired table. The last one row will be of Msg_type status and should normally be OK. If you don’t get OK, you should try repairing the table with myisamchk -o, as REPAIR TABLE does not yet implement all the options of myisamchk. In the near future, we will make it more exible.
If QUICK is given then MySQL will try to do a REPAIR of only the index tree.
If you use EXTENDED then MySQL will create the index row by row instead of creating one
index at a time with sorting; This may be better than sorting on fixed-length keys if you
have long char() keys that compress very good.
Using myisamchk for Table Maintenance and Crash Recovery
To check/repair MyISAM tables (.MYI and .MYD) you should use the myisamchk utility.
To check/repair ISAM tables (.ISM and .ISD) you should use the isamchk utility.
In the following text we will talk about myisamchk, but everything also applies to the old
isamchk.
You can use the myisamchk utility to get information about your database tables, check and
repair them, or optimise them. The following sections describe how to invoke myisamchk
(including a description of its options), how to set up a table maintenance schedule, and
how to use myisamchk to perform its various functions.
0 commenti:
Post a Comment