MySQL
  Home arrow MySQL arrow Page 4 - MySQL Administration
Dev Shed Forums 
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Sun Developer Network 
Dedicated Servers 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Moblin 
JMSL Numerical Library 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

MySQL Administration
By: W.J. Gilmore
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 17
    1999-03-30

    Table of Contents:
  • MySQL Administration
  • The Privilege System
  • A Real-Life example
  • More Basics

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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:
    HostUser
    %root
    %dario
    localhostroot
    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.

    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

     

       

    MYSQL ARTICLES

    - Take Some Load off MySQL with MemCached
    - MySQL Table Prefix Changer Tool in PHP
    - Using the SIGNAL Statement for Error Handling
    - Error Handling Examples
    - Error Handling
    - Completing a Search Engine with MySQL and PH...
    - Paginating Result Sets for a Search Engine B...
    - Building a Search Engine with MySQL and PHP 5
    - Using Boolean Operators for Full Text and Bo...
    - PHP, MySQL and the PEAR Database
    - Working with PHP and MySQL
    - Getting PHP to Talk to MySQL
    - Creating an RSS Reader: the Reader
    - MySQL Security Overview
    - Creating the Admin Script for a PHP/MySQL Bl...





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway