Technical Interview Questions
Oracle Interview Questions
MsSql Interview Questions
My SQL Interview Questions and Answers
How to Make MySQL Secure Against Crackers ?
When you connect to a MySQL server, you normally should
use a password. The password is not transmitted in clear
text over the connection, however the encryption
algorithm is not very strong, and with some effort a
clever attacker can crack the password if he is able to
sniff the traffic between the client and the server. If
the connection between the client and the server goes
through an untrusted network, you should use an SSH
tunnel to encrypt the communication.
All other information is transferred as text that can be
read by anyone who is able to watch the connection. If
you are concerned about this, you can use the compressed
protocol (in MySQL Version 3.22 and above) to make
things much harder. To make things even more secure you
should use ssh (see http://www.cs.hut.fi/ssh). With
this, you can get an encrypted TCP/IP connection between
a MySQL server and a MySQL client.
To make a MySQL system secure, you should strongly
consider the following suggestions:
Use passwords for all MySQL users. Remember that anyone
can log in as any other person as simply as mysql -u
other_user db_name if other_user has no password. It is
common behavior with client/server applications that the
client may specify any user name. You can change the
password of all users by editing the mysql_install_db
script before you run it, or only the password for the
MySQL root user like this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
mysql> FLUSH PRIVILEGES;
Don't run the MySQL daemon as the Unix root user. It is
very dangerous as any user with FILE privileges will be
able to create files as root (for example, ~root/.bashrc).
To prevent this mysqld will refuse to run as root unless
it is specified directly via --user=root option. mysqld
can be run as any user instead. You can also create a
new Unix user mysql to make everything even more secure.
If you run mysqld as another Unix user, you don't need
to change the root user name in the user table, because
MySQL user names have nothing to do with Unix user
names. You can edit the mysql.server script to start
mysqld as another Unix user. Normally this is done with
the su command.
If you put a password for the Unix root user in the
mysql.server script, make sure this script is readable
only by root. Check that the Unix user that mysqld runs
as is the only user with read/write privileges in the
database directories. On Unix platforms, do not run
mysqld as root unless you really need to. Consider
creating a user named mysql for that purpose.
Don't give the process privilege to all users. The
output of mysqladmin processlist shows the text of the
currently executing queries, so any user who is allowed
to execute that command might be able to see if another
user issues an UPDATE user SET password=PASSWORD('not_secure')
query. mysqld reserves an extra connection for users who
have the process privilege, so that a MySQL root user
can log in and check things even if all normal
connections are in use. Don't give the file privilege to
all users. Any user that has this privilege can write a
file anywhere in the file system with the privileges of
the mysqld daemon! To make this a bit safer, all files
generated with SELECT ... INTO OUTFILE are readable to
everyone, and you can't overwrite existing files. The
file privilege may also be used to read any file
accessible to the Unix user that the server runs as.
This could be abused, for example, by using LOAD DATA to
load `/etc/passwd' into a table, which can then be read
If you don't trust your DNS, you should use IP numbers
instead of hostnames in the grant tables. In principle,
the --secure option to mysqld should make hostnames
safe. In any case, you should be very careful about
creating grant table entries using hostname values that
contain wild cards!
If you want to restrict the number of connections for a
single user, you can do this by setting the
max_user_connections variable in mysqld.
MySQL - Startup options to mysqld which concerns
The following mysqld options affect networking security:
IP numbers returned by the gethostbyname() system call
are checked to make sure they resolve back to the
original hostname. This makes it harder for someone on
the outside to get access by pretending to be another
host. This option also adds some sanity checks of
hostnames. The option is turned off by default in MySQL
Version 3.21 because sometimes it takes a long time to
perform backward resolutions. MySQL Version 3.22 caches
hostnames and has this option enabled by default.
This option causes the server not to use the privilege
system at all. This gives everyone full access to all
databases! (You can tell a running server to start using
the grant tables again by executing mysqladmin
flush-privileges or mysqladmin reload.)
Hostnames are not resolved. All Host column values in
the grant tables must be IP numbers or localhost.
Don't allow TCP/IP connections over the network. All
connections to mysqld must be made via Unix sockets.
This option is unsuitable for systems that use MIT-pthreads,
because the MIT-pthreads package doesn't support Unix
SHOW DATABASE command doesn't return anything.
SHOW DATABASE only returns databases for which the user
have some kind of privilege.
Page Numbers : 1