(Continued from previous part...)
MySQL - Keeping Your Password Secure
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 initialization 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.
Privileges Provided by MySQL
Information about user privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql
database (that is, in the database named mysql).
The names used in this manual to refer to the privileges provided by MySQL are shown below, along with the table column name
associated with each privilege in the grant tables and the context in which the privilege applies:
Privilege Column Context
select Select_priv tables
insert Insert_priv tables
update Update_priv tables
delete Delete_priv tables
index Index_priv tables
alter Alter_priv tables
create Create_priv databases, tables, or indexes
drop Drop_priv databases or tables
grant Grant_priv databases or tables
references References_priv databases or tables
reload Reload_priv server administration
shutdown Shutdown_priv server administration
process Process_priv server administration
file File_priv file access on server
The select, insert, update, and delete privileges allow you to perform operations on rows in existing tables in a database.
SELECT statements require the select privilege only if they actually retrieve rows from a table. You can execute certain
SELECT statements even without permission to access any of the databases on the server. For example, you could use the mysql
client as a simple calculator:
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
The index privilege allows you to create or drop (remove) indexes.
The alter privilege allows you to use ALTER TABLE.
The create and drop privileges allow you to create new databases and tables, or to drop (remove) existing databases and
tables.
Note that if you grant the drop privilege for the mysql database to a user, that user can drop the database in which the
MySQL access privileges are stored!
The grant privilege allows you to give to other users those privileges you yourself possess.
The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO
OUTFILE statements. Any user to whom this privilege is granted can read or write any file that the MySQL server can read or
write.
The remaining privileges are used for administrative operations, which are performed using the mysqladmin program. The table
below shows which mysqladmin commands each administrative privilege allows you to execute:
Privilege Commands permitted to privilege holders
reload reload, refresh, flush-privileges, flush-hosts, flush-logs, and flush-tables
shutdown shutdown
process processlist, kill
The reload command tells the server to re-read the grant tables. The refresh command flushes all tables and opens and closes
the log files. flush-privileges is a synonym for reload. The other flush-* commands perform functions similar to refresh but
are more limited in scope, and may be preferable in some instances. For example, if you want to flush just the log files,
flush-logs is a better choice than refresh.
The shutdown command shuts down the server.
The processlist command displays information about the threads executing within the server. The kill command kills server
threads. You can always display or kill your own threads, but you need the process privilege to display or kill threads
initiated by other users.
It is a good idea in general to grant privileges only to those users who need them, but you should exercise particular
caution in granting certain privileges:
The grant privilege allows users to give away their privileges to other users. Two users with different privileges and with
the grant privilege are able to combine privileges.
The alter privilege may be used to subvert the privilege system by renaming tables.
The file privilege can be abused to read any world-readable file on the server into a database table, the contents of which
can then be accessed using SELECT. This includes the contents of all databases hosted by the server!
The shutdown privilege can be abused to deny service to other users entirely, by terminating the server.
The process privilege can be used to view the plain text of currently executing queries, including queries that set or
change passwords.
Privileges on the mysql database can be used to change passwords and other access privilege information. (Passwords are
stored encrypted, so a malicious user cannot simply read them to know the plain text password). If they can access the
mysql.user password column, they can use it to log into the MySQL server for the given user. (With sufficient privileges,
the same user can replace a password with a different one.)
There are some things that you cannot do with the MySQL privilege system:
You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and
then refuse the connection.
You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database
itself.
(Continued on next part...)