Technical Interview Questions
Oracle Interview Questions
MsSql Interview Questions
My SQL Interview Questions and Answers
MySQL - What the Privilege System Does
The primary function of the MySQL privilege system is to
authenticate a user connecting from a given host, and to
associate that user with privileges on a database such
as select, insert, update and delete.
Additional functionality includes the ability to have an
anonymous user and to grant privileges for
MySQL-specific functions such as LOAD DATA INFILE and
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.
Note that even if the password is
stored 'scrambled', and knowing your 'scrambled'
password is enough to be able to connect to the MySQL
Connecting to the MySQL Server
MySQL client programs generally require that you specify
connection parameters when you want to access a MySQL
server: the host you want to connect to, your user name,
and your password. For example, the mysql client can be
started like this (optional arguments are enclosed
between `[' and `]'):
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]
Alternate forms of the -h, -u, and -p options are
--host=host_name, --user=user_name, and --password=your_pass.
Note that there is no space between -p or --password=
and the password following it.
NOTE: Specifying a password on the command line is not
secure! Any user on your system may then find out your
password by typing a command like: ps auxww.
mysql uses default values for connection parameters that
are missing from the command line:
The default hostname is localhost.
The default user name is your Unix login name.
No password is supplied if -p is missing.
Thus, for a Unix user joe, the following commands are
shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
Other MySQL clients behave similarly.
On Unix systems, you can specify different default
values to be used when you make a connection, so that
you need not enter them on the command line each time
you invoke a client program. This can be done in a
couple of ways:
You can specify connection parameters in the [client]
section of the `.my.cnf' configuration file in your home
directory. The relevant section of the file might look
You can specify connection parameters using environment
variables. The host can be specified for mysql using
MYSQL_HOST. The MySQL user name can be specified using
USER (this is for Windows only). The password can be
specified using MYSQL_PWD (but this is insecure; see the
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
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:
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.
Page Numbers : 1