- 5.7 The MySQL Access Privilege System
- 5.7.1 What the Privilege System Does
- 5.7.2 How the Privilege System Works
- 5.7.3 Privileges Provided by MySQL
- 5.7.4 Connecting to the MySQL Server
- 5.7.5 Access Control, Stage 1: Connection Verification
- 5.7.6 Access Control, Stage 2: Request Verification
- 5.7.7 When Privilege Changes Take Effect
- 5.7.8 Causes of Access denied Errors
- 5.7.9 Password Hashing as of MySQL 4.1
MySQL client programs generally expect you to specify certain connection parameters when you want to access a MySQL server:
The name of the host where the MySQL server is running
Your username
Your password
For example, the mysql client can be started
as follows from a command-line prompt (indicated here by
shell>):
shell> mysql -h host_name -u user_name -pyour_pass
Alternative forms of the -h,
-u, and -p options are
--host=,
host_name--user=,
and
user_name--password=.
Note that there is no space between
your_pass-p or --password= and the
password following it.
If you use a -p or --password
option but do not specify the password value, the client program
prompts you to enter the password. The password is not displayed
as you enter it. This is more secure than giving the password on
the command line. Any user on your system may be able to see a
password specified on the command line by executing a command
such as ps auxw. See
Section 5.8.6, “Keeping Your Password Secure”.
MySQL client programs use default values for any connection parameter option that you do not specify:
The default hostname is
localhost.The default username is
ODBCon Windows and your Unix login name on Unix.No password is supplied if neither
-pnor--passwordis given.
Thus, for a Unix user with a login name of
joe, all of the following commands are
equivalent:
shell>mysql -h localhost -u joeshell>mysql -h localhostshell>mysql -u joeshell>mysql
Other MySQL clients behave similarly.
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 an option file. The relevant section of the file might look like this:[client] host=
host_nameuser=user_namepassword=your_passSection 4.3.2, “Using Option Files”, discusses option files further.
You can specify some connection parameters using environment variables. The host can be specified for mysql using
MYSQL_HOST. The MySQL username can be specified usingUSER(this is for Windows and NetWare only). The password can be specified usingMYSQL_PWD, although this is insecure; see Section 5.8.6, “Keeping Your Password Secure”. For a list of variables, see Section 2.14, “Environment Variables”.
User Comments
As root I ran the included mysql_secure_installation program this set the necessary permssions for me to have it require a password by default.
If you CAN connect to the databse from the local host, but you get a can't connect message from a remote host,
you meight want to check, if the "skip-networking" option in /etc/mysql/my.conf is enabled.
This is the default e.g. for DEBIAN WOODY and prevents all remote connections.
To force the mysql client program to ask for a password all times add in some configuration file (my.cfg for example) in the section "[mysql]" the option: "password"
In Debian 3.1 (sarge) the access from a remote host is disabled by default using:
bind-address = 127.0.0.1
If the the access from a remote host is needed you should comment this line and also:
skip-networking
To restrict the access to port 3306 you may use in /etc/hosts.deny:
mysql: ALL
and in /etc/hosts.allow:
mysql: IP_ADDRESS_ALLOWED_1 IP_ADDRESS_ALLOWED_2
If you have different users and you don't want to type their profile on the command line every time, you can write their profile in a [clientxxx] part of the my.cnf file.
Here is an example:
[client]
# global options for every user:
password
port = 3306
socket = /tmp/mysql.sock
[clientcouple]
# specific options for the user couple
user = couple
database = CajaParis
[clientadmin]
# specific options for the user admin
user = admin
database = Accounts
Then, to launch the mysql client with one of these profiles,
you can do:
shell> mysql --defaults-group-suffix=admin
To be quicker, I have written a script bash mysqlu:
#!/bin/sh
[ -z $1 ] && echo "*** Usage: mysqlu <suffixe>" && exit 1
mysql --defaults-group-suffix=$1
exit 0
Then you just have to put this script in your PATH and
type:
shell> mysqlu admin
Add your own comment.