MySQL Administration - More Basics (
Page 4 of 4 )
Correct ways to enter
database:To test your new permission setup, try to enter the database
using the following commands:
$ mysql -u user database
(Assuming a password was not established.) Note: Do not
establish an account without designating a password, unless you have a
very good reason for doing so.
ex.
Assuming the username is
'dario', and database 'pasta' (Note: this would only work from the localhost, as
we have designated pasta to only be reachable via localhost):
$ mysql -u dario pasta
or, if a password is set:
$ mysql -u user -p password database
ex.
Assuming the username is 'dario', password
'mamamia', and database 'chicken' (Note: this would only work from both
localhost and dv1, as we have designated chicken to be accessible from
both):
$ mysql -u dario -p chicken
enter password: mamamia
If you have correctly configured the tables, you will see the
following message (more or less):
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5602 to server version: 3.21.23-beta-log
Type 'help' for help.
mysql>
Assuming you have learned tons from this article ;) , and
everything went smooth, great. If not, check out the following section,
troubleshooting the privilege
tables.
Troubleshooting:Troubleshooting #1:If:
$ mysql -u username test
works, but:
$ mysql -h your_hostname -u username test
returns the famous 'Access denied' message, then the wrong
hostname is entered in the 'user' table.
[see notes below for
clarification by Monty]If 'mysql -u username -h hostname
databasename' give the error:
Access to database denied
Then this means that you don't have a row with 'user=username',
'host=hostname' and 'db=databasename' in the db table.
Remember that
just because your organization name may be 'devshed', the host name is
'devshed.com'! So don't type 'devshed' as the hostname and assume it will work.
The hostname must be entered as 'devshed.com'!
Troubleshooting
#2:
If you receive the 'Access denied' message after entering:
$ mysql -u user database
There is a problem with the user table. Check the data
entered in the user table, and you will most likely find the answer to your
problem.
[see notes below for clarification by Monty]If you
think that you have set up all privileges correct and they still doesn't work,
try doing 'mysqladmin reload' and try again. Troubleshooting
#3:If a client needs to use LOAD DATA INFILE or SELECT INTO OUTFILE, but
keeps getting the 'Access to database denied' command, then the File_priv
command isn't set to 'Y' within the 'User' table.
Troubleshooting
#4:IF within the '
user' table, a '' or '%' has been inserted as
the host, the server will look to the '
host' table for the specific
privileges. All three privilege tables will sort by non-wild card hosts first,
and then by wild-card, choosing the
first row that fits the
bill.
Let's assume we have the following within the '
user'
table:
| Host |
User |
| % |
root |
| % |
dario |
| localhost |
root |
| localhost |
|
The table will be sorted as
follows:
localhost/root
localhost/any
any/dario
any/root
Thus,
which set of privileges will be used if Dario attempts to connect from the
localhost? The one with user as dario, right? Wrong. It will use the row with
localhost as host and 'any' as user. Thus, pay attention to which privileges are
being assigned to users.
[see notes below for clarification by
Monty]This description is slightly wrong. The host table is only
accessed if the db table has a host='' entry. host='%' will match all hosts!
Basic Security tips: (READ: IMPORTANT)
- Above all, take a moment to update the root privileges, and give it a
password! Someone managing to get on the localhost can enter simply by typing:
mysql -u root mysql, if a password is not
established.
- Assign passwords for every user.
- If the user doesn't have a specific need for it, don't assign permission to
use File_priv. This gives the user the ability to write files to the MySQL
server. In addition, don't give permission to the process_priv, unless there is
a good reason for doing so.
- Don't use '%' within the host names. It allows a user to enter that server
from any outside host. In addition, don't use wildcards (i.e. devshed%) It would
theoretically allow someone to use http://devshed.badcracker.com to aid them in
entering the server.
Hopefully, this article has at least helped
somewhat in providing new MySQL administrator's some insight into the privilege
tables. I guess the best advice I can give you, however, is to become an active
member of the MySQL mailing list, and read the documentation. These two
resources will greatly aid you in quickly taking control of your MySQL server.
If you have any further questions, feel free to email me.
