Home arrow MySQL arrow Page 4 - MySQL Administration

More Basics - MySQL

MySQL Administration correctly administering the MySQL server, including the manipulation the privilege system, and making intelligent decisions about the capabilities (permissions) given to users.

  1. MySQL Administration
  2. The Privilege System
  3. A Real-Life example
  4. More Basics
By: W.J. Gilmore
Rating: starstarstarstarstar / 21
March 30, 1999

print this article


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.

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

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 #1:

$ 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:

The table will be sorted as follows:


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
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: