More Basics - MySQL

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)

1. 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.
2. Assign passwords for every user.
3. 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.
4. 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.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus